Asyncpg: SSL Certificate Verify Failed

[Modelmat]

• 13.0:
• 9.6.1:
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install? Yes, Heroku.:
• 3.6.3:
• Windows 10:
• Do you use pgbouncer? No:
• Did you install asyncpg with pip? Yes:

I was getting the same issue as #119 . I enabled the ssl=True in the await asyncpg.create_pool(self.dsn, ssl=True) - and the same occurs with connect.

So then I used the SSL keyword param, and got ssl.SSLError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:777).

[amsb]

@Modelmat Are you missing root certificates to validate against? The certifi python package provides these if your current python installation doesn't provide them already.

[Modelmat]

@amsb Nope. It is already installed.

[elprans]

@Modelmat Are you passing the correct SSL context? Your system is missing the necessary root and/or intermediate certs to verify the server certificate. If the server is not using a self-signed cert, then the following should work in most cases:

await asyncpg.connect(..., ssl=ssl.create_default_context(capath=certifi.where()))

[Modelmat]

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

[zharben]

@Modelmat @elprans I had the same problem.

If you happen to be using AWS RDS, it looks like you need to use their root certificate (e.g. when connecting via asyncio/asyncpg):
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html

We haven't encountered this problem when using psycopg2. Perhaps that's because it's built with libpq bindings?

After banging my head for a while, I was able to successfully connect by appending the contents of AWS' root certificate bundle to the CA bundle in my environment (docker/ubuntu 14.04, postgres 9.5)

[Modelmat]

@zharben I do happen to be using Amazon AWS. However, adding the certificate file in (Windows), in Internet Options > Content > Certificates > Import, then importing the pem file that was provided in the link does not seem to work. Same with the p7b file

[Modelmat]

Is there a way to make asyncpg not care about database verification?

[elprans]

The whole point of SSL is that it is verified.

1. Download the RDS root cert
2. Download the intermediate certificate for your region from https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html.
3. Concatenate them.
4. Pass the path to the resulting file to ssl.create_default_context(capath=.

[Modelmat]

So like this?

ssl_object = ssl.create_default_context(capath=r"MyPath\concat.pem")
self.pool = await asyncpg.create_pool(self.dsn, ssl=ssl_object)

Because that doesn't seem to work.

[elprans]

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...

[Modelmat]

Those made it work. I can send the file if you want

[elprans]

Duh. Replace capath with cafile and it should work.

[Modelmat]

That doesn't work.

[elprans]

Works for me. Double-check your CA file and that the intermediate cert matches your region.

[savv]

FYI, this didn't work for me either, despite following the steps carefully.

[avli]

Doesn't work for me either. I use the version 0.15.0 if asyncpg. I have checked it with Python 3.6 installed from brew and Python 3.6 Docker image.

[elprans]

@savvopoulos @avli Are you on RDS?

[savv]

Yes, via heroku.

[elprans]

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

[avli]

Same for me – RDS via Heroku.

@elprans Thank you for the proposed solution. I'll check it out and let you know.

[Modelmat]

I just tried this, it doesn't like it either -

I don't suppose using the entire postgres:// URL as the dsn argument would affect this?

Traceback (most recent call last):
  File "C:\Users\Modelmat\Desktop\db.py", line 23, in <module>
    loop.run_until_complete(aws_ssl())
  File "D:\Program Files\Python3\lib\asyncio\base_events.py", line 467, in run_until_complete
    return future.result()
  File "C:\Users\Modelmat\Desktop\db.py", line 12, in aws_ssl
    ssl=ctx
  File "D:\Program Files\Python3\lib\site-packages\asyncpg\pool.py", line 356, in _async__init__
    await first_ch.connect()
  File "D:\Program Files\Python3\lib\site-packages\asyncpg\pool.py", line 126, in connect
    **self._connect_kwargs)
  File "D:\Program Files\Python3\lib\site-packages\asyncpg\connection.py", line 1512, in connect
    max_cacheable_statement_size=max_cacheable_statement_size)
  File "D:\Program Files\Python3\lib\site-packages\asyncpg\connect_utils.py", line 314, in _connect
    raise last_error
  File "D:\Program Files\Python3\lib\site-packages\asyncpg\connect_utils.py", line 306, in _connect
    connection_class=connection_class)
  File "D:\Program Files\Python3\lib\site-packages\asyncpg\connect_utils.py", line 276, in _connect_addr
    connector, timeout=timeout, loop=loop)
  File "D:\Program Files\Python3\lib\asyncio\tasks.py", line 358, in wait_for
    return fut.result()
  File "D:\Program Files\Python3\lib\site-packages\asyncpg\connect_utils.py", line 345, in _create_ssl_connection
    server_hostname=host)
  File "D:\Program Files\Python3\lib\asyncio\base_events.py", line 803, in create_connection
    sock, protocol_factory, ssl, server_hostname)
  File "D:\Program Files\Python3\lib\asyncio\base_events.py", line 829, in _create_connection_transport
    yield from waiter
  File "D:\Program Files\Python3\lib\asyncio\sslproto.py", line 501, in data_received
    ssldata, appdata = self._sslpipe.feed_ssldata(data)
  File "D:\Program Files\Python3\lib\asyncio\sslproto.py", line 201, in feed_ssldata
    self._sslobj.do_handshake()
  File "D:\Program Files\Python3\lib\ssl.py", line 689, in do_handshake
    self._sslobj.do_handshake()
ssl.SSLError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:777)

[avli]

I have tried it too – got the same error as in the comment above.

[Modelmat]

Bump?

[iamjem]

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.

[iamjem]

My point above is basically that I _believe_ @elprans comment is likely the correct solution on Heroku.

[imbolc]

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)

[savv]

I'm sorry if this is a naive question, but what is the point of a private key (=pem), if it's available online?

[elprans]

PEM is an encoding format. These are all certificates in pem format.

[willyhakim]

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)

Had the same problem trying to connect to aiven.io pg instance. After downloading the certificate from aiven project, this code worked. Don't forget to import ssl

[Modelmat]

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

[lbenka]

Thanks for this https://github.com/MagicStack/asyncpg/issues/238#issuecomment-403843846 works for me ;)

[GabrielNicolasAvellaneda]

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

Thought the same thing. You are right.

[infnetdanpro]

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

[ZacharyThomas]

@elprans With the example RDS setup provided I actually couldn't connect to my RDS instance - perhaps due to my org's SSL setup. However, I can connect with a psycopg based driver using my DSN. The key difference for me, I _think_, is that pyscopg supports sslmode and sslrootcert connect args - I wonder if it makes sense to support sslrootcert?

[elprans]

I wonder if it makes sense to support sslrootcert?

sslrootcert is just a connection option, which is equivalent to the above explicit context configuration. We can add the shortcut, but that'll not change the functionality. Please double check that you are specifying the correct certificate chain to cafile.

[ZacharyThomas]

@elprans I managed to get it to work but only by disabling the hostname check (not the verify_mode). I guess this is because sslmode verify-ca doesn't verify the hostname by default, verify-full does. However the default SSL context does attempt to verify the hostname.

I guess from a user perspective it's somewhat unclear that the sslmode connection param is supported by asyncpg but it is somewhat overriden by the SSL context. Thanks for your help also, this thread was very useful to me :).

[elprans]

I guess from a user perspective it's somewhat unclear that the sslmode connection param is supported by asyncpg but it is somewhat overriden by the SSL context

Good point. I'll add a clarification.

[cheburakshu]

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:~#