A PostgreSQL server may be accessible from the Internet, in the sense that it may listen on a public IP address and a TCP port accepting connections from any origin.

With the rising popularity of the DBaaS (“Database As A Service”) model, database servers can be legitimately accessible from the Internet, but it can also be the result of an unintentional misconfiguration.

As a data point, shodan.io, a scanner service that monitors such things, finds currently more than 650,000 listening Postgres instances on the Internet, without prejudging how they’re protected by host-based access rules, strong passwords, and database-level grants.

Such an open configuration at the network level is opposed to the more traditional, secure one where database servers are at least protected by a firewall, or don’t even have a network interface connected to the Internet, or don’t listen on it if they have one.

One consequence of having an instance listening to connections from the Internet is that intrusion attempts on the default port 5432 may happen anytime, just like it happens for other services such as ssh, the mail system or popular web applications like Drupal, Wordpress or phpMyAdmin.

If you have a server on the Internet, you may put its IP address in the search field of shodan.io to see what it knows about it.

The purpose of this post is to put together a few thoughts on this topic, for people who already manage PostgreSQL instances accepting public connections, or plan to do that in the future, or on the contrary, want to make sure that their instances don’t do that.

Do not mistakenly open your instance to the Internet!

When asking “how to enable remote access to PostgreSQL?”, the typical answer is almost invariably to add some rules in pg_hba.conf and set in postgresql.conf:

listen_addresses = *

(replacing the default listen_addresses = localhost).

It does work indeed, by making all the network interfaces to listen, but not necessarily only those where these connections are expected. In the case that they should come only from a private or local network, it’s safer to set only the addresses of the corresponding interfaces, but that point is rarely made in public answers. For instance, if the server is addressed as 192.168.1.12 in the the local network, we could have instead:

listen_addresses = localhost, 192.168.1.12

Why use these addresses instead of * ? Or more generally: why hasn’t PostgreSQL listen_addresses = * by default, so that a remote host would connect right away, without requiring an administrator to modify the configuration?

MongoDB used to do that, and the scale of successful attacks against it somewhat illustrate why it’s not a great idea. In 2015, shodan was reporting that about 30,000 instances were accessible from anyone who cared to try, exposing 595 TB of data. At the end of 2016, the “Mongo Lock” ransomware started to impact a large fraction of these servers. The attack consisted of deleting or encrypting data, replacing them with a demand for ransom in bitcoins. This episode has been a real setback for MongoDB’s reputation, even though strictly speaking it was never a security hole but rather just an insecure configuration.

The fact that installations were password-less by default played a big role in the attacks, but the scale of the problem would have been much less if the service has been listening only to its local network address, as postgres does by default, since it’s sufficient in the simple case when client and server-side are located on the same node.

Even though MongoDB has changed this default configuration a long time ago (in 2014, in fact way before the more dire attacks), it’s still being exploited currently, with incidents like the leak of 200 million chinese CVs just last month (January 2019). There are always installations out there that are left unmaintained, or kept running by people unaware of their exposure, and that they should change a configuration even though “it works”.

When an instance is purposefully open

Of course, database accounts must be protected by strong passwords, but that’s not sufficient.

A pre-requisite is to stay informed on security updates and ready to apply them urgently when necessary.

Such was the case in 2013, when the CVE-2013-1899 vulnerability was announced: it allowed anyone with network access to a PostgreSQL instance to compromise it, independently of the passwords and rules in pg_hba.conf. That was pretty bad, and a concrete example of why instances shouldn’t be exposed with listen_addresses = * unless really necessary. As far as can be seen in public news or user reports, it did not result in any campaign remotely comparable to what happened with MongoDB.

The CVE-2013-1899 vulnerability seems tested by uninvited probes still today, because when I’m looking to recent logs of my own instance open to the outside, I can see entries like these:

2019-01-31 05:51:44 CET FATAL:  no pg_hba.conf entry for host "185.x.x.x", 
user "postgres", database "template0", SSL on
2019-01-31 05:51:44 CET FATAL:  no pg_hba.conf entry for host "185.x.x.x", 
user "postgres", database "template0", SSL off
2019-01-31 05:51:44 CET FATAL:  unsupported frontend protocol 65363.19778: serve
r supports 1.0 to 3.0
2019-01-31 05:51:44 CET FATAL:  no pg_hba.conf entry for host "185.x.x.x", 
user "postgres", database "-h", SSL on
2019-01-31 05:51:44 CET FATAL:  no pg_hba.conf entry for host "185.x.x.x", 
user "postgres", database "-h", SSL off

-h as a database name is not choosen randomly, the above vulnerability being described as:

Argument injection vulnerability in PostgreSQL 9.2.x before 9.2.4, 9.1.x before 9.1.9, and 9.0.x before 9.0.13 allows remote attackers to cause a denial of service (file corruption), and allows remote authenticated users to modify configuration settings and execute arbitrary code, via a connection request using a database name that begins with a “-“ (hyphen)

That kind of intrusion attempt can come from a service like shodan, or from a botnet spreading malware, or even an attacker that targets specifically this machine, it’s hard to know.

The “Scarlett Johansson” cryptomining malware

There are some examples of successful attacks involving Postgres to make it mine Monero.

As far as can be judged from outside, these attacks don’t exploit any actual Postgres vulnerability, but succeed in connecting as a superuser probably because of weak passwords, or mistakes or laxism in pg_hba.conf, or by hacking first another app or service having a privileged database access.

For instance in this question on dba.stackexchange: Mysterious postgres process pegging CPU at 100%; no running queries, a user was asking why postgres was launching a ./Ac2p20853 command using up all the CPU. The most plausible explanation was that the postgres account or engine was compromised and this command was planted using postgres as a vector.

This other question on stackoverflow.com (CPU 100% usage caused by unknown postgres query) is similar, but in addition shows the queries launching the parasitic program:

pg_stat_activity:

pid   datname   username  query
19882 postgres  postgres  select Fun013301 ('./x3606027128 &')
19901 postgres  postgres  select Fun013301 ('./ps3597605779 &')

top:

PID   USER      PR  NI    VIRT    RES    SHR S %CPU %MEM   TIME+   COMMAND
19885 postgres  20   0  192684   3916   1420 S 98.3  0.1   5689:04 x3606027128

This behavior looks pretty much like the attack that Imperva detected with their honeypot instances, and dissected in: A Deep Dive into Database Attacks [Part III]: Why Scarlett Johansson’s Picture Got My Postgres Database to Start Mining Monero.

In short, once a superuser SQL session is obtained (by unspecified means), the malware creates a SQL function able to execute any program on disk. It creates such a program with lo_export() to download another program that does the actual cryto-mining. The program resided on a public image site, in this case hidden in a photo of Scarlett Johansson, hence the unlikely reference to her in this story.

Conclusion: superuser accounts should be limited to administration, and whenever possible limited also to local connections, through pg_hba.conf.

Forbid non-SSL remote connections

ssl=on in the server configuration implies that encryption is possible when the client initiates it, but not that it’s mandatory. Without encryption, an outsider with network access may read all data between client and server, or even compromise it in transit.

If we want to force encryption from the point of view of the server, this can be done through a set of rules in pg_hba.conf, such as in the sample below. Remember that rules are interpreted in order of appearance and that their evaluation stops as soon as a match is found, as if it was an IF…ELSIF…ELSIF…END IF cascade).

# allow for "Unix domain sockets" password-less local connexions
# when OS user=db user

local      all  all                 peer

# allow for non-SSL locally, but with a password

host       all  all  127.0.0.1/32   md5   # or scram with PG 10 or newer
host       all  all  ::1/128        md5

# reject non-encrypted remote connections

hostnossl  all  all  0.0.0.0/0     reject
hostnossl  all  all  ::/0          reject

# add other rules starting here
...
...

The most common client library, libpq, when compiled with SSL, tries by default an encrypted connection, followed by a non-encrypted connection if the former fails. This behavior corresponds to sslmode=prefer in the connection parameters (see SSL support in the documentation). That’s why in the logs, an unsuccessful attempt to connect appears as two entries, once with SSL=on and a second time with SSL=off.

Since version 9.5, it is possible to tell, among the established connections, which are encrypted and which are not, with the system view pg_stat_ssl:

SELECT datname,usename, ssl, client_addr 
  FROM pg_stat_ssl
  JOIN pg_stat_activity
    ON pg_stat_ssl.pid = pg_stat_activity.pid;

Short of disallowing unencrypted sessions with pg_hba.conf, this query makes it possible to check whether there are such connections and where they come from.