Modern database environments demand more than just strong passwords and firewall rules. As teams grow and applications multiply, managing who can do what inside a PostgreSQL database becomes one of the most critical aspects of security architecture. This article explores a production-ready approach to PostgreSQL permission management through Role-Based Access Control (RBAC): a model where permissions are assigned to roles, and users simply inherit the roles they need.
Rather than focusing solely on SQL syntax, we’ll examine the architectural patterns and design decisions that make RBAC scalable, auditable, and aligned with security standards like NIST SP 800-53 and OWASP’s Principle of Least Privilege.
The RBAC Model
What Is RBAC?
IBM defines Role-Based Access Control as:
“A model for authorizing end-user access to systems, applications, and data based on a user’s predefined role. For example, a security analyst can configure a firewall but cannot view customer data, while a sales rep can see customer accounts but cannot touch firewall settings.”
In PostgreSQL, this translates directly: roles carry permissions, and users carry roles. The key insight is that a PostgreSQL USER is simply a role with the LOGIN attribute — there is no architectural distinction between the two.
RBAC Layer Architecture
┌──────────────────────────────────────────────────┐│Users / Applications ││ ││reporting_user app_user alexarty(dba) │└────────────┬───────────┬────────┬────────┬───────┘│ │ │ │▼ ▼ ▼ ▼┌──────────────────────────────────────────────────┐│Roles ││ ││ ┌──────────┐ ┌───────────┐ ┌─────┐ ┌─────┐ ││ │readonly │ │ readwrite │ │ ddl │ │db_ │ ││ └──────────┘ └───────────┘ └─────┘ │admin│ ││ └─────┘ │└────────────┬───────────┬────────┬────────┬───────┘│ │ │ │▼ ▼ ▼ ▼┌──────────────────────────────────────────────────┐│Permissions ││ ││SELECT SELECT CREATE CREATEROLE ││ON ALL INSERT ON CREATEDB ││TABLES UPDATE SCHEMA BYPASSRLS ││DELETE pg_monitor │└──────────────────────────────────────────────────┘
Key Characteristics:
- Role Hierarchy: Permissions flow downward from roles to users — never upward
- Decoupled Identity: What a user can do is defined by their role, not their identity
- Centralized Control: Changing a role’s permissions instantly affects all members
- Least Privilege by Design: Each role is narrowly scoped to its function
Role Hierarchy and Inheritance
PostgreSQL supports role nesting — roles can be members of other roles, inheriting permissions from their parent.
┌─────────────┐│superuser │ ← PostgreSQL built-in└──────┬──────┘│(managed by)┌──────▼──────┐│db_admin │ ← Create roles, create DB└──────┬──────┘│(manages)┌───────────────┼───────────────┐│ │ │┌──────▼──────┐ │ ┌──────▼──────┐│ddl │ │ │ (future ││(schema & │ │ │ roles) ││objects) │ │ └─────────────┘└──────┬──────┘ ││ ┌──────▼──────┐ ┌──────────────┐│ │readwrite │ │ readonly ││ │(CRUD ops) │ │ (SELECT ││ └──────┬──────┘ │only) ││ │ └──────┬───────┘│ │ │┌──────▼───────────────▼────────────────▼───────┐│Users ││alex (dev) app (svc)artyreporting │└───────────────────────────────────────────────┘
Benefits of This Hierarchy:
- Layered Administration:
db_adminmanages structure;ddlmanages objects;readwrite/readonlymanage data - Blast Radius Control: A compromised
appservice account only hasreadwrite, notddlor admin powers - Easy Role Expansion: New permission tiers can be inserted without restructuring existing users
Permission Architecture: Roles as Data Contracts
Permission Flow Diagram
┌──────────────────────────────┐│Role Definition Object ││ ││Role: readwrite ││Grants: ││- CONNECT on database ││- USAGE on schema ││- SELECT, INSERT, ││UPDATE, DELETE ││on all tables ││- USAGE, SELECT, UPDATE ││on all sequences │└──────────────┬───────────────┘│┌──────────────────┼──────────────────┐│ │ │▼ ▼ ▼┌───────────────┐ ┌──────────────┐ ┌──────────────┐│Existing │ │ Future │ │ User ││Objects │ │ Objects │ │ Assignments │└───────┬───────┘ └──────┬───────┘ └──────┬───────┘│ │ │▼ ▼ ▼GRANT ... ON ALTER DEFAULT GRANT readwriteALL TABLES → PRIVILEGES → TO app_user;IN SCHEMA FOR ROLE ddl GRANT readwriteTO alice;
Benefits of Treating Roles as Contracts:
- Consistency: All users under the same role receive identical permissions
- Maintainability: Update the role once — all members are updated instantly
- Documentation: Role definitions serve as living permission documentation
- Auditability: “Who can write to the orders table?” is answered in one query
- Compliance: Role definitions map cleanly to NIST and CIS control requirements
PostgreSQL Privilege Reference
| Privilege | Applies To | Description |
|---|---|---|
SELECT | Tables, Views, Sequences | Read rows or current value |
INSERT | Tables | Add new rows |
UPDATE | Tables, Columns | Modify existing rows |
DELETE | Tables | Remove rows |
TRUNCATE | Tables | Remove all rows efficiently |
REFERENCES | Tables, Columns | Create foreign key constraints |
TRIGGER | Tables | Create triggers on the table |
CREATE | Databases, Schemas | Create objects within the target |
CONNECT | Databases | Establish a connection |
EXECUTE | Functions, Procedures | Call a function or stored procedure |
USAGE | Schemas, Sequences, Types | Access the object |
Why RBAC Is Best Practice for PostgreSQL
4.1 Enforces the Principle of Least Privilege (PoLP)
OWASP defines PoLP as granting every subject only the minimum permissions required to perform its function. RBAC enforces this structurally. Rather than relying on administrators to remember which grants to skip, the role design itself limits what is possible.
A readonly role cannot insert data — not because someone checked a box, but because INSERT was never granted to that role in the first place. This drastically limits the blast radius of a breach.
4.2 Scales Cleanly With Team Growth
In a direct-grant model, onboarding a new reporting analyst requires manually re-executing every GRANT statement against every schema and table. With RBAC:
-- One statement to onboardGRANT readonly TO new_analyst;-- One statement to offboardREVOKE readonly FROM departing_analyst;DROP ROLE departing_analyst;
The role already encapsulates everything the analyst needs. As the team grows from 5 to 500, the permission model stays constant.
4.3 Separates Administrative Actions From Application Actions
┌──────────────────────────────────────────────────────┐│Responsibility Separation ││ ││ ┌───────────────┐ ┌───────────────────┐ ││ │db_admin │ │ Application │ ││ │(Human DBA) │ │ Service Account │ ││ │ │ │ │ ││ │ ✔Create roles│ │ ✔ Read data │ ││ │ ✔Create DB │ │ ✔ Write data │ ││ │ ✔Grant perms │ │ │ ││ │ │ │ ✘Create tables │ ││ │ ✘Run in app │ │ ✘ Alter schemas │ ││ │ ✘Always on │ │ ✘ Grant others │ ││ └───────────────┘ └───────────────────┘ │└──────────────────────────────────────────────────────┘
The superuser role must be reserved exclusively for creating users, defining roles, and configuring databases — never for application operations. Even the db_admin role should sit above applications, used only when structural changes are needed.
4.4 Simplifies Auditing and Compliance
When permissions are scattered as individual GRANT statements across many users, answering “who can modify the orders table?” requires scanning every user record. With RBAC:
-- Immediate answer: any user holding the readwrite roleSELECT r.rolname AS role, m.rolname AS memberFROM pg_auth_members amJOIN pg_roles r ON r.oid = am.roleidJOIN pg_roles m ON m.oid = am.memberWHERE r.rolname = 'readwrite';
Centralized role definitions make periodic access reviews and compliance audits orders of magnitude faster. NIST SP 800-53 (AC-3 and AC-6) explicitly endorses this model.
4.5 Enables Clean Offboarding and Access Revocation
Removing all database privileges from a departing user or compromised service account is a single operation — no manual hunting for scattered grants required.
4.6 Supports Future-Proof Schema Growth
Without Default Privileges: With Default Privileges:ddl creates new_table ddl creates new_table│ │▼ ▼app_user tries SELECT ALTER DEFAULT PRIVILEGES│FOR ROLE ddl GRANT SELECT▼ON TABLES TO readonly;✘Permission denied ││ ▼DBA must manually readonly auto-gets access ✔GRANT SELECT again readwrite auto-gets CRUD ✔
Using ALTER DEFAULT PRIVILEGES ensures every future table automatically inherits the permissions defined in the role. Without it, newly created tables are invisible to applications — and that silence can masquerade as bugs.
Direct Grant vs. RBAC: Side-by-Side Comparison
| Aspect | Direct Permission Grant | RBAC |
|---|---|---|
| Permission target | Individual user | Role (shared template) |
| Adding a new user | Re-grant every permission manually | Assign one role |
| Revoking access | Hunt & revoke each individual grant | Remove role from user |
| Audit clarity | Scattered, hard to trace | Centralized in role definition |
| Least privilege | Easy to over-grant by accident | Enforced by role design |
| Scalability | Poor — grows with user count | Excellent — roles stay constant |
| Compliance mapping | Manual cross-referencing | Role definitions map to controls |
| Offboarding | Multi-step, error-prone | Single REVOKE + DROP ROLE |
Recommended RBAC Implementation
Step 1 — Create Roles
CREATE ROLE db_admin WITH CREATEROLE BYPASSRLS;CREATE ROLE ddl;CREATE ROLE readwrite;CREATE ROLE readonly;
Role Responsibilities:
db_admin: Second-tier administrator. Can create roles and schemas, cannot create databases directly (superuser handles that). Bypasses RLS for maintenance tasks.ddl: Schema and object management. Owns the tables and sequences — default privilege grants flow from this role.readwrite: Application data operations. Full CRUD access; the typical role for backend service accounts.readonly: Reporting and analytics. SELECT-only access across all tables and sequences.
Step 2 — Grant Permissions to Roles
-- Database-level access for all rolesGRANT CONNECT ON DATABASE my_db TO db_admin, ddl, readwrite, readonly;GRANT TEMPORARY, CREATE ON DATABASE my_db TO db_admin;-- Schema-level accessGRANT USAGE ON SCHEMA my_schema TO db_admin, ddl, readwrite, readonly;GRANT CREATE ON SCHEMA my_schema TO ddl;-- readonly: SELECT onlyGRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO readonly;GRANT SELECT ON ALL SEQUENCES IN SCHEMA my_schema TO readonly;-- readwrite: Full CRUDGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_schema TO readwrite;GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA my_schema TO readwrite;
⚠️ Caution on Predefined Roles: PostgreSQL includes built-in roles like
pg_read_all_dataandpg_write_all_data. Use them carefully — they grant global access across all schemas, bypassing the schema-scoped isolation this architecture is designed to provide.
Step 3 — Handle Future Tables (Default Privileges)
-- readwrite: auto-receives CRUD on tables created by ddlALTER DEFAULT PRIVILEGES FOR ROLE ddlGRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;ALTER DEFAULT PRIVILEGES FOR ROLE ddlGRANT USAGE, SELECT, UPDATE ON SEQUENCES TO readwrite;-- readonly: auto-receives SELECT on tables created by ddlALTER DEFAULT PRIVILEGES FOR ROLE ddlGRANT SELECT ON TABLES TO readonly;ALTER DEFAULT PRIVILEGES FOR ROLE ddlGRANT SELECT ON SEQUENCES TO readonly;
Note on Schema-Level Defaults: The following line is intentionally left commented out. Auto-granting
USAGEon every new schema created bydb_adminmay be too broad — if a schema should be access-controlled separately, auto-granting defeats that isolation. Grant USAGE manually to roles (not users) when a new schema is introduced:
-- ALTER DEFAULT PRIVILEGES FOR ROLE db_admin-- GRANT USAGE ON SCHEMAS TO ddl, readwrite, readonly;
Step 4 — Create Users and Assign Roles
-- Application service accountCREATE USER app WITH PASSWORD 'secret_password';-- Reporting / analytics accountCREATE USER reporting WITH PASSWORD 'secret_password';-- alex is a developer (needs schema/object management)CREATE USER alex WITH PASSWORD 'secret_password';-- soni is a DBA (needs administrative + DDL capabilities)CREATE USER soni WITH PASSWORD 'secret_password';-- Role assignmentsGRANT readwrite TO app;GRANT readonly TO reporting;GRANT ddl TO alex;GRANT db_admin, ddl TO soni;
Step 5 — Revoke Access (When Needed)
-- Offboard a developerREVOKE ddl FROM alex;DROP ROLE IF EXISTS alex;-- Offboard a DBAREVOKE db_admin, ddl FROM soni;DROP ROLE IF EXISTS soni;
Step 6 — Revoke Default PUBLIC Role (PostgreSQL < 15)
PostgreSQL versions below 15 attach the PUBLIC role to all new users by default — meaning any newly created user can CREATE tables in the public schema. This default was removed in PostgreSQL 15, but upgraded instances may still carry it.
-- Revoke connection access for PUBLICREVOKE CONNECT ON DATABASE my_db FROM PUBLIC;-- Revoke only the default CREATE privilege (minimal restriction)REVOKE CREATE ON SCHEMA public FROM PUBLIC;-- Full lockdown (most secure — recommended for production)REVOKE ALL ON SCHEMA public FROM PUBLIC;
Step 7 — Review and Maintain Roles
This policy should be reviewed periodically to ensure alignment with security best practices and organizational requirements.
Critical maintenance point: Default privileges on schemas created by db_admin are intentionally not automatically granted to ddl, readwrite, and readonly. When db_admin creates a new schema and those roles need access, the USAGE grant must be applied manually to the role — not to individual users. This keeps the permission model consistent even as the database structure evolves.
RBAC Approach: Pros and Cons
Advantages
Complete Permission Centralization
- All permissions are managed at the role level — no scattered grants
- Adding or removing a privilege from a role instantly affects all members
- New tables automatically inherit role permissions via default privileges
- One source of truth for the entire permission model
Scales With Team and Application Growth
- Onboarding requires a single GRANT statement
- Offboarding requires a single REVOKE statement
- The role model stays constant regardless of team size
- No permission drift as the team expands
Compliance and Audit Readiness
- NIST SP 800-53 (AC-3, AC-6) and CIS Controls explicitly endorse this model
- Periodic access reviews reduce to inspecting role memberships
- “Who has write access to orders?” has an immediate, deterministic answer
- Clean audit trail at the role level, not scattered across hundreds of grants
Security Boundary Enforcement
- Applications never receive administrative capabilities
- Superuser and db_admin roles are separated from application roles
- Compromised service accounts have a limited blast radius
- The Principle of Least Privilege is enforced structurally, not procedurally
Future-Proof Schema Growth
- Default privileges ensure new tables are accessible to the right roles automatically
- Schema isolation is supported via per-schema USAGE grants
- New application tiers can be added by creating a new role — no existing grants need modification
Disadvantages
Requires Upfront Design Investment
- Role structure must be planned before deployment
- Poorly designed roles are difficult to restructure without touching all members
- Teams unfamiliar with PostgreSQL’s permission model face a learning curve
Default Privileges Are Scoped to a Creator Role
ALTER DEFAULT PRIVILEGES FOR ROLE ddlonly applies whenddlcreates the object- Objects created by superuser or other roles will not inherit these defaults
- Requires discipline in who creates database objects
No Native Row-Level Scoping
- RBAC alone does not restrict which rows a user can access — only which operations on which tables
- Multi-tenant row-level isolation requires combining RBAC with Row-Level Security (RLS)
- RLS policies add complexity and require additional planning
PostgreSQL < 15 Requires Manual PUBLIC Cleanup
- Upgraded instances may still carry dangerous PUBLIC role defaults
- Requires explicit revocation steps during setup
- Easy to forget in environments that pre-date PostgreSQL 15
When to Apply Each Role
Choose readonly When:
The subject only needs to observe data
- Reporting tools, business intelligence dashboards, data exports
- Monitoring and alerting queries against application tables
- Audit log readers who need visibility but must not alter records
- External systems pulling data via read replicas
Choose readwrite When:
The subject is an active application component
- Backend service accounts that power APIs and business logic
- ETL pipelines that both read source data and write transformed results
- Queue workers that read jobs and update their status upon completion
- Application users with standard CRUD capabilities
Choose ddl When:
The subject manages schema and structure
- Developers running migrations during deployments
- CI/CD pipelines that apply schema changes in controlled environments
- Database tooling that creates or modifies tables, indexes, and sequences
- Roles that own objects whose default privileges flow to
readwrite/readonly
Choose db_admin When:
The subject requires administrative capabilities
- Database administrators performing role management and configuration
- Automation that needs to create or drop roles in response to lifecycle events
- Maintenance tasks that require bypassing Row-Level Security
- The second tier of administration below the PostgreSQL superuser
Comparison: RBAC vs. Direct Grant vs. Schema Isolation
Feature Comparison Matrix
| Feature | Direct Grant | RBAC (Roles) | RBAC + Schema Isolation | Advantages |
|---|---|---|---|---|
| Permission Management | Per-user, manual | Centralized in roles | Centralized + scoped | RBAC: Single point of control |
| Onboarding Speed | Slow — many grants | Fast — one GRANT | Fast — one GRANT | RBAC: Assign role, done |
| Offboarding Safety | Risk of missed grants | Complete with one REVOKE | Complete with one REVOKE | RBAC: No missed privileges |
| Audit Clarity | Scattered, hard to trace | Role-level, immediate | Role + schema, immediate | RBAC: Immediate answers |
| Multi-tenant Isolation | Requires careful grants | Role-based isolation | Schema + role isolation | Schema Isolation: Strongest |
| Future Object Coverage | Manual re-grant | Default privileges | Default privileges per schema | RBAC: Auto-covered |
| Scalability | Poor | Excellent | Excellent | RBAC: Constant complexity |
| Setup Complexity | Low initially | Medium | Higher | Mixed: RBAC trades setup for long-term simplicity |
| Compliance Mapping | Manual | Role-to-control mapping | Granular control mapping | RBAC: Cleaner audit evidence |
When to Choose Each Strategy
Choose Direct Grant When:
The environment is very small and static
- A single-developer project with one or two database users
- Short-lived environments (development sandboxes, demos) that won’t scale
- Situations where role creation is restricted by the hosting environment
Choose RBAC (Roles) When:
The team or application portfolio is growing
- Multiple service accounts or application tiers need distinct permission levels
- Regular onboarding and offboarding of users or service accounts is expected
- Compliance requirements demand documented, auditable permission structures
- The principle of least privilege must be enforced consistently
Choose RBAC + Schema Isolation When:
Multi-tenancy or strict data boundaries are required
- A single database instance serves multiple clients or business units
- Different schemas must be completely inaccessible to other tenant roles
- Regulatory requirements mandate data separation at the schema level
- A SaaS product needs per-customer data isolation within one database
Hybrid Approach
Many organizations combine strategies:
Direct grants for: Temporary access, one-off investigative queries, emergency access with immediate revocation.
RBAC for: All standing access — service accounts, developers, analysts, and administrators. The default for everything that persists.
Schema isolation for: Multi-tenant data separation, compliance boundaries, or production vs. audit partitioning within the same instance.
Conclusion
Implementing Role-Based Access Control in PostgreSQL is not just a security checkbox — it is a foundational architectural decision that determines how easily your database environment can be managed, audited, and scaled. The patterns demonstrated here — role hierarchy, permission centralization, default privileges, and administrative separation — apply equally well across any PostgreSQL environment, from single-tenant applications to complex multi-tenant platforms.
The key principles to carry forward:
- Assign permissions to roles, never directly to users
- Use default privileges to keep future objects covered automatically
- Separate administrative roles from application roles structurally, not just by policy
- Keep the PUBLIC role locked down in environments running PostgreSQL below version 15
- Review role memberships periodically — especially after team changes or security incidents
A well-implemented RBAC model transforms PostgreSQL from a simple data store into a secure, auditable, and operationally sound foundation — one where access is governed not by ad-hoc grants, but by deliberate, documented, and enforceable role design.
References
- NIST SP 800-53 AC-3(7) — Access Enforcement: Role-Based Access Control — https://csf.tools/reference/nist-sp-800-53/r4/ac/ac-3/ac-3-7/
- NIST SP 800-53 AC-6 — Least Privilege — https://csf.tools/reference/nist-sp-800-53/r4/ac/ac-6/
- IBM — What is Role-Based Access Control (RBAC)? — https://www.ibm.com/think/topics/rbac
- AWS Database Blog — Managing PostgreSQL Users and Roles — https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/
- OWASP — Principle of Least Privilege — https://owasp.org/www-community/controls/Least_Privilege_Principle
- PostgreSQL Official Documentation — Database Roles — https://www.postgresql.org/docs/current/user-manag.html
Leave a Reply