SQLAlchemy验证SSL连接 [英] SQLAlchemy verify SSL connection

问题描述

我想验证使用 create_engine 连接到PostgreSQL数据库时SQLAlchemy设置的SSL连接。例如，如果我具有以下Python 3代码：

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)

我知道我可以打印 engine .__ dict __ 的内容，但是不包含用于连接的SSL设置（TLS版本，密码套件等）的任何信息：

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
}

我知道我可以执行类似 SELECT * FROM pg_stat_ssl; 的操作，但是SQLAlchemy引擎是否将此类信息存储为类属性/方法？

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?

谢谢！

推荐答案

我不使用postgres，所以希望这适用

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

SQLAlchemy接受您在URL中提供的信息，并将其传递给也在该URL中指定的基础dbapi库，在您的情况下为psycopg2

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.

您的 engine 实例仅在需要时连接到数据库，而sqlalchemy只是将连接信息传递给url中指定的驱动程序，该驱动程序返回sqlalchemy使用的连接。

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 tha t这是mysql，但对您来说应该基本相同：

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>

调用 engine.connect（）返回一个 sqlalchemy.engine.base.Connection 实例具有 连接属性 ，文档字符串说：

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

此连接管理的基础DB-API连接。

The underlying DB-API connection managed by this Connection.

但是，您可以从除此之外，它实际上会返回 sqlalchemy.pool._ConnectionFairy 对象，该对象来自其文档字符串：

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

代理DBAPI连接...

Proxies a DBAPI connection...

这是 __ init __（） 连接方法仙女，并且您可以看到它的 connection 属性，它是实际的基础dbapi连接。

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

关于dbapi连接对象上可用的信息，取决于特定驱动程序的实现。例如，psycopg2连接对象具有 info 属性：

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 对象公开了有关本机libpq
连接的信息。

A ConnectionInfo object exposing information about the native libpq connection.

该 info 对象具有诸如 ssl_in_use ：

That info object has attributes such as ssl_in_use:

如果连接使用SSL，则为真，否则为False。

True if the connection uses SSL, False if not.

href = http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.ConnectionInfo.ssl_attribute rel = noreferrer> ssl_attribute ：

And ssl_attribute:

返回与SSL相关的信息有关连接的信息。

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.

此外，如果您要确保所有客户端连接都是ssl，则可以始终 将其强制为 。

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

这篇关于SQLAlchemy验证SSL连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！