asyncpg: SSL Certificate Verify Failed

Thanks, guys, here is what eventually works for me:

ctx = ssl.create_default_context(cafile='./rds-combined-ca-bundle.pem')
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE
con = await asyncpg.connect(settings.DATABASE_URL, ssl=ctx)

@imbolc I think there’s little point in actually using the certificate if you disable verification (ctx.verify_mode = ssl.CERT_NOTE).

ctx = ssl.create_default_context(cafile='')
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE
...
await asyncpg.create_pool(dns='...', ssl=ctx)

Work too

OK, so I went and created a test RDS instance to test. The following works perfectly for me:

import asyncio
import asyncpg
import os.path
import ssl


async def aws_ssl():
    ctx = ssl.create_default_context(
        cafile=os.path.join(os.path.dirname(__file__), 'rds-combined-ca-bundle.pem'))
    pool = await asyncpg.create_pool(
        host='asyncpg-test.cpa3bgqi3hfw.us-east-1.rds.amazonaws.com',
        user='postgres',
        database='test',
        password='***',
        ssl=ctx
    )

    async with pool.acquire() as conn:
        try:
            print(await conn.fetchval('SELECT 1'))
        finally:
            pass

if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(aws_ssl())

Where rds-combined-ca-bundle.pem was downloaded from https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

Asyncpg doesn’t work with verify-full. Can you please suggest a solution? Thanks.

Connecting with psql:

postgres@raas:/tmp$ psql 'postgres://root:****@localhost:5432/postgres?sslrootcert=/var/lib/postgresql/.postgresql/root.crt&sslkey=/var/lib/postgresql/.postgresql/client.key&sslcert=/var/lib/postgresql/.postgresql/client.crt&sslmode=verify-full'
psql (10.12 (Ubuntu 10.12-1.pgdg18.04+1), server 10.15 (Ubuntu 10.15-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# 

Asyncpg connect script:

import asyncpg
import asyncio
async def run():
    dsn = 'postgres://root:****@localhost:5432/postgres?sslrootcert=/var/lib/postgresql/.postgresql/root.crt&sslkey=/var/lib/postgresql/.postgresql/client.key&sslcert=/var/lib/postgresql/.postgresql/client.crt&sslmode=verify-full'
    con = await asyncpg.connect(dsn)
    # Tried like below too, no dice.
    # con = await asyncpg.connect(
    #         host='localhost', user='root', password='****', database='postgres', ssl='verify-full',
    #         server_settings={
    #             'ssl_ca_file': '/var/lib/postgresql/.postgresql/root.crt',
    #             'ssl_cert_file': "/var/lib/postgresql/.postgresql/client.crt",
    #             'ssl_key_file': '/var/lib/postgresql/.postgresql/client.key'
    #             })
    types = await con.fetch('SELECT * FROM pg_type')
    print(types)
asyncio.get_event_loop().run_until_complete(run())

Error:

root@raas:~# python3 try.py
Traceback (most recent call last):
  File "try.py", line 18, in <module>
    asyncio.get_event_loop().run_until_complete(run())
  File "/usr/lib/python3.6/asyncio/base_events.py", line 484, in run_until_complete
    return future.result()
  File "try.py", line 6, in run
    con = await asyncpg.connect(dsn)
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connection.py", line 1677, in connect
    max_cacheable_statement_size=max_cacheable_statement_size)
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connect_utils.py", line 662, in _connect
    raise last_error
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connect_utils.py", line 654, in _connect
    connection_class=connection_class)
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connect_utils.py", line 621, in _connect_addr
    connector, timeout=timeout)
  File "/usr/lib/python3.6/asyncio/tasks.py", line 358, in wait_for
    return fut.result()
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connect_utils.py", line 590, in _create_ssl_connection
    return await conn_factory(sock=sock)
  File "/usr/lib/python3.6/asyncio/base_events.py", line 820, in create_connection
    sock, protocol_factory, ssl, server_hostname)
  File "/usr/lib/python3.6/asyncio/base_events.py", line 846, in _create_connection_transport
    yield from waiter
  File "/usr/lib/python3.6/asyncio/sslproto.py", line 505, in data_received
    ssldata, appdata = self._sslpipe.feed_ssldata(data)
  File "/usr/lib/python3.6/asyncio/sslproto.py", line 201, in feed_ssldata
    self._sslobj.do_handshake()
  File "/usr/lib/python3.6/ssl.py", line 689, in do_handshake
    self._sslobj.do_handshake()
ssl.SSLError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:852)

Tried the same with sqlalchemy with psycopg2 driver. It works great.

root@raas:~# cat alc.py 
from sqlalchemy import create_engine
engine = create_engine('postgres://root:****@localhost:5432/postgres?sslrootcert=/var/lib/postgresql/.postgresql/root.crt&sslkey=/var/lib/postgresql/.postgresql/client.key&sslcert=/var/lib/postgresql/.postgresql/client.crt&sslmode=verify-full')
r = engine.execute('SELECT 1').fetchall()
print(r)
root@raas:~# 
root@raas:~# python3 alc.py 
[(1,)]
root@raas:~# 

I still get the same error. It works with pyscopg2, but not with asyncpg, os not sure why

I’ve noticed this problem recently as well.

A few things I’ll add:

• Heroku Postgres does NOT use RDS under the hood (and I’m assuming people here are actually using Heroku Postgres)
• There’s several sslmode settings for postgres. I’d imagine asyncpg should properly translate values like required, verify-full, etc to the right SSL context? Or make it clear how users can emulate those different behaviors by constructing it themselves? The value thats mentioned throughout Heroku devcenter is required, which (according to the PG docs) forces SSL, but doesn’t do any validation. I believe the solution above in this comment achieves that?

I’ve used Node on Heroku before, with this pg package, and I always used ssl: true in my configuration. That seems to translate to something similar in that package here (since this.ssl is true, all of the attribute access on it returns undefined) as the SSL context without validation mentioned above.

Duh. Replace capath with cafile and it should work.

Well, that’s weird. I’ll try to reproduce. Meanwhile you can disable the certificate verification like this:

ssl_object = ...
ssl_object.check_hostname = False
ssl_object.verify_mode = ssl.CERT_NONE
# connect...