Postgres

EntraID Admin Login

• Databases live in Isolated subnets, and connections are only allowed from Protected subnets in the same VNet.

  • The guide below shows how to connect as SQL Server EntraID admin through the use of Ubuntu VMs (bastion VM in public subnet + private VM in protected subnet).

  • See the Networks section for further details on network segmentation.

• The admin AD group of each environment are assigned SQL Server EntraID Admin permissions and can connect through generating a temporary access token.

  • In environments where a Bastion VM has been configured, the admin AD group also has EntraID SSH access to the Bastion.

Pre-requisites

• Must be a member of the ms-<customer>-<environment>-admin group for the corresponding environment.

• Private connectivity to the Protected subnet.

  • In this example we use 2 VMs; one in a public subnet and one in a protected subnet, to reach the database.

  • We've also added a rule to the environments public NSG, allowing inbound SSH from our local IP.

• Installed the required packages on the Protected VM (or equivalent):

  • sudo apt install postgresql-client

  • curl -sL https://aka.ms/InstallAzureCLIDeb | sudo bash

Steps

Step 1 - Access the protected VM

1. Connect to the public Bastion VM using EntraID SSH.

   • az login

   • az ssh vm -n <vm_name> -g <vm_resource_group_name>

2. Jump from the Bastion VM to the Protected VM (adjust key and private IP accordingly).

   • sudo ssh -i /home/azureuser/ms-example-stg-baprot-noe-vm-key.pem [email protected]

Step 2 - Connect to SQL Server

1. az login

   • You will be prompted to open a link in your local browser and sign in.

2. export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query "[accessToken]" -o tsv)

   • Generates and sets the temporary access token.

3. az logout

   • Once the temporary access token has been generated you don't need the azcli session anymore.

4. psql "host=ms-<customer_name>-<environment>-pgres-<region>-server.postgres.database.azure.com user=ms-<customer>-<environment>-admin dbname=postgres sslmode=require"

   • You must connect to the postgres DB to manage EntraID DB Users.

5. Connected!

EntraID User Creation

• Used by EntraID principals to access databases through EntraID auth.

  • Supports various EntraID entities like User Assigned + System Assigned Identities, AD Users, and Groups.

• Allows DB connectivity through generating temporary access tokens instead of traditional DB Users with static credentials.

Prerequisites

• Connected to the postgres DB as an EntraID SQL Admin.

  • See Entra ID Admin Login for instructions.

• Existing Managed Identity that you want to create a User for.

  • If you attempt to create an EntraID SQL User for a Managed Identity Name that doesn’t exist within your tenant, step 1 will throw an error.

Steps

1. Create the EntraID Managed Identity DB User:

   • select * from pg_catalog.pgaadauth_create_principal(roleName text, isAdmin boolean, isMfa boolean)

2. Grant the necessary permissions to the <entra_principal_name> User (same as any other DB User).

3. Done!

Example - Python App

• An SQL EntraID User is created for the User Assigned Managed Identity associated to this example app.

  • The environment variable AZURE_CLIENT_ID has been set to the client id of the UAMI.

• Sample Python code that generates a temporary access token using the Azure Identity client library for Python.

select * from pgaadauth_create_principal('ms-example-stg-app-noe-uami', false, false);
GRANT CREATE, CONNECT ON DATABASE test_db TO "ms-example-stg-app-noe-uami";

Sample Python code for connecting to DB

import psycopg
import os
from azure.identity import DefaultAzureCredential

print("getting env values...")
DATABASE_NAME     = os.getenv("DATABASE_NAME")
DATABASE_USER     = os.getenv("AZURE_CLIENT_NAME")
ENVIRONMENT       = os.getenv("ENVIRONMENT")
REGION            = os.getenv("REGION")
SQL_SERVER_DOMAIN = f"ms-example-{ENVIRONMENT}-pgres-{REGION}-server.postgres.database.azure.com"
SCHEMA_NAME       = "test123"
TABLE_NAME        = "test_table"

try:
    print("getting UAMI auth token...")
    credential = DefaultAzureCredential()
    token = credential.get_token("https://ossrdbms-aad.database.windows.net/.default").token
except Exception as e:
    print(f"UAMI session token error: {e}")

conn_string = (
    f"host={SQL_SERVER_DOMAIN} "
    f"dbname={DATABASE_NAME} "
    f"user={DATABASE_USER} "
    f"password={token} "
    f"sslmode=require"
)

try:
    print("writing to DB...")
    conn = psycopg.connect(conn_string)
    with conn.cursor() as cur:
        cur.execute(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA_NAME}")
        cur.execute(f"CREATE TABLE IF NOT EXISTS {SCHEMA_NAME}.{TABLE_NAME} (id SERIAL PRIMARY KEY, data TEXT)")
        cur.execute(f"INSERT INTO {SCHEMA_NAME}.{TABLE_NAME} (data) VALUES ('Example DB entry from UAMI')")
        conn.commit()
except Exception as e:
    print(f"DB conn error: {e}")
finally:
    conn.close()