postgresql.exceptions.AuthenticationMethodError postgresql.protocol only supports: MD5, crypt, plaintext, and trust

Question

I've started a new postgresql database in docker, PG_VERSION 14.0-1.pgdg110+1. I can connect with my GUI (Postico) and command line tools. However, py-postgresql (library version 1.2.2, python 3.9.0) won't connect.

python3 -m postgresql.bin.pg_python -h localhost -p 5432 -U postgres -d postgres

Traceback (most recent call last):
  File "/Users/steven/.pyenv/versions/3.9.0/lib/python3.9/runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/Users/steven/.pyenv/versions/3.9.0/lib/python3.9/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/Users/steven/Documents/customers/Green/BQ/.venv/lib/python3.9/site-packages/postgresql/bin/pg_python.py", line 136, in <module>
    sys.exit(command(sys.argv))
  File "/Users/steven/Documents/customers/Green/BQ/.venv/lib/python3.9/site-packages/postgresql/bin/pg_python.py", line 75, in command
    connection.connect()
  File "/Users/steven/Documents/customers/Green/BQ/.venv/lib/python3.9/site-packages/postgresql/driver/pq3.py", line 2427, in connect
    self._establish()
  File "/Users/steven/Documents/customers/Green/BQ/.venv/lib/python3.9/site-packages/postgresql/driver/pq3.py", line 2553, in _establish
    self.typio.raise_client_error(could_not_connect, creator = self, cause = exc)
  File "/Users/steven/Documents/customers/Green/BQ/.venv/lib/python3.9/site-packages/postgresql/driver/pq3.py", line 514, in raise_client_error
    raise client_error
postgresql.exceptions.ClientCannotConnectError: could not establish connection to server
  CODE: 08001
  LOCATION: CLIENT
CONNECTION: [failed]
  failures[0]:
    socket('::1', 5432)
    Traceback (most recent call last):
      File "/Users/steven/Documents/customers/Green/BQ/.venv/lib/python3.9/site-packages/postgresql/protocol/client3.py", line 136, in connect
        self.socket = self.socket_factory(timeout = timeout)
      File "/Users/steven/Documents/customers/Green/BQ/.venv/lib/python3.9/site-packages/postgresql/python/socket.py", line 65, in __call__
        s.connect(self.socket_connect)
    ConnectionRefusedError: [Errno 61] Connection refused

    The above exception was the direct cause of the following exception:

    postgresql.exceptions.ConnectionRejectionError: Connection refused
      CODE: 08004
      LOCATION: CLIENT
  failures[1]:
    NOSSL socket('127.0.0.1', 5432)
    postgresql.exceptions.AuthenticationMethodError: unsupported authentication request '<unknown>'(10)
      CODE: --AUT
      LOCATION: CLIENT
      HINT: 'postgresql.protocol' only supports: MD5, crypt, plaintext, and trust.
CONNECTOR: [Host] pq://postgres:***@localhost:5432/postgres
  category: None
DRIVER: postgresql.driver.pq3.Driver

I get the same error from my code, and from this example code:

import postgresql
postgressql.open("pq://postgres:password@localhost:5432/postgres")

I've used this driver, postgresql, and python before, but I have no idea where to go from here. The docs say that settings can be provided, but they don't say what they might be.

I'm going to say it is related to the SCRAM Support. See here Password Auth. Can you connect with psql? — Adrian Klaver
– Adrian Klaver, Commented Oct 8, 2021 at 0:19
@AdrianKlaver psql works fine (I've set $PGPASSWORD) psql -d postgres -U postgres -h localhost -p 5432 psql (13.4, server 14.0 (Debian 14.0-1.pgdg110+1)) WARNING: psql major version 13, server major version 14. Some psql features might not work. Type "help" for help. postgres=# — Steven Ensslen
– Steven Ensslen, Commented Oct 8, 2021 at 1:54
If this is SCRAM, how do I specify that for the python client? I've tried calls like postgresql.open("pq://postgres:password@localhost:5432/postgres", settings={"postgresql.protocol":"password"}) — Steven Ensslen
– Steven Ensslen, Commented Oct 8, 2021 at 1:57

Steven Ensslen · Accepted Answer · 2021-10-08 07:10:27Z

2

py-postgresql doesn't support SCRAM authentication, which is what method 10 is. (v1.3 is targeted to support SCRAM when that comes out, according to the issue tracker on github).

Other than waiting for a new version to come out, this has to be fixed on the server side. There are two reasons the server could be demanding SCRAM. One is that the pg_hba is set to demand SCRAM. The other is that the pg_hba is set to allow md5, but the password hash itself is stored in the SCRAM format on the server. You can't use a SCRAM format to support md5 authentication, so in this case it automatically gets upgraded from md5 to demand SCRAM.

To use md5, you would need to do one or both of: set password_encryption to md5 and change the user's password, so that it gets stored in the md5 format. And/or update the pg_hba to allow md5.

edited Oct 8, 2021 at 7:10

Steven Ensslen

1,45613 silver badges23 bronze badges

answered Oct 8, 2021 at 2:47

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steven Ensslen Over a year ago

I created a new login with md5 and I'm no longer getting this error. create role py_reader LOGIN INHERIT ENCRYPTED PASSWORD 'md5...'

Steven Ensslen Over a year ago

pg_hba.conf was already set to MD5, as the docs say.

Steven Ensslen · Accepted Answer · 2021-10-08 21:18:56Z

1

While I prefer py-postgresql as it installs cleanly with pip everywhere, I've made this problem entirely go away by switching libraries to psycopg2.

answered Oct 8, 2021 at 21:18

Steven Ensslen

1,45613 silver badges23 bronze badges

Collectives™ on Stack Overflow

postgresql.exceptions.AuthenticationMethodError postgresql.protocol only supports: MD5, crypt, plaintext, and trust

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related