Python psycopg2 SCRAM authentication

Question

I'm trying to run a python application that uses psycopg2-binary==2.9.1, but I'm hitting this error:

psycopg2.OperationalError: SCRAM authentication requires libpq version 10 or above

When I check the version of libpq installed, it suggests I have 12.8:

sudo dpkg -l | grep libpq
ii  libpq5:arm64                       12.8-0ubuntu0.20.04.1             arm64        PostgreSQL C client library

I searched around and saw some recommendations to switch off scram authentication, so I changed authentication to md5 in pg_hba.conf and postgresql.conf and then reloaded config (and restarted my database).

I'm still getting this problem. Does anyone know what is wrong ? Thanks in advance

Gorman

How did you compile psycopg2? Did the compilation use that version of libpq or a different one? – Castano

You can strace it and see what libpq file (if any) it is actually opening. – Slug

@Slug I gave that a try and I suspect psycopg2-binary itself has the libpq library (I'm using pyinstaller): venv$ find . | grep libpq ./lib/python3.8/site-packages/psycopg2_binary.libs/libpq-c98caf99.so.5.9 – Gorman

That's weird. For me it has 'libpq-6f24e430.so.5.13'. What if you do -U for pip? Or uninstall and reinstall? – Slug

I'm seeing the same on another server I am running as well: ./venv/lib/python3.7/site-packages/psycopg2_binary.libs/libpq-6f24e430.so.5.13 I wonder if this is a bad distribution for ubuntu 20.0 – Gorman

Answer 1

I'm using apple silicon (M1 Pro).

In my environment (python debian image on docker), the solution is to upgrade libpq and install build tools, then build psycopg2-binary from source.

ubuntu example code:

sudo apt update -y && sudo apt install -y build-essential libpq-dev
pip install psycopg2-binary --no-binary psycopg2-binary

Kolyma

Why not just pip install psycopg2 instead? That's the no binary version of the package, I believe? – Martel

Answer 2

I have the same issue with my arm64 build. When I build a Docker Container based on Python3 (Debian). If I build for arm64 I see it will install libpq version 9

WARNING: The requested image's platform (linux/arm64) does not match the detected host platform (linux/amd64) and no specific platform was requested
Python 3.10.0 (default, Nov 18 2021, 00:02:14) [GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2 
>>> print(psycopg2.__libpq_version__)
90623
>>> print(psycopg2.extensions.libpq_version())
90623
>>> 

but if I build the exact same dockerfile on a amd64 CPU i got a different version installed.

Python 3.10.0 (default, Nov 17 2021, 15:26:39) [GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> print(psycopg2.__libpq_version__)
130003
>>> print(psycopg2.extensions.libpq_version())
130003

I did also tried with python:3-alpine for arm64 and get a much better result.

WARNING: The requested image's platform (linux/arm64) does not match the detected host platform (linux/amd64) and no specific platform was requested
/src # python
Python 3.10.0 (default, Nov 30 2021, 00:28:27) [GCC 10.3.1 20211027] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> print(psycopg2.__libpq_version__)
140001
>>> print(psycopg2.extensions.libpq_version())
140001
>>> 

So I guess the issue is the repo that Debian/Ubuntu are using. Maybe you should open a ticket with Debian and Ubuntu to update that package for Postgresql-client for arm64.

Outoftheway

Answer 3

This is now properly handled by the new version of psycopg2 https://github.com/psycopg/psycopg2/releases/tag/2.9.6

Acculturation

You're right, worked for me! – Atlantis

This worked for me for running my code in an AWS lambda function with python 3.9 on arm64 with psycopg2-binary==2.9.6. – Ojibwa

Answer 4

There must be a second copy of libpq on your machine, and psycopg is using that copy. Identify that file and remove or upgrade it. Perhaps that requires updating psycopg.

Antipasto

I looked at the wheel of psycopg-binary (which is the python package I’m using). For reasons unknown, version 2.9.1 had an old version of libpq. This was for Ubuntu 20.0 arm64 – Gorman

That was a good reminder – Sememe

Answer 5

If you are using docker to deploy your app, this worked for me

# FROM pypy:3.6-7.3.3-slim
FROM python:3.10
WORKDIR /usr/src/test_repo

    
COPY requirements.txt requirements.txt
RUN pip install --upgrade pip && pip install -r requirements.txt
RUN apt-get autoremove -y

RUN apt-get install -y libpq-dev gcc

RUN pip install psycopg2-binary --no-binary psycopg2-binary

COPY . .
EXPOSE 5001
ENTRYPOINT ["flask","run","--host=0.0.0.0","--port=5001"]

Histaminase