Azure Postgres

#!/bin/bash
# Azure CLI commands for Azure PostgreSQL Entra ID authentication
# This file contains commonly used commands - copy/paste and modify as needed

# =============================================================================
# ENTRA ADMIN MANAGEMENT
# =============================================================================

# List Entra admins
# This will fail if Entra auth is not enabled yet
az postgres flexible-server microsoft-entra-admin list \
  --resource-group <resource-group> \
  --server-name <server-name>

# Get your own object ID (current signed-in user)
OBJECT_ID=$(az ad signed-in-user show --query id -o tsv)

# IMPORTANT: Must enable Entra auth before attempting to create an admin
az postgres flexible-server update \
  --subscription <subscription-id> \
  --resource-group <resource-group> \
  --name <server-name> \
  --microsoft-entra-auth Enabled

# Add Entra admin
# This will fail if Entra auth is not enabled yet
az postgres flexible-server microsoft-entra-admin create \
  --resource-group <resource-group> \
  --server-name <server-name> \
  --display-name "admin@domain.com" \
  --object-id $OBJECT_ID \
  --type User

# Remove Entra admin
az postgres flexible-server microsoft-entra-admin delete \
  --resource-group <resource-group> \
  --server-name <server-name> \
  --object-id <object-id>

# =============================================================================
# TOKEN ACQUISITION & CONNECTION
# =============================================================================

# Get access token (use as password)
az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv

# Connect to PostgreSQL with token
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)
psql "host=<server>.postgres.database.azure.com user=<user>@domain.com dbname=<database> sslmode=require"

# For sovereign clouds, find resource URL
az cloud show --query endpoints.ossrdbmsResourceId

# =============================================================================
# IDENTITY LOOKUPS
# =============================================================================

# Get user object ID
az ad user show --id user@domain.com --query id -o tsv

# Get group object ID
az ad group show --group "Group Name" --query id -o tsv

# Get managed identity object ID
az identity show --name <identity-name> --resource-group <rg> --query principalId -o tsv

# Get service principal object ID (use Enterprise App, not App Registration)
az ad sp show --id <app-id> --query id -o tsv

# =============================================================================
# SERVER PARAMETERS
# =============================================================================

# Enable group sync
az postgres flexible-server parameter set \
  --resource-group <resource-group> \
  --server-name <server-name> \
  --source user-override \
  --name pgaadauth.enable_group_sync \
  --value ON

# Disable group sync
az postgres flexible-server parameter set \
  --resource-group <resource-group> \
  --server-name <server-name> \
  --source user-override \
  --name pgaadauth.enable_group_sync \
  --value OFF

# Check current value
az postgres flexible-server parameter show \
  --resource-group <resource-group> \
  --server-name <server-name> \
  --name pgaadauth.enable_group_sync

# =============================================================================
# AUTHENTICATION MODE
# =============================================================================

# !!!IMPORTANT: Must enable Entra auth before attempting to create an admin

# Enable Entra-only authentication (disable password auth)
az postgres flexible-server update \
  --resource-group <resource-group> \
  --name <server-name> \
  --microsoft-entra-auth Enabled \
  --password-auth Disabled

# Enable both Entra and password authentication
az postgres flexible-server update \
  --resource-group <resource-group> \
  --name <server-name> \
  --microsoft-entra-auth Enabled \
  --password-auth Enabled

# Disable Entra authentication (password-only)
az postgres flexible-server update \
  --resource-group <resource-group> \
  --name <server-name> \
  --microsoft-entra-auth Disabled \
  --password-auth Enabled

#!/bin/bash
# Migrate existing PostgreSQL roles from password auth to Entra ID
# Usage: ./migrate-to-entra.sh <resource-group> <server-name>

set -e

# Parameters
RESOURCE_GROUP="${1:?Resource group required}"
SERVER_NAME="${2:?Server name required}"

echo "=== Azure PostgreSQL Migration to Entra ID ==="
echo "Resource Group: $RESOURCE_GROUP"
echo "Server: $SERVER_NAME"
echo ""
echo "WARNING: This script will help migrate password-based roles to Entra ID."
echo "Please ensure you have tested Entra authentication before running this."
echo ""

# Step 1: Verify server exists and get FQDN
echo "[1/5] Verifying server..."
SERVER_FQDN=$(az postgres flexible-server show \
  --resource-group "$RESOURCE_GROUP" \
  --name "$SERVER_NAME" \
  --query fullyQualifiedDomainName -o tsv)

if [ -z "$SERVER_FQDN" ]; then
  echo "ERROR: Server not found or not accessible"
  exit 1
fi
echo "Server FQDN: $SERVER_FQDN"

# Step 2: Check current authentication mode
echo ""
echo "[2/5] Checking authentication mode..."
AUTH_CONFIG=$(az postgres flexible-server show \
  --resource-group "$RESOURCE_GROUP" \
  --name "$SERVER_NAME" \
  --query authConfig -o json)

echo "Current auth config:"
echo "$AUTH_CONFIG" | jq .

# Step 3: Get admin credentials
echo ""
echo "[3/5] Getting current user info..."
CURRENT_USER=$(az ad signed-in-user show --query userPrincipalName -o tsv)
echo "Current user (Entra admin): $CURRENT_USER"

export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)
if [ -z "$PGPASSWORD" ]; then
  echo "ERROR: Failed to acquire access token. Are you an Entra admin?"
  exit 1
fi
echo "Token acquired"

# Step 4: List existing roles
echo ""
echo "[4/5] Listing existing PostgreSQL roles..."
echo ""
echo "=== Current PostgreSQL Roles ==="
psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -c \
  "SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole 
   FROM pg_roles 
   WHERE rolname NOT LIKE 'pg_%' 
   AND rolname NOT LIKE 'azure%' 
   AND rolname != 'replication'
   ORDER BY rolname;"

echo ""
echo "=== Existing Entra-Mapped Roles ==="
psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -c \
  "SELECT * FROM pgaadauth_list_principals(false);" 2>/dev/null || echo "No Entra roles found"

# Step 5: Provide migration instructions
echo ""
echo "[5/5] Migration Instructions"
echo ""
echo "=== Migration Steps ==="
echo ""
echo "For EACH role you want to migrate to Entra ID:"
echo ""
echo "1. Find the Azure AD object ID for the user/group/service principal:"
echo "   # For a user:"
echo "   az ad user show --id user@domain.com --query id -o tsv"
echo ""
echo "   # For a group:"
echo "   az ad group show --group 'Group Name' --query id -o tsv"
echo ""
echo "   # For a managed identity:"
echo "   az identity show --name <identity-name> --resource-group <rg> --query principalId -o tsv"
echo ""
echo "2. Map the existing role to the Entra identity using SECURITY LABEL:"
echo "   SECURITY LABEL for \"pgaadauth\" on role \"existing_role_name\" is 'aadauth,oid=<object-id>,type=user';"
echo ""
echo "   Object types: user, group, service (for managed identities and service principals)"
echo ""
echo "3. Test Entra authentication for the migrated role:"
echo "   export PGPASSWORD=\$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)"
echo "   psql \"host=$SERVER_FQDN user=user@domain.com dbname=<database> sslmode=require\""
echo ""
echo "4. After verifying Entra auth works, disable the password:"
echo "   ALTER ROLE \"existing_role_name\" PASSWORD NULL;"
echo ""
echo "5. Once all roles are migrated, switch to 'Microsoft Entra authentication only' mode:"
echo "   az postgres flexible-server update \\"
echo "     --resource-group $RESOURCE_GROUP \\"
echo "     --name $SERVER_NAME \\"
echo "     --microsoft-entra-auth Enabled \\"
echo "     --password-auth Disabled"
echo ""
echo "=== Example Migration Commands ==="
echo ""
echo "# Connect as Entra admin"
echo "export PGPASSWORD=\$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)"
echo "psql \"host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require\""
echo ""
echo "# Inside psql, run for each role:"
echo "-- Get object ID first: az ad user show --id developer@company.com --query id -o tsv"
echo "SECURITY LABEL for \"pgaadauth\" on role \"developer_role\" is 'aadauth,oid=abc12345-1234-1234-1234-123456789012,type=user';"
echo ""
echo "# Verify the mapping"
echo "SELECT * FROM pgaadauth_list_principals(false);"
echo ""
echo "# After testing Entra auth, disable password"
echo "ALTER ROLE \"developer_role\" PASSWORD NULL;"

#!/bin/bash
# Setup Group-based access for Azure PostgreSQL Flexible Server
# Usage: ./setup-group.sh <resource-group> <server-name> <group-name> <database> <permission-level> [enable-sync]
# Permission levels: readonly, readwrite, admin
# enable-sync: true/false (default: false)

set -e

# Parameters
RESOURCE_GROUP="${1:?Resource group required}"
SERVER_NAME="${2:?Server name required}"
GROUP_NAME="${3:?Group name required}"
DATABASE="${4:?Database name required}"
PERMISSION_LEVEL="${5:-readonly}"
ENABLE_SYNC="${6:-false}"

echo "=== Azure PostgreSQL Group Access Setup ==="
echo "Resource Group: $RESOURCE_GROUP"
echo "Server: $SERVER_NAME"
echo "Group: $GROUP_NAME"
echo "Database: $DATABASE"
echo "Permission Level: $PERMISSION_LEVEL"
echo "Enable Group Sync: $ENABLE_SYNC"
echo ""

# Step 1: Verify server exists and get FQDN
echo "[1/8] Verifying server..."
SERVER_FQDN=$(az postgres flexible-server show \
  --resource-group "$RESOURCE_GROUP" \
  --name "$SERVER_NAME" \
  --query fullyQualifiedDomainName -o tsv)

if [ -z "$SERVER_FQDN" ]; then
  echo "ERROR: Server not found or not accessible"
  exit 1
fi
echo "Server FQDN: $SERVER_FQDN"

# Step 2: Get group details from Azure AD
echo ""
echo "[2/8] Getting group details from Azure AD..."
GROUP_INFO=$(az ad group show --group "$GROUP_NAME" --query "{id:id, displayName:displayName}" -o json 2>/dev/null || echo "")

if [ -z "$GROUP_INFO" ] || [ "$GROUP_INFO" = "" ]; then
  echo "ERROR: Group '$GROUP_NAME' not found in Azure AD"
  exit 1
fi

GROUP_ID=$(echo "$GROUP_INFO" | jq -r '.id')
GROUP_DISPLAY_NAME=$(echo "$GROUP_INFO" | jq -r '.displayName')

echo "Group ID: $GROUP_ID"
echo "Group Display Name: $GROUP_DISPLAY_NAME"

# List group members
echo ""
echo "Group members:"
az ad group member list --group "$GROUP_NAME" --query "[].{displayName:displayName, userPrincipalName:userPrincipalName}" -o table

# Step 3: Check if Entra admin exists
echo ""
echo "[3/8] Checking Entra admin status..."
ADMIN_COUNT=$(az postgres flexible-server microsoft-entra-admin list \
  --resource-group "$RESOURCE_GROUP" \
  --server-name "$SERVER_NAME" \
  --query "length(@)" -o tsv)

if [ "$ADMIN_COUNT" -eq 0 ]; then
  echo "ERROR: No Entra admin configured. Add an Entra admin first."
  exit 1
fi
echo "Entra admin configured: $ADMIN_COUNT admin(s)"

# Step 4: Get current user info (must be Entra admin)
echo ""
echo "[4/8] Getting current user info..."
CURRENT_USER=$(az ad signed-in-user show --query userPrincipalName -o tsv)
echo "Current user: $CURRENT_USER"

# Step 5: Get access token
echo ""
echo "[5/8] Acquiring access token..."
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)

if [ -z "$PGPASSWORD" ]; then
  echo "ERROR: Failed to acquire access token"
  exit 1
fi
echo "Token acquired (valid for 5-60 minutes)"

# Step 6: Configure group sync parameter if requested
echo ""
echo "[6/8] Configuring group sync..."
if [ "$ENABLE_SYNC" = "true" ]; then
  az postgres flexible-server parameter set \
    --resource-group "$RESOURCE_GROUP" \
    --server-name "$SERVER_NAME" \
    --source user-override \
    --name pgaadauth.enable_group_sync \
    --value ON \
    --output none
  echo "Group sync ENABLED (members will get individual roles, auto-synced every 30 min)"
else
  echo "Group sync DISABLED (members use group name as username)"
fi

# Step 7: Create PostgreSQL role for the group
echo ""
echo "[7/8] Creating PostgreSQL role for group..."

# Check if role already exists
ROLE_EXISTS=$(psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -t -c \
  "SELECT 1 FROM pgaadauth_list_principals(false) WHERE objectid = '$GROUP_ID';" 2>/dev/null | tr -d ' ')

if [ "$ROLE_EXISTS" = "1" ]; then
  echo "Role already exists for group '$GROUP_DISPLAY_NAME'"
else
  # Create role using object ID
  psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -c \
    "SELECT * FROM pgaadauth_create_principal_with_oid('$GROUP_DISPLAY_NAME', '$GROUP_ID', 'group', false, false);"
  echo "Role created successfully"
fi

# If sync is enabled, trigger manual sync
if [ "$ENABLE_SYNC" = "true" ]; then
  echo ""
  echo "Triggering manual group member sync..."
  psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -c \
    "SELECT * FROM pgaadauth_sync_roles_for_group_members();" || echo "Note: Sync may take a moment to complete"
fi

# Step 8: Grant permissions based on level
echo ""
echo "[8/8] Granting $PERMISSION_LEVEL permissions on $DATABASE..."

# Escape the group name for PostgreSQL (handle spaces)
ESCAPED_GROUP_NAME=$(echo "$GROUP_DISPLAY_NAME" | sed 's/"/\\"/g')

case "$PERMISSION_LEVEL" in
  readonly)
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=$DATABASE sslmode=require" <<EOF
GRANT CONNECT ON DATABASE $DATABASE TO "$ESCAPED_GROUP_NAME";
GRANT USAGE ON SCHEMA public TO "$ESCAPED_GROUP_NAME";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "$ESCAPED_GROUP_NAME";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "$ESCAPED_GROUP_NAME";
EOF
    ;;
  readwrite)
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=$DATABASE sslmode=require" <<EOF
GRANT CONNECT ON DATABASE $DATABASE TO "$ESCAPED_GROUP_NAME";
GRANT USAGE ON SCHEMA public TO "$ESCAPED_GROUP_NAME";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "$ESCAPED_GROUP_NAME";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "$ESCAPED_GROUP_NAME";
EOF
    ;;
  admin)
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=$DATABASE sslmode=require" <<EOF
GRANT ALL PRIVILEGES ON DATABASE $DATABASE TO "$ESCAPED_GROUP_NAME";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "$ESCAPED_GROUP_NAME";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "$ESCAPED_GROUP_NAME";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO "$ESCAPED_GROUP_NAME";
EOF
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -c \
      "GRANT azure_pg_admin TO \"$ESCAPED_GROUP_NAME\";"
    ;;
  *)
    echo "ERROR: Invalid permission level. Use: readonly, readwrite, or admin"
    exit 1
    ;;
esac

echo ""
echo "=== Setup Complete ==="
echo ""
echo "Group: $GROUP_DISPLAY_NAME"
echo "Group ID: $GROUP_ID"
echo "Group Sync: $ENABLE_SYNC"
echo ""

if [ "$ENABLE_SYNC" = "true" ]; then
  echo "Group members can connect using their individual UPN:"
  echo "  export PGPASSWORD=\$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)"
  echo "  psql \"host=$SERVER_FQDN user=member@domain.com dbname=$DATABASE sslmode=require\""
  echo ""
  echo "Note: New group members are synced automatically every 30 minutes."
  echo "To force sync: SELECT * FROM pgaadauth_sync_roles_for_group_members();"
else
  echo "Group members connect using the GROUP NAME as username:"
  echo "  export PGPASSWORD=\$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)"
  echo "  psql \"host=$SERVER_FQDN user='$GROUP_DISPLAY_NAME' dbname=$DATABASE sslmode=require\""
  echo ""
  echo "Note: Spaces in group name must be escaped: 'Group\\ Name'"
fi

#!/bin/bash
# Setup Managed Identity access for Azure PostgreSQL Flexible Server
# Usage: ./setup-managed-identity.sh <resource-group> <server-name> <identity-name> <identity-resource-group> <database> <permission-level>
# Permission levels: readonly, readwrite, admin

set -e

# Parameters
RESOURCE_GROUP="${1:?Resource group required}"
SERVER_NAME="${2:?Server name required}"
IDENTITY_NAME="${3:?Managed identity name required}"
IDENTITY_RG="${4:?Identity resource group required}"
DATABASE="${5:?Database name required}"
PERMISSION_LEVEL="${6:-readwrite}"

echo "=== Azure PostgreSQL Managed Identity Setup ==="
echo "Resource Group: $RESOURCE_GROUP"
echo "Server: $SERVER_NAME"
echo "Managed Identity: $IDENTITY_NAME (in $IDENTITY_RG)"
echo "Database: $DATABASE"
echo "Permission Level: $PERMISSION_LEVEL"
echo ""

# Step 1: Verify server exists and get FQDN
echo "[1/7] Verifying server..."
SERVER_FQDN=$(az postgres flexible-server show \
  --resource-group "$RESOURCE_GROUP" \
  --name "$SERVER_NAME" \
  --query fullyQualifiedDomainName -o tsv)

if [ -z "$SERVER_FQDN" ]; then
  echo "ERROR: Server not found or not accessible"
  exit 1
fi
echo "Server FQDN: $SERVER_FQDN"

# Step 2: Get managed identity details
echo ""
echo "[2/7] Getting managed identity details..."
IDENTITY_INFO=$(az identity show \
  --name "$IDENTITY_NAME" \
  --resource-group "$IDENTITY_RG" \
  --query "{principalId:principalId, clientId:clientId}" -o json)

PRINCIPAL_ID=$(echo "$IDENTITY_INFO" | jq -r '.principalId')
CLIENT_ID=$(echo "$IDENTITY_INFO" | jq -r '.clientId')

if [ -z "$PRINCIPAL_ID" ] || [ "$PRINCIPAL_ID" = "null" ]; then
  echo "ERROR: Managed identity not found"
  exit 1
fi

echo "Principal ID (Object ID): $PRINCIPAL_ID"
echo "Client ID: $CLIENT_ID"

# Step 3: Check if Entra admin exists
echo ""
echo "[3/7] Checking Entra admin status..."
ADMIN_COUNT=$(az postgres flexible-server microsoft-entra-admin list \
  --resource-group "$RESOURCE_GROUP" \
  --server-name "$SERVER_NAME" \
  --query "length(@)" -o tsv)

if [ "$ADMIN_COUNT" -eq 0 ]; then
  echo "ERROR: No Entra admin configured. Add an Entra admin first."
  exit 1
fi
echo "Entra admin configured: $ADMIN_COUNT admin(s)"

# Step 4: Get current user info (must be Entra admin)
echo ""
echo "[4/7] Getting current user info..."
CURRENT_USER=$(az ad signed-in-user show --query userPrincipalName -o tsv)
echo "Current user: $CURRENT_USER"

# Step 5: Get access token
echo ""
echo "[5/7] Acquiring access token..."
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)

if [ -z "$PGPASSWORD" ]; then
  echo "ERROR: Failed to acquire access token"
  exit 1
fi
echo "Token acquired (valid for 5-60 minutes)"

# Step 6: Create PostgreSQL role for the managed identity
echo ""
echo "[6/7] Creating PostgreSQL role for managed identity..."

# Check if role already exists
ROLE_EXISTS=$(psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -t -c \
  "SELECT 1 FROM pgaadauth_list_principals(false) WHERE objectid = '$PRINCIPAL_ID';" 2>/dev/null | tr -d ' ')

if [ "$ROLE_EXISTS" = "1" ]; then
  echo "Role already exists for this managed identity"
  ROLE_NAME=$(psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -t -c \
    "SELECT rolename FROM pgaadauth_list_principals(false) WHERE objectid = '$PRINCIPAL_ID';" | tr -d ' ')
else
  # Create role using object ID (more reliable for managed identities)
  psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -c \
    "SELECT * FROM pgaadauth_create_principal_with_oid('$IDENTITY_NAME', '$PRINCIPAL_ID', 'service', false, false);"
  ROLE_NAME="$IDENTITY_NAME"
  echo "Role created successfully"
fi

echo "PostgreSQL role name: $ROLE_NAME"

# Step 7: Grant permissions based on level
echo ""
echo "[7/7] Granting $PERMISSION_LEVEL permissions on $DATABASE..."

case "$PERMISSION_LEVEL" in
  readonly)
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=$DATABASE sslmode=require" <<EOF
GRANT CONNECT ON DATABASE $DATABASE TO "$ROLE_NAME";
GRANT USAGE ON SCHEMA public TO "$ROLE_NAME";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "$ROLE_NAME";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "$ROLE_NAME";
EOF
    ;;
  readwrite)
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=$DATABASE sslmode=require" <<EOF
GRANT CONNECT ON DATABASE $DATABASE TO "$ROLE_NAME";
GRANT USAGE ON SCHEMA public TO "$ROLE_NAME";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "$ROLE_NAME";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "$ROLE_NAME";
EOF
    ;;
  admin)
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=$DATABASE sslmode=require" <<EOF
GRANT ALL PRIVILEGES ON DATABASE $DATABASE TO "$ROLE_NAME";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "$ROLE_NAME";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "$ROLE_NAME";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO "$ROLE_NAME";
EOF
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -c \
      "GRANT azure_pg_admin TO \"$ROLE_NAME\";"
    ;;
  *)
    echo "ERROR: Invalid permission level. Use: readonly, readwrite, or admin"
    exit 1
    ;;
esac

echo ""
echo "=== Setup Complete ==="
echo ""
echo "Managed Identity: $IDENTITY_NAME"
echo "PostgreSQL Role: $ROLE_NAME"
echo "Client ID: $CLIENT_ID"
echo ""
echo "Your application can now connect using Azure Identity SDK."
echo "See the examples/ folder for code samples in different languages."
echo ""
echo "Connection string format:"
echo "  host=$SERVER_FQDN;database=$DATABASE;user=$ROLE_NAME;sslmode=require"

#!/bin/bash
# Setup Entra ID user access for Azure PostgreSQL Flexible Server
# Usage: ./setup-user.sh <resource-group> <server-name> <user-upn> <database> <permission-level>
# Permission levels: readonly, readwrite, admin

set -e

# Parameters
RESOURCE_GROUP="${1:?Resource group required}"
SERVER_NAME="${2:?Server name required}"
USER_UPN="${3:?User UPN required (e.g., user@domain.com)}"
DATABASE="${4:?Database name required}"
PERMISSION_LEVEL="${5:-readonly}"

echo "=== Azure PostgreSQL Entra User Setup ==="
echo "Resource Group: $RESOURCE_GROUP"
echo "Server: $SERVER_NAME"
echo "User: $USER_UPN"
echo "Database: $DATABASE"
echo "Permission Level: $PERMISSION_LEVEL"
echo ""

# Step 1: Verify server exists and get FQDN
echo "[1/6] Verifying server..."
SERVER_FQDN=$(az postgres flexible-server show \
  --resource-group "$RESOURCE_GROUP" \
  --name "$SERVER_NAME" \
  --query fullyQualifiedDomainName -o tsv)

if [ -z "$SERVER_FQDN" ]; then
  echo "ERROR: Server not found or not accessible"
  exit 1
fi
echo "Server FQDN: $SERVER_FQDN"

# Step 2: Check if Entra admin exists
echo ""
echo "[2/6] Checking Entra admin status..."
ADMIN_COUNT=$(az postgres flexible-server microsoft-entra-admin list \
  --resource-group "$RESOURCE_GROUP" \
  --server-name "$SERVER_NAME" \
  --query "length(@)" -o tsv)

if [ "$ADMIN_COUNT" -eq 0 ]; then
  echo "ERROR: No Entra admin configured. Add an Entra admin first:"
  echo "  az postgres flexible-server microsoft-entra-admin create \\"
  echo "    --resource-group $RESOURCE_GROUP \\"
  echo "    --server-name $SERVER_NAME \\"
  echo "    --display-name <admin-upn> \\"
  echo "    --object-id <admin-object-id> \\"
  echo "    --type User"
  exit 1
fi
echo "Entra admin configured: $ADMIN_COUNT admin(s)"

# Step 3: Get current user info (must be Entra admin)
echo ""
echo "[3/6] Getting current user info..."
CURRENT_USER=$(az ad signed-in-user show --query userPrincipalName -o tsv)
echo "Current user: $CURRENT_USER"

# Step 4: Get access token
echo ""
echo "[4/6] Acquiring access token..."
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)

if [ -z "$PGPASSWORD" ]; then
  echo "ERROR: Failed to acquire access token"
  exit 1
fi
echo "Token acquired (valid for 5-60 minutes)"

# Step 5: Create PostgreSQL role for the user
echo ""
echo "[5/6] Creating PostgreSQL role for $USER_UPN..."

# Check if role already exists
ROLE_EXISTS=$(psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -t -c \
  "SELECT 1 FROM pgaadauth_list_principals(false) WHERE rolename = '$USER_UPN';" 2>/dev/null | tr -d ' ')

if [ "$ROLE_EXISTS" = "1" ]; then
  echo "Role already exists for $USER_UPN"
else
  psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -c \
    "SELECT * FROM pgaadauth_create_principal('$USER_UPN', false, false);"
  echo "Role created successfully"
fi

# Step 6: Grant permissions based on level
echo ""
echo "[6/6] Granting $PERMISSION_LEVEL permissions on $DATABASE..."

case "$PERMISSION_LEVEL" in
  readonly)
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=$DATABASE sslmode=require" <<EOF
GRANT CONNECT ON DATABASE $DATABASE TO "$USER_UPN";
GRANT USAGE ON SCHEMA public TO "$USER_UPN";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "$USER_UPN";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "$USER_UPN";
EOF
    ;;
  readwrite)
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=$DATABASE sslmode=require" <<EOF
GRANT CONNECT ON DATABASE $DATABASE TO "$USER_UPN";
GRANT USAGE ON SCHEMA public TO "$USER_UPN";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "$USER_UPN";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "$USER_UPN";
EOF
    ;;
  admin)
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=$DATABASE sslmode=require" <<EOF
GRANT ALL PRIVILEGES ON DATABASE $DATABASE TO "$USER_UPN";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "$USER_UPN";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "$USER_UPN";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO "$USER_UPN";
EOF
    psql "host=$SERVER_FQDN user=$CURRENT_USER dbname=postgres sslmode=require" -c \
      "GRANT azure_pg_admin TO \"$USER_UPN\";"
    ;;
  *)
    echo "ERROR: Invalid permission level. Use: readonly, readwrite, or admin"
    exit 1
    ;;
esac

echo ""
echo "=== Setup Complete ==="
echo ""
echo "User $USER_UPN can now connect using:"
echo ""
echo "  # Get token"
echo "  export PGPASSWORD=\$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)"
echo ""
echo "  # Connect"
echo "  psql \"host=$SERVER_FQDN user=$USER_UPN dbname=$DATABASE sslmode=require\""

{
  "schema_version": "2.0",
  "meta": {
    "generated_at": "2026-02-21T08:39:29.657Z",
    "slug": "microsoft-azure-postgres",
    "source_url": "https://github.com/microsoft/github-copilot-for-azure/tree/main/plugin/skills/azure-postgres/",
    "source_ref": "main",
    "model": "claude",
    "analysis_version": "3.0.0",
    "source_type": "community",
    "content_hash": "8b8a21bed230124b7d177b4bc5c4912494cb2b1c966c59e1f22b6ecb188eff97",
    "tree_hash": "d0454cfebc9af09e059ead912e63e7d7c7117c4483f555ec19c14d5a250e741f"
  },
  "skill": {
    "name": "azure-postgres",
    "description": "Create new Azure Database for PostgreSQL Flexible Server instances and configure passwordless authentication with Microsoft Entra ID. Set up developer access, managed identities for apps, group-based permissions, and migrate from password-based to Entra ID authentication. Trigger phrases include \"passwordless for postgres\", \"entra id postgres\", \"azure ad postgres authentication\", \"postgres managed identity\", \"migrate postgres to passwordless\".",
    "summary": "Configure passwordless Entra ID authentication for Azure PostgreSQL Flexible Server with managed identities and group-based access control",
    "icon": "📦",
    "version": "1.0.0",
    "author": "microsoft",
    "license": "MIT",
    "tags": [
      "azure",
      "postgresql",
      "entra-id",
      "passwordless",
      "managed-identity"
    ],
    "supported_tools": [
      "claude",
      "codex",
      "claude-code"
    ],
    "risk_factors": [
      "external_commands",
      "filesystem",
      "env_access",
      "network"
    ]
  },
  "security_audit": {
    "risk_level": "safe",
    "is_blocked": false,
    "safe_to_publish": true,
    "summary": "This is an official Microsoft skill providing Azure PostgreSQL documentation. All static findings are false positives: the detected patterns are Azure CLI commands and SQL code examples in markdown documentation, not executable code. The 'Windows SAM database' alerts are triggered by PostgreSQL SECURITY LABEL syntax. No malicious intent detected.",
    "critical_findings": [],
    "high_findings": [],
    "medium_findings": [],
    "low_findings": [],
    "dangerous_patterns": [],
    "files_scanned": 15,
    "total_lines": 1983,
    "audit_model": "claude",
    "audited_at": "2026-02-21T08:39:29.657Z",
    "risk_factors": [
      "scripts",
      "network",
      "filesystem",
      "env_access",
      "external_commands"
    ],
    "risk_factor_evidence": [
      {
        "factor": "external_commands",
        "evidence": [
          {
            "file": "SKILL.md",
            "line_start": 22,
            "line_end": 110
          }
        ]
      },
      {
        "factor": "filesystem",
        "evidence": [
          {
            "file": "SKILL.md",
            "line_start": 56,
            "line_end": 62
          }
        ]
      },
      {
        "factor": "env_access",
        "evidence": [
          {
            "file": "references/sdk/azure-postgres-ts.md",
            "line_start": 14,
            "line_end": 14
          }
        ]
      },
      {
        "factor": "network",
        "evidence": [
          {
            "file": "SKILL.md",
            "line_start": 56,
            "line_end": 62
          }
        ]
      }
    ]
  },
  "content": {
    "user_title": "Configure Azure PostgreSQL Passwordless Authentication",
    "value_statement": "Set up Microsoft Entra ID authentication for Azure PostgreSQL Flexible Server to eliminate password-based access and enable secure, passwordless connections for applications and developers.",
    "seo_keywords": [
      "Azure PostgreSQL",
      "passwordless authentication",
      "Entra ID postgres",
      "Microsoft Entra PostgreSQL",
      "Azure AD authentication",
      "postgres managed identity",
      "Claude Codex postgres",
      "claude-code azure postgres",
      "Azure flexible server postgres",
      "postgresql entra id setup"
    ],
    "actual_capabilities": [
      "Configure Entra ID authentication for existing Azure PostgreSQL Flexible Server instances",
      "Set up managed identity access for Azure-hosted applications",
      "Create user and group-based permissions using Microsoft Entra ID",
      "Migrate existing password-based authentication to passwordless Entra ID",
      "Execute SQL queries and manage database roles through Azure MCP tools or CLI",
      "Troubleshoot Entra ID authentication issues with diagnostic commands"
    ],
    "limitations": [
      "Does not create new PostgreSQL servers - focuses on configuring authentication for existing servers",
      "Requires Azure subscription with existing PostgreSQL Flexible Server",
      "Entra ID admin must be set up on the server before other users can be configured",
      "Does not manage PostgreSQL server provisioning or infrastructure"
    ],
    "use_cases": [
      {
        "title": "Enable Passwordless Authentication for Development Team",
        "description": "Configure Azure PostgreSQL to use Microsoft Entra ID so developers can connect without storing database passwords",
        "target_user": "Development team leads and DevOps engineers"
      },
      {
        "title": "Set Up Application Managed Identity Access",
        "description": "Configure Azure-hosted applications to authenticate to PostgreSQL using managed identities instead of credentials",
        "target_user": "Application developers and cloud architects"
      },
      {
        "title": "Migrate from Password to Entra ID Authentication",
        "description": "Move existing PostgreSQL users and applications from password-based auth to Microsoft Entra ID",
        "target_user": "Database administrators and security teams"
      }
    ],
    "prompt_templates": [
      {
        "title": "List PostgreSQL Servers",
        "prompt": "List all Azure Database for PostgreSQL Flexible Server instances in my subscription using the azure__postgres MCP tool",
        "scenario": "Quick inventory check"
      },
      {
        "title": "Configure Entra ID Admin",
        "prompt": "Set up Microsoft Entra ID as the authentication method for my Azure PostgreSQL server named {server-name} in resource group {rg-name}",
        "scenario": "Initial Entra ID setup"
      },
      {
        "title": "Create User with Entra ID",
        "prompt": "Create a new database user in Azure PostgreSQL for Entra ID user {user-email} with read-only access to database {db-name}",
        "scenario": "Adding new team member"
      },
      {
        "title": "Migrate to Passwordless Auth",
        "prompt": "Help me migrate my Azure PostgreSQL database from password authentication to Microsoft Entra ID passwordless authentication. Current server is {server-name} in {resource-group}",
        "scenario": "Authentication migration"
      }
    ],
    "output_examples": [
      {
        "input": "Configure Entra ID authentication for my Azure PostgreSQL server",
        "output": "I can help you set up Microsoft Entra ID authentication for Azure PostgreSQL. Here are the steps:\n\n1. Set Entra ID admin on your server:\n   az postgres flexible-server ad-admin create \\\n     --server-name YOUR_SERVER \\\n     --resource-group YOUR_RG \\\n     --display-name YOUR_ADMIN \\\n     --object-id ADMIN_OBJECT_ID\n\n2. Create database users from Entra identities:\n   SELECT * FROM pgaadauth_create_principal('user@domain.com', false, false);\n\nWould you like me to help with any specific step?"
      },
      {
        "input": "Set up managed identity for my app to access PostgreSQL",
        "output": "To configure managed identity access:\n\n1. Enable managed identity on your Azure app service:\n   az webapp identity assign --name APP_NAME --resource-group RG\n\n2. Create a database role for the managed identity:\n   SELECT * FROM pgaadauth_create_principal_with_oid(\n     'app-name',\n     'MANAGED-IDENTITY-OBJECT-ID',\n     'service',\n     false,\n     false\n   );\n\n3. Grant database permissions:\n   GRANT CONNECT ON DATABASE yourdb TO \"app-name\";\n   GRANT USAGE ON SCHEMA public TO \"app-name\";\n   GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"app-name\";"
      }
    ],
    "best_practices": [
      "Always use Microsoft Entra ID authentication instead of password-based access for production databases",
      "Enable managed identities for Azure-hosted applications to avoid storing credentials",
      "Use group-based access control in Entra ID to manage PostgreSQL permissions centrally",
      "Configure MFA requirements in Entra ID for additional security on database access"
    ],
    "anti_patterns": [
      "Do not use password-based authentication in production - it exposes credentials in connection strings",
      "Avoid creating individual database users for each application - use managed identities instead",
      "Do not grant excessive privileges like azure_pg_admin to application service principals",
      "Avoid hardcoding database credentials in application code - always use Entra ID or managed identities"
    ],
    "faq": [
      {
        "question": "What is Entra ID authentication for Azure PostgreSQL?",
        "answer": "Entra ID authentication is Microsoft's passwordless authentication method that allows users and applications to connect to Azure PostgreSQL using Azure AD identities instead of database passwords."
      },
      {
        "question": "How do I enable Entra ID on my PostgreSQL server?",
        "answer": "Use Azure CLI to create an Entra ID admin: az postgres flexible-server ad-admin create --server-name SERVER --resource-group RG --display-name ADMIN --object-id OBJECT_ID"
      },
      {
        "question": "What are managed identities and why use them?",
        "answer": "Managed identities are Azure-provided identities for Azure resources. They eliminate the need to store credentials in application code and automatically rotate, improving security."
      },
      {
        "question": "Can I migrate existing password users to Entra ID?",
        "answer": "Yes, you can map existing PostgreSQL roles to Entra identities using SECURITY LABEL commands, then drop the password authentication method."
      },
      {
        "question": "What MCP tools are available for Azure PostgreSQL?",
        "answer": "The azure__postgres MCP tool provides: postgres_server_list, postgres_database_list, postgres_database_query, postgres_server_param_get, and postgres_server_param_set."
      },
      {
        "question": "Does this skill create new PostgreSQL servers?",
        "answer": "No, this skill focuses on configuring authentication for existing Azure PostgreSQL Flexible Server instances. Server creation is handled separately."
      }
    ]
  },
  "file_structure": [
    {
      "name": "references",
      "type": "dir",
      "path": "references",
      "children": [
        {
          "name": "sdk",
          "type": "dir",
          "path": "references/sdk",
          "children": [
            {
              "name": "azure-identity-py.md",
              "type": "file",
              "path": "references/sdk/azure-identity-py.md",
              "lines": 26
            },
            {
              "name": "azure-identity-ts.md",
              "type": "file",
              "path": "references/sdk/azure-identity-ts.md",
              "lines": 23
            },
            {
              "name": "azure-postgres-ts.md",
              "type": "file",
              "path": "references/sdk/azure-postgres-ts.md",
              "lines": 28
            },
            {
              "name": "azure-resource-manager-postgresql-dotnet.md",
              "type": "file",
              "path": "references/sdk/azure-resource-manager-postgresql-dotnet.md",
              "lines": 29
            }
          ]
        },
        {
          "name": "entra-rbac-overview.md",
          "type": "file",
          "path": "references/entra-rbac-overview.md",
          "lines": 172
        },
        {
          "name": "group-sync.md",
          "type": "file",
          "path": "references/group-sync.md",
          "lines": 256
        },
        {
          "name": "permission-templates.md",
          "type": "file",
          "path": "references/permission-templates.md",
          "lines": 205
        },
        {
          "name": "sql-functions.md",
          "type": "file",
          "path": "references/sql-functions.md",
          "lines": 82
        },
        {
          "name": "troubleshooting.md",
          "type": "file",
          "path": "references/troubleshooting.md",
          "lines": 301
        }
      ]
    },
    {
      "name": "scripts",
      "type": "dir",
      "path": "scripts",
      "children": [
        {
          "name": "az-commands.sh",
          "type": "file",
          "path": "scripts/az-commands.sh",
          "lines": 122
        },
        {
          "name": "migrate-to-entra.sh",
          "type": "file",
          "path": "scripts/migrate-to-entra.sh",
          "lines": 126
        },
        {
          "name": "setup-group.sh",
          "type": "file",
          "path": "scripts/setup-group.sh",
          "lines": 194
        },
        {
          "name": "setup-managed-identity.sh",
          "type": "file",
          "path": "scripts/setup-managed-identity.sh",
          "lines": 158
        },
        {
          "name": "setup-user.sh",
          "type": "file",
          "path": "scripts/setup-user.sh",
          "lines": 136
        }
      ]
    },
    {
      "name": "SKILL.md",
      "type": "file",
      "path": "SKILL.md",
      "lines": 125
    }
  ]
}