DB Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

===== Entra ID Authentication =====
[[https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-manage-azure-ad-users]]
==== Logon ====

<code powershell>
az login
az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken
$env:PGPASSWORD='<Access_Token>'
psql "host=server.postgres.database.azure.com user=postgres_users dbname=postgres sslmode=require"
</code>

==== Administration ====
<code postgresql>
-- Display all existing Entra principals
select * from pg_catalog.pgaadauth_list_principals(false);

-- Add Entra principal to the server, "roleName" must match the name of an existing Entra principal
select * from pg_catalog.pgaadauth_create_principal(roleName text, isAdmin boolean, isMfa boolean)
select * from pg_catalog.pgaadauth_create_principal('postgres_users', false, false)
</code>

<code postgresql>
-- Enable Microsoft Entra authentication for an existing PostgreSQL role
SECURITY LABEL for "pgaadauth" on role "postgres_admin" is 'aadauth,oid=<objectId>,type=<objectType>,admin';
</code>