Roles, Ownership, and Least Privilege
PostgreSQL roles represent identities and privilege groups. Object owners can alter or drop what they own, so application logins should not own schemas or tables. A least-privilege design separates deployment ownership, migration, runtime read/write, read-only reporting, support, backup, replication, and administration, then verifies both allowed and denied actions as each real role.
What will you be able to do?
- Distinguish login roles, group roles, membership, ownership, and privileges.
- Design owner, migrator, runtime, worker, and read-only roles.
- Control schema, table, sequence, function, and default privileges.
- Test privilege escalation and denial.
Prerequisites and mental model
Ownership is stronger than a grant. Group roles collect privileges; login roles inherit only intended groups. The superuser bypasses normal controls and is not an application shortcut.
How does a role design look?
Conceptual separation:
- signaldesk_owner: NOLOGIN, owns schema/objects.
- signaldesk_migrator: login assumed only by deployment, can act as owner through controlled membership.
- signaldesk_app: runtime login with USAGE on schema and specific DML.
- signaldesk_readonly: approved views only.
- signaldesk_worker: queue/tool functions only.
Example skeleton—review in an isolated database and use secret/identity management:
Future objects require ALTER DEFAULT PRIVILEGES executed for the role that will create them. Defaults are creator-specific, a frequent source of surprise.
Why does this matter in AI-native systems?
Model-facing tools need narrower roles than the application. A retrieval role reads authorized views; a suggestion tool writes proposals, not business outcomes; an approved executor calls a specific function. Even if generated SQL escapes a prompt, database privileges should limit blast radius.
Prove it worked
Use SET ROLE only when authorized in a learning/admin session, or connect as test roles. Build an access matrix and test every cell: allowed ticket read/write, denied schema alteration, denied unrelated tables, denied cross-tenant access after RLS, denied function creation, and denied role changes.
Production judgment
- Review PUBLIC privileges on database, schemas, and functions.
- Rotate credentials or use managed/short-lived identity where available.
- SECURITY DEFINER, role inheritance, and SET ROLE require threat modeling.
- Backups and monitoring need separate privileges, not application credentials.
Common mistakes
- App can drop table: app owns objects → transfer ownership to NOLOGIN owner.
- New tables inaccessible or overexposed: default privileges misunderstood → set/test for creator role.
- Read-only role executes dangerous function: function grants ignored → inventory/revoke/grant explicitly.
AI Pair-Programmer Prompt
Hands-on exercise
Design roles for an API, migration job, RAG retriever, AI proposal writer, and human-approved executor.
Acceptance criterion: no runtime role owns objects, retrieval cannot write, proposal cannot execute outcomes, and denial tests pass.
Knowledge check
- Why should runtime not own tables?
- Whose default privileges matter?
- Are parameters a replacement for least privilege?
Answers
- Owners can alter/drop and bypass ordinary grant restrictions on their objects.
- The role that creates future objects.
- No; parameters reduce injection risk but privileges limit authorized capability.
Completion checklist
Primary references