Azpg roles rbac

Access control & Entra auth on Azure Database for PostgreSQL Flexible Server

Flexible Server uses PostgreSQL's native role system, with two Azure-specific twists: there is no customer superuser (the azure_pg_admin pseudo-superuser role is the ceiling), and roles can be backed by Microsoft Entra ID for passwordless, centrally-governed authentication instead of (or alongside) local passwords.

This skill is read-write and explicit. Listing roles, grants, and memberships changes nothing and is auto-runnable. CREATE ROLE, GRANT, REVOKE, ALTER DEFAULT PRIVILEGES, and creating Entra principals mutate the security posture — an over-broad GRANT or a wrong REVOKE can either expose data or lock out an application — so every write is guarded and deliberate.

Live instance context (dynamic injection)

Ground access decisions in the roles and grants that actually exist:

• Roles and their attributes (login? superuser-ish? can create roles/dbs?): !psql "$PGCONN" -tAc "SELECT rolname, rolcanlogin, rolcreaterole, rolcreatedb, rolinherit FROM pg_roles WHERE rolname NOT LIKE 'pg\_%' ORDER BY rolname;"
• Who holds the keys — members of the azure_pg_admin pseudo-superuser role: !psql "$PGCONN" -tAc "SELECT m.rolname FROM pg_auth_members am JOIN pg_roles r ON r.oid=am.roleid JOIN pg_roles m ON m.oid=am.member WHERE r.rolname='azure_pg_admin';"
• Is Entra authentication enabled on the server? !az postgres flexible-server show --resource-group "$RG" --name "$SERVER" --query "authConfig" -o json

Anyone in azure_pg_admin effectively owns the database — keep that list small.

When to use this skill

• "Create a read-only user for analytics / a read-write user for the app."
• "Set up least privilege — the app shouldn't be able to DROP tables."
• "Let our app connect with its managed identity, no password."
• "Add <person/group> as a database administrator."
• "Audit who can read the customers table."
• "New tables aren't visible to the app role" → ALTER DEFAULT PRIVILEGES.

Decision flow

1. Inventory current access (read · auto) — scripts/inspect_roles.sql: roles, attributes, group memberships, azure_pg_admin members, and per-schema grants. You can't design least privilege without seeing the baseline.
2. Design roles as groups, not individuals (read · auto) — a NOLOGIN group role per access level (app_readwrite, app_readonly), with login roles / Entra principals made members. Grants attach to the group; people/services come and go without re-granting.
3. Choose the auth model (read · auto) — local password vs Entra (passwordless). For app identities prefer Entra + managed identity: no secret to leak or rotate.
4. Apply grants least-privilege (write · guarded · explicit) — scripts/create_role.sh emits transaction-wrapped CREATE ROLE + scoped GRANT + ALTER DEFAULT PRIVILEGES, --dry-run by default. Grant USAGE on schema + the specific privileges needed, never ALL / SUPERUSER, never ownership to an app.
5. Wire Entra identities (write · guarded · explicit) — add the Entra server admin with az … microsoft-entra-admin create, and create Entra-backed roles in-database with pgaadauth_create_principal(...); then add them to the group role.
6. Verify the boundary (read · auto) — re-run inspect_roles.sql; ideally confirm the app role is denied what it shouldn't have (e.g. it can SELECT but a DROP fails).

Role design cheat-sheet

Goal	Pattern
Reusable access tier	CREATE ROLE app_readonly NOLOGIN; then GRANT to it; add members.
Read-only analytics	GRANT USAGE ON SCHEMA app TO app_readonly; GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly; + default privileges.
App read/write (no DDL)	GRANT SELECT, INSERT, UPDATE, DELETE … — not ALL, so no TRUNCATE/REFERENCES/ownership.
Future tables inherited	ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly;
Passwordless app login	Entra role via pgaadauth_create_principal + managed identity token.
Human/group admin	az … microsoft-entra-admin create (member of azure_pg_admin).

Safety protocol

1. Least privilege by construction. Start from zero and add the specific privileges an identity needs. GRANT ALL, SUPERUSER, CREATEROLE, or making an app role a table owner are almost always wrong — an owner/ALL grantee can drop or truncate your data.
2. create_role.sh is dry-run by default and transaction-wrapped. It prints the CREATE ROLE/GRANT SQL inside BEGIN … COMMIT; nothing runs until --apply. A wrong grant set rolls back cleanly.
3. Group roles over per-person grants. Attaching privileges to individuals creates unauditable sprawl and orphaned access. Grant to a group; manage membership.
4. REVOKE can lock out the app. Before revoking, confirm from inspect_roles.sql that no live login role depends on the grant. Revoking the wrong grant is an outage.
5. Guard azure_pg_admin membership. It's the pseudo-superuser — the closest thing to root here. Only true administrators belong; never add an application role to it.
6. Prefer Entra + managed identity for services. A passwordless token-based login removes a stored secret entirely; it's more secure than a password in a connection string. See reference.md for the token flow.

Bundled files

• reference.md — the no-superuser model and azure_pg_admin; group-role recipes; ALTER DEFAULT PRIVILEGES; the full Entra story (server admin, pgaadauth_* functions, managed-identity token login), password vs Entra.
• azure-constraints.md — no superuser / no azuresu; what azure_pg_admin can and can't do; Entra prerequisites; roles Azure manages; pg_signal_backend for termination rights.
• scripts/inspect_roles.sql — read-only audit of roles, attributes, memberships, azure_pg_admin, and grants.
• scripts/create_role.sh — guarded write; emits transaction-wrapped least-privilege CREATE ROLE/GRANT/default-privileges SQL, --dry-run default, --apply to execute.
• scripts/README.md — script catalog + safety class.
• examples/app_least_privilege.md — worked "read-only analytics user + read/write app role via Entra managed identity", including verifying the deny boundary.
• EVALUATION.md — trigger prompts, expected behaviour, conventions self-review.