Search code examples
pythonpostgresqlazuresqlalchemyazure-active-directory

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?

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?

Solution

  • FATAL: password authentication failed for user ""

    I tried to log in to the database with incorrect DB admin credentials in Python, but I encountered the same result.

    enter image description here

    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:

    enter image description here