Don't let your PostgreSQL get exploited

As you may have heard, there are reportedly over 1,500 PostgreSQL servers that have been exploited to mine Bitcoin. And your server could be next if you haven't taken precautions. Firstly, you need to update to the latest minor release, just so no known exploitable bugs exist on your system. But regardless of whether you update, your PostgreSQL instance could still be misconfigured in a way that would allow unwelcome visitors access, so you need to make sure you shore up your defenses. Here are some steps you should take.

1. Get it off the internet

Your database shouldn't be accessible from the open internet. People run port scanners on public IP addresses all the time, and yours will eventually be found. Edit postgresql.conf and ensure listen_addresses isn't set to "*", because this tells PostgreSQL to accept connections from any network interface. Instead, make sure it only listens to connections from the local network.

For example:

listen_addresses = 'localhost,192.168.0.133'

2. Nail down authentication

Make sure you have no "trust" entries in pg_hba.conf. This allows users to access the database unchalleneged and unauthenticated. That's an absolute minimum. Really, you should have very restricted entries, ones that allow certain users to access certain databases from certain sources, requiring them to authenticate with a password or certificate, and over an SSL connection. And if using a password, not using MD5, but instead using SCRAM. This is because MD5 is considered weak nowadays and has become compromised. You can make sure all passwords are hashed using SCRAM by setting the following in postgresql.conf:

password_encryption = scram-sha-256

You can find all users that have an MD5 password with the following query:

SELECT usename
FROM pg_shadow
WHERE passwd LIKE 'md5%';

If this returns any users, it is strongly recommended a new password is set, which should correctly hash it with SCRAM if password_encryption is set correctly in postgresql.conf.

ALTER USER myuser WITH PASSWORD 'new password';

3. Regular users mustn't have special privileges

Check all users in PostgreSQL to ensure none of them have the SUPERUSER attribute, which gives them the power to do anything they want in the database. But while you're doing that, also check for CREATEDB (lets them create new databases), REPLICATION (allows them to replicate data to an external system) or BYPASSRLS (allows them to bypass row-level security) attributes. You can do that by running this query:

postgres=# SELECT usename, usecreatedb, usesuper, userepl, usebypassrls
FROM pg_user
WHERE usecreatedb OR usesuper OR userepl OR usebypassrls;

  usename  | usecreatedb | usesuper | userepl | usebypassrls 
-----------+-------------+----------+---------+--------------
 thom      | t           | t        | t       | t
 reporting | f           | t        | f       | f
(2 rows)

If they have one of the special privileges, and they aren't supposed to have it, you can revoke access by running the following (remove attributes that don't need revoking):

ALTER ROLE myuser
WITH NOCREATEDB NOSUPERUSER NOREPLICATION NOBYPASSRLS;

4. Most users shouldn't access external programs

In PostgreSQL, you can use COPY ... FROM PROGRAM to execute an external program and use the output of that as input into a table as data. However, it is this that is currently being exploited in the wild and allowing hackers to set up cryptocurrency mining. But you can stop it by making sure your roles don't have permission to do this. Users that are part of the pg_execute_server_program group have this permission. To identify who is part of this group, you can run the following:

postgres=# SELECT u.rolname AS execute_server_program_users
FROM pg_roles u
JOIN pg_auth_members m ON u.oid = m.member
JOIN pg_roles r ON m.roleid = r.oid
WHERE r.rolname = 'pg_execute_server_program';

 execute_server_program_users 
------------------------------
 audit
(1 row)

You can then revoke access from a user with:

REVOKE pg_execute_server_program FROM audit;

This user now cannot be used to surreptitiously mine digital currency on the system.

But, if you do have a user that needs to do this, consider putting it into a function or procedure, so you can at least control how it is used. You'll still need to sanitise any inputs to ensure only limited values are accepted. For example, a procedure can accept a file name as a parameter, and the function can check that it only contains a-z, A-Z, 0-9, "_" and ".".

Are you safe now?

These measures should be sufficient to stop your system being exploited via PostgreSQL. If the user hasn't got privileges to run COPY ... FROM PROGRAM, they can't run programs at all. If they don't have special privileges, they will be limited to their specific privileges. If they can't authenticate successfully, they can't get into your database. If they can't find you on the internet, they can't even try connecting. Just cut them off at every level, and they won't stand a chance.

Thom's memory leak