PostgreSQL Database Security: Authentication

Recently, I wrote an overview about what you need to know about PostgreSQL security. For this post, I want to write about PostgreSQL authentication. It is divided into three categories: 1. PostgreSQL Internal Authentication, 2. OS-based Authentication, and 3. External Server-Based Authentication.  In most cases, PostgreSQL is configured to be used with internal authentication, so here we will discuss each and every internal authentication method in detail. The next blog will cover the OS authentication methods such as PAM, Peer, and Ident.

The following is the list of PostgreSQL internal authentication supported methods.

• Trust
• Reject
• md5
• SCRAM
• Cert

PostgreSQL has a configuration file to configure authentication called pg_hba.conf. All the authentication-related settings are part of this configuration file. Here is the sample pg_hba.conf file:

The first column of the “pg_hbaa.conf” file is the “host”. It can be either local or host. The local is fixed for the Unix-Domain socket, and in the case of a host, you need to specify the host IP address in the address column. The second column is the database, which is used to specify the database name. You can set the authentication method based on databases, meaning your database can have its own authentication method. If these values are set to all, then all the databases will use the same authentication method. The third column of the file is the user, which means you can set separate authentication methods for different users and “all” means apply to all users. The fourth parameter is to specify the IP address, which means which IP address can use that authentication method. The next column is the auth-method which can be any of the authentication methods shown in Figure 1. The last column is auth-options, in case any authentication method has some options.

Trust and Reject

When you specify the authentication method Trust, then any user who fulfills the requirement will not require any password. Similarly, in the case of Reject, any user who fulfills the requirement will not be allowed to login into the system. Here is the example of Trust and Reject:

The pg_hba.conf file has two entries; the first one has authentication method trust and the second one has authentication method reject. A local host user no longer needs to have a password and will be granted permission to login into the system without the password. But any computer other than localhost will be rejected, because of the second line of the pg_hba.conf file.

Trust Authentication

Reject Authentication

In the case of md5 authentication, you need to provide the password. Let’s look at a simple example of that.

SCRAM

The SCRAM, or more specifically scram-sha-256, is a challenge-response scheme that prevents password sniffing on untrusted connections. It is one of the most secure authentication methods, using secure, cryptographically-hashed security, to store the passwords on the server.

Step 1: Change the password of the user

Step 2: Change the pg_hba.conf file.

Step 3: Test the connection

Percona Distribution for PostgreSQL is free to download and use. It is the best and most critical enterprise-level components from the open-source community, designed and tested to work together in one single source. 

Server Key and Certificate

Step 1: Generate Server keys

Step 2: Generate Server Certificate

Client Keys and Certificate

Step 3: Generate a client certificate

Step 4: Copy root.crt to the client.

PostgreSQL Settings

Step 5:  In postgrsql.conf file set ssl = on

Step 6: Restart PostgreSQL

Connection

Now, all set here, and you can test the connection using the psql command.

Conclusion

This is the second part of the security series blog, and in the first blog post, we see the main features of security we need to consider. In this blog, we started with authentication and only focused on the PostgreSQL authentication mechanism and still need to see how external authentication methods work in PostgreSQL. Stay tuned!

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!