postgresql - SQLAlchemy 在 Unix 套接字或 IPv4 之前尝试通过 IPv6 连接 PostgreSQL

Question

如何确保我的 SQLAlchemy 与 PostgreSQL 的连接是通过 Unix 套接字甚至 IPv4 建立的？它只会通过 IPv6 连接。

我将 create_engine 配置为 @localhost 但注意底部的主机 IPv6 主机“::1”失败。我故意删除了 pg_hba.conf 中的 IPv6 信任(见下文)以显示错误。如果我取消注释该行，这将通过 IPv6 成功连接并跳过 Unix 和 IPv4。

>>> from sqlalchemy import *
db = create_engine('postgresql://vaderade@localhost:5432/mydb', echo=True)
>>> metadata = MetaData(db)
>>> users = Table('users', metadata,
... Column('user_id', Integer, primary_key=True),
... Column('name', String(40)),
... Column('age', Integer),
... Column('password', String),
... )
>>> users.create()
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1044, in _do_get
    return self._pool.get(wait, self._timeout)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/queue.py", line 145, in get
    raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2074, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 376, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 713, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 480, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1060, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1057, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 323, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 449, in __init__
    self.connection = self.__connect()
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 607, in __connect
    connection = self.__pool._invoke_creator(self)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 97, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 385, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.5/site-packages/psycopg2/__init__.py", line 164, in connect
    conn = _connect(dsn, connection_factory=connection_factory, async=async)
psycopg2.OperationalError: FATAL:  no pg_hba.conf entry for host "::1", user "vaderade", database "mydb", SSL off

相关信息来自/var/lib/pgsql/9.5/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
#host    all             all             ::1/128                 trust

相关信息来自/var/lib/pgsql/9.5/data/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = 'localhost'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

细节:

• 红帽 7.2
• PostgreSQL 9.5.4
• python 3.5.2
• SQLAlchemy==1.0.14
• psycopg2==2.6.2

Answer 1

当您使用 localhost 时，它将通过系统中配置的任何内容使用 TCP 连接来响应 localhost 作为。在 Linux 上，它通常映射到 /etc/hosts，通常是 IPv4 127.0.0.1 或 IPv6 ::1(因为它可能是您的例)。

现在，如果您想在本地连接，您可以选择:

• postgresql://vaderade@localhost:5432/mydb:通过 TCP 本地主机连接(如果 IPv4 或 IPv6 取决于您的系统)
• postgresql://vaderade@127.0.0.1:5432/mydb:通过 TCP IPv4 本地主机连接
• postgresql://vaderade@[::1]:5432/mydb:通过 TCP IPv6 本地主机连接
• postgresql://vaderade@:5432/mydb:通过默认的 unix 域套接字位置连接(即您正在寻找的答案)
• postgresql://vaderade@:5432/mydb?host=/path/to/socket:通过非默认位置的 unix 域套接字连接 /path/to/socket