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
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
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
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
= * 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
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:
pid datname username query 19882 postgres postgres select Fun013301 ('./x3606027128 &') 19901 postgres postgres select Fun013301 ('./ps3597605779 &')
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
Conclusion: superuser accounts should be limited to administration,
and whenever possible limited also to local connections, through
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,
when compiled with SSL, tries by default an encrypted connection, followed by
a non-encrypted connection if the former fails. This behavior corresponds
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
Since version 9.5, it is possible to tell, among the established connections, which are encrypted and which are not, with the system view
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.