SSL 'prefer' option in asyncpg behaves different than libpq

[fvannee]

• asyncpg version: 0.22.0
• PostgreSQL version: 12/13
• **Do you use a PostgreSQL SaaS? No
• Python version: 3.6
• Platform: CentOs
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes

Since v0.22 some of our scripts broke with an error that they could not connect to the server anymore. I noticed that the default ssl mode changed to 'prefer' in 0.22, so this likely had something to do with it. Indeed, setting ssl to 'off' made our scripts connect again.
This is a bit strange, as the 'prefer' setting should try ssl=on first and if it doesn't work fallback to non-ssl.
Looking at the code, it looks like asyncpg behaves different here than libpq.
This case does not work:

• Have a server which does support SSL (ssl=on on the server-side)
• Explicitly disallow (some) users from logging in with ssl, by specifying hostnossl in the pg_hba.conf
• Open an asyncpg connection with a user that only matches a hostnossl line in pg_hba.conf
  Asyncpg wil only try the ssl connection. It will not fallback to the connection without ssl but instead throw an error after the first connect fails.

Libpq, on the other hand, will happily connect with ssl=prefer (connecting with psycopg2 and sslmode=prefer works for example). It will try ssl=on first, sees that it cannot connect and then retry with ssl off.

[fantix]

Explicitly disallow (some) users from logging in with ssl, by specifying hostnossl in the pg_hba.conf

To reproduce, I'm using the following pg_hba.conf:

hostnossl .... trust   # allow non-SSL connections
host      .... reject  # reject other (SSL) connections

Refs protocol document, the server is configured with ssl=on so it replies S for SSLRequest, then asyncpg tries SSL first, but the SSL handshake is rejected by the pg_hba.con. In libpq, it retries with non-SSL in this case, looks like that's what we need to follow too.

[fvannee]

Thanks for merging a fix for this! I've got one more question. Is there any release planning available for asyncpg - when do you think a release would be created which includes this fix?