Postgres Roles and Least Privilege: The Setup That Survives Audits

The most common Postgres role setup I see in production: one application user, named after the application, with full ownership of all schema objects. The application connects with this role for every operation: reads, writes, schema migrations.

This is convenient and wrong. A SQL injection bug, a leaked credential, or a malicious insider can do anything the application can do — including dropping tables. The blast radius is the entire database.

The right setup is not complicated. Here is the role hierarchy I use.

The four roles

1. app_user: read/write/delete on application tables. Cannot create or drop tables. This is what the application uses for normal traffic.

2. app_migrator: can run migrations — CREATE, ALTER, DROP. Used only by the migration process, not by the application at runtime.

3. app_readonly: read-only on application tables. Used by analytics tools, BI dashboards, ad-hoc queries.

4. dba: superuser-equivalent. Used by humans for emergency operations.

These four cover most use cases. More specialized roles (audit-only, schema-A-only) layer on top.

The setup

-- Create roles
CREATE ROLE app_user LOGIN PASSWORD '...';
CREATE ROLE app_migrator LOGIN PASSWORD '...';
CREATE ROLE app_readonly LOGIN PASSWORD '...';

-- Migration role owns the schema
CREATE SCHEMA application AUTHORIZATION app_migrator;

-- Application role can use the schema (read/write tables) but not create/drop
GRANT USAGE ON SCHEMA application TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA application TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA application TO app_user;

-- Default privileges for future tables created by the migrator
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator IN SCHEMA application
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator IN SCHEMA application
  GRANT USAGE ON SEQUENCES TO app_user;

-- Read-only role
GRANT USAGE ON SCHEMA application TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA application TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator IN SCHEMA application
  GRANT SELECT ON TABLES TO app_readonly;

The ALTER DEFAULT PRIVILEGES part is critical. Without it, every new table created by the migrator needs explicit GRANTs. With it, new tables automatically get the right privileges.

What this prevents

SQL injection that drops a table: app_user cannot DROP. The injection cannot do worse than what the app could do legitimately.

A leaked app_user credential: an attacker with the credential can read and modify data, but cannot escalate to schema changes or take over the database.

An accidental DELETE FROM users with no WHERE: serious damage, but recoverable from backup. Compare to an accidental DROP TABLE users, which cascades through foreign keys and is much harder to undo.

A migration mistake: the migrator role is used only by the migration process, which is reviewed and tested. Most migrations have a rollback. The application running with reduced privileges cannot trigger an unreviewed migration.

Special-case roles

Background job roles. If you have a background worker that runs different queries from the main application (e.g., heavy aggregations), give it a separate role with appropriate privileges. Limit what each can do.

Per-tenant roles in multi-tenant systems. Each tenant's connection uses a role that has access only to that tenant's data, enforced via row-level security. Stronger isolation than application-level filters.

Audit roles. A role that only reads audit-relevant tables. Used by compliance tools.

Adding more specialized roles incrementally is easy once the four-role baseline is in place. Adding them after years of one-superuser flat structure is hard.

Operational details

Connection pooling: each role needs its own connection pool. PgBouncer pools are role-keyed; the app, the migrator, and the readonly all have separate pools.

Migration process: the migration tool (Flyway, Liquibase, your ORM's migration framework) connects as app_migrator. The application connects as app_user. They are different paths.

Password rotation: rotate the application's password independently from the migration process's password. If the application's password leaks, the migration process is unaffected.

Audit trail: queries from app_user and app_migrator are distinguishable in pg_stat_activity. "Why did this DROP TABLE happen?" → check whether it came from app_migrator (legitimate migration) or app_user (suspicious).

Migrating to this from a flat setup

For existing databases with a single application user, the migration:

1. Create the new roles (without LOGIN at first if you want to be safe).
2. Grant appropriate privileges from the existing tables to each.
3. Set ALTER DEFAULT PRIVILEGES.
4. Add LOGIN to the new roles.
5. Update the application's connection config to use app_user.
6. Update the migration process to use app_migrator.
7. Verify everything works.
8. Eventually revoke privileges from the old single-user role, or repurpose it as app_user.

For a busy production system, this is a multi-week process. The benefit is durable: every audit, every security review, every "least privilege" requirement is satisfied without further work.

What I have seen go wrong

A team I worked with had not done this. A SQL injection in a deprecated admin tool dropped three production tables. Recovery from backup took 6 hours. The deprecated tool ran as superuser because "it was easier."

If the tool had run as app_user, the worst-case damage would have been a table-full DELETE. Painful but quickly recoverable. Instead, the tables had to be restored from backup, all writes since the backup were lost, and the postmortem ran for months.

The role hierarchy is one of those things where the cost is small and the worst case is large. Worth doing on day one.

A final note: ROLEs vs USERs

In modern Postgres, there is no functional difference between CREATE ROLE and CREATE USER. They are the same thing. CREATE USER is just CREATE ROLE WITH LOGIN. The Postgres docs use ROLE consistently; the older USER syntax is still accepted.

The distinction between "role" and "user" in Postgres terminology: a role is anything the database can grant privileges to. Some roles can log in (LOGIN); some cannot (NOLOGIN). Group roles (NOLOGIN) are useful for managing privileges — grant the group role to multiple individual users, and managing the group's privileges affects all of them.

For the four-role pattern above, all four LOGIN. For more elaborate setups with group roles, you would have NOLOGIN "app" group and LOGIN individual users granted into it.