PostgreSQL Security: 12 rules for database hardening

UPDATED August 2023: Hardening PostgreSQL has become ever more important. Security is king these days and people want to know how to make PostgreSQL safe. Some of us might still remember what happened to MongoDB in recent years and we certainly want to avoid similar security problems in the PostgreSQL world. What happened to MongoDB is actually stunning: Thousands of databases were held ransom because of poor default security settings - it was an absolute nightmare and greatly damaged the reputation of not just MongoDB but the entire industry. PostgreSQL people do everything they can to avoid a repeat performance of that in our ecosystem.

The hashtag #ransomware is not what you want to see when somebody is talking about your company. To help you avoid the most common pitfalls, we have compiled a “best of PostgreSQL security problems” list:

1. Avoid relaxed listen_addresses settings

PostgreSQL is running as a server process and people want to connect to the database. The question is: Where are those connections coming from? The listen_addresses setting which can be found in postgresql.conf controls those bind addresses.

In other words: If listen_addresses is set to ‘*’, PostgreSQL will listen on all network devices, consider those connections and move on to the next stage, which evaluates the content of pg_hba.conf.

Listening on all devices is a problem, because a bad actor could easily spam you with authentication requests - disaster is then just one pg_hba.conf entry away.

Recommendation to avoid listening on all devices:

• In case only local connections are needed:
  • Set listen_addresses = ‘localhost’
• In case remote connections are needed:
  • Set listen_addresses = ‘localhost, ’

If you don’t listen at all, you are definitely more secure. PostgreSQL will not even have to reject your connection if you are already limiting network access.

2. Using “trust” in pg_hba.conf

After dealing with listen_addresses (= bind addresses), PostgreSQL is going to process pg_hba.conf to figure out if a connection is actually allowed or not. The main question which arises is: What can possibly go wrong when talking about pg_hba.conf? Well, there are some things which are worth mentioning.

What we frequently see is that people use “trust” to ensure that people can connect to PostgreSQL without a password. Using trust is basically the worst thing you can do if you are working on hardening PostgreSQL. For local connections, “peer” might be a valid choice - trust is certainly not.

Recommendation concerning "trust" in PostgreSQL:

• Do not use trust (especially not for remote connections) if you are hardening PostgreSQL.
• Use scram-sha-256 and SSL instead
• Avoid entries that allow connections from everywhere (netmask 0.0.0.0/0) if possible. Instead, restrict access to those hosts and subnets that really need access.
• Don't create pg_hba.conf entries that allow connections to all databases or for all users. Be specific.
• Add documentation to every line in the file, so that you know why that access is needed, who requested it and whom to contact in case of need. Otherwise, you have little chance to clean up and remove entries that are no longer needed. Moreover, if a password needs to be changed, you know whom to contact.

3. Getting rid of md5 passwords in PostgreSQL

For many years md5 was the method of choice to do password authentication in PostgreSQL. However, the days of md5 are long gone. You can even download ready-made files containing the most frequently used hashes from the internet, and crack passwords even faster.

In other words: Forget about md5 and move to stronger hashes. If you want to find out how to migrate from md5 to scram-sha-256 make sure you check out our post “From MD5 to scram-sha-256 in PostgreSQL”.

Recommendation concerning md5 in PostgreSQL:

• Move md5 to scram-sha-256

4. Handle PUBLIC permissions on schemas and databases

In PostgreSQL there is a thing called PUBLIC. It is basically the database equivalent of “UNIX world”. As you will see, in PG versions prior to version 15, it may cause some issues - which can, however, be avoided. For the most recent information about schema PUBLIC since PostgreSQL version 15, see this blog.

Here’s what typically happens when you unknowingly use schema PUBLIC (prior to v15):

We have created a user and logged in as joe. What we see here is that joe is not allowed to create a new database, which is exactly what we expect. BUT: joe is allowed to connect to some other database we have not even heard of so far. Fortunately, joe is not allowed to read any objects in this database:

However, joe is allowed to create tables by default (public schema) which is certainly not a good idea:

Now, we have identified two key problems:

• joe is allowed to connect to other databases
• joe is allowed to spam the public schema

This is worse than it looks at first glance. Experience shows that most of the privilege escalation attack vectors discovered in PostgreSQL over the years work by creating malicious objects in a database.

So, here's how to fix it - revoke privileges on schema PUBLIC:

Now that we have done that, we can reconnect to the demo database as “joe”. In psql one can do that using the c command. If you are using a graphical user interface, change your database connection and log in as user joe:

First, we have revoked permission from PUBLIC to make sure that the database connection is not possible anymore. Then we have fixed permissions on the public schema. As you can see, the connection is no longer possible.

Recommendations concerning schema PUBLIC (PRIOR to PG v15):

• Remove PUBLIC from your database permissions
• Remove PUBLIC from your public-schema permissions

It may also be a good idea to revoke the TEMP privilege on databases from all users that do not need it. Some privilege escalation attacks work just as well with temporary objects.
Also, make sure that you properly test your setup to ensure that no leaks are left open.

Again, as of v15 schema PUBLIC has changed... see this blog for details.

5. Avoid ALTER USER … SET PASSWORD …

Changing a password in PostgreSQL is easy. Most people use ALTER USER … SET PASSWORD to do that. However, there is a problem: This SQL statement ends up in your database log in PLAIN text which is of course a major issue.

Recommendation:

• Use your trusted PostgreSQL client's facilities for changing passwords rather than ALTER ROLE. For example, psql has password and pgAdmin has a "change password" dialog.

This recommendation may seem absurd. However, there is some reasoning behind it. To change the password, there is protocol support which bypasses the problem of plain text passwords in the log. By doing things visually - and not via plain SQL - most GUIs will fix the problem for you.

Hint: CYBERTEC PostgreSQL Enterprise Edition (PGEE) even prohibits ALTER USER … SET PASSWORD explicitly to avoid the risk passwords in the log stream.

6. Make use of ALTER DEFAULT PRIVILEGES

Suppose you are running an application. Your database contains 200 tables. Permissions are set perfectly for countless users. Let us assume that we want to update this application so DDLs are executed to make the change. But what if somebody makes a mistake? What if permissions are not set properly? Small problems will start accumulating.

The solution to this problem is ALTER DEFAULT PRIVILEGES:

The idea is to define default permissions long before objects are created. Whenever you create a database object, default privileges will automatically kick in and fix things for you. PostgreSQL will greatly simplify your hardening process in this case by automatically setting permissions on new objects.

7. Make use of SSL

SSL is one of the most important topics in the realm of PostgreSQL security. If you want to harden your PostgreSQL database, there is no way around SSL.

PostgreSQL provides various levels of SSL, and allows you to encrypt connections between client and server. In general, we recommend using at least TLS 1.2 to ensure a high enough level of security.

If you want to learn more about SSL, and figure out how to set it up, please check out our page about that.

8. Write SECURITY DEFINER functions safely

Stored procedures and server side functions in general can be a major security concern. There are two ways in PostgreSQL to execute a function:

• Execute a function as “who you are”
• Execute a function as the author of the code

By default, a function is executed as the current user. In other words: If you are currently user joe the function will run as joe. However, sometimes it can be useful to run code as the author of the function and thus with different security settings. That way, you can let a user with low privileges perform certain actions that need elevated privileges in a controlled fashion. The way to do that is to use the SECURITY DEFINER option when creating the function.

But powerful tools are also dangerous, so you have to define those functions carefully. Read our article about SECURITY DEFINER functions for details.

9. Avoid SQL injection in database functions

SQL injection is not only a problem on the application (client) side, it can affect procedural code in the database just as well. If you want to avoid SQL injection, we recommend you continue reading and learn more.
Consider this silly function:

Now, any attacker who has control over the argument supplied to the function can launch a denial-of-service attack:

Or they could find out how much money is on your account:

Use the usual security precautions:

• Use dynamic SQL only where necessary.
• Use string data types for parameters only where necessary.
• Always use format() to construct SQL query strings.

10. Restrict superuser access as much as possible

It is always good if the number of people who have administrative access to a security critical system is as limited as possible. How far you want to go here depends on your security needs:

• Harden the database machine, so that nobody except database administrators can get shell access to the machine. Whoever has shell access as a PostgreSQL operating system user has full control over the database.
• Use personalized superuser accounts for the administrators, so that you can quickly revoke access to an administrator when needed.
• Use superusers only where really required. For example, you don't need a superuser for replication connections, or to take a backup.
• Restrict access with superuser accounts in pg_hba.conf. Ideally, only local connections are allowed. If you don't want that, restrict access to the personal system of the administrator.
• For very high security requirements, make sure that nobody knows the superuser password, but place it in a safe that can only be opened by at least two people. Change the password after every use.

In general, using superusers is dangerous.

Recommendation:

• Never, ever, run applications as a superuser
• Limit superusers as much as possible

11. Update your PostgreSQL database on a regular basis

Finally, it is important to update PostgreSQL on a regular basis. Keep in mind that most minor releases updates (eg. 13.0 -> 13.2, etc.) come with security-related updates which are vital to reducing the attack surface of your system.
PostgreSQL security updates are available on a regular basis, and we recommend applying them as soon as possible.

12. Encrypting your entire server: PostgreSQL TDE

So far, you have learned how to encrypt the connection between client and server. However, sometimes it is necessary to encrypt the entire server including storage. PostgreSQL TDE does exactly that:

To find out more, check out our website about PostgreSQL TDE. We offer a fully encrypted stack to help you achieve maximum PostgreSQL security. TDE is available for free (Open Source).

Finally...

Materialized views are an important feature in most databases, including PostgreSQL. They can help to speed up large calculations - or at least to cache them.

If you want to make sure that your materialized views are up to date and if you want to read more about PostgreSQL right now, check out our blog about pg_timetable which shows you how to schedule jobs in PostgreSQL. Why is pg_timetable so useful? Our scheduler makes sure that identical jobs cannot overlap, but simply don’t execute again, in case the same job is already running. When you run long jobs, this is super important - especially if you want to use materialized views.
 

---

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.