ERROR: psql: SCRAM authentication requires libpq version 10 or above

[todd-mickle]

MWAA local runner for Airflow Version 2.4

I am running the MWAA local runner on an M2 Mac and I'm attempting to connect to an RDS PostgreSQL instance (version 14.6). From the terminal on local-runner-1 in Docker:

psql --host=<my_RDS_instance>.us-east-2.rds.amazonaws.com --username=postgres --dbname=sandbox --port=5432 --password

Result:

psql: SCRAM authentication requires libpq version 10 or above

---

From a DAG:

    pg_hook = PostgresHook(postgres_conn_id = 'my_connection')
    conn = pg_hook.get_conn()

Result:

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

---

Any suggestions to resolve this problem?

[kpodl-inoqo]

The image for Amazon Linux 2 seems to be using an older version of libpq that does not support SCRAM authentication.

We are working around that by setting the environment variable POSTGRES_HOST_AUTH_METHOD=password in the database container (in docker-compose.yaml).
⚠️ You have to make sure that the database is created with this setting (i.e. delete the current DB that has been configured for SCRAM).

[holly-evans]

Add psycopg2-binary to the requirements file. This includes a recent enough version of libpq to avoid the issue.

[umesecke]

Apparently there was a building/packaging error in the released versions of psycopg2 that resulted in an old version of libpq used for building on arm64. Therefore adding psycopg2-binary to the requirements file will not work as the constraint is still on version 2.9.5. There was a new version 2.9.6 released that fixed the error for us. See psycopg/psycopg2#1360

Maybe we could change the constraint file to use the new version. Nevermind, the version 2.9.5 is in the upstream constraint file of apache-airflow as well.

[brianobot]

@umesecke you solution worked for me! Thank you very much