PostgreSQL – Password Authentication Methods
There are several password-based authentication methods available. in PostgreSQL These methods operate in the same way but differ in how the users’ passwords are stored on the server and the way the password provided by a client is shipped across the connection. The methods are as follows:
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.
Crypt() may be a common and readily available Unix function to try, well, encryption. The crypt function accepts the following two arguments:
- The password to encrypt.
- 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:
- Password submitted.
- 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.
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.
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.
LDAP et al:
PostgreSQL also contains a set of authentication methods related to passwords:
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.
Depending on the environment’s needs, security and cryptography are tough. But with SCRAM, PostgreSQL uses recognized public standards and is now in a good position to adapt in the future.