Roles and privileges
How do roles and privileges work in PostgreSQL?
In PostgreSQL, users and groups are both roles. GRANT gives a role privileges on objects, and a role that is a member of another role can use that role's privileges.
PostgreSQL controls access with roles. A role can act as a user (it has the LOGIN attribute), as a group, or both, and roles own database objects. Privileges such as SELECT, INSERT, UPDATE and USAGE are given on specific objects with GRANT and taken away with REVOKE.
Making one role a member of another lets it use the other role's privileges, so a common pattern is to grant privileges to group roles and add login roles to those groups. Roles exist for the whole database cluster, not per database. An object's owner and superusers can do anything with it, so keep application roles separate from owners and superusers.
Example
The example creates a group role named reporting, a login role named analyst that belongs to it, and grants SELECT to the group. has_table_privilege confirms that analyst can read the table but cannot insert into it.
CREATE ROLE reporting NOLOGIN;
CREATE ROLE analyst LOGIN IN ROLE reporting;
CREATE TABLE revenue_daily (day date PRIMARY KEY, amount numeric);
GRANT SELECT ON revenue_daily TO reporting;
SELECT has_table_privilege('analyst', 'revenue_daily', 'SELECT') AS can_select,
has_table_privilege('analyst', 'revenue_daily', 'INSERT') AS can_insert;Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).