Roles & Access Control

Granting privileges one user at a time works fine when you have a handful of accounts, but it quickly becomes unmanageable as a team or application grows. A role solves this by acting as a named, reusable bundle of privileges — you define what the role can do once, and then assign that role to as many users as need those capabilities. In fact, in most modern databases (including PostgreSQL), a "user" is really just a role that happens to be allowed to log in.

Creating a role

A role with no login of its own

SQL

CREATE ROLE analyst;

A plain role like this cannot log in by itself — it exists purely as a container of privileges, ready to be handed out to real user accounts.

Granting privileges to a role, then the role to users

Instead of granting privileges directly to each individual, grant them once to the role, and then grant the role itself to every user who should have those capabilities:

Building an analyst role and assigning it

SQL

CREATE ROLE analyst;
GRANT SELECT ON orders TO analyst;
GRANT SELECT ON customers TO analyst;
GRANT SELECT ON products TO analyst;

-- now assign the role to real users:
GRANT analyst TO priya;
GRANT analyst TO wei;

Both priya and wei immediately inherit every privilege granted to analyst. If a new table is added to the reporting set later, granting SELECT on it to the analyst role instantly extends access to every user holding that role — no need to touch each account individually.

Worked example: read-only analyst vs full-access admin

Two roles with clearly different scopes

SQL

-- read-only role for reporting and analytics
CREATE ROLE analyst;
GRANT SELECT ON orders, customers, products TO analyst;

-- full-access role for platform administrators
CREATE ROLE admin;
GRANT ALL PRIVILEGES ON orders, customers, products TO admin;

-- assign each person exactly the role that matches their job
GRANT analyst TO priya;
GRANT analyst TO wei;
GRANT admin TO devops_lead;

Later, revoking access from someone who changes teams is just as simple — remove them from the role rather than hunting down and revoking a list of individual privileges:

Removing access when responsibilities change

SQL

REVOKE analyst FROM priya;
GRANT admin TO priya;

Why this is better than granting to individuals

Centralized management — privileges live in one place (the role definition), so a policy change is a single statement instead of one statement per user.
Audit clarity — asking "who can delete orders?" becomes "who holds the role that has DELETE on orders?", which is far easier to answer and to review than scanning individual grants.
Consistency — every analyst genuinely has the same access as every other analyst, because they all inherit from the same role rather than from privileges granted by hand over time.
Safer onboarding/offboarding — granting or revoking one role when someone joins or leaves a team is quick and hard to get wrong, compared to reconstructing a list of individual grants.

Model roles after job functions, not individuals

Design roles around what a job needs to do — analyst, support agent, billing admin, platform admin — rather than creating a bespoke set of privileges per person. When someone's job changes, you swap their role instead of auditing a unique, hand-built privilege set.