Roles & Permissions

PostgreSQL uses a single, unified concept — the "role" — for what many other systems split into two separate ideas: users and groups. A role can log in like a user, act as a group that other roles belong to, or both at once.

Note

Unlike databases with entirely separate user and role/group systems, every PostgreSQL role is the same underlying object. Whether a particular role behaves like a "user" or a "group" just comes down to its attributes (does it have `LOGIN`?) and how other roles are made members of it.

CREATE ROLE vs CREATE USER

CREATE USER is simply CREATE ROLE with the LOGIN attribute already turned on — they create the same kind of object.

create-role-vs-user.sql

SQL

-- These two are functionally almost identical:
CREATE ROLE app_user WITH LOGIN PASSWORD 'change-me';
CREATE USER app_user WITH PASSWORD 'change-me'; -- LOGIN is implied

-- A role WITHOUT login is typically used as a permission group,
-- never connected to directly:
CREATE ROLE reporting_group NOLOGIN;

GRANT and REVOKE

Privileges on specific database objects — tables, schemas, sequences, functions — are given out with GRANT and taken away with REVOKE.

grant-revoke.sql

SQL

GRANT SELECT ON orders TO reporting_group;
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
REVOKE INSERT, UPDATE ON orders FROM app_user;

Role Membership and Inheritance

Roles can be members of other roles, inheriting their privileges. This is how you bundle permissions into reusable groups instead of granting the same set of privileges to every individual user one-by-one.

role-membership.sql

SQL

CREATE ROLE alice WITH LOGIN PASSWORD 'change-me';
GRANT reporting_group TO alice;
-- alice now inherits every privilege granted to reporting_group

Worked Example: A Read-Only Reporting Role and a Scoped App Role

least-privilege-roles.sql

SQL

-- A role for BI/reporting tools: read-only, nothing else.
CREATE ROLE reporting_group NOLOGIN;
GRANT CONNECT ON DATABASE storefront TO reporting_group;
GRANT USAGE ON SCHEMA public TO reporting_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_group;

-- Make sure FUTURE tables are covered too, not just existing ones.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO reporting_group;

CREATE ROLE bi_tool WITH LOGIN PASSWORD 'change-me';
GRANT reporting_group TO bi_tool;

-- An application role: only the privileges the app actually needs,
-- nothing that lets it alter schema or touch tables it doesn't use.
CREATE ROLE storefront_app WITH LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE storefront TO storefront_app;
GRANT USAGE ON SCHEMA public TO storefront_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders, order_items, customers
  TO storefront_app;
-- Deliberately no privileges on, say, an internal audit_log table.

Least privilege

Grant each role only the privileges it actually needs to do its job — nothing more. An application role almost never needs `SUPERUSER`, the ability to create/drop tables, or access to every table in the database. Narrower roles limit the damage a compromised credential or a buggy query can do.

\du in psql lists all roles and their attributes.
REVOKE ALL ... FROM PUBLIC is worth knowing — by default some privileges are granted to the implicit PUBLIC pseudo-role, which every role is a member of.
Role passwords set inline in SQL as shown above are for illustration — manage real credentials through your organization's secrets tooling.