How can I solve Postgresql SCRAM authentication problem?

For those on M1-Based macs who are currently seeing this issue in docker - there appears to a bug upstream in libpg that's building against the wrong library version on ARM.

Until it's fixed, a workaround is to (at a performance hit) is to just run it via rosetta.

export DOCKER_DEFAULT_PLATFORM=linux/amd64, and re-build your images.

You'll get the latest version of libpq, and things should Just Work.

Ref: https://github.com/psycopg/psycopg2/issues/1360

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

I ran into this while running a python:3.9 docker image where I had installed psycopg2-binary==2.9.3. Installing psycopg2==2.9.3 instead resolved it for me.

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.

For Amazone linux users:

$ sudo yum install -y amazon-linux-extras

then re install the postgres client again

$ sudo amazon-linux-extras install postgresql10

Then the main part install the python package in my case it was (psycopg2) reinstall it

$ pip3 install --force-reinstall psycopg2==2.93

specification: python3.8.9

If you want to keep the scram-sha-256 for security. You need to update your client postgreSQL libraries, due to php-pgsql default version does not support it.

CentOS/Rhel/Rocky

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql13

This will update the server/client libpq in order to keep using scram-sha-256

I find 2 solutions. I didn't try local, solutions are for docker container.

Preliminary information: According to the main image we defined at the beginning of the Dockerfile, our libq versions may differ. It is explained with examples in the answer given here.

If you want to know your libq version used in your docker-image, you can do the following, respectively.

• After docker-compose run

  docker-compose exec <app_name_in_docker-compose_file> sh

  python

  import psycopg2

  print(psycopg2.extensions.libpq_version())

If you're getting an error, you'll probably see a number like 90xxx here.

Solutions:

• First basic one. I used 'python:3.9-slim-buster' at docker and I've got error. Change base image name to 'python:3.9.6-alpine3.14'
• The second solution is to build the psycopg2 file and install libq and other dependencies. For this, add the following commands to the dockerfile.

RUN apt update -y && apt install -y build-essential libpq-dev

RUN pip3 install psycopg2-binary --no-binary psycopg2-binary

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

Had this issue and fixed it by switching from the binary version of psycopg2 to psycopg2 = "^2.9.3"

All the answers that are suggesting that password encryption should be reverted back to md5 are forgetting that PostgreSQL changed its default password encryption from md5 to scram-sha-256 for security reasons. The issue then is those client applications that fail to use this modern encryption method. The libpq file that ships with PostgreSQL version 10 and above is fine. Just change your client applications if you can so that you use only the ones that support this new method.

For instance, For a long time I was using a package called RPostgreSQL that allows users to connect to a PostgreSQL database from R. Turns out that package doesn't support scram-sha-256. I have now replaced it with its modern equivalent called rpostgres. Problem solved, everybody happy now!

This issue still affects m1 macs running python:3.10.* docker containers (based off aarch64 Debian 11). Solution for me was to install psycopg2 (build from source) instead of psycopg2-binary which is advised for production anyways:

pip install psycopg2

The python container has all the build dependencies already (like gcc). A more production appropriate container most likely won't have these https://www.psycopg.org/docs/install.html#build-prerequisites

Here is a more mundane answer, but this is what happened to me so I'll add it here.

I got this error when I tried to start PostgreSQL 9.4. I realized I already had PostgreSQL 11 running - it started automatically with my computer. Once I stopped version 11, I was able to start version 9.4. They can't listen on a port already in use, and both had the same port set. But I'm not sure exactly why the misleading error wording.

RUN apt-get -y update \
    && apt-get install -y build-essential gettext libpq-dev\
    && apt-get install -y wkhtmltopdf\
    && apt-get install -y gdal-bin\
    && apt-get install -y libgdal-dev\
    && apt-get install -y --no-install-recommends software-properties-common\
    && apt-add-repository contrib\
    && apt-get update

Try this.