6

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.

Daniele974
  • 123
  • 1
  • 1
  • 10
  • What error did you get? What did the log file say? – jjanes Apr 26 '21 at 11:19
  • The error is *Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (could not connect postgres@127.0.0.1:5432 on dbname "progetto_bd": SCRAM authentication requires libpq version 10 or above )* – Daniele974 Apr 26 '21 at 12:19
  • I'm pretty sure that the library version is above 10 – Daniele974 Apr 26 '21 at 12:20
  • But the error message is pretty sure it is not. I think we can go with the error message here. I have seen it a lot, and never known it to be wrong. – jjanes Apr 26 '21 at 15:04

2 Answers2

5

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

oxo
  • 946
  • 9
  • 21
Daniele974
  • 123
  • 1
  • 1
  • 10
3

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
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263