Identifying Azure AD Users in Azure PostgreSQL

Azure PostgreSQL with Entra (Azure AD) authentication is great for security. But when troubleshooting access issues, you need to know which principals actually have database access.

Azure AD Users in PostgreSQL

When you create an Azure AD user in PostgreSQL:

CREATE ROLE "[email protected]" WITH LOGIN IN ROLE azure_ad_user;

The user is stored in pg_roles but with some nuances.

Listing All Azure AD Users

-- List all Azure AD principals
SELECT
  r.rolname as principal_name,
  r.rolcanlogin as can_login,
  r.rolcreaterole as can_create_roles,
  r.rolinherit as inherits_privileges,
  ARRAY_AGG(m.rolname) as member_of
FROM pg_roles r
LEFT JOIN pg_auth_members am ON r.oid = am.member
LEFT JOIN pg_roles m ON am.roleid = m.oid
WHERE r.rolname LIKE '%@%'  -- Email format indicates Azure AD
   OR r.rolname IN (SELECT rolname FROM pg_roles WHERE pg_has_role(r.oid, oid, 'member') AND rolname = 'azure_ad_user')
GROUP BY r.rolname, r.rolcanlogin, r.rolcreaterole, r.rolinherit;

Finding Azure AD Groups

Azure AD groups appear as roles too:

-- Check if a role is backed by an Azure AD group
SELECT
  rolname,
  CASE
    WHEN rolname LIKE '%@%' THEN 'User'
    WHEN EXISTS (
      SELECT 1 FROM pg_roles member
      WHERE pg_has_role(member.oid, pg_roles.oid, 'member')
        AND member.rolname LIKE '%@%'
    ) THEN 'Group (has user members)'
    ELSE 'Local role or Service Principal'
  END as principal_type
FROM pg_roles
WHERE pg_has_role(oid, 'azure_ad_user', 'member');

Getting Object IDs

Azure AD principals can also be referenced by their Object ID. To find the mapping:

-- Create an extension to help (requires admin)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- The Object ID isn't directly stored, but you can query Azure AD via Azure CLI
-- From your admin workstation:

# Get Object ID for a user
az ad user show --id "[email protected]" --query id -o tsv

# Get Object ID for a group
az ad group show --group "DB-Readers" --query id -o tsv

# Find principal by Object ID
az ad signed-in-user show --query id

Checking Specific User Access

-- What roles does a specific user have?
SELECT
  r.rolname as role,
  m.rolname as member_of
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.member
JOIN pg_roles m ON am.roleid = m.oid
WHERE r.rolname = '[email protected]';

-- What tables can they access?
SELECT
  grantee,
  table_schema,
  table_name,
  privilege_type
FROM information_schema.table_privileges
WHERE grantee = '[email protected]'
   OR grantee IN (
     SELECT m.rolname
     FROM pg_auth_members am
     JOIN pg_roles r ON am.member = r.oid
     JOIN pg_roles m ON am.roleid = m.oid
     WHERE r.rolname = '[email protected]'
   );

Common Access Patterns

Direct User Access

-- Grant direct access to a user
CREATE ROLE "[email protected]" WITH LOGIN IN ROLE azure_ad_user;
GRANT CONNECT ON DATABASE mydb TO "[email protected]";
GRANT USAGE ON SCHEMA public TO "[email protected]";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "[email protected]";

Group-Based Access (Recommended)

-- Create a role for readers
CREATE ROLE db_readers;
GRANT CONNECT ON DATABASE mydb TO db_readers;
GRANT USAGE ON SCHEMA public TO db_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_readers;

-- Add Azure AD group to the role
CREATE ROLE "[email protected]" WITH LOGIN IN ROLE azure_ad_user;
GRANT db_readers TO "[email protected]";

Troubleshooting Access Issues

User Can't Connect

-- Check if user exists
SELECT * FROM pg_roles WHERE rolname = '[email protected]';

-- Check if they have login permission
SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = '[email protected]';

-- Check if they have CONNECT
SELECT
  datname,
  has_database_privilege('[email protected]', datname, 'CONNECT') as can_connect
FROM pg_database
WHERE datname = current_database();

User Can't See Tables

-- Check schema access
SELECT has_schema_privilege('[email protected]', 'public', 'USAGE');

-- Check table permissions
SELECT
  table_name,
  has_table_privilege('[email protected]', 'public.' || table_name, 'SELECT') as can_select
FROM information_schema.tables
WHERE table_schema = 'public';

Audit Query

Complete audit of all Azure AD access:

-- Full access audit
WITH azure_ad_principals AS (
  SELECT rolname, oid
  FROM pg_roles
  WHERE rolname LIKE '%@%'
     OR pg_has_role(oid, 'azure_ad_user', 'member')
)
SELECT
  p.rolname as principal,
  'database' as object_type,
  current_database() as object_name,
  has_database_privilege(p.rolname, current_database(), 'CONNECT') as connect,
  has_database_privilege(p.rolname, current_database(), 'CREATE') as create_schema
FROM azure_ad_principals p

UNION ALL

SELECT
  p.rolname,
  'schema',
  s.schema_name,
  has_schema_privilege(p.rolname, s.schema_name, 'USAGE'),
  has_schema_privilege(p.rolname, s.schema_name, 'CREATE')
FROM azure_ad_principals p
CROSS JOIN information_schema.schemata s
WHERE s.schema_name NOT IN ('pg_catalog', 'information_schema')

ORDER BY principal, object_type, object_name;

Need help with Azure database security? Get in touch - we help organisations implement secure data access patterns.