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.
Table of Contents
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:
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.
listen_addresses = ‘localhost’
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.
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.
trust
(especially not for remote connections) if you are hardening PostgreSQL.pg_hba.conf
entries that allow connections to all databases or for all users. Be specific.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”.
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.
1 2 3 4 5 6 7 8 9 |
postgres=# CREATE USER joe; CREATE ROLE postgres=# c postgres joe You are now connected to database 'postgres' as user 'joe'. postgres=> SELECT current_user; current_user -------------- joe (1 row) |
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:
1 2 3 4 5 6 |
postgres=> CREATE DATABASE joedb; ERROR: permission denied to create database postgres=> \c demo You are now connected to database "demo" as user "joe". demo=> SELECT * FROM t_demo; ERROR: permission denied for table t_demo |
However, joe
is allowed to create tables by default (public schema) which is certainly not a good idea:
1 2 |
demo=> CREATE TABLE bad_idea (id int); CREATE TABLE |
joe
is allowed to connect to other databasesjoe
is allowed to spam the public schemaThis 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.
1 2 3 4 |
demo=# REVOKE ALL ON DATABASE demo FROM public; REVOKE demo=# REVOKE ALL ON SCHEMA public FROM public; REVOKE |
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
:
1 2 3 4 |
demo=# \c demo joe FATAL: permission denied for database "demo" DETAIL: User does not have CONNECT privilege. Previous connection kept |
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.
PUBLIC
from your database permissionsPUBLIC
from your public-schema permissionsIt 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.
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:
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.
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.
ALTER DEFAULT PRIVILEGES
:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
ALTER DEFAULT PRIVILEGES Description: define default access privileges Syntax: ALTER DEFAULT PRIVILEGES [ FOR { ROLE | USER } target_role [, ...] ] [ IN SCHEMA schema_name [, ...] ] abbreviated_grant_or_revoke where abbreviated_grant_or_revoke is one of: GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON TABLES TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] … |
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.
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.
Stored procedures and server side functions in general can be a major security concern. There are two ways in PostgreSQL to execute a function:
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.
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:
1 2 3 4 5 6 7 8 9 10 11 |
CREATE FUNCTION tally(table_name text) RETURNS bigint LANGUAGE plpgsql AS $$ DECLARE result bigint; BEGIN EXECUTE 'SELECT count(*) FROM ' || table_name INTO result; RETURN result; END; $$; |
Now, any attacker who has control over the argument supplied to the function can launch a denial-of-service attack:
1 |
SELECT tally('generate_series(1, 100000000000000000000)'); |
Or they could find out how much money is on your account:
1 2 3 |
SELECT tally('generate_series(1, 1000000) UNION SELECT amount::bigint FROM account WHERE name = ''loser'''); |
Use the usual security precautions:
format()
to construct SQL query strings.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:
pg_hba.conf
. Ideally, only local connections are allowed. If you don't want that, restrict access to the personal system of the administrator.In general, using superusers is dangerous.
Recommendation:
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.
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).
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 Twitter, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply