GRANT/REVOKE Patterns

PostgreSQL privileges are explicit by default. New objects do not automatically grant access to application roles. Model schema, table, column, and sequence grants deliberately.

Recipe

-- Application read path
GRANT USAGE ON SCHEMA app TO app_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readers;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readers;
 
-- Application write path (narrower)
GRANT USAGE ON SCHEMA app TO app_writers;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_writers;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_writers;

When to reach for this: Every new schema, every migration that creates tables, and every new microservice role.

Working Example

CREATE SCHEMA app AUTHORIZATION app_owner;
 
CREATE ROLE app_api LOGIN PASSWORD 'vault';
CREATE ROLE app_ro LOGIN PASSWORD 'vault';
GRANT app_readers TO app_ro;
GRANT app_writers TO app_api;
 
-- Table-level with column restriction
CREATE TABLE app.users (
  id bigint PRIMARY KEY,
  email text NOT NULL,
  ssn_last4 char(4)  -- sensitive
);
 
GRANT SELECT (id, email) ON app.users TO app_ro;
GRANT SELECT, INSERT, UPDATE ON app.users TO app_api;
REVOKE ALL ON app.users FROM PUBLIC;
 
-- Verify effective grants
SELECT grantee, privilege_type, column_name
FROM information_schema.column_privileges
WHERE table_schema = 'app' AND table_name = 'users';

What this demonstrates:

• Schema USAGE is required before table access
• Column-level GRANT SELECT (cols) hides sensitive fields from analysts
• REVOKE ALL FROM PUBLIC closes the default-public hole on new clusters (when hardening)

Deep Dive

Privilege Layers

PUBLIC and Default ACLs

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE myapp FROM PUBLIC;
GRANT CONNECT ON DATABASE myapp TO app_readers;

• PostgreSQL grants some rights to PUBLIC historically on public schema.
• Hardened baselines revoke PUBLIC and grant explicitly to group roles.
• ALTER DEFAULT PRIVILEGES handles future objects (see dedicated page).

Ownership vs Grants

• Object owner bypasses grants for their objects (except RLS).
• Migrations should run as app_owner NOLOGIN; runtime connects as app_api.
• REASSIGN OWNED moves ownership during role renames.

Gotchas

• Forgot schema USAGE - permission denied for schema app despite table GRANT. Fix: GRANT USAGE ON SCHEMA app.
• Sequence not granted - INSERT works with serial until sequence exhausted on new row. Fix: GRANT USAGE, SELECT ON SEQUENCE.
• ALL TABLES misses future tables - Migration adds table; app 403 until grant rerun. Fix: ALTER DEFAULT PRIVILEGES for migrator role.
• **Column GRANT + SELECT *** - App uses SELECT * and fails on hidden columns inconsistently. Fix: Explicit column lists in app queries.
• Superuser in connection string - Grants irrelevant; audit nightmare. Fix: Least-privilege app role always.

Alternatives

FAQs

Related

• Roles Basics - group role model
• Default Privileges - automatic grants on new objects
• Row-Level Security - row filters on top of grants

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.