Postgres Role

PostgreSQL Roles — Production Guide

PostgreSQL unifies the ideas of “user” and “group” into a single object called a Role. In production, well-designed role management enforces the Principle of Least Privilege, reduces blast radius on compromise, and simplifies operational tasks such as migrations, monitoring, and auditing.

Core concepts

Roles with LOGIN are used by people or applications to connect.
Roles without LOGIN are typically group/permission containers (grant these to login roles).
Roles are cluster-global: one role can be used across multiple databases in the same Postgres instance.

Naming and hierarchy (recommended)

db_owner_<app> — NOLOGIN role that owns schema objects and performs DDL. Only assigned to migration/deploy accounts.
db_writer_<app> — NOLOGIN role for application DML (SELECT/INSERT/UPDATE/DELETE).
db_reader_<app> — NOLOGIN role for read-only access (BI, analytics).
deploy_<app> — LOGIN role used by CI/CD or operators for schema changes (granted db_owner_<app>).
app_<app> — LOGIN role used by the application process (granted db_writer_<app>).

Keep names consistent and include the application/environment (e.g., db_writer_api_prod).

Best practices

Principle of Least Privilege: Give roles only the privileges they need and nothing more.
Avoid using SUPERUSER for routine tasks. Prefer targeted predefined roles (e.g., pg_monitor, pg_signal_backend).
Use NOINHERIT vs INHERIT intentionally: NOINHERIT prevents implicit privilege inheritance when a role logs in and runs queries.
Use ALTER DEFAULT PRIVILEGES so newly created objects automatically grant the correct rights to your writer/reader roles.
Rotate credentials and avoid hardcoding passwords in code; use secrets managers (Vault, AWS Secrets Manager, etc.).
Use a connection pooler (pgbouncer) with a dedicated application role to limit connection churn and avoid credential sprawl.
Limit pg_hba.conf access by source IP ranges and prefer network-level controls (VPC, security groups) when possible.
Audit actions: enable logging for DDL and DML as needed and use monitoring roles for metrics collection, not for full administration.

Security-focused tips

Lockdown public schema: revoke public privileges and create dedicated schemas per application.
Separate schema ownership from application runtime roles to prevent an app from dropping or altering schema.
Create short-lived privileged accounts for human operators or automation that require DDL, then revoke the role after use.
Use role membership for responsibilities: grant db_owner to a deploy user, not to the application.
For cloud-managed Postgres, integrate with cloud IAM where supported (RDS IAM, Cloud SQL IAM) instead of long-lived passwords when possible.

Example production layout and safe script

The following example shows a safer, explicit setup. Run as a superuser (e.g., postgres) connected to the target database. Replace my_prod_db, schema, and password placeholders with secure values sourced from your secrets manager.


-- 1) Reduce default privileges
REVOKE ALL ON DATABASE my_prod_db FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
 
-- 2) Create group roles (NOLOGIN)
CREATE ROLE db_owner_app NOLOGIN;
CREATE ROLE db_writer_app NOLOGIN;
CREATE ROLE db_reader_app NOLOGIN;
 
-- 3) Create schema and set ownership to the owner role
CREATE SCHEMA IF NOT EXISTS app_data AUTHORIZATION db_owner_app;
 
-- 4) Grant minimum usage to writer/reader
GRANT USAGE ON SCHEMA app_data TO db_writer_app, db_reader_app;
 
-- 5) Ensure future objects are permissioned correctly
ALTER DEFAULT PRIVILEGES FOR ROLE db_owner_app IN SCHEMA app_data
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO db_writer_app;
 
ALTER DEFAULT PRIVILEGES FOR ROLE db_owner_app IN SCHEMA app_data
    GRANT SELECT ON TABLES TO db_reader_app;
 
-- 6) Create actual login users and grant roles
CREATE ROLE deploy_app WITH LOGIN PASSWORD '<<DEPLOY_PASSWORD_FROM_VAULT>>';
GRANT db_owner_app TO deploy_app;
 
CREATE ROLE app_user WITH LOGIN PASSWORD '<<APP_PASSWORD_FROM_VAULT>>';
GRANT db_writer_app TO app_user;
 
CREATE ROLE analyst_jane WITH LOGIN PASSWORD '<<ANALYST_PASSWORD_FROM_VAULT>>';
GRANT db_reader_app TO analyst_jane;
 
-- 7) Monitoring / maintenance roles (use built-in roles where appropriate)
GRANT pg_monitor TO monitoring_agent; -- `monitoring_agent` is a login role used by your monitoring tool
 
-- 8) Example: restrict app_user from creating objects
ALTER ROLE app_user NOCREATEROLE NOCREATEDB;
 
-- 9) Test: ensure app_user can access application tables but not alter them
-- (Run as app_user) SELECT * FROM app_data.some_table LIMIT 1;

Checklist before go-live

Remove any default superuser credentials from app config.
Ensure passwords are stored in a secrets manager and not in source control.
Configure connection pooling and set connection limits for app roles.
Verify ALTER DEFAULT PRIVILEGES for each schema used by the app.
Test backup and restore with the role layout to ensure restores preserve ownership and privileges.
Confirm monitoring roles have only read access to system views and no DDL privileges.

Operational notes

Emergency access: keep one minimally used break-glass superuser credential locked in a vault; audit all uses.
Role cleanup: periodically review role membership and privileges; revoke unused roles and rotate credentials.
Migrations: run schema changes via the deploy account; prefer CI/CD pipelines with ephemeral credentials.