Roles Basics

8 examples for PostgreSQL roles - 6 basic and 2 intermediate. In PostgreSQL, roles are the universal principal: login users and group-like permission bundles are the same object type.

Prerequisites

-- Connect as a role that can CREATE ROLE (typically superuser or CREATEROLE)
\du

• Prefer one role per application service, not shared human passwords.
• Use group roles (NOLOGIN) to model permission tiers.

Basic Examples

1. Create Login Role

CREATE ROLE app_api LOGIN PASSWORD 'from-vault-rotate-quarterly';
ALTER ROLE app_api CONNECTION LIMIT 50;

• LOGIN allows password or cert authentication.
• CONNECTION LIMIT caps runaway pool misconfiguration per role.
• Store passwords in a secrets manager; rotate on offboarding.

2. Create Group Role (NOLOGIN)

CREATE ROLE app_readers NOLOGIN;
CREATE ROLE app_writers NOLOGIN;
 
GRANT app_readers TO app_api;
GRANT app_writers TO app_migrator;

• Group roles hold privileges; login roles inherit via GRANT group TO user.
• SET ROLE lets a login assume a group temporarily.
• Model: app_api inherits app_readers only in read-only deploy profiles if needed.

Related: GRANT/REVOKE Patterns - object privileges

3. Role Inheritance

CREATE ROLE tenant_admin NOLOGIN;
CREATE ROLE support_agent LOGIN INHERIT PASSWORD 'vault';
 
GRANT tenant_admin TO support_agent;
 
-- support_agent automatically has tenant_admin privileges when INHERIT true (default)

• INHERIT (default): granted roles' privileges apply automatically.
• NOINHERIT: must SET ROLE tenant_admin to activate privileges.
• Use NOINHERIT for break-glass roles audited on activation.

4. List Roles and Membership

\du+
SELECT r.rolname, m.rolname AS member_of
FROM pg_roles r
LEFT JOIN pg_auth_members am ON am.member = r.oid
LEFT JOIN pg_roles m ON m.oid = am.roleid
WHERE r.rolname = 'app_api';

• \du+ shows attributes: Superuser, Create role, Create DB, Replication.
• Membership graph gets complex - diagram it in onboarding docs.
• pg_roles includes roles you cannot LOGIN as.

5. Alter Role Attributes

ALTER ROLE app_api SET statement_timeout = '30s';
ALTER ROLE app_api SET search_path = app, public;
ALTER ROLE app_migrator CREATEDB;  -- rarely - prefer explicit database grants

• Per-role GUC defaults apply at login.
• search_path on app roles prevents schema hijack surprises.
• Avoid broad CREATEDB/CREATEROLE on application login roles.

6. Drop Role Safely

-- Reassign owned objects first
REASSIGN OWNED BY old_app TO app_owner;
DROP OWNED BY old_app;
DROP ROLE old_app;

• Cannot drop role that owns objects or has active sessions.
• DROP OWNED removes privileges; review before running on production.
• Offboarding checklist: reassign, drop memberships, revoke login.

Intermediate Examples

7. Role Hierarchy for Environments

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_staging LOGIN PASSWORD 'vault';
CREATE ROLE app_prod LOGIN PASSWORD 'vault';
 
GRANT app_owner TO app_staging, app_migrator;
-- app_prod inherits narrower grants applied separately

• Same schema code, different login roles per environment.
• CI uses app_migrator; runtime uses app_api without DDL rights.
• Never copy production password hash to staging.

Related: Default Privileges - migrations create objects as owner

8. SET ROLE for Audited Elevation

CREATE ROLE ddl_runner NOLOGIN;
GRANT ddl_runner TO deploy_user WITH ADMIN OPTION FALSE;
 
-- In migration session only
SET ROLE ddl_runner;
CREATE TABLE audit.sample (id int);
RESET ROLE;

• SET ROLE records in logs when log_line_prefix includes user and session user.
• Migration role should not be the object owner in runtime if avoidable.
• Pair with NOINHERIT on deploy_user for strict elevation.

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+.