Skip to content

SSL 'prefer' option in asyncpg behaves different than libpq #716

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
fvannee opened this issue Mar 16, 2021 · 2 comments · Fixed by #720
Closed

SSL 'prefer' option in asyncpg behaves different than libpq #716

fvannee opened this issue Mar 16, 2021 · 2 comments · Fixed by #720
Assignees
@fantix

Comments

@fvannee
Copy link
Contributor

fvannee commented Mar 16, 2021 •
edited
Loading

  • 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
Copy link
Member

fantix commented Mar 19, 2021

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.

@fantix fantix self-assigned this Mar 19, 2021
fantix added a commit to fantix/asyncpg that referenced this issue Mar 22, 2021
@fantix
Add sslmode=allow support and fix =prefer retry
77d4742 
We didn't really retry the connection without SSL if the first SSL
connection fails, that led to an issue when the server has SSL support
but explicitly denies SSL connection through pg_hba.conf. This commit
adds a retry in a new connection, which makes it easy to implement the
sslmode=allow retry.

Fixes MagicStack#716
@fantix fantix mentioned this issue Mar 22, 2021
Merged
fantix added a commit to fantix/asyncpg that referenced this issue Mar 22, 2021
@fantix
Add sslmode=allow support and fix =prefer retry
c993737 
We didn't really retry the connection without SSL if the first SSL
connection fails under sslmode=prefer, that led to an issue when the
server has SSL support but explicitly denies SSL connection through
pg_hba.conf. This commit adds a retry in a new connection, which
makes it easy to implement the sslmode=allow retry.

Fixes MagicStack#716
fantix added a commit to fantix/asyncpg that referenced this issue Mar 23, 2021
@fantix
Add sslmode=allow support and fix =prefer retry
2190895 
We didn't really retry the connection without SSL if the first SSL
connection fails under sslmode=prefer, that led to an issue when the
server has SSL support but explicitly denies SSL connection through
pg_hba.conf. This commit adds a retry in a new connection, which
makes it easy to implement the sslmode=allow retry.

Fixes MagicStack#716
fantix added a commit to fantix/asyncpg that referenced this issue Mar 23, 2021
@fantix
Add sslmode=allow support and fix =prefer retry
bbc0558 
We didn't really retry the connection without SSL if the first SSL
connection fails under sslmode=prefer, that led to an issue when the
server has SSL support but explicitly denies SSL connection through
pg_hba.conf. This commit adds a retry in a new connection, which
makes it easy to implement the sslmode=allow retry.

Fixes MagicStack#716
fantix added a commit to fantix/asyncpg that referenced this issue Mar 23, 2021
@fantix
Add sslmode=allow support and fix =prefer retry
c74564f 
We didn't really retry the connection without SSL if the first SSL
connection fails under sslmode=prefer, that led to an issue when the
server has SSL support but explicitly denies SSL connection through
pg_hba.conf. This commit adds a retry in a new connection, which
makes it easy to implement the sslmode=allow retry.

Fixes MagicStack#716
fantix added a commit to fantix/asyncpg that referenced this issue Mar 24, 2021
@fantix
Add sslmode=allow support and fix =prefer retry
a4731c8 
We didn't really retry the connection without SSL if the first SSL
connection fails under sslmode=prefer, that led to an issue when the
server has SSL support but explicitly denies SSL connection through
pg_hba.conf. This commit adds a retry in a new connection, which
makes it easy to implement the sslmode=allow retry.

Fixes MagicStack#716
fantix added a commit that referenced this issue Mar 24, 2021
@fantix
Add sslmode=allow support and fix =prefer retry (#720)
075114c 
We didn't really retry the connection without SSL if the first SSL
connection fails under sslmode=prefer, that led to an issue when the
server has SSL support but explicitly denies SSL connection through
pg_hba.conf. This commit adds a retry in a new connection, which
makes it easy to implement the sslmode=allow retry.

Fixes #716
@fvannee
Copy link
Contributor Author

fvannee commented Mar 25, 2021

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@fantix fantix

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Add sslmode=allow support and fix =prefer retry fantix/asyncpg
2 participants
@fvannee @fantix