sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) SCRAM authentication requires libpq version 10 or above
dagknows opened this issue · 45 comments
- OS: Docker running ubuntu 18.04
- Psycopg version: 2.8.2 (tried lates psycopg2-binary as well)
- Python version: 3.7.3
- PostgreSQL version: docker pull postgres:latest
- pip version: 19.1.1
1: what you did
I have an application with multiple containers, one of which, container-A makes sql requests to another postgres container.
My container-A Dockerfile has (among other things):
RUN pip install libpq5
RUN pip install psycopg2-binary
2: what you expected to happen
Not see the error. It used to work before.
3: what happened instead
Getting the error:
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) SCRAM authentication requires libpq version 10 or above
RUN pip install libpq5
RUN pip install psycopg2-binary
These two together are not needed. If you install psycopg2-binary it will ignore the system libpq.
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) SCRAM authentication requires libpq version 10 or above
According to the news file, psycopg 2.8 binary was packaged with Postgres 11.2 libpq.
Line 179 in 3430dcd
So I think you might have a stray psycopg2 version. I am sorry that the psycopg2 and psycopg2-binary version don't play well together.
To look into it better try to get more info about the package installed in your container:
>>> import psycopg2
# Checking where it is installed
>>> print(psycopg2)
<module 'psycopg2' from '/home/piro/dev/psycopg2/.venv/lib/python3.8/site-packages/psycopg2/__init__.py'>
# Checking the versions:
>>> print(psycopg2.__version__)
2.9.1 (dt dec pq3 ext lo64)
>>> print(psycopg2.__libpq_version__)
130003
>>> print(psycopg2.extensions.libpq_version())
130003If the problem is a conflicting package try to add a RUN pip uninstall psycopg2 before those commands. However I would advise to install the libpq-dev and python3-dev packages and install psycopg non-binary.
I also saw this error running a container on an M1 laptop. Seems to be related to this SO question.
I can run the ARM and x86 images on the machine via Docker Desktop, and this shows that the libpq versions differ
# x86
$ docker run --platform linux/amd64 -it python:3.8-slim bash -c 'pip install psycopg2-binary && find / -name "libpq*"'
…
Successfully installed psycopg2-binary-2.9.1
…
/usr/local/lib/python3.8/site-packages/psycopg2_binary.libs/libpq-6f24e430.so.5.13
# ARM
$ docker run --platform linux/arm64 -it python:3.8-slim bash -c 'pip install psycopg2-binary && find / -name "libpq*"'
…
Successfully installed psycopg2-binary-2.9.1
…
/usr/local/lib/python3.8/site-packages/psycopg2_binary.libs/libpq-c98caf99.so.5.9ARM gets 5.9 whilst x86 gets the newer 5.13. My guess is the usage of 5.9 is causing the problem reported in this issue.
Could this be a packaging problem? If the aarch64 wheel is being built with an old 5.9 library by mistake?
Edit: You can see the library version difference in the latest package build workflow:
- x86_64 log shows libpq version as
130003. - aarch64 log shows libpq version as
90623. This is less than version 10, and so explains the SCRAM error (as that's only available in >= 10).
Debugging further, I tried running the steps in the build script on the ARM container directly.
$ docker run --rm -it quay.io/pypa/manylinux_2_24_aarch64 sh
# curl -s https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
OK
# echo "deb http://apt.postgresql.org/pub/repos/apt stretch-pgdg main" > /etc/apt/sources.list.d/pgdg.list
# apt-get -y update
Get:1 http://apt.postgresql.org/pub/repos/apt stretch-pgdg InRelease [81.6 kB]
Ign:2 http://deb.debian.org/debian stretch InRelease
Get:3 http://security.debian.org/debian-security stretch/updates InRelease [53.0 kB]
Get:4 http://deb.debian.org/debian stretch-updates InRelease [93.6 kB]
Get:5 http://deb.debian.org/debian stretch Release [118 kB]
Get:6 http://security.debian.org/debian-security stretch/updates/main arm64 Packages [711 kB]
Get:7 http://deb.debian.org/debian stretch Release.gpg [3,177 B]
Get:8 http://deb.debian.org/debian stretch/main arm64 Packages [6,921 kB]
Fetched 7,981 kB in 2s (3,594 kB/s)
Reading package lists... Done
N: Skipping acquire of configured file 'main/binary-arm64/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt stretch-pgdg InRelease' doesn't support architecture 'arm64'
# apt-get install -y libpq-dev
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libpq5
Suggested packages:
postgresql-doc-9.6
The following NEW packages will be installed:
libpq-dev libpq5
0 upgraded, 2 newly installed, 0 to remove and 2 not upgraded.
Need to get 328 kB of archives.
After this operation, 1,566 kB of additional disk space will be used.
Get:1 http://security.debian.org/debian-security stretch/updates/main arm64 libpq5 arm64 9.6.23-0+deb9u1 [126 kB]
Get:2 http://security.debian.org/debian-security stretch/updates/main arm64 libpq-dev arm64 9.6.23-0+deb9u1 [202 kB]
Fetched 328 kB in 0s (1,125 kB/s)
debconf: delaying package configuration, since apt-utils is not installed
Selecting previously unselected package libpq5:arm64.
(Reading database ... 16730 files and directories currently installed.)
Preparing to unpack .../libpq5_9.6.23-0+deb9u1_arm64.deb ...
Unpacking libpq5:arm64 (9.6.23-0+deb9u1) ...
Selecting previously unselected package libpq-dev.
Preparing to unpack .../libpq-dev_9.6.23-0+deb9u1_arm64.deb ...
Unpacking libpq-dev (9.6.23-0+deb9u1) ...
Setting up libpq5:arm64 (9.6.23-0+deb9u1) ...
Processing triggers for libc-bin (2.24-11+deb9u4) ...
Setting up libpq-dev (9.6.23-0+deb9u1) ...
This doesn't look good:
N: Skipping acquire of configured file 'main/binary-arm64/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt stretch-pgdg InRelease' doesn't support architecture 'arm64'
You can see that libpq 9.6.23 gets installed.
From what I can tell the PostgreSQL arm64 builds don't support Stretch (Debian 9), which the manylinux containers are based off.
Will keep digging.
These are these differences between the two build scripts (build_manylinux2014 .sh is used for x86_64 and build_manylinux_2_24 .sh is used for arm64):
diff --git a/scripts/build/build_manylinux2014.sh b/scripts/build/build_manylinux_2_24.sh
index 0e87bd54..d83c8414 100755
--- a/scripts/build/build_manylinux2014.sh
+++ b/scripts/build/build_manylinux_2_24.sh
@@ -1,10 +1,6 @@
#!/bin/bash
-# Create manylinux2014 wheels for psycopg2
-#
-# manylinux2014 is built on CentOS 7, which packages an old version of the
-# libssl, (1.0, which has concurrency problems with the Python libssl). So we
-# need to build these libraries from source.
+# Create manylinux_2_24 wheels for psycopg2
#
# Look at the .github/workflows/packages.yml file for hints about how to use it.
@@ -30,8 +26,12 @@ if [[ "${PACKAGE_NAME:-}" ]]; then
"${prjdir}/setup.py"
fi
-# Build depending libraries
-"${dir}/build_libpq.sh" > /dev/null
+# Install prerequisite libraries
+curl -s https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
+echo "deb http://apt.postgresql.org/pub/repos/apt stretch-pgdg main" \
+ > /etc/apt/sources.list.d/pgdg.list
+apt-get -y update
+apt-get install -y libpq-dev
# Create the wheel packages
for pyver in $PYVERS; do
@@ -45,7 +45,7 @@ for whl in "${prjdir}"/dist/*.whl; do
done
# Make sure the libpq is not in the system
-for f in $(find /usr/local/lib -name libpq\*) ; do
+for f in $(find /usr/lib /usr/lib64 -name libpq\*) ; do
mkdir -pv "/libpqbak/$(dirname $f)"
mv -v "$f" "/libpqbak/$(dirname $f)"
doneTo avoid using the PostgreSQL APT repository I tried modifying build_libpq.sh; I figured we could build libpq from source on all architectures. With these changes the build script completed:
diff --git a/scripts/build/build_libpq.sh b/scripts/build/build_libpq.sh
index 32d2222d..c6483582 100755
--- a/scripts/build/build_libpq.sh
+++ b/scripts/build/build_libpq.sh
@@ -10,7 +10,8 @@ ldap_version="2.4.59"
sasl_version="2.1.27"
postgres_version="13.3"
-yum install -y zlib-devel krb5-devel pam-devel
+apt-get update -y
+apt-get install -y zlib1g-dev libkrb5-dev libpam-dev
# Build openssl if needed(It feels a little sketchy to me to download the Red Hat PG distribution on Debian, but maybe it's OK?)
To test the full packaging flow, spin up a DB:
$ docker run --rm -e POSTGRES_PASSWORD=password postgresThen run the container:
$ docker run --rm -v $(pwd):/src \
-e PLAT=manylinux_2_24_aarch64 \
-e PACKAGE_NAME=psycopg2-binary \
-e PYVERS="cp38-cp38" \
-e PSYCOPG2_TESTDB=postgres \
-e PSYCOPG2_TESTDB_HOST=172.17.0.3 \
-e PSYCOPG2_TESTDB_USER=postgres \
-e PSYCOPG2_TESTDB_PASSWORD=password \
-e PSYCOPG2_TEST_FAST=1 \
--workdir /src \
quay.io/pypa/manylinux_2_24_aarch64 \
./scripts/build/build_manylinux_2_24.sh
…
+ /opt/python/cp38-cp38/bin/python -c 'import psycopg2; print(psycopg2.__version__)'
2.9.1 (dt dec pq3 ext lo64)
+ /opt/python/cp38-cp38/bin/python -c 'import psycopg2; print(psycopg2.__libpq_version__)'
90623
+ /opt/python/cp38-cp38/bin/python -c 'import psycopg2; print(psycopg2.extensions.libpq_version())'
130003(Use docker inspect <postgres container ID> to get the DB's IP address to pass to PSYCOPG2_TESTDB_HOST .)
One of the versions is now correct but the other is still outdated.
I hit with the above error when I was using psycopg2 with the image FROM python:3.6-stretch. I simply installed psycopg2-binary instead of psycopg2 and got rid of this error!
FYI, I didn't install libpq5 (no need of it!)
I am also seeing this problem when installing psycopg2-binary on the official docker python:3.8 image (debian bullseye), for aarch64.
This gets installed:
/usr/local/lib/python3.8/site-packages/psycopg2_binary.libs/libpq-4fa63d26.so.5.9
Also tested with the pthon:3.9 image, same issue:
/usr/local/lib/python3.9/site-packages/psycopg2_binary.libs/libpq-4fa63d26.so.5.9
from pip list: psycopg2-binary 2.9.2
@smolyn what are the __version__ and the __libpq_version__
>>> import psycopg2
>>> psycopg2.__version__
'2.9.2 (dt dec pq3 ext lo64)'
>>> psycopg2.__libpq_version__
90623
@smolyn on aarch64 the PostgreSQL packages don't seem upgraded
https://github.com/psycopg/psycopg2/runs/4184035217?check_suite_focus=true#step:5:147
So you get version 9.6 there.
Is there a way to get upgraded packages in this script?
@dvarrazzo I tried updating that script but only managed to get one of the versions updated. Quoting:
$ docker run --rm -v $(pwd):/src \
-e PLAT=manylinux_2_24_aarch64 \
-e PACKAGE_NAME=psycopg2-binary \
-e PYVERS="cp38-cp38" \
-e PSYCOPG2_TESTDB=postgres \
-e PSYCOPG2_TESTDB_HOST=172.17.0.3 \
-e PSYCOPG2_TESTDB_USER=postgres \
-e PSYCOPG2_TESTDB_PASSWORD=password \
-e PSYCOPG2_TEST_FAST=1 \
--workdir /src \
quay.io/pypa/manylinux_2_24_aarch64 \
./scripts/build/build_manylinux_2_24.sh
…
+ /opt/python/cp38-cp38/bin/python -c 'import psycopg2; print(psycopg2.__version__)'
2.9.1 (dt dec pq3 ext lo64)
+ /opt/python/cp38-cp38/bin/python -c 'import psycopg2; print(psycopg2.__libpq_version__)'
90623
+ /opt/python/cp38-cp38/bin/python -c 'import psycopg2; print(psycopg2.extensions.libpq_version())'
130003
I wasn't able to understand why psycopg2.__libpq_version__ would differ from psycopg2.extensions.libpq_version().
The __libpq_version__ is eventually dictated by the pg_config found at compile time. Maybe the paths are wrong?
use psycopg3 to resolve this problem
I'm experiencing this issue as well. Is there a temporary workaround anyone has found short of emulating amd64?
Workarounds are discussed here:
This is how you can really fix it:
Debian Stretch
Here you have the problem that the Postgres Debian repo doesn't have ARM builds for libpq so you first have to build your own from source.
Once you have that, you can just copy the two packages from the first Docker build to your machine and you'll never have to do the libpq build again.
Versions might change depending on when you run this.
FROM debian:stretch AS libpqbuilder
RUN apt-get update && apt-get -y install curl ca-certificates gnupg dpkg-dev devscripts
RUN curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
RUN echo "deb-src http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main" >> /etc/apt/sources.list.d/pgdg.list
# get source packages
RUN cd ~ && apt-get update && apt-get source pgdg-keyring postgresql-common postgresql-14
# disable running the postgres test suite after building
ENV DEB_BUILD_OPTIONS=nocheck
# build deps for building postgres
RUN cd ~/pgdg-* && mk-build-deps -ir -t "apt-get -o Debug::pkgProblemResolver=yes -y --no-install-recommends"
RUN cd ~/pgdg-* && debuild -i -us -uc -b
RUN cd ~/postgresql-common-* && mk-build-deps -ir -t "apt-get -o Debug::pkgProblemResolver=yes -y --no-install-recommends"
RUN cd ~/postgresql-common-* && debuild -i -us -uc -b
# install deps
RUN apt install -y ~/pgdg-keyring_*.deb
RUN apt install -y ~/postgresql-client-common_*.deb
RUN apt install -y ~/postgresql-common_*.deb
# build postgres
RUN cd ~/postgresql-14-14.* && mk-build-deps -ir -t "apt-get -o Debug::pkgProblemResolver=yes -y --no-install-recommends"
RUN cd ~/postgresql-14-14.* && debuild -i -us -uc -b
RUN mkdir /out && mv ~/libpq-dev_*.deb /out/ && mv ~/libpq5_*.deb /out/
RUN ls -alh /out
# use it
FROM python:3.7-stretch
COPY --from=libpqbuilder /out/libpq-dev_14.3-1.pgdg90+1_arm64.deb /var/cache/apt/archives/
COPY --from=libpqbuilder /out/libpq5_14.3-1.pgdg90+1_arm64.deb /var/cache/apt/archives/
# install libpq
RUN dpkg -i /var/cache/apt/archives/libpq-dev_14.3-1.pgdg90+1_arm64.deb /var/cache/apt/archives/libpq5_14.3-1.pgdg90+1_arm64.deb
# build a psycopg2 wheel
RUN pip install psycopg2==2.9.3Debian Bullseye
Bullseye has libpq 13 so you can build directly against that.
Note that psycopg2-binary==2.9.3 is still statically linked against the libpq10 so it won't work on ARM.
FROM python:3.8.13-bullseye
RUN pip install psycopg2==2.9.3Changing my requirements.txt from psycopg2-binary to psycopg2 resolved this issue for me on M1 Mac.
Changing my requirements.txt from psycopg2-binary to psycopg2 resolved this issue for me on M1 Mac.
@piyh great job!
to make sure you override the installation- use pip install psycopg2 -I --no-cache-dir
I know that it's not a root of the problem, but I've changed the Postgres version from 14 to 11 and it's worked for me. With psycopg2-binary installed
I know that it's not a root of the problem, but I've changed the Postgres version from 14 to 11 and it's worked for me. With psycopg2-binary installed
That worked like a charm, thanks @hyzyla
For anyone who doesn't know how to try this, open the file called docker-compose.yml and find this line:
image: postgres:14-alpine
just change 14 to 11
@hyzyla @Doctorkvothe
I was using 13.3 and noticed this error when a coworker installed the project and got 14 since we did not define a version.
You can rollback to 13.3, you don't have to go all the way back to 11.
services:
db:
image: postgres:13.3
env_file:
- ./.env
ports:
- "5432:5432"
volumes:
- your_volume:/var/lib/postgresql/data/
You'll probably need to delete and recreate your db container, and volumes if you are using them.
$ docker-compose stop db
$ docker-compose rm -f db
$ docker volume rm your_volume
$ docker volume create --name=your_volume
@steezeburger Awesome, much appreciated!
We'd like to stick with psycopg2-binary to keep our dependencies simple. Ideally, it would be possible to resolve this issue without introducing a gcc build requirement since using psycopg2 would require a build step.
@brylie-wolt are you doing this in a docker image? What is your base image?
At the time of the above comment, I was testing with the postgres:14 image on Dockerhub
However, we are now switching to a Postgres 13 image to avoid the error and build dependency by using psycopg2-binary.
@brylie-wolt I mean in which Docker image is your python program. Unless you are running your program on a raspberry pi or something else which is not an x86_64?
We are using Python 3.10 slim from the AWS ECR public Docker registry.
If you use a x86_64 based image, binary wheels work already and ship with a more recent libpq library.
In order to extend wheel support to other platforms, there is quite some work to do. Is started in #1459 but it's going in the wrong direction and it will have to be done in a completely different way.
If for your company this is an important issues to solve, please get in touch to organise how to do fund this work.
amongst all conversation here, this was the only one that worked for me.
@matheushent, which answer in that StackOverflow discussion worked for you? There seem to be several suggested solutions. :-)
run export DOCKER_DEFAULT_PLATFORM=linux/amd64 and re-build the images. This is what worked for me.
run
export DOCKER_DEFAULT_PLATFORM=linux/amd64and re-build the images. This is what worked for me.
I wouldn't say this is a solution. You just switch to a different platform at the expense of a huge performance penalty.
run
export DOCKER_DEFAULT_PLATFORM=linux/amd64and re-build the images. This is what worked for me.I wouldn't say this is a solution. You just switch to a different platform at the expense of a huge performance penalty.
Absolutely, you're right. It "worked" in my case because I was running the docker image in dev environment.
Switching to linux/amd64 comes with a huge performance hit as mentioned above & in Ryan Mcgrath's comment here, and we didn't want to downgrade from Postgres 15, so here's how we've solved it:
FROM python:3.10-slim
...
# other_package incorrectly declares a depedency on psycopg2-binary
# which pulls in a libpq version incompatible with Apple Silicon
RUN pip install other_package
RUN pip uninstall psycopg2-binary -y
RUN apt update -y && apt install -y build-essential libpq-dev
RUN pip install psycopg2
...@hyzyla @Doctorkvothe I was using 13.3 and noticed this error when a coworker installed the project and got 14 since we did not define a version.
You can rollback to 13.3, you don't have to go all the way back to 11.
services: db: image: postgres:13.3 env_file: - ./.env ports: - "5432:5432" volumes: - your_volume:/var/lib/postgresql/data/You'll probably need to delete and recreate your db container, and volumes if you are using them.
$ docker-compose stop db $ docker-compose rm -f db $ docker volume rm your_volume $ docker volume create --name=your_volume
This worked! Thank you! I am using Mac M1 and it worked!
Will be fixed in 2.9.6.
@dvarrazzo Is is a work in progress? If so, can you please give a ref to the work? Thanks in advance.
2.9.6 being upload on PyPI right now.
Switching to
linux/amd64comes with a huge performance hit as mentioned above & in Ryan Mcgrath's comment here, and we didn't want to downgrade from Postgres 15, so here's how we've solved it:FROM python:3.10-slim ... # other_package incorrectly declares a depedency on psycopg2-binary # which pulls in a libpq version incompatible with Apple Silicon RUN pip install other_package RUN pip uninstall psycopg2-binary -y RUN apt update -y && apt install -y build-essential libpq-dev RUN pip install psycopg2 ...
Thanks! U help me. I love u
@brumsilva This problem is resolved long time ago, as @dvarrazzo #1360 (comment) stated on his comment. No need for workarounds.
@dvarrazzo I am using 2.9.6/2.9.9 of psycopgy2 and Python 3.8.18 still getting same error.
>>> import psycopg2
>>> print(psycopg2.__libpq_version__)
90224
when I run this command in Python3.9 I get 150003
wondering if Python should be upgraded. My environment is for Airflow
Hi, I'm using airflow version 2.8.2 and Python 3.8.10
It was found
psycopg2-binary==2.9.1
and there was a mistake
File "/usr/local/lib/python3.8/dist-packages/psycopg2/__init__.py", line 122, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: SCRAM authentication requires libpq version 10 or above
Installed
psycopg2-binary==2.9.6
when building your container with airflow
Checked
python -c "import psycopg2; print(psycopg2.__libpq_version__)"
90622
The problem has not been solved
@Crogs366 I can try to replicate and check if you can share a snippet to replicate 🌸
I'm also encountering the same authentication error when attempting to connect to my Supabase database using the Supabase Vecs library on macOS M2. However, the connection works successfully when using psycopg2.connect.
Is this issue has closed?
I m still looking for the solution.
@bajpaiabhas The issue is closed, as you can see from its status, and yes, it's resolved. Just try to use the latest version of the related tools.