SSL Certificate Verify Failed

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

@modelmat

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

@amsb

@amsb Nope. It is already installed.

@modelmat

@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()))

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

@modelmat

@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)

@zharben

@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

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

The whole point of SSL is that it is verified.

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

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.

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

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

Duh. Replace capath with cafile and it should work.

That doesn't work.

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

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

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

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

@imbolc

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

Thanks for this #238 (comment) works for me ;)

@imbolc

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

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

Add ssl context for database connection

Added certificate by instruction MagicStack/asyncpg#238 (comment)

Can run locally, not only from server with ssl, thanks MagicStack/asy…

@elprans

@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?

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.

@elprans

@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 :).

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.

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

Navigation Menu

Repository navigation

SSL Certificate Verify Failed #238

12 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

SSL Certificate Verify Failed #238

Description

Activity

amsb commented on Dec 29, 2017

modelmat commented on Dec 30, 2017

elprans commented on Dec 31, 2017

modelmat commented on Dec 31, 2017

zharben commented on Jan 3, 2018

modelmat commented on Jan 3, 2018

modelmat commented on Jan 3, 2018

elprans commented on Jan 3, 2018

modelmat commented on Jan 3, 2018

elprans commented on Jan 3, 2018

modelmat commented on Jan 3, 2018

elprans commented on Jan 3, 2018

modelmat commented on Jan 3, 2018

elprans commented on Jan 4, 2018

12 remaining items

savv commented on Jul 11, 2018

elprans commented on Jul 11, 2018

willyhakim commented on Nov 8, 2018

modelmat commented on Nov 17, 2018

lbenka commented on Nov 21, 2018

GabrielNicolasAvellaneda commented on Nov 28, 2018

infnetdanpro commented on Feb 19, 2020

ZacharyThomas commented on Dec 2, 2020

elprans commented on Dec 2, 2020

ZacharyThomas commented on Dec 2, 2020

elprans commented on Dec 2, 2020

cheburakshu commented on Dec 9, 2020

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions