[SOLVED] PostgreSQL downgrade password encryption from SCRAM to md5

PostgreSQL downgrade password encryption from SCRAM to md5

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.

Solution

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