Random errors on MD5 auth with pgbouncer

[vharitonsky]

I am connecting to the same pgbouncer with the the same credentials like a hundred times per second and sometimes I have this error:

  File "/usr/local/lib/python3.5/dist-packages/asyncpg/connection.py", line 558, in connect
    await connected
  File "uvloop/future.pyx", line 241, in __await__ (uvloop/loop.c:94875)
  File "uvloop/task.pyx", line 186, in uvloop.loop.BaseTask._fast_wakeup (uvloop/loop.c:100074)
  File "uvloop/future.pyx", line 101, in uvloop.loop.BaseFuture._result_impl (uvloop/loop.c:93110)
asyncpg.exceptions.ProtocolViolationError: Auth failed

Switching to plain auth in pgbouncer fixes the error. I suspect that something in salt is causing the issue, like some cruft in the buffer or leftover message that is not fully read:

        elif status == AUTH_REQUIRED_PASSWORDMD5:
            # AuthenticationMD5Password
            # Note: MD5 salt is passed as a four-byte sequence
            md5_salt = cpython.PyBytes_FromStringAndSize(
                self.buffer.read_bytes(4), 4)
            self.auth_msg = self._auth_password_message_md5(md5_salt)

Simple retry will lower the number of these errors significantly:

            try:
                con = await connect(
                    self.dsn,
                    timeout=self.timeout,
                    command_timeout=self.timeout,
                )
            except ProtocolViolationError:
                con = await connect(
                    self.dsn,
                    timeout=self.timeout,
                    command_timeout=self.timeout,
                )

But maybe there is a cleaner solution? Maybe the connection is not closed properly or some buffer was not flushed here:

            row = await con.fetchrow(fetch_image_query, int(file_id), name)
            await con.close()

[elprans]

Can you reproduce this on direct connections to PostgreSQL server? This might be a pgbouncer bug.

[vharitonsky]

I also could not reproduce it locally, maybe production setup bug

[vharitonsky]

But maybe there is a workaround?

[elprans]

I don't think we can do anything about this. I'll close this issue now, but feel free to reopen if you can reproduce without pgbouncer in the mix.

As a workaround I would suggest to continue doing what you are doing, maybe with a retry counter.

[AmatanHead]

I'm experiencing the same error. And I can't reproduce it without pgbouncer. @vharitonsky, were you able to figure it out?

[elprans]

This actually seems to be a duplicate of #158, which is fixed in master.

[AmatanHead]

Indeed it is, thanks.

[Azim0313]

Hi elprans,

This is a closed issue, but did you guys got any update for any possible solution. We actually need a rca for this in our company. We sometimes get this issue on our production and its raising concern. Please guide.

Cheers,
TripleA