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:

2. Nail down authentication Make sure you have no "trust" entries inpg_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: You can find all users that have an MD5 password with the following query: 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.

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: 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):

4. Most users shouldn't access external programs In PostgreSQL, you can useCOPY ... 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: You can then revoke access from a user with: 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.