Database security is paramount, and understanding what authentication methods are available and ideal for your database systems is crucial. Working with PostgreSQL, you will encounter the MD5 authentication method for PostgreSQL 10 and earlier versions. However, PostgreSQL introduced the SCRAM authentication for PostgreSQL 10 and newer versions to enhance the database security. Everything regarding SCRAM is discussed in this post. Read on!
PostgreSQL Authentication Method
PostgreSQL specifies the authentication method in the “pg_hba.conf” file, and when you open it, you will find the currently set password authentication method for different users and the database system. Ideally, PostgreSQL has three password authentication options.
1. Plain Text
It is the most unreliable password authentication option that you can use. Here, the password is sent in clear text, and anyone sniffing the network can successfully get hold of the password and use it to compromise the system. However, you can use the plain text password method if you enabled the SSL encryption for PostgreSQL.
2. MD5
Here, the password is saved in a hashed format and utilizes a challenge-response mechanism for authentication. Unlike the plain text option, MD5 guarantees that the password won’t be accessed as plain text, even if sniffed. However, this method is also unreliable as it can easily be attacked and compromised.
3. SCRAM
Salted Challenge Response Authentication Mechanism is the recent PostgreSQL password authentication method available for PostgreSQL 10 and newer versions. The authentication method offers additional benefits to guarantee the database security; more about it in the next section.
Understanding SCRAM Authentication in PostgreSQL
SCRAM is a reliable password authentication that is used in securing numerous databse systems. PostgreSQL adopted it in the PostgreSQL 10 version. It designed this authentication method to offer a better password-based authentication which is ideal for securing the databases.
SCRAM is an improvement of other password authentication methods including MD5. If you are running a newer PostgreSQL version, you will notice that SCRAM is the default authentication method
and displays what is in the following image:
SCRAM is implemented as a challenge-response scheme that guarantees that passwords don’t get sniffed by attackers. Let’s discuss the steps that SCRAM takes in authenticating the users.
Step 1: Whenever the clients connect to any PostgreSQL server, they get requested to authenticate themselves by sending their authentication credentials.
Step 2: The server sends the client an authentication request confirming that SCRAM is supported. The client must respond with their username, and a random value is generated.
Step 3: The generated random value (salt) gets checked against a password database. The server then responds by sending the user’s salt and a newly generated random value.
Step 4: Using the received salt and the random value, the client calculates the client proof, and the server calculates the server proof to confirm the client’s authenticity without requiring them to send their password like in the case of MD5.
Step 5: Once the proofs match to confirm the authenticity, the authentication goes through and the client can then access the database and perform their intended actions.
SCRAM authentication is defined in the RFC5802 standard, and PostgreSQL implements the scram-sha-256 confirming that it relies on the sha-256 hashing function.
How to Check and Change the Password Authentication
When you access PostgreSQL using the psql shell, you can quickly check the currently set password authentication. Start by executing the following command to log in to the psql shell:
Once the window appears, execute the next command to check the currently set password authentication method:
In our case, we have the SCRAM authentication method.
Suppose we had it as MD5. We could quickly change that by modifying the pg_hba.conf, execute the command below to set the new password authentication method.
You will get an output which confirms that the password authentication method has been successfully set
That’s how you set the SCRAM authentication in PostgreSQL.
Conclusion
This post discussed what SCRAM means in PostgreSQL. We discussed about the password authentication methods and SCRAM in detail, including how to set it as the default password authentication for your PostgreSQL.
