Recently, a few PostgreSQL users reported that they got connection failures after switching to PostgreSQL 14.
“Why do I get the error FATAL: password authentication failed for a user in the new server?” has become one of the most intriguing questions.
At least in one case, it was a bit of a surprise that the application message was as follows:
FATAL: Connection to database failed: connection to server at “localhost” (::1), port 5432 failed: fe_sendauth: no password supplied
The reason for these errors is the defaults for password encryption are changed in new versions of PostgreSQL to SCRAM authentication. Even though the last one appears nothing directly related to SCRAM, oh yes, some post-installation script failed which was looking for “md5”.
SCRAM authentication is not something new in PostgreSQL. It was there from PostgreSQL 10 onwards but never affected DBA life in general because that has never been the default. It was an opt-in feature by explicitly changing the default settings. Those who do an opt-in generally understand and do it intentionally, and it’s never been known to cause any problem. The PostgreSQL community was reluctant to make it a prime method for years because many of the client/application libraries were not ready for SCRAM authentication.
But that is changing in PostgreSQL 14. With PostgreSQL 9.6 going out of support, the landscape is changing. Now we expect all old client libraries to get upgraded and SCRAM authentication is becoming the prime password authentication method. But, those who are completely unaware are going to be greeted with a surprise one day or another. The purpose of this post is to create a quick awareness for those who are not yet, and address some of the commonly asked questions.
Percona and PostgreSQL work better together. Try Percona Distribution for PostgreSQL today.
What is SCRAM Authentication?
In simple words, the database client and the server prove and convince each other that they know the password without exchanging the password or the password hash. Yes, it is possible by doing a Salted Challenge and Responses, SCRAM-SHA-256, as specified by RFC 7677. This way of storing, communicating, and verifying passwords makes it very hard to break a password.
This method is more resistant to:
- Dictionary attacks
- Replay attacks
- Stollen hashes
Overall it becomes very hard to break a password-based authentication.
What Has Changed Over Time?
Channel Binding
Authentication is only one part of secured communication. After authentication, a rogue server in the middle can potentially take over and fool the client connection. PostgreSQL 11 introduced SCRAM-SHA-256-PLUS which supports the channel binding. This is to make sure that there is no rogue server acting as a real server OR doing a man-in-middle attack.
From PostgreSQL 13 onwards, a client can request and even insist on channel binding.
For example:
1 2 3 | psql -U postgres -h c76pri channel_binding=prefer or psql -U postgres -h c76pri channel_binding=require |
The channel binding works over SSL/TLS, so SSL/TLS configuration is mandatory to get the channel binding work.
Setting Password Encryption
The md5 was the only available option for password encryption before PostgreSQL 10, so PostgreSQL allows settings to indicate that “password encryption is required” which is defaulted to md5.
1 2 3 | –Upto PG 13 postgres=# set password_encryption TO ON; SET |
Due to the same reason, the above statement was effectively the same as:
1 2 | postgres=# set password_encryption TO MD5; SET |
We could even use “true”, “1”,”yes” instead of “on” as an equivalent value.
But now we have multiple encryption methods and “ON” doesn’t really convey what we really want. So from PostgreSQL 14 onwards, the system expects us to specify the encryption method.
1 2 | postgres=# set password_encryption TO 'scram-sha-256'; SET |
1 2 | postgres=# set password_encryption TO 'md5'; SET |
Any attempt to use “on”/”true”, ”yes” will be rejected with an error.
1 2 3 4 | –-From PG 14 postgres=# set password_encryption TO 'on'; ERROR: invalid value for parameter "password_encryption": "on" HINT: Available values: md5, scram-sha-256. |
So please check your scripts and make sure that they don’t have the old way of “enabling” encryption.
Some Frequently Asked Questions
- Does my logical backup and restore get affected?
Logical backup and restore of PostgreSQL globals (pg_dumpall) won’t affect the SCRAM authentication, the same password should work after the restore. In fact, it will be interesting to recollect that the SCRAM authentication is more resilient to changes. For example, if we rename a USER the old MD5 password won’t work anymore, because the way PostgreSQL generates the MD5 it uses the username also.
postgres=# ALTER USER jobin1 RENAME TO jobin;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
As the NOTICE indicates the password hash in the pg_authid will be cleared as the old one is no longer valid. But this won’t be the case with SCRAM authentication, as we can rename the users without affecting the password.
postgres=# ALTER USER jobin RENAME TO jobin1;
ALTER ROLE
- The existing/old method of encryption (md5) was a big vulnerability. Was there a big risk?
This worry mainly comes from the name “MD5” which is way too silly for modern hardware. The way PostgreSQL uses md5 is different is not just the hash of the password, but it considers the username also. Additionally, it is communicated over the wire after preparing a hash with a random salt provided by the server. Effectively what is communicated will be different from the password hash, so it is not too vulnerable. But prone to dictionary attacks and leaked username password hash problems. - Is the new scram authentication ads complex to authenticate? Is my connection request is going to take more time?
The wire protocol SCRAM is very efficient and not known to cause any degradation in connection time. Moreover, compared to other overheads of server-side connection management, the overhead created by SCRAM becomes very negligible - Is it mandatory to use SCRAM authentication from PostgreSQL 14 and force all my user accounts to switch to it?
Definitely not, only the defaults are changed. Old method md5 is still a valid method that works great, and if the access to the PostgreSQL environment is restricted by firewall/hba rules, there is already less risk in using md5. - Why do I get the “: FATAL: password authentication failed for user “ error when I switched to PostgreSQL 14?
The most probable reason is the pg_hba.conf entries. If we specify “md5” as the authentication method, PostgreSQL will allow SCRAM authentication also. But the reverse won’t work. When you created the PostgreSQL 14 environment, most probably it may have “scram-sha-256” as the authentication method. In some of the PostgreSQL packages, the installation script automatically does it for you 🙂 In case the authentication works from the PostgreSQL client tools and not from the application, please check the driver version check the scope for upgrade - Why do I get other types of authentication errors?
The most probable reasons are the post-installation scripts. It is a regular practice in many organizations to use DevOps tools (Ansible/Chef) or even shell scripts to do the post-installation customizations. Many of those will be doing a range of things that involves steps like set password_encryption TO ON; or even modification to pg_hba.conf using sed, which is expected to fail if it is trying to modify an entry that is not there anymore.
Why Should I Care and What To Do
Anything starting from automation/deployment scripts, tools, application connections, and connection poolers could potentially break. One of the major arguments for delaying this change till PostgreSQL 14 is that the oldest supported version (9.6) is going out of support soon. So this is the right time to inspect your environments to see if any of those environments have old PostgreSQL libraries (9.6 or older) and have a plan for the upgrade, as the old version PostgreSQL libraries cannot handle SCRAM negotiations.
In summary, having a good plan to migrate will help, even though it is not urgent.
- Inspect the environments and application drivers to see whether any of them are still using old versions of PostgreSQL client libraries and upgrade them wherever required.
Please refer to: https://wiki.postgresql.org/wiki/List_of_drivers
Encourage / Drive the upgrade of client libraries with a timeline - If the existing environment is using md5, encourage users to switch to SCRAM authentication.
Remember that the authentication method mentioned as “md5” in pg_hba.conf will continue to work for both SCRAM and MD5 authentication in PostgreSQL 14 also. - Take every opportunity to test and migrate automation, connection poolers, and other infrastructure to SCRAM authentication.
- Inspect the environments and application drivers to see whether any of them are still using old versions of PostgreSQL client libraries and upgrade them wherever required.
By changing the default authentication, the PostgreSQL community is showing a clear direction about the future.
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!
