Roles & Permissions

PostgreSQL doesn't actually have a separate concept of "users" — everything is a role, and a role that's allowed to log in is what other databases would call a user.

Creating a role

CREATE ROLE app_reader WITH LOGIN PASSWORD 'a-real-secret';

CREATE ROLE

WITH LOGIN is what makes this role usable as a connection identity — a role created without it can still be useful (for grouping permissions together), but nothing can authenticate as it directly. This is the "roles vs. users" unification: CREATE USER is actually just an alias for CREATE ROLE ... WITH LOGIN.

Granting privileges

A freshly created role can't do anything until you explicitly grant it access:

GRANT SELECT ON products, customers TO app_reader;

GRANT

This role can now read from products and customers, but has no INSERT, UPDATE, or DELETE rights, and can't touch any other table — exactly the least-privilege setup you'd want for, say, a reporting tool that only needs to read data.

Grouping permissions with a role

CREATE ROLE analytics_team;
GRANT SELECT ON orders, order_totals TO analytics_team;
GRANT analytics_team TO app_reader;

CREATE ROLE
GRANT
GRANT

analytics_team here is a role with no LOGIN — nobody connects as it directly. Instead it's a bundle of permissions that gets granted to actual login roles, so adding a new analyst later is one GRANT analytics_team TO new_person; instead of re-granting every individual table.

Revoking access

REVOKE SELECT ON customers FROM app_reader;

REVOKE