How to change Encryption type from ‘scram-sha-256’ to ‘md5’ in Postgres for password Authentication

Recently I encountered an issue in connecting to postgres client and the error looked like

Authentication method 10 not supported

The authentication type 10 is not supported

This is due to, in the recent versions ( mine is 14.3), Postgres changed the default user password encryption type to ‘Scram-sha-256’ from md5 , but some PostgreSQL clients don’t yet support scram-sha-256 yet and hence the connection fails and you will see one of the following or related errors:

Good news you can set the password_encryption_type for the user back to ‘md5’ using following commands but the user password needs to be changed and that is the Bad News!

To change the password encryption type, First we need to install rds_tools Extension. You need to install it as root user and postgres database

1. Login to postgres database with root user

psql -h $HOSTNAME -U root -d postgres

2. Install rds_tools extension

CREATE EXTENSION rds_tools;

3. Run the following command to check the encryption type for different users

select * from rds_tools.role_password_encryption_type();

Output should look something like this

postgres=> select * from rds_tools.role_password_encryption_type();

rolname | encryption_type

— — — + — — — — — —

root | scram-sha-256

user1 | scram-sha-256

4. Run the set encryption type command

postgres=> SET password_encryption = ‘md5’;

SET

5. You will need to Change the password of the user whose encryption is changed . That is the sad news, but there is no workaround.

ALTER USER user1 with password ‘user123’;

6. Run the query listed in 3) again to verify.

Hope this helps!