Postgres Role Design: Permissions That Scale Beyond Three Users

Most production Postgres deployments are running with a single superuser account that the application uses, that the migration tool uses, that operations engineers connect with for ad-hoc queries, and that contractors are temporarily given when they need to look at production data. The single-account pattern works until the first time you need to audit who did what, rotate credentials without a coordinated app deploy, or grant time-limited read-only access to someone you do not entirely trust. At that point the cost of building role structure under load is much higher than the cost of building it earlier.

We do not yet run Postgres in production across our four products (DocuMint, CronPing, FlagBit, WebhookVault) because SQLite handles the current scale, but the role design we plan to use when we graduate is the same design we have used at past employers and it bears writing down.

What Postgres roles actually are

Postgres roles are a unified abstraction over users and groups: a role can log in (if it has the LOGIN attribute) or not, can be granted to other roles to form group hierarchies, and can hold privileges on database objects. There is no distinction at the role level between a human user, a service account, and a group. The flexibility is genuinely useful but the unified naming sometimes confuses teams coming from databases where users and roles are separate.

Roles can have attributes like SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS, LOGIN, NOLOGIN, and INHERIT. Superuser bypasses every check including row-level security and most operational constraints. The discipline of avoiding superuser for application connections is the first rule of role design.

The three-role baseline

The minimum viable role structure has three roles plus the postgres superuser that comes with the installation. First, an application role that the app server connects as. Second, a migration role that runs schema changes. Third, a read-only role for ad-hoc queries and reporting. The application role can SELECT, INSERT, UPDATE, DELETE on the data tables but cannot create or drop tables. The migration role can change schema but typically does not need to read or write business data outside the migration itself. The read-only role can SELECT on the data tables and nothing else.

The reasoning is that the application role has the largest blast radius if compromised (it has the credentials embedded in the running application), and giving it schema-modification permissions multiplies that blast radius without buying anything. A compromised application role with read-write access to data is bad. A compromised application role that can also drop tables is much worse.

GRANT, REVOKE, and the default-permissive problem

Postgres defaults are permissive in a way that bites new teams. The default privileges on a newly-created table are SELECT, INSERT, UPDATE, DELETE for the table owner and nothing for anyone else. But the public schema is granted CREATE and USAGE to PUBLIC by default in Postgres versions before 15, meaning any role can create objects in the public schema. Postgres 15 changed the default to no CREATE for PUBLIC, but older databases need explicit REVOKE statements to fix the inherited permissive default.

The discipline that works is to grant permissions on specific objects to specific roles, never to PUBLIC, and to REVOKE the default permissive grants on any database that started before Postgres 15. The ALTER DEFAULT PRIVILEGES statement is the tool for setting the privileges that newly-created objects will have, and it is worth setting up at the start of a project to avoid the situation where each new table needs manual GRANT statements.

Schema-level organization

Tables can be grouped into schemas, and schemas are the right granularity for permission grants in most cases. Granting SELECT on all tables in a schema is one statement; granting it on every table individually is one statement per table that has to be updated when new tables are added. The pattern that scales is to organize tables into a small number of schemas (often one for application data, one for analytics, one for audit logs) and to grant role permissions at the schema level.

The default search_path setting determines which schema gets searched for unqualified table references. The Postgres default is $user, public, which means each role looks in a schema named after itself first and then falls back to public. This default is rarely what you want for application code, which typically expects all tables to be in the public schema or in an application-specific schema. The fix is to set the search_path explicitly per role or per database.

Group roles for human users

The pattern for human users is to create a group role per access tier (read_only, read_write, admin) and then create individual login roles per person that inherit from the group role. The group role holds the permissions; the individual roles hold the credentials and the audit identity. Adding a new engineer is one CREATE ROLE plus one GRANT; removing them is one DROP ROLE. The permission structure does not need to be touched.

The audit benefit is real: log_line_prefix can be configured to include the role name, and a query for who ran a particular query at a particular time has an answer rather than always being the application role. The Postgres logs are the canonical audit trail for ad-hoc query access, and per-person login roles are what make those logs useful.

Service accounts and connection pools

The wrinkle for application servers is that a connection pool typically authenticates once per connection and then reuses the connection for many requests. If the application has multiple internal roles (e.g., one role per tenant), the connection pool either needs to be per-role (which multiplies the pool count) or the application needs to issue SET ROLE statements after acquiring a connection from the pool. SET ROLE switches the current role for the session to a role the connecting user is a member of, which lets a single pooled connection serve multiple effective roles.

The pgbouncer transaction-pooling mode complicates this because SET ROLE is session-scoped and pgbouncer does not preserve session state across transactions. The standard workaround is SET LOCAL ROLE inside the transaction, which is reset at COMMIT and so survives transaction-level pooling. The application's data-access layer needs to wrap every database operation in an explicit transaction with the role-set as the first statement.

Row-level security as policy layer

Row-level security (RLS), available since Postgres 9.5, lets you attach predicates to tables that are automatically applied to every query. A common pattern is a multi-tenant application where every table has a tenant_id column and an RLS policy that restricts each query to rows matching the current session's tenant_id, stored in a session variable set by the application.

RLS is not a substitute for role-based permission grants, but it is a useful complement when application code might forget to add the tenant_id filter. The combination is grant role permissions at the schema and table level, use RLS policies for the per-row tenant boundaries that are easy to miss in application code. The performance cost of RLS policies on indexed predicates is small but non-zero, and the policies become part of the query plan in ways that occasionally surprise the EXPLAIN output.

Rotation discipline

Credentials need to rotate. The mechanism that works in practice is to have two valid credentials per service role at any time and to overlap the validity windows during rotation. Postgres supports multiple passwords per role only awkwardly (through SCRAM-SHA-256 channel binding tricks or external authentication), so the simpler pattern is to have two roles per service (app_v1 and app_v2) and to alternate between them on rotation. The rotation cycle is: deploy app config pointing at app_v2 alongside the old app_v1 config; verify; remove app_v1 credentials; create new app_v3 credentials for the next rotation.

The discipline that does not work is having a single credential and rotating it in place, which requires a synchronized application restart and produces an outage if anything goes wrong. The double-credential pattern eliminates the outage risk at the cost of a few hours of overlap.

Five anti-patterns

Anti-pattern one: the application connects as the database owner. Owner role has implicit ALL PRIVILEGES on everything they own, including DROP TABLE. A compromised application can destroy the database. The fix is a separate application role with explicit grants.

Anti-pattern two: every developer has superuser access. Convenient in development, dangerous in production. The fix is a read-only group role for most developers and a small set of break-glass procedures for the rare cases that need elevated access.

Anti-pattern three: GRANT statements scattered through migration files with no overarching pattern. The fix is a single source of truth (often a roles.sql file run idempotently) that establishes the role structure, with migrations only adding object-level grants for new tables.

Anti-pattern four: passwords stored in plain text in connection strings. The fix is .pgpass files for human users (with appropriate file permissions), and environment variables or secret managers for application credentials.

Anti-pattern five: the read-only role can SELECT from system catalogs in ways that expose sensitive information. The fix is to use the pg_read_all_data role (Postgres 14+) for explicit data read-only access without inadvertent catalog access, or to REVOKE catalog access from custom read-only roles.

The deeper observation

Role design is one of the database operational disciplines that pays off slowly and asymmetrically. The cost is upfront and visible: a few hours of design work, a slightly more complex setup, a discipline around how new objects get permissioned. The payoff is invisible most days and very valuable on the rare days when something has gone wrong: an incident response that can answer who ran what query, a credential rotation that does not require an emergency deploy, a contractor onboarding that takes minutes rather than hours. Teams that invest in role design early tend to discover that the discipline of explicit permissions improves their thinking about access patterns more broadly. Teams that put it off tend to discover it on the day they have to retrofit it under incident pressure.