PostgreSQL downgrade password encryption from SCRAM to md5

Question

I need to downgrade password encryption for user postgres from scram-sha-265 to md5.

I've tried modifying pg_hba.conf and postgresql.conf files changing password encryption from scram-sha-256 to md5 but after that I was unable to connect to the database.

I'm using PostgreSQL 13 and PgAdmin 4 v5.

Thanks for any help and suggestion!

PS: I have to do this because RStudio can't manage connections with scram authentication.

Answer 1

I solved following these steps:

Change password_encryption to md5 in file postgresql.conf

Change the first 3 occurrences of scram-sha-256 to trust in file pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Restart postgresql service

Execute psql -U postgres (you won't be asked for password)

Change password with command \password username

Change the first 3 occurrences of trust to md5 in file pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Restart postgresql service

Answer 2

You need to reload the database, then set the user's password again (probably using a superuser account), so that the user has an MD5-hashed password again. Connect to the database as superuser with psql, then:

SELECT pg_reload_conf();

-- to verify the settings are like you want:

SHOW password_encryption;
SELECT * FROM pg_hba_file_rules();

-- change the password

\password myuser