使用psycopg2与SQLAlchemy连接AWS RDS的认证行为差异排查

背景信息 #

• 连接目标：AWS RDS PostgreSQL数据库
• 部署环境：Docker容器，同时在本地及AWS Batch环境运行
• 认证方式：本地使用个人IAM用户凭证；AWS Batch中Pod通过IAM角色获取凭证

连接测试结果 #

1. psql二进制工具 #

本地及AWS Batch均正常连接，命令如下：

export AWS_PROFILE="{YOUR_PROFILE}"
export RDSHOST="redacted.redacted.us-east-1.rds.amazonaws.com"
export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-east-1 --username myuser)"
psql "host=$RDSHOST port=5432 dbname=mydb user=myuser password=$PGPASSWORD"

连接成功终端输出：

psql (12.14 (Ubuntu 12.14-0ubuntu0.20.04.1), server 12.11)
SSL connection (protocol: TLSv1.2, cipher: <redacted>-SHA384, bits: 256, compression: off)

2. psycopg2脚本 #

本地及AWS Batch环境均能正常连接，执行查询无报错。

3. SQLAlchemy脚本 #

本地连接正常，但AWS Batch环境中报错：
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: PAM authentication failed for user "myuser"

测试脚本 #

import boto3  
import os  
import psycopg2  
from sqlalchemy import create_engine, text  
from sqlalchemy.orm import Session  

os.environ["AWS_DEFAULT_REGION"] = "us-east-1"  

# 配置信息  
ENDPOINT = "redacted.redacted.us-east-1.rds.amazonaws.com"  
PORT = 5432  
USER = "myuser"  
REGION = "us-east-1"  
DBNAME = "mydb"  

# 生成认证令牌  
session = boto3.Session()  
client = session.client('rds')  
token = client.generate_db_auth_token(DBHostname=ENDPOINT, Port=PORT, DBUsername=USER, Region=REGION)  

# psycopg2连接测试  
conn = psycopg2.connect(host=ENDPOINT, port=PORT, database=DBNAME, user=USER, password=token)  
cur = conn.cursor()  
cur.execute("""SELECT * FROM my_table""")  
query_results_psycopg2 = cur.fetchall()  

# SQLAlchemy连接测试  
engine_url = f"postgresql+psycopg2://{USER}:{token}@{ENDPOINT}:{PORT}/{DBNAME}?sslmode=require"  
db = create_engine(engine_url,  
                   connect_args={  
                       'sslmode': 'require',  
                   })  
with Session(db) as session:  
    query_results_sqlalchemy = session.execute(statement=text("SELECT * FROM my_table")).fetchall()  

# 仅本地环境断言成立
assert query_results_psycopg2 == query_results_sqlalchemy

依赖版本（pip freeze输出） #

aiobotocore==2.4.2
aiohttp==3.8.4
aiohttp-retry==2.8.3
aioitertools==0.11.0
aiosignal==1.3.1
amqp==5.1.1
antlr4-python3-runtime==4.9.3
appdirs==1.4.4
async-timeout==4.0.2
asyncssh==2.13.1
atpublic==3.1.1
attrs==22.2.0
audioread==2.1.8
awscli==1.18.223
billiard==3.6.4.0
boto3==1.24.59
botocore==1.27.59
celery==5.2.7
certifi==2022.12.7
cffi==1.15.1
charset-normalizer==3.1.0
click==8.1.3
click-didyoumean==0.3.0
click-plugins==1.1.1
click-repl==0.2.0
colorama==0.4.3
configobj==5.0.8
cryptography==40.0.1
cycler==0.11.0
Cython==0.29.23
dataclasses-json==0.5.7
decorator==5.1.1
Deprecated==1.2.13
dictdiffer==0.9.0
diskcache==5.4.0
distro==1.8.0
docutils==0.15.2
dpath==2.1.5
dulwich==0.21.3
dvc==2.50.0
dvc-data==0.44.1
dvc-http==2.30.2
dvc-objects==0.21.1
dvc-render==0.3.1
dvc-s3==2.21.0
dvc-studio-client==0.6.1
dvc-task==0.2.0
exceptiongroup==1.1.1
filelock==3.10.7
flatten-dict==0.4.2
flufl.lock==7.1.1
frozenlist==1.3.3
fsspec==2023.3.0
funcy==2.0
gitdb==4.0.10
GitPython==3.1.31
grandalf==0.8
greenlet==2.0.2
hmmlearn==0.2.6
hydra-core==1.3.2
idna==3.4
importlib-resources==5.12.0
iniconfig==2.0.0
iterative-telemetry==0.0.8
jmespath==0.10.0
joblib==1.2.0
jsonpatch==1.32
jsonpointer==2.3
jsonschema==4.17.3
kiwisolver==1.4.4
kombu==5.2.4
librosa==0.7.0
llvmlite==0.31.0
madmom==0.16.1
markdown-it-py==2.2.0
marshmallow==3.19.0
marshmallow-enum==1.5.1
marshmallow-sqlalchemy==0.29.0
matplotlib==3.3.2
mdurl==0.1.2
mido==1.2.10
multidict==6.0.4
mypy-extensions==1.0.0
nanotime==0.5.2
networkx==3.0
nptyping==1.4.4
numba==0.48.0
numpy==1.19.2
omegaconf==2.3.0
orjson==3.8.9
packaging==23.0
pandas==1.4.4
pathlib==1.0.1
pathspec==0.11.1
Pillow==9.4.0
pkgutil_resolve_name==1.3.10
platformdirs==3.2.0
pluggy==1.0.0
prompt-toolkit==3.0.38
psutil==5.9.4
psycopg2==2.9.5
py==1.11.0
pyasn1==0.4.8
pycparser==2.21
pydantic==1.10.5
pydot==1.4.2
pydub==0.25.1
pygit2==1.11.1
PyGithub==1.58.1
Pygments==2.14.0
pygtrie==2.5.0
PyJWT==2.6.0
PyNaCl==1.5.0
pyparsing==3.0.9
pyrsistent==0.19.3
pytest==7.2.2
python-dateutil==2.8.2
pytz==2023.3
PyYAML==5.3.1
requests==2.28.2
resampy==0.3.1
rich==13.3.3
rsa==4.5
ruamel.yaml==0.17.21
ruamel.yaml.clib==0.2.7
s3fs==2023.3.0
s3transfer==0.6.0
scikit-learn==0.23.2
scipy==1.5.2
scmrepo==0.1.17
shortuuid==1.0.11
shtab==1.5.8
six==1.16.0
smmap==5.0.0
SoundFile==0.10.3.post1
sox==1.4.1
SQLAlchemy==2.0.6
sqltrie==0.3.0
tabulate==0.9.0
threadpoolctl==3.1.0
tomli==2.0.1
tomlkit==0.11.7
tqdm==4.65.0
typing-inspect==0.8.0
typing_extensions==4.5.0
typish==1.9.3
urllib3==1.26.15
vine==5.0.0
voluptuous==0.13.1
warlock==2.0.1
wcwidth==0.2.6
wrapt==1.15.0
yarl==1.8.2
zc.lockfile==3.0.post1
zipp==3.15.0

Docker容器内psql版本 #

root@94af66abe2d2:/usr/src/app# psql --version
psql (PostgreSQL) 13.9 (Debian 13.9-0+deb11u1)

问题 #

为何psycopg2在本地和AWS Batch环境均能正常连接，而SQLAlchemy仅本地正常，在AWS Batch中出现PAM认证失败？两者使用同一认证令牌且处于相同Docker环境，是否为SQLAlchemy连接字符串的问题？

内容的提问来源于stack exchange，提问作者sigma