asyncpg
asyncpg copied to clipboard
MagicStack
authentication issue with md5 on postgres 11
- asyncpg version: 0.21.0 and lower
- PostgreSQL version: 11
- Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?:
- Python version: 3.6.0
- Platform: linux 3.10.0
- Do you use pgbouncer?: No
- Did you install asyncpg with pip?: Yes
- If you built asyncpg locally, which version of Cython did you use?:
- Can the issue be reproduced under both asyncio and uvloop?:
We have the same code. When running with useridAAA with password, it works on some servers, but not on other servers. The useridAAA's password is authenticated with md5 on the postgres. If we switch to useridBBB, which is authenticated as SHA256 on postgres. It works on all servers. It's the same password for useridAAA and useridBBB. What's dictating the hash on the password on the client side?
Below is the error message when couldn't be authenticated. `DEBUG: Using selector: EpollSelector
DEBUG: Get address info edclpgsd320c.bcbsfl.com:5420, type=<SocketKind.SOCK_STREAM: 1>
DEBUG: Getting address info edclpgsd320c.bcbsfl.com:5420, type=<SocketKind.SOCK_STREAM: 1> took 2.792 ms: [(<AddressFamily.AF_INET: 2>, <SocketKind.SOCK_STREAM: 1>, 6, '', ('))]
DEBUG: poll 59997.326 ms took 1.820 ms: 1 events
DEBUG: connect <socket.socket fd=6, family=AddressFamily.AF_INET, type=2049, proto=6, laddr=('0.0.0.0', 0)> to ()
DEBUG: poll 59993.166 ms took 0.141 ms: 1 events
DEBUG: <socket.socket fd=6, family=AddressFamily.AF_INET, type=2049, proto=6, laddr=('), raddr=(')> connected to : (<_SelectorSocketTransport fd=6 read=polling write=<idle, bufsize=0>>, <asyncpg.protocol.protocol.Protocol object at 0x7fc691bc7858>)
DEBUG: poll 59990.760 ms took 0.014 ms: 1 events
INFO: poll 59990.568 ms took 60000.243 ms: 1 events
DEBUG: <_SelectorSocketTransport fd=6 read=polling write=<idle, bufsize=0>> received EOF
Traceback (most recent call last):
File "asyncdb3c.py", line 52, in
app = loop.run_until_complete(init_app())
File "/usr/lib64/python3.6/asyncio/base_events.py", line 484, in run_until_complete
return future.result()
File "asyncdb3c.py", line 34, in init_app
pool = await asyncpg.create_pool("postgres://useridAAA:[email protected]:5490/xxx01s1?application_name=aaa",min_size=1, max_size=3,max_inactive_connection_lifetime=3)
File "/usr/lib64/python3.6/asyncio/coroutines.py", line 110, in next
return self.gen.send(None)
File "/u/i3ye/ppx/lib64/python3.6/site-packages/asyncpg/pool.py", line 398, in async__init_
await self._initialize()
File "/usr/lib64/python3.6/asyncio/coroutines.py", line 110, in next
return self.gen.send(None)
File "/u/i3ye/ppx/lib64/python3.6/site-packages/asyncpg/pool.py", line 426, in _initialize
await first_ch.connect()
File "/usr/lib64/python3.6/asyncio/coroutines.py", line 110, in next
return self.gen.send(None)
File "/u/i3ye/ppx/lib64/python3.6/site-packages/asyncpg/pool.py", line 125, in connect
self._con = await self._pool._get_new_connection()
File "/usr/lib64/python3.6/asyncio/coroutines.py", line 110, in next
return self.gen.send(None)
File "/u/i3ye/ppx/lib64/python3.6/site-packages/asyncpg/pool.py", line 472, in _get_new_connection
**self._connect_kwargs)
File "/usr/lib64/python3.6/asyncio/coroutines.py", line 110, in next
return self.gen.send(None)
File "/u/i3ye/ppx/lib64/python3.6/site-packages/asyncpg/connection.py", line 1727, in connect
max_cacheable_statement_size=max_cacheable_statement_size)
File "/usr/lib64/python3.6/asyncio/coroutines.py", line 110, in next
return self.gen.send(None)
File "/u/i3ye/ppx/lib64/python3.6/site-packages/asyncpg/connect_utils.py", line 666, in _connect
connection_class=connection_class)
File "/usr/lib64/python3.6/asyncio/coroutines.py", line 110, in next
return self.gen.send(None)
File "/u/i3ye/ppx/lib64/python3.6/site-packages/asyncpg/connect_utils.py", line 642, in _connect_addr
await asyncio.wait_for(connected, timeout=timeout)
File "/usr/lib64/python3.6/asyncio/tasks.py", line 358, in wait_for
return fut.result()
asyncpg.exceptions.ConnectionDoesNotExistError: connection was closed in the middle of operation
`
The error traceback you provided doesn't seem to be related to authentication. Looks like a network issue to me. What do server-side logs show?
Here is the server log. It doesn't seem related either. Regardless, which code in the client is deciding to do MD5 or SHA256?
020-09-22 09:32:42.141 EDT [26229]: [26229-10] db=----,user=useridAAA,app=[unknown],client=---.comLOG: disconnection: session time: 0:00:03.012 user=useridAAA database=--- host=----.com port=61034 2020-09-22 09:32:42.154 EDT [25375]: [25375-2] db=----,user=useridAAA,app=[unknown],client=---.comFATAL: canceling authentication due to timeout
Authentication method is decided by the server based on configuration, asyncpg only reacts to what the server is asking it to do. See _parse_msg_authentication.
Trying to find out how the postgres server to instruct the client to hash, but didn't find much. What's the constant that the client is using from the server to do the hash? I doubt on that because we have the same server and the same client code.But the different hash was used by the same client code. It seems the client code is influenced by something else.
Also, looking at code below from https://gemfury.com/agriconnect/python:asyncpg/0.18.2/content/protocol/coreproto.pyx. It doesn't seem it's handling the scram sha 256?
`cdef _parse_msg_authentication(self):
cdef:
int32_t status
bytes md5_salt
status = self.buffer.read_int32()
if status == AUTH_SUCCESSFUL:
# AuthenticationOk
self.result_type = RESULT_OK
elif status == AUTH_REQUIRED_PASSWORD:
# AuthenticationCleartextPassword
self.result_type = RESULT_OK
self.auth_msg = self._auth_password_message_cleartext()
elif status == AUTH_REQUIRED_PASSWORDMD5:
# AuthenticationMD5Password
# Note: MD5 salt is passed as a four-byte sequence
md5_salt = self.buffer.read_bytes(4)
self.auth_msg = self._auth_password_message_md5(md5_salt)
elif status in (AUTH_REQUIRED_KERBEROS, AUTH_REQUIRED_SCMCRED,
AUTH_REQUIRED_GSS, AUTH_REQUIRED_GSS_CONTINUE,
AUTH_REQUIRED_SSPI):
self.result_type = RESULT_FAILED
self.result = apg_exc.InterfaceError(
'unsupported authentication method requested by the '
'server: {!r}'.format(AUTH_METHOD_NAME[status]))
else:
self.result_type = RESULT_FAILED
self.result = apg_exc.InterfaceError(
'unsupported authentication method requested by the '
'server: {}'.format(status))
self.buffer.discard_message()`
It doesn't seem it's handling the scram sha 256?
You are looking at a fairly old version of asyncpg. Why 0.18.2?
look at the latest code. where is the PROTOCOL_AUTH value from?
cdef _connect(self):
cdef:
WriteBuffer buf
WriteBuffer outbuf
if self.con_status != CONNECTION_BAD:
raise apg_exc.InternalClientError('already connected')
self._set_state(PROTOCOL_AUTH)
self.con_status = CONNECTION_STARTED
google led me to this old open issue. I have a related problem with asyncpg. I understood from #769 that kerberos authentication is not supported. However the error information is quite strange.
what I got is
Traceback (most recent call last):
File "asyncpg/protocol/coreproto.pyx", line 154, in asyncpg.protocol.protocol.CoreProtocol._process__auth
File "asyncpg/protocol/coreproto.pyx", line 621, in asyncpg.protocol.protocol.CoreProtocol._parse_msg_authentication
NameError: name 'AUTH_METHOD_NAME' is not defined
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
...
File "/home/yun/.conda/envs/saffron/lib/python3.9/asyncio/tasks.py", line 479, in wait_for
return fut.result()
asyncpg.exceptions._base.InternalClientError: unexpected error while performing authentication: name 'AUTH_METHOD_NAME' is not defined
which indicates that AUTH_METHOD_NAME is not defined. That's quite strange.
