How to secure your PostgreSQL database

Follow these steps to enable SSL connections and properly configure user privileges to keep your database secure

Another month, another database security disaster. It seems database security breaches are becoming more common as bad actors realize that databases contain lots of, well, data.

How does this keep happening? Usually, the answer is poorly executed database security protocols. How can we stop it from happening?

The good news is that it’s preventable. Databases do not need to be at risk, as long as you take the necessary precautions. Usually the answer is configuration. Don’t just use the out-of-box settings. Do use iptables to secure access. (As far as can be determined in the recent Exactis breach, had the setup made use of iptables or a similar feature, then the breach would have been prevented.)

In this article, we’ll look at steps you can take to secure PostgreSQL against intrusions and attacks. First we’ll look at implementing SSL connections and certificate-based authentication in PostgreSQL. Then we’ll examine how to create users and groups in PostgreSQL that provide the minimum appropriate level of database access.

As a tried-and-true open source database, PostgreSQL is used in many enterprise and start-up database deployments. There is certainly lots of data in PostgreSQL to protect.

Configure secure connections in PostgreSQL

PostgreSQL allows you to enable encryption of data over the wire through SSL. PostgreSQL uses SSL connections to encrypt the communication between client and server. In order to use SSL, you must have OpenSSL installed both on your client and server.

Steps to encrypt connections using SSL

To proceed further, you must create the server and client certification files that are signed by a Certification Authority.

1. Create a certificate signing request (CSR) and a key file.

2. Create a server certificate signed by the root certificate authority.

(Note that CN in the second line above should be the server name for server validation.)

3. Create a client certificate signed by the root certificate authority.

4. Copy the server certification file (server.crt), server private key (server.key), and root certification authority (rootCA.crt) to either the PostgreSQL data directory or a secured location accessible by PostgreSQL superuser (postgres). Once copied, allow read-only privileges to PostgreSQL users.

5. Set the appropriate parameters in PostgreSQL to enable SSL mode.

6. OpenSSL supports a wide range of ciphers. You will want to choose the cipher that suits your organizational standards. Once you have chosen the cipher you wish to use, set the following parameter in PostgreSQL.

Default cipher is: HIGH:MEDIUM:+3DES:!aNULL

7. You may either reload or restart PostgreSQL server to get these parameters into effect.

or

8. To enable encryption using SSL, you must modify host to hostssl in the pg_hba.conf file. For example, to enable SSL for local connections, replace host with hostssl for local connections.

9. You may add similar entries to enable encryption of connections from your remote applications or clients.

10. Once you have made the changes to pg_hba.conf file, you must perform a SIGHUP or reload.

11. Test your local connections for SSL. This is how it looks before and after enabling SSL for local connections.

Before

After

If you use hostssl for your remote application connections, the communication to the database server is automatically encrypted.

Steps to enable client certificate authentication

12. A client certificate can be used for user identity by the client or application server. It is especially useful for authentication. You can now copy the client certificates generated in Step 3 (above) to your remote application server. Once copied, set appropriate privileges. For example, the IP of my application server for this test is 192.168.0.13.

Once copied to the application server, give read-only privileges to the appropriate OS user in the application server.

13. In order to use client certificate authentication, add the following entry to the pg_hba.conf of your remote PostgreSQL server.

Here, 192.168.0.13 is the application server’s IP address. You may wish to have multiple entries this way.

Observe the difference between Step 9 and Step 13. The pg_hba.conf entry in Step 9 would enable communication over encryption, with connections encrypted automatically. The pg_hba.conf entry in Step 13 goes further. It would also force the client certification authentication to ensure that the communication is happening between trusted parties.

Give a SIGHUP or reload to put the changes to pg_hba.conf into effect.

14. Validate the remote connection using the psql client.

You should see the connections happening over SSL.

Create secure user and group roles in PostgreSQL

PostgreSQL allows you to enable encryption of data over the wire through SSL. However, it is also important to understand how security hardening and user management works in PostgreSQL. A user in PostgreSQL must have appropriate privileges that don’t give too much access. The steps below should help you understand how PostgreSQL implements user management. They also present some best practices.

What is a PostgreSQL user?

A PostgreSQL user is a role that has CONNECT privilege. Both CREATE USER and CREATE ROLE work well to create a PostgreSQL user. However, the user must have a LOGIN role. The login role is assigned to a user when you use any of the following three approaches:

or

or

When you use CREATE USER with any of the above, PostgreSQL automatically modifies the syntax internally with the following one:

In the above syntax automatically modified by PostgreSQL, we see that it is not granting the ROLES that are needed to manage PostgreSQL.

After creating the user or role you may use the following query to see if the user can login (has the CONNECT privilege):

This returns a boolean that tells whether the user can log in or not. For example, when you use the following syntax to create a ROLE you see that the role does not have CONNECT privilege.

Please note: Always avoid creating any users or roles with prefix pg_%.

Difference between CREATE USER and CREATE ROLE in PostgreSQL

When you create a user using CREATE USER, the user automatically gets a LOGIN role:

However, when you create a role you must explicitly add the LOGIN role to allow the user to log in:

What is a group or a group role in PostgreSQL?

In PostgreSQL, a role can INHERIT another role. This means one role can get access using the privileges of another role. This can be achieved using GRANT or INHERIT keywords.

It is always recommended to provide access privileges to users by using GROUP ROLES.

For example, consider an organization with:

• 1 database (percona)
• 2 schemas (scottand tiger)

Let’s say we then need to create 10 individual users and 10 application users observing the following requirements:

• Five of the application users must have read-only access to the tables of the schema scott and read-write access to tables of schema tiger.
• The other five application users must have read-write access to the tables of schema scott and read-only access to tables of schema tiger.
• All 10 individual user accounts can have only read-only access to the tables of schemas scott and tiger.

In this situation, you can create four group roles like the following:

scott_readonly:

scott_readwrite:

tiger_readonly:

tiger_readwrite:

Now, you can GRANT these group roles to the users based on the above three requirements.