postgresql.exceptions.AuthenticationMethodError postgresql.protocol only supports: MD5, crypt, plain-CodePudding

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.

CodePudding user response：

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.