Intermittent no pg_hba.conf entry for host

[masonk]

I have a long-running script which initializes an asyncpg.Pool at the start of the script, then spawns a ton of tasks on an eventloop which occasionally acquire a connection, write something and put the connection back. An individual connection could in theory go a long time between usages, since the writes are only occasional. Plenty of time for some of them to idle out. That may be relevant here, because exactly every ten minutes, I get a few errors like this in my task log:

asyncpg.exceptions.InvalidAuthorizationSpecificationError: no pg_hba.conf entry for host

The script goes through its recovery logic, throwing away of bunch of work that the task already did, and the script resumes working again. The connections continue working again for another 10 minutes - plenty of writes get through, so this is not really an auth error - then the cycle repeats.

This feels like asyncpg acquires a connection which is invalidated in some way, then raises when it checks the connection but before passing it along. I am not sure why the connections are invalidated, but the extreme regularity of it implies that the connections failed a keepalive or idled out and asyncpg doesn't account for the possibility in the acquire logic. It's also very possible that I have initialized the pool incorrectly and I'm seeing expected behavior. My pool's connection settings are all default. The connection is directly to PG, no middleware.

PostgreSQL 15.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
asyncpg 0.30.0

Here is what I believe is the relevant part of the stack trace. All stack frames above this are my own code and the exception is thrown on a async with pool.acquire() as conn: line.

Am I running into expected behavior?

  File "/home/redacted/miniconda3/envs/redacted/lib/python3.12/site-packages/asyncpg/pool.py", line 864, in _acquire
    return await _acquire_impl()
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/redacted/miniconda3/envs/redacted/lib/python3.12/site-packages/asyncpg/pool.py", line 849, in _acquire_impl
    proxy = await ch.acquire()  # type: PoolConnectionProxy
            ^^^^^^^^^^^^^^^^^^
  File "/home/redacted/miniconda3/envs/redacted/lib/python3.12/site-packages/asyncpg/pool.py", line 140, in acquire
    await self.connect()
  File "/home/redacted/miniconda3/envs/redacted/lib/python3.12/site-packages/asyncpg/pool.py", line 132, in connect
    self._con = await self._pool._get_new_connection()
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/redacted/miniconda3/envs/redacted/lib/python3.12/site-packages/asyncpg/pool.py", line 517, in _get_new_connection
    con = await self._connect(
          ^^^^^^^^^^^^^^^^^^^^
  File "/home/redacted/miniconda3/envs/redacted/lib/python3.12/site-packages/asyncpg/connection.py", line 2421, in connect
    return await connect_utils._connect(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/redacted/miniconda3/envs/redacted/lib/python3.12/site-packages/asyncpg/connect_utils.py", line 1049, in _connect
    conn = await _connect_addr(
           ^^^^^^^^^^^^^^^^^^^^
  File "/home/redacted/miniconda3/envs/redacted/lib/python3.12/site-packages/asyncpg/connect_utils.py", line 891, in _connect_addr
    return await __connect_addr(params_retry, False, *args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/redacted/miniconda3/envs/redacted/lib/python3.12/site-packages/asyncpg/connect_utils.py", line 934, in __connect_addr
    await connected

[masonk]

Also, looking at where the exception is raised, it looks like sslmode is relevant here.

My server hosted on Azure. ssl is ON for this instance. But I don't see anything about "sslmode".

cc @fantix just in case, by a miracle, you still have context from when you were working on retries four years ago :)

[elprans]

How does your pool initialization look like? It could be that SSL (re)negotiation is in play here.

[js-krinay]

Also, looking at where the exception is raised, it looks like sslmode is relevant here.

My server hosted on Azure. ssl is ON for this instance. But I don't see anything about "sslmode".

cc @fantix just in case, by a miracle, you still have context from when you were working on retries four years ago :)

@masonk

        encoded_password = urllib.parse.quote_plus(self.postgres_password)
        return (f"postgresql+asyncpg://{self.postgres_user}:{encoded_password}@"
                f"{self.postgres_host}:{self.postgres_port}/{self.postgres_db}"
                f"?ssl=require")