SQLAlchemy verify SSL connection

Question

I would like to verify the SSL connection that SQLAlchemy sets up when using create_engine to connect to a PostgreSQL database. For example, if I have the following Python 3 code:

from sqlalchemy import create_engine

conn_string = "postgresql+psycopg2://myuser:******@someserver:5432/somedb"

conn_args = {
    "sslmode": "verify-full",
    "sslrootcert": "/etc/ssl/certs/ca-certificates.crt",
}

engine = create_engine(conn_string, connect_args=conn_args)

I know that I can print the contents of engine.__dict__, but it doesn't contain any information about the SSL settings (TLS version, cipher suite, etc) that it's using to connect:

{
    '_echo': False,
    'dialect': <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x7f988a217978>,
    'dispatch': <sqlalchemy.event.base.ConnectionEventsDispatch object at 0x7f988938e788>,
    'engine': Engine(postgresql+psycopg2://myuser:******@someserver:5432/somedb),
    'logger': <Logger sqlalchemy.engine.base.Engine (DEBUG)>,
    'pool': <sqlalchemy.pool.impl.QueuePool object at 0x7f988a238c50>,
    'url': postgresql+psycopg2://myuser:******@someserver:5432/somedb
}

I know I can do something like SELECT * FROM pg_stat_ssl;, but does the SQLAlchemy engine store this kind of information as a class attribute / method?

Thank you!

Answer 1

I don't use postgres so hopefully this holds true for you.

SQLAlchemy takes the info that you provide in the url and passes it down to the underlying dbapi library that is also specified in the url, in your case it's psycopg2.

Your engine instance only connects to the database when needed, and sqlalchemy just passes the connection info along to the driver specified in the url which returns a connection that sqlalchemy uses.

Forgive that this is mysql, but should be fundamentally the same for you:

>>> engine
Engine(mysql+mysqlconnector://test:***@localhost/test)
>>> conn = engine.connect()
>>> conn
<sqlalchemy.engine.base.Connection object at 0x000001614ACBE2B0>
>>> conn.connection
<sqlalchemy.pool._ConnectionFairy object at 0x000001614BF08630>
>>> conn.connection.connection
<mysql.connector.connection_cext.CMySQLConnection object at 0x000001614AB7E1D0>

Calling engine.connect() returns a sqlalchemy.engine.base.Connection instance that has a connection property for which the docstring says:

The underlying DB-API connection managed by this Connection.

However, you can see from above that it actually returns a sqlalchemy.pool._ConnectionFairy object which from it's docstring:

Proxies a DBAPI connection...

Here is the __init__() method of the connection fairy, and as you can see it has a connection attribute that is the actual underlying dbapi connection.

def __init__(self, dbapi_connection, connection_record, echo):
    self.connection = dbapi_connection
    self._connection_record = connection_record
    self._echo = echo

As to what info is available on the dbapi connection object, it depends on the implementation of that particular driver. E.g psycopg2 connection objects have an info attribute:

A ConnectionInfo object exposing information about the native libpq connection.

That info object has attributes such as ssl_in_use:

True if the connection uses SSL, False if not.

And ssl_attribute:

Returns SSL-related information about the connection.

So you don't have to dig too deep to get at the actual db connection to see what is really going on.

Also, if you want to ensure that all client connections are ssl, you can always force them to.

Answer 2

Here´s a quick and dirty of what SuperShoot spelled out in detail:

>>> from sqlalchemy import create_engine
>>> db_string = "postgresql+psycopg2://myuser:******@someserver:5432/somedb"
>>> db = create_engine(db_string)
>>> conn = db.connect()
>>> conn.connection.connection.info.ssl_in_use

Should return True if using SSL.