英文:
Connecting to Postgres via python using AAD
问题
If I am a member of an AD group that has an AD role defined and connect/select privileges on a Postgres DB, how can I use SQL Alchemy to connect to the DB with my username/pass?
如果我是一个拥有 AD 角色定义和 Postgres 数据库上的连接/选择权限的 AD 组的成员，我如何使用 SQL Alchemy 使用我的用户名/密码连接到数据库？
If I just give my username/password, it tells me:
如果我只提供用户名/密码，它会告诉我：
FATAL: password authentication failed for user "<user>"
致命错误：用户 "<user>" 的密码验证失败。
How can I tell it to use AAD for authentication?
我如何告诉它使用 AAD 进行身份验证？
英文:
If I am a member of an AD group that has an AD role defined and connect/select privileges on a Postgres DB, how can I use SQL Alchemy to connect to the DB with my username/pass?
If I just give my username/password, it tells me:
FATAL: password authentication failed for user "<user>"
How can I tell it to use AAD for authentication?
答案1
得分: 0
Here is the translation of the content you provided:
> FATAL: 用户“<user>”的密码验证失败
我尝试使用错误的 DB 管理员凭据在 Python 中登录数据库，但遇到了相同的结果。
为了使用 Azure AD 凭据进行身份验证，您需要使用访问令牌。
我已经按照这个MS Doc1和MS Doc 2来连接 PostgreSQL 使用 Azure AD 身份验证。
在连接时，最好将访问令牌用作 PostgreSQL 用户密码。
以下是获取访问令牌的命令。
az account get-access-token --resource https://ossrdbms-aad.database.windows.net
在运行 Python 代码之前，请使用以下命令安装模块。
pip install sqlalchemy psycopg2
连接到 PostgreSQL 的 Python 代码
import psycopg2
host = 'sampledatabase.postgres.database.azure.com'
dbname = 'postgres'
user = 'user.com'
password = 'Accesstoken'
sslmode = 'require'
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string)
print("连接已建立")
输出：
英文:
> FATAL: password authentication failed for user "<user>"
I tried to log in to the database with incorrect DB admin credentials in Python, but I encountered the same result.
In order to authenticate with Azure AD credentials, you need to use an access token.
I have followed this MS Doc1 & MS Doc 2 to connect PostgreSQL with Azure AD authentication.
When connecting, it's best to use the access token as the PostgreSQL user password.
Here is the command to get the access token.
az account get-access-token --resource https://ossrdbms-aad.database.windows.net
Install the module using the command below before running the Python code.
pip install sqlalchemy psycopg2
Python code to connect PostgreSQL
import psycopg2
host = 'sampledatabase.postgres.database.azure.com'
dbname = 'postgres'
user = 'user.com'
password = 'Accesstoken'
sslmode = 'require'
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string)
print("Connection established")
Output:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论