Roles, privileges and least privilege

conceptThe concept

A role with LOGIN is a user; without LOGIN it is a group. GRANT readers TO alice makes alice a member. Privileges: USAGE on a schema is needed to see anything in it; SELECT, INSERT, UPDATE, DELETE on tables (even per column); USAGE on sequences for identity defaults; EXECUTE on functions.

GRANT ... ON ALL TABLES IN SCHEMA covers existing tables only. ALTER DEFAULT PRIVILEGES covers tables created later by a given role, which is what makes migrations not break read-only users. The owner of an object can always do everything with it, so applications should not log in as the owner.

Predefined roles cover common needs: pg_read_all_data and pg_write_all_data (14), pg_monitor for monitoring views, pg_maintain (17) for VACUUM, ANALYZE and REINDEX without ownership, pg_signal_backend to cancel queries.

whyWhy it matters

When an application account owns its tables, a SQL injection can DROP them. When a reporting user has write access, a mistaken query can change production data. Least privilege limits the blast radius of every mistake.

Use caseReal-world scenario

The shop uses four roles: shop_owner (owns objects, used by migrations), shop_app (read-write data, no DDL), shop_readonly (analysts) and monitoring (pg_monitor).

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE ROLE shop_readonly NOLOGIN;
CREATE ROLE shop_app NOLOGIN;
CREATE ROLE alice LOGIN IN ROLE shop_readonly;
CREATE ROLE web LOGIN IN ROLE shop_app;

GRANT USAGE ON SCHEMA public TO shop_readonly, shop_app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO shop_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO shop_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO shop_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO shop_readonly;

SELECT r.rolname, r.rolcanlogin, array_agg(g.rolname) AS member_of
FROM pg_roles r LEFT JOIN pg_auth_members m ON m.member = r.oid LEFT JOIN pg_roles g ON g.oid = m.roleid
WHERE r.rolname IN ('alice', 'web', 'shop_readonly', 'shop_app')
GROUP BY 1, 2 ORDER BY 1;
ROLLBACK;

BEGIN;
CREATE ROLE shop_readonly NOLOGIN;
GRANT USAGE ON SCHEMA public TO shop_readonly;
GRANT SELECT ON customers TO shop_readonly;
SET LOCAL ROLE shop_readonly;
SELECT current_user, count(*) FROM customers;
UPDATE customers SET tier = 'gold' WHERE id = 1;

BEGIN;
CREATE ROLE support NOLOGIN;
GRANT USAGE ON SCHEMA public TO support;
GRANT SELECT (id, name, city, country, tier) ON customers TO support;
SET LOCAL ROLE support;
SELECT id, name, tier FROM customers WHERE id = 1;
SELECT email FROM customers WHERE id = 1;

SELECT grantee, privilege_type, count(*) AS tables
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
GROUP BY 1, 2
ORDER BY 1, 2;
SELECT rolname FROM pg_roles WHERE rolname LIKE 'pg\_%' ORDER BY 1;

internalsUnder the hood

Privileges are stored as ACLs on each object (relacl in pg_class), shown by \dp in psql. PUBLIC is a pseudo-role meaning everyone; since 15 PUBLIC no longer has CREATE on the public schema. Passwords should use SCRAM-SHA-256; MD5 password support is deprecated as of 18.

Avoid / preferDo this, not that

GRANT ALL ON ALL TABLES IN SCHEMA public TO app;

GRANT SELECT, INSERT, UPDATE ON orders, order_items TO app;
GRANT SELECT ON products, customers TO app;

-- the application connects as the table owner

-- an owner role runs migrations; the application uses a separate role with DML only

PitfallsCommon mistakes

• Letting the application log in as the table owner or a superuser.
• Granting on ALL TABLES once and forgetting ALTER DEFAULT PRIVILEGES, so the next migration's tables are invisible to read-only users.
• Granting privileges directly to individual users instead of to group roles, which makes access reviews painful.

LimitsWhen not to use it

Do not create one database role per end user of a web application. Use a few application roles and enforce per-user access with row-level security and session settings.

SecuritySecurity notes

Use ALTER DEFAULT PRIVILEGES so tables created later get the right grants. Since PostgreSQL 15, ordinary users can no longer create objects in the public schema by default; keep it that way.

ChallengeTry it yourself

BEGIN;
CREATE ROLE reporting NOLOGIN;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
SELECT has_table_privilege('reporting', 'orders', 'SELECT') AS can_read,
       has_table_privilege('reporting', 'orders', 'UPDATE') AS can_update;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Privileges
• Row security policies
• Predefined roles