Roles, Privileges & Row-Level Security

Least-privilege roles, GRANT and REVOKE, and policies that filter rows per user inside the database.

PostgreSQL has one concept for users and groups: roles. Privileges are granted to roles per object (table, schema, function). Row-level security goes further and decides which rows of a table each role may see or change.

A reporting role and a team lead

reporting may only SELECT departments and projects: reading employees and updating projects are refused.

SET ROLE switches the current role inside the session (as a superuser can) to test what it is allowed.

With row-level security enabled and a policy USING (dept_id = 2), lead_vlsi sees only VLSI Design's three people.

The session's own role (postgres, the table owner and a superuser) is not restricted by the policy, so it still sees all 12 rows: owners bypass row-level security unless FORCE ROW LEVEL SECURITY is set, and superusers always do.

Good practice

The web application connects as postgres Create an application role with only SELECT/INSERT/UPDATE/DELETE on its own tables

If the application is compromised (e.g. SQL injection), a superuser connection gives the attacker the whole server, including reading files and running programs.

How do you give a role read-only access to all current and future tables in a schema?

GRANT USAGE ON SCHEMA s TO r; GRANT SELECT ON ALL TABLES IN SCHEMA s TO r; ALTER DEFAULT PRIVILEGES IN SCHEMA s GRANT SELECT ON TABLES TO r;

What does row-level security add on top of GRANT?

GRANT decides whether a role may access a table at all; RLS policies decide which rows within it are visible or modifiable, per role and command.