Roles and Permissions in PostgreSQL

PostgreSQL uses a single unified concept called a "role" to represent both users and groups — there is no separate USER object distinct from ROLE. A role can log in (acting as a user), can be granted to other roles (acting as a group), or both. Managing roles and the privileges granted to them is how PostgreSQL controls who can do what to which objects.

Why it matters

Every connection to PostgreSQL acts as a role, and what that role may do is the main safeguard for your data. An application that connects as a superuser turns any bug or injection flaw into total loss. Granting each role only what it needs is one of the most effective security measures there is.

Creating Roles

CREATE ROLE role_name LOGIN PASSWORD 'secret'; creates a role that can connect to the database (the LOGIN attribute is what distinguishes a "user-like" role from a pure group). CREATE ROLE app_readers; without LOGIN creates a group-like role meant only to hold and distribute a set of privileges. Other useful attributes include SUPERUSER, CREATEDB, and CREATEROLE, granted sparingly.

GRANT and REVOKE

Privileges on specific objects are given with GRANT privilege ON object TO role; — for example, GRANT SELECT, INSERT ON orders TO app_writer;. REVOKE removes a previously granted privilege using the same shape: REVOKE INSERT ON orders FROM app_writer;. Common object-level privileges include SELECT, INSERT, UPDATE, DELETE for tables, and USAGE for schemas and sequences (needed alongside table grants for a role to actually reach objects inside a non-public schema or use identity/sequence-backed columns).

Role Inheritance and Group Membership

A role can be granted membership in another role with GRANT group_role TO member_role;. By default, a member role automatically inherits (uses) the privileges of any role it belongs to, without needing to explicitly switch — this is controlled by the INHERIT/NOINHERIT attribute. This makes it practical to define privilege sets once on a group role (like app_readers with SELECT on all app tables) and simply add or remove individual login roles from that group as staff change, rather than re-granting privileges one by one.

Example

CREATE ROLE app_readers;
GRANT USAGE ON SCHEMA public TO app_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readers;

CREATE ROLE analyst LOGIN PASSWORD 'S3curePass!';
GRANT app_readers TO analyst;

-- 'analyst' can now SELECT from every table in public via inherited membership

CREATE ROLE
GRANT
GRANT
CREATE ROLE
GRANT ROLE

Exercise

This exercise uses PostgreSQL roles, so it needs a PostgreSQL server. Create a group role readonly that cannot log in and may only read every table in the public schema. Then create a login role report_user with a password and make it a member of readonly, so that it receives those permissions through membership.

Expected result: connected as report_user, SELECT works on the tables of the public schema and INSERT, UPDATE and DELETE are refused.

-- TODO: group role readonly, without login

-- TODO: let readonly use the public schema and read all its tables

-- TODO: login role report_user with a password

-- TODO: make report_user a member of readonly

CREATE ROLE readonly NOLOGIN;

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

CREATE ROLE report_user LOGIN PASSWORD 'use-a-long-random-password';

GRANT readonly TO report_user;

Quick quiz

Interview questions