How can I solve Postgresql SCRAM authentifcation problem?

9
votes

I am getting an error after moving the project to production. The error is as follows while running with production server

pg_connect(): Unable to connect to PostgreSQL server: SCRAM authentication requires libpq version 10 or above.

Here is my postgreSQL version:

Development Version :

PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

Production Version :

PostgreSQL 11.5 (EnterpriseDB Advanced Server 11.5.12) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

3
databasepostgresqlauthenticationlibpq
You probably have an old libpq on your client computer - a_horse_with_no_name
@ a_horse_with_no_name is there duplicate libpq file? - Alimin
How should we know? The "installed" version refers to the server, not to the client (where your program runs) - a_horse_with_no_name
I am already check both version between development postgre version and production server by running select version() the result I post in my question.Please read my question carefully you will get both version installed - Alimin
You need to check the version on the client (where you run your code), not the server - a_horse_with_no_name

3 Answers

8
votes

Your application uses an API that is linked with the PostgreSQL client C library libpq.

The version of that library must be 9.6 or older, and SCRAM authentication was introduced in v10.

Upgrade libpq on the application end and try again.

If you don't need scram-sha-256 authentication, you can revert to md5:

  • set password_encryption = md5 in postgresql.conf
  • change the authentication method to md5 in pg_hba_conf
  • reload PostgreSQL
  • change the password of the user to get an MD5 encrypted password
3
votes

I used to get an error SCRAM authentication requires libpq version 10 or above when running php artisan migrate in laravel 8. Then I fixed it as follows: Change authentication from scram-sha-256 to md5, then reset your password and restart the postgresql-x64-13 service and here are step by step:

  1. Step 1: Find file postgresql.conf in C:\Program Files\PostgreSQL\13\data then set password_encryption = md5
  2. Step 2: Find file pg_hba.conf in C:\Program Files\PostgreSQL\13\data then change all METHOD to md5
  3. Step 3: Open command line (cmd,cmder,git bash...) and run psql -U postgres then enter your password when installed postgres sql
  4. Step 4: Then change your password by run ALTER USER postgres WITH PASSWORD 'new-password' in command line
  5. Final: Restart service postgresql-x64-13 in your Service.
2
votes

Encountered the same issue and applied @Laurenz Albe's fix but I would get an authentication error on my user for the database, because of encryption strategy change.

So instead of replacing scram-sha-256 with md5, replace it with trust in pg_hba.conf