Database Permissions

Last Updated: June 12, 2026 • 20 min read

1. Introduction

Database permissions control who can access what data and what operations they can perform. PostgreSQL implements a granular privilege system using the GRANT and REVOKE commands, with support for table-level, column-level, and schema-level permissions.

2. Why It Matters

Permission management is a critical security skill. The most common database security vulnerability is excessive privileges. Interviewers test whether you understand the GRANT/REVOKE model, role inheritance, and the principle of least privilege in database security.

3. Real-World Analogy

Database permissions are like building access cards in an office complex. The receptionist (public schema) is accessible to everyone. Engineering floors (app schemas) require specific badges. The server room (admin operations) needs the highest clearance. Row-level security adds another layer — even within engineering, each team can only access their own projects.

4. How It Works

PostgreSQL organizes permissions in a hierarchy from broad to granular:

Cluster level: SUPERUSER, CREATEDB, CREATEROLE, REPLICATION.
Database level: CONNECT, CREATE (schemas), TEMPORARY.
Schema level: USAGE (access objects), CREATE (create objects).
Table level: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER.
Column level: SELECT, INSERT, UPDATE on specific columns.
Sequence level: USAGE, SELECT, UPDATE on sequences.
Function level: EXECUTE on stored procedures and functions.

5. Internal Architecture

PostgreSQL stores permissions in system catalogs: pg_class (table-level ACLs), pg_auth_members (role membership), and information_schema.table_privileges. Group roles (NOLOGIN) bundle permissions; user roles (LOGIN) inherit them via membership.

6. Visual Explanation

7. Practical Example

Role-based access control setup:

8. Common Mistakes

Common Pitfall

The PUBLIC role has USAGE on the public schema by default. Any authenticated user can see table names and structure. For sensitive environments, revoke this: REVOKE ALL ON SCHEMA public FROM PUBLIC;

Interview Insight

When asked about database security, always mention: (1) never use superuser for applications, (2) use role groups for permission management, (3) revoke PUBLIC default permissions, and (4) audit permissions regularly.

Granting ALL PRIVILEGES: Always enumerate specific privileges (SELECT, INSERT, UPDATE) rather than using ALL.
Forgetting ALTER DEFAULT PRIVILEGES: New tables won't inherit permissions, causing production "permission denied" errors.
Not auditing: Regularly query information_schema.table_privileges to detect permission drift.

9. Quick Quiz

Q1: What happens when you REVOKE SELECT from a role that also inherits SELECT from group membership?

The direct REVOKE only removes the direct grant. The role still has SELECT via group membership. You'd need to remove them from the group or revoke at the group level.

Q2: What is Row-Level Security (RLS)?

RLS restricts which rows a user can see/modify based on policies. Essential for multi-tenant apps where each tenant must only see their own data, enforced at the database level.

10. Scenario-Based Challenge

Scenario

You're building a multi-tenant SaaS application. Each tenant should only see their own data, even if the application code has a bug that forgets to filter by tenant_id. Design the RLS policy. How do you ensure even the table owner is subject to the policy?

11. Debugging Exercise

A developer can connect to the database but gets this error on every query:

Fix: The developer's role lacks USAGE on the schema. Run: GRANT USAGE ON SCHEMA app_data TO developer_role;

12. Interview Questions

Q: What's the difference between GRANT ALL and GRANT SELECT, INSERT, UPDATE, DELETE?

GRANT ALL includes additional privileges like TRUNCATE, REFERENCES, and TRIGGER. Explicit grants follow the principle of least privilege — only give what's needed.

Q: How do you audit current permissions?

Query information_schema.table_privileges for table grants, pg_roles for role attributes, and pg_auth_members for role membership. Combine these to build a complete permission map.

Q: What does REVOKE ... CASCADE do?

When a role granted privileges to others, revoking with CASCADE also revokes those dependent grants. Without CASCADE, the REVOKE fails if dependent grants exist.

13. Production Considerations

Microservice isolation: Each service gets a dedicated role with permissions limited to only the tables it needs — preventing lateral movement if one service is compromised.
RLS for multi-tenancy: Use FORCE ROW LEVEL SECURITY to enforce policies even on table owners. This is more secure than application-level filtering.
Compliance (SOC2, HIPAA): Audit and document all database permissions to satisfy security audit requirements. Automate audits with scheduled queries.
REASSIGN OWNED: Before dropping a role that owns objects, transfer ownership: REASSIGN OWNED BY old_role TO new_role;