What is the correct way to connect to a database using sqlalchemy and pg8000 with SSL?

Question

I'm getting confused by documentation and examples of sqlalchemy and pg8000 to connect to another postgres database using SSL. A private key isn't required or anything. I'm trying to connect with SSL in require mode. This is a screenshot of the SSL settings in a connection to the database through a SQL client (Datagrip):

As you can see, the text boxes to type in the key file's location or password are all blank; I've just selected the mode as Require.

Now in my code, the following used to work but suddenly stopped working.

DATABASE_DB_URL=postgresql+pg8000://user:[email protected]:5432/database?ssl=True

db = records_client.Database(DATABASE_DB_URL)

# Returns error: 
TypeError: connect() got an unexpected keyword argument 'ssl'

Here's the full stack trace

Traceback (most recent call last):
  File "C:/Users/user/Documents/django-app/test2.py", line 44, in <module>
    db = records_client.Database(DATABASE_DB_URL) #, connect_args={'ssl_context':True})
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\records.py", line 245, in __init__
    self.db = self._engine.connect()
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\engine\base.py", line 3166, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\engine\base.py", line 96, in __init__
    else engine.raw_connection()
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\engine\base.py", line 3245, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\engine\base.py", line 3212, in _wrap_pool_connect
    return fn()
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\pool\base.py", line 301, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\pool\base.py", line 761, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\pool\base.py", line 419, in checkout
    rec = pool._do_get()
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\pool\impl.py", line 145, in _do_get
    self._dec_overflow()
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\util\langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\pool\impl.py", line 142, in _do_get
    return self._create_connection()
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\pool\base.py", line 247, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\pool\base.py", line 362, in __init__
    self.__connect()
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\pool\base.py", line 605, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\util\langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\pool\base.py", line 599, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\engine\create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\user\Documents\django-app\venv-dev\lib\site-packages\sqlalchemy\engine\default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
TypeError: connect() got an unexpected keyword argument 'ssl'

Process finished with exit code 1

So I thought to add in the SSL parameter in the Python call instead of the environment variable like so:

DATABASE_DB_URL=postgresql+pg8000://user:[email protected]:5432/database?

db = records_client.Database(DATABASE_DB_URL, connect_args={'ssl_context':True})

# Returns error: ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: IP address mismatch, certificate is not valid for '98.765.4.321'. (_ssl.c:1091)

I'm pretty sure that IP address in the error is whitelisted to connect to the database.

I've been trying to find an example for proper syntax and usage, but am getting confused. What is the proper argument to pass into the connect call?

Answer 1

The example code below works for me, using a self-signed certificate.

Note that ssl.CERT_REQUIRED is the default for ssl.create_default_context, but you would want to set it if the context was created by calling SSLContext() directly.

According to the docs, passing ssl_context=True will use the result of create_default_context(), so there doesn't seem to be any difference between this code and what you are doing already, apart from loading a non-standard certificate path. The SSLCertVerificationError implies that your code is correct, but the combination of client machine and certificate is not. You may need to check the pg_hba.conf file on the server.

import ssl

import sqlalchemy as sa


ssl_context = ssl.create_default_context()
ssl_context.verify_mode = ssl.CERT_REQUIRED
# Probably necessary because I was using a self-signed cert.
ssl_context.load_verify_locations(CERTIFICATE_PATH)



engine = sa.create_engine('postgresql+pg8000://ssluser:ssluser@hostname/test',
                          connect_args={'ssl_context': ssl_context})
                          


q = """\
SELECT ssl, version, cipher
FROM pg_stat_ssl
WHERE pid IN (SELECT pid
              FROM pg_stat_activity
              WHERE usename = 'ssluser')
"""

with engine.connect() as conn:
    with conn.begin():
        res = conn.execute(sa.text(q))
        for row in res:
            print(row)
        print()

Output:

(True, 'TLSv1.2', 'ECDHE-RSA-AES256-GCM-SHA384')

For completeness, a psycopg2 secure connection would look like this:

engine = sa.create_engine('postgresql+psycopg2://ssluser:ssluser@host/test?sslmode=require')