TopolearnLeast-privilege roles limit the damage of a leaked password or an SQL injection bug.
In PostgreSQL users and groups are both roles. A role with LOGIN can connect; a role without it acts as a group that others are granted membership in. Roles are cluster-wide, shared by all databases.
Privileges are granted on objects:
GRANT CONNECT ON DATABASE shop TO app;GRANT USAGE ON SCHEMA app TO app;— needed to see anything inside the schema.GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app;GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app;forserialcolumns.
ON ALL TABLES only covers tables that exist now. For future tables, use ALTER DEFAULT PRIVILEGES — it applies to objects created later by the role that runs it (or FOR ROLE owner).
A clean pattern: a owner role (app_owner, no login) owns the schema and runs migrations; group roles app_rw and app_ro hold read-write and read-only privileges; login roles for services and people are members of those groups. Avoid letting the application connect as the owner or as a superuser: owners can drop tables and superusers bypass all checks, including row-level security.
Since PostgreSQL 14 the predefined roles pg_read_all_data and pg_write_all_data make read-only analytics users easy. Use \du and \dp in psql to inspect roles and table privileges.
Example
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_ro NOLOGIN;
CREATE ROLE app_rw NOLOGIN;
CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_ro, app_rw;
-- tables app_owner creates later get these privileges automatically
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
CREATE ROLE api LOGIN PASSWORD 'change-me' IN ROLE app_rw;
CREATE ROLE analyst LOGIN PASSWORD 'change-me' IN ROLE app_ro;
SET ROLE app_owner;
CREATE TABLE app.orders (id int PRIMARY KEY);
RESET ROLE;
SELECT has_table_privilege('analyst', 'app.orders', 'SELECT'); -- t
SELECT has_table_privilege('analyst', 'app.orders', 'INSERT'); -- fRemember
- Users and groups are both roles; LOGIN makes a role a user
- Schema USAGE plus table privileges are both needed
- ALTER DEFAULT PRIVILEGES covers tables created in the future
Watch out for
- Granting on ALL TABLES once and wondering why new tables are inaccessible.