How should I structure PostgreSQL roles for a web application?

Create at minimum three roles: app_reader (SELECT on non-PII tables and masked views), app_writer (SELECT, INSERT, UPDATE on application tables), and app_admin (all privileges on application schema, no superuser). The web application connects as app_writer. Reporting tools connect as app_reader. Only DevOps uses app_admin.

How do I detect privilege escalation in PostgreSQL?

Run pgcomply.access_map() to see the actual effective permissions for each role. Look for roles with DELETE on PII tables, roles with access to tables outside their function, and roles that inherit from overly permissive parent roles. role_hierarchy() helps trace inheritance chains.

Least Privilege Access Control in PostgreSQL: A Practical Guide

The Problem with Default Permissions

New PostgreSQL installations often have one superuser account used for everything — application connections, migrations, backups, and ad-hoc queries. This is the opposite of least privilege.

Designing a Role Hierarchy

-- Base roles (no login)
CREATE ROLE app_reader;
CREATE ROLE app_writer;
CREATE ROLE app_admin;
CREATE ROLE dpo;

-- Grant hierarchically
GRANT app_reader TO app_writer;
GRANT app_writer TO app_admin;

-- Login roles inherit from base roles
CREATE ROLE web_api LOGIN PASSWORD 'strong_password';
GRANT app_writer TO web_api;

CREATE ROLE analyst LOGIN PASSWORD 'strong_password';
GRANT app_reader TO analyst;

Permission Matrix

-- Reader: SELECT on masked views only
GRANT SELECT ON pgcomply.masked_public_users TO app_reader;
GRANT SELECT ON pgcomply.masked_public_orders TO app_reader;

-- Writer: CRUD on application tables
GRANT SELECT, INSERT, UPDATE ON users, orders, profiles TO app_writer;

-- DPO: access to compliance functions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pgcomply TO dpo;

Visualizing Access

SELECT * FROM pgcomply.access_map();

 role       | table    | select | insert | update | delete | rls  | pii  | risk
------------+----------+--------+--------+--------+--------+------+------+------
 analyst    | users    | ✓      | ✗      | ✗      | ✗      | ✗    | ✓    | LOW
 web_api    | users    | ✓      | ✓      | ✓      | ✗      | ✓    | ✓    | MEDIUM
 dev_staging| users    | ✓      | ✓      | ✓      | ✓      | ✗    | ✓    | HIGH

HIGH risk means: write access to PII tables without RLS. This should trigger a review.

-- Role inheritance tree
SELECT * FROM pgcomply.role_hierarchy();

Periodic Access Reviews

Permissions drift over time. Quarterly reviews catch this:

-- Pro: Start a review
SELECT pgcomply.start_access_review('Q1-2026');

-- Review each finding
SELECT pgcomply.review_decide('Q1-2026', 'dev_staging', 'users', 'revoke',
  'Dev staging should not have production access');

Common Anti-Patterns

Anti-Pattern 1: The "God Role"

-- DON'T: One role for everything
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_role;

This gives INSERT, UPDATE, DELETE, TRUNCATE on every table. Your web API probably doesn't need TRUNCATE on the users table.

-- DO: Granular grants
GRANT SELECT, INSERT, UPDATE ON users TO app_writer;
GRANT SELECT ON orders TO app_writer;
-- No DELETE, no TRUNCATE — handle deletion via pgcomply.forget()

Anti-Pattern 2: Direct Superuser Access in Applications

-- DON'T: Application connects as postgres
DATABASE_URL=postgresql://postgres:password@host/db

-- DO: Application connects as a limited role
DATABASE_URL=postgresql://app_writer:password@host/db

Anti-Pattern 3: No Separation Between Read and Write

-- DON'T: Same role for analytics and application
GRANT SELECT, INSERT, UPDATE ON ALL TABLES TO app_role;

-- DO: Separate roles
CREATE ROLE app_reader;
CREATE ROLE app_writer;
GRANT SELECT ON pgcomply.masked_public_users TO app_reader;  -- Masked views only
GRANT SELECT, INSERT, UPDATE ON users TO app_writer;           -- Real tables

Privilege Escalation Detection

Check for unexpected permission paths:

-- Who has DELETE on PII tables? (This should be very few roles)
SELECT role, table_name, risk_level
FROM pgcomply.access_map()
WHERE has_delete = true
  AND table_name IN (SELECT DISTINCT table_name FROM pgcomply.pii_registry);

Expected: only postgres and possibly app_admin. If analyst or web_api has DELETE on PII tables, that's a finding.

-- Who inherits from overly permissive roles?
SELECT * FROM pgcomply.role_hierarchy()
WHERE member_of IN ('rds_superuser', 'app_admin');

Implementing Changes Safely

When tightening permissions, do it gradually:

-- 1. Audit current state
SELECT * FROM pgcomply.access_map();

-- 2. Create new restricted role
CREATE ROLE app_reader_v2;
GRANT SELECT ON pgcomply.masked_public_users TO app_reader_v2;

-- 3. Test with the new role
SET ROLE app_reader_v2;
SELECT * FROM users;  -- Should be denied
SELECT * FROM pgcomply.masked_public_users;  -- Should work (masked)
RESET ROLE;

-- 4. Migrate analytics tools to new role
ALTER ROLE metabase_user SET ROLE app_reader_v2;

-- 5. Verify in access_map
SELECT * FROM pgcomply.access_map() WHERE role = 'metabase_user';

Summary

Least privilege access control is a process, not a one-time setup. Use pgcomply.access_map() to see your current state, design role hierarchies that minimize permissions, and run quarterly access reviews to prevent privilege drift.