Continuing from my first blog we will check some more access and authentication configurations. Focusing on removing unnecessary database privileges. After that we will configure the backend parameters correctly to have more robust PostgreSQL server/client sessions. And at last SSL encryption for these sessions will be configured.

User access and authentication

In my previous blog we already setup the pg_wheel group and configured the users to login as the postgres user using sudo to authenticate themselves. This allows better management of who is allowed to login as the postgres user and also adds a necessary authentication.

In the database only the superuser should have elevated privileges. Meaning the other users should not be allowed to create new databases, roles, replications or other privileged actions. Only the most necessary privileges should be on each regular user. This reduces the risk of unintended changes of the database as well as the likeliness of harm done by a malicious user.

Login to the postgres user and list which user has what privileges:

psql -c "du"

This will return an output similar to this:

Role name List of roles Attributes Member of
postgres Superuser, Create role, Create DB, Replication, Bypass RLS {}

There might be more users listed. To make sure they do not have too many privileges you should remove the unnecessary ones with the following commands. The appuser is a placeholder and should be replaced by the actual users name.

psql -c "ALTER ROLE appuser NOSUPERUSER;"
psql -c "ALTER ROLE appuser NOCREATEROLE;"
psql -c "ALTER ROLE appuser NOCREATEDB;"
psql -c "ALTER ROLE appuser NOREPLICATION;"
psql -c "ALTER ROLE appuser NOBYPASSRLS;"

 

Checking runtime parameters

These settings define how the server functions and how well it performs. We will look at settings that could be compromised and exploited and thus making your database vulnerable.
The PostgreSQL Server creates a new backend process for each client to deal with. This could be used to cause an DOS (denial of service) attack by having slow queries. Also the determination of the cause can be compromised by the backend parameters.
To see which backend parameters are set, issue the query below:

SELECT name, setting FROM pg_settings WHERE context IN('backend','superuser-backend') ORDER BY 1;

The output should be like this:

Name setting
ignore_system_indexes off
jit_debugging_support off
jit_profiling_support off
log_connections on
log_disconnections on
post_auth_delay 0

To update these settings you will have to change the postgresql.conf file and then restart the server.
The ignore_system_indexes, jit_debugging_support and jit_profiling_support are not in the postgresql.conf file, since they should not be set to on in a production database.
Logging settings can be found under “REPORTING AND LOGGING”.

The post_auth_delay has to be added via shared_preload_libaries. Add the following to your postgresql.conf under “CLIENT CONNECTION DEFAULTS”:
shared_preload_libraries = ‘auth_delay’
custom_variable_classes = ‘auth_delay’
auth_delay.milliseconds = ‘0’
The auth_delay can be higher than 0 to reduce the speed of brute force attacks.

For encrypt TCP traffic the SSL should be set to on. Verify the setting by executing the sql query:

show ssl;

If the setting is off you should check if the default is set correctly:

SELECT name, setting, source FROM pg_settings WHERE name = 'ssl';

If this is also off SSL is not configured. You should get a certificate from a CA for you production databases. For test purposes you can use a self-signed certificate:

openssl req -new -text -out server.req

After filling the required fields continue with:

openssl rsa -in privkey.pem -out server.key && rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out server.crt
cp server.key server.crt $PGDATA
chmod og-rwx server.key

To enable SSL edit the postgresql.conf under the section “CONNECTIONS AND AUTHENTICATION”.
ssl = on
ssl ciphers = ‘HIGH:MEDIUM:+3DES:!aNULL’
ssl_cert_file = ‘server.crt’
ssl_key_file = ‘server.key’
password_encryption = scram-sha-256
After that you will have to restart the server to apply the change.

Summary

In this blog we removed unnecessary privileges from the regular database users. Configured more performant and secure backend sessions. And added SSL encryption for our database messages.
If you want even more details about certain settings feel free to checkout the CIS PostgreSQL 12 Benchmark yourself.