PostgreSQL - Password Authentication Methods

When managing a PostgreSQL database, securing user access is crucial. PostgreSQL offers several password-based authentication methods that vary in how user passwords are stored and transmitted across the connection. In this article, we’ll explore the different password-based authentication methods available in PostgreSQL.

The main password-based authentication methods in PostgreSQL include:

1. Password

The 'password' method sends a clear-text password and is therefore susceptible to "sniffing" attacks. If possible, it should be avoided. If the connection is protected by SSL encryption then passwords are often used safely. Though SSL certificate authentication could be a far better choice if one is counting on using SSL.

2. Crypt

Crypt() may be a common and readily available Unix function to try, well, encryption. The crypt function accepts the following two arguments:

1. The password to encrypt.
2. Salt to be used when encrypting.

In order to make PostgreSQL available for us, we always have to use the 'gen_salt' function. We use crypt again to authenticate a user, but this time we pass the following arguments:

1. Password submitted.
2. The encrypted password that we already have in the database.

If the password matches, crypt returns the same value as the one we already have in the database. It is good as long as crypt() is only used to encrypt local passwords, as was the original usage, but breaks when you try to communicate across various systems across a network.

3. Md5

The 'md5' approach employs a less reliable mechanism for the challenge-response. It prevents password sniffing and prevents passwords from being stored in plain text on the server, but does not provide protection if an attacker manages to steal a password hash from the server. Also, the MD5 hash algorithm is currently not considered safe against determined attacks. Also, md5 method can't be used with the db_user_namespace feature.

4. Scram

SCRAM is the only SASL mechanism currently in place. It is described in detail in RFC 7677 and RFC 5802 respectively.

When SCRAM-SHA-256 is used in PostgreSQL, the server ignores the username the client sends in the first-message client. Instead, the username which was already sent in the startup message is used. Multiple character encodings are provided by PostgreSQL, while SCRAM specifies that UTF-8 be used for the username, so it may not be possible to represent the PostgreSQL username in UTF-8.

The SCRAM specification dictates that the password is also in UTF-8 and is processed using the SASLprep algorithm. PostgreSQL, however, does not require the use of UTF-8 for a password. When a user's password is set, it is processed with SASLprep as if it was in UTF-8, no matter the particular encoding used. However, if it isn't a legal UTF-8 byte sequence or contains UTF-8 byte sequences that are prohibited by the SASLprep algorithm, the raw password is getting to be used without processing SASLprep instead of an error.

5. LDAP et al

PostgreSQL also contains a set of authentication methods related to passwords:

• LDAP (Lightweight Directory Access Protocol): Common in corporate environments, allowing centralized authentication.
• RADIUS (Remote Authentication Dial-In User Service): Often used in larger networked environments.
• PAM (Pluggable Authentication Modules): Offers a flexible way to integrate various authentication methods.
• BSD Authentication: Typically used on BSD operating systems.

With regard to the customer and the protocol, these are equivalent to the plain-text authentication method "password". The sole difference is that the server doesn't compare the password to what's stored in 'pg_authid' but the respective external service. This prevents the password from being stored in clear text in the database, but it still has all the other problems associated with this method. Using SSL for the PostgreSQL connection and configuring the LDAP server and connection securely eases many of these concerns, but it won't be as bullet-proof as SCRAM.

Conclusion

Choosing the right password-based authentication method in PostgreSQL depends on your environment’s security needs, performance requirements, and compatibility with existing systems. But with SCRAM, PostgreSQL uses recognized public standards and is now in a good position to adapt in the future.