critwire · Learn · Learn's data

Roles and privileges d5

Prerequisites: Transactions (d4)

Give a reporting tool exactly the access it needs: roles, membership, owners, GRANT and REVOKE, default privileges, least privilege and a taste of row-level security. Everything runs in your own PostgreSQL, on the lab tables (the setup page).

Concept

A database server decides who may do what with roles and privileges:

How to look: in psql, \du lists roles and their attributes, \drg (psql 16+) memberships with their options, \dn+ schemas with their privileges, \dp tables' privileges and policies, \ddp default privileges. In SQL, has_table_privilege(role, table, privilege) and its siblings (has_schema_privilege, has_database_privilege, has_function_privilege) answer for any role, and information_schema.role_table_grants lists table grants.

These labs create roles, so they need the CREATEROLE privilege. The postgres superuser has it. On your own install, as another user, run once as the superuser: ALTER ROLE <you> CREATEROLE; and, on PostgreSQL 16 and later, ALTER ROLE <you> SET createrole_self_grant = 'set, inherit';, then reconnect. Since PostgreSQL 16, a non-superuser who creates a role may administer it but not act as it: without the second setting, the labs' SET ROLE, ALTER DEFAULT PRIVILEGES FOR ROLE and DROP OWNED BY are refused. (Tested on PostgreSQL 16 as a login with only those two settings.)

Every lab below is one transaction, typed exactly as shown in psql, BEGIN to ROLLBACK, with ON_ERROR_STOP off as in Transactions, d4: a statement marked -- fails: fails on purpose, and the savepoint before it lets the transaction go on. Each lab drops its roles before the ROLLBACK anyway: that is the cleanup a script you COMMIT on a real server needs, and roles, being the server's, are the thing most easily left behind.

Worked example

How do I give a reporting tool read-only access to one schema and nothing else? Make a group role that holds the privileges, learn_reporting, and a login for the tool, learn_powerbi, that is a member of it. The group gets USAGE on learn_curriculum and SELECT on its tables; nothing else. Then act as the tool: reading works, writing and making tables don't.

Checked 2026-11-02 on PostgreSQL 16.15, PostGIS 3.4.2, GEOS 3.12.1, GDAL 3.8.4.

d5-reporting · PostgreSQL with PostGIS 

BEGIN;
CREATE ROLE learn_reporting NOLOGIN;
GRANT USAGE ON SCHEMA learn_curriculum TO learn_reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA learn_curriculum TO learn_reporting;
CREATE ROLE learn_powerbi LOGIN IN ROLE learn_reporting;
SET ROLE learn_powerbi;
SAVEPOINT try;
-- fails: permission denied for table species
UPDATE species SET common_name = 'Devil' WHERE id = 48;
ROLLBACK TO SAVEPOINT try;
-- fails: permission denied for schema learn_curriculum
CREATE TABLE my_report (id integer);
ROLLBACK TO SAVEPOINT try;
SELECT current_user AS reading_as, count(*) AS records, count(DISTINCT species_id) AS species
FROM occurrences;
RESET ROLE;
DROP OWNED BY learn_powerbi, learn_reporting;
DROP ROLE learn_powerbi, learn_reporting;
ROLLBACK;
-- result: d5-reporting · 1 row · sha256 df4ba6a052e1
reading_as | records | species
learn_powerbi | 40395 | 71

In d5.sql, with the blocks it runs after.

learn_powerbi holds no privilege of its own: it reads through its membership in learn_reporting. SET ROLE is how you test a role without its password; a superuser may switch to any role, and you, with createrole_self_grant, to those you made. For the real tool, give the login a password with psql's \password learn_powerbi (it isn't echoed or logged), and put the server's address, the database and that login in the tool's connection settings (Power Query, p1).

Lab

  1. What the reporting role may do, as the server sees it. has_…_privilege answers for any role, so you needn't switch to it:

    d5-privileges · PostgreSQL with PostGIS 

    BEGIN;
CREATE ROLE learn_reporting NOLOGIN;
GRANT USAGE ON SCHEMA learn_curriculum TO learn_reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA learn_curriculum TO learn_reporting;
SELECT object, privilege, allowed
FROM (
  SELECT 1 AS level, 'this database' AS object, p AS privilege,
         has_database_privilege('learn_reporting', current_database(), p) AS allowed
  FROM unnest(ARRAY['CONNECT', 'TEMPORARY', 'CREATE']) AS p
  UNION ALL
  SELECT 2, 'schema ' || s, p, has_schema_privilege('learn_reporting', s, p)
  FROM unnest(ARRAY['learn_curriculum', 'public']) AS s, unnest(ARRAY['USAGE', 'CREATE']) AS p
  UNION ALL
  SELECT 3, 'table species', p, has_table_privilege('learn_reporting', 'species', p)
  FROM unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE']) AS p
  UNION ALL
  SELECT 4, 'function ST_Area(geometry)', 'EXECUTE',
         has_function_privilege('learn_reporting', 'ST_Area(geometry)', 'EXECUTE')
) AS checked
ORDER BY level, object, privilege;
DROP OWNED BY learn_reporting;
DROP ROLE learn_reporting;
ROLLBACK;
    -- result: d5-privileges · 13 rows · sha256 18b775de531d
object | privilege | allowed
this database | CONNECT | false
this database | CREATE | false
this database | TEMPORARY | false
schema learn_curriculum | CREATE | false
schema learn_curriculum | USAGE | true
… 8 more rows

    In d5.sql, with the blocks it runs after.

    The two grants show as SELECT on the table and USAGE on learn_curriculum. The other trues come from PUBLIC: USAGE on public and every function. The database rows are this server's answer, read from its ACLs: critwire's test database grants CONNECT and TEMPORARY only to named roles, so the role has neither. In a new database of yours PUBLIC has both, and the same block shows true for them; likewise the false for CREATE on public holds only where PUBLIC's CREATE is gone (a new database since PostgreSQL 15, or revoked by hand). So run the block on the server that matters instead of reasoning from the defaults. PUBLIC's privileges are harmless for reading, but "nothing else" also means other databases on the server: there, revoke CONNECT from PUBLIC, as critwire's test database does. As the database's owner, on a server you run (critwire's tests never change a database's privileges, so this block isn't run here; learn is your lab database):

    d5-database · PostgreSQL with PostGIS 

    REVOKE CONNECT, TEMPORARY ON DATABASE learn FROM PUBLIC;
GRANT CONNECT ON DATABASE learn TO learn_reporting;
ALTER ROLE learn_powerbi SET default_transaction_read_only = on;

    Not run here: changes the database's own privileges, which the tests never do.

    In d5.sql, with the blocks it runs after.

    The last line is a guard against mistakes, not a lock: every session of that login starts read-only, so a privilege granted by mistake doesn't turn into a write, but the session itself may switch it off. Settings like this belong to the login; members don't inherit them from a group.

  2. Default privileges. The loading job, learn_etl, owns a schema of report tables, learn_reports, and the reporting role reads them. Grant on all tables, make another, then set default privileges and make a third:

    d5-defaults · PostgreSQL with PostGIS 

    BEGIN;
CREATE ROLE learn_etl NOLOGIN;
CREATE ROLE learn_reporting NOLOGIN;
GRANT USAGE ON SCHEMA learn_curriculum TO learn_etl;
GRANT SELECT ON occurrences TO learn_etl;
CREATE SCHEMA learn_reports AUTHORIZATION learn_etl;
GRANT USAGE ON SCHEMA learn_reports TO learn_reporting;
SET ROLE learn_etl;
CREATE TABLE learn_reports.report_1_by_year AS
SELECT year, count(*) AS records FROM occurrences WHERE year IS NOT NULL GROUP BY year;
GRANT SELECT ON ALL TABLES IN SCHEMA learn_reports TO learn_reporting;
CREATE TABLE learn_reports.report_2_by_species AS
SELECT species_id, count(*) AS records FROM occurrences GROUP BY species_id;
RESET ROLE;
ALTER DEFAULT PRIVILEGES FOR ROLE learn_etl IN SCHEMA learn_reports
  GRANT SELECT ON TABLES TO learn_reporting;
SET ROLE learn_etl;
CREATE TABLE learn_reports.report_3_by_country AS
SELECT country_code, count(*) AS records FROM occurrences GROUP BY country_code;
RESET ROLE;
SELECT tablename AS table_name, tableowner AS owner,
       has_table_privilege('learn_reporting', format('%I.%I', schemaname, tablename), 'SELECT') AS reporting_can_read
FROM pg_tables
WHERE schemaname = 'learn_reports'
ORDER BY tablename;
DROP SCHEMA learn_reports CASCADE;
DROP OWNED BY learn_etl, learn_reporting;
DROP ROLE learn_etl, learn_reporting;
ROLLBACK;
    -- result: d5-defaults · 3 rows · sha256 637b64b91525
table_name | owner | reporting_can_read
report_1_by_year | learn_etl | true
report_2_by_species | learn_etl | false
report_3_by_country | learn_etl | true

    In d5.sql, with the blocks it runs after.

    The grant on all tables covered the one table that existed; the second was made after it and nobody can read it, the usual "the dashboard broke after last night's load". The third was covered by the default privileges, because learn_etl made it. Had you made it yourself, they wouldn't apply: name the role that really creates the tables in FOR ROLE. \ddp lists the defaults; the fix for tables already missed is one more GRANT … ON ALL TABLES. DROP SCHEMA … CASCADE removes the reports; DROP OWNED BY learn_etl would have removed them too, since it owns them.

  3. Why DROP ROLE alone fails. A role that was granted anything can't be dropped:

    d5-drop-role · PostgreSQL with PostGIS 

    BEGIN;
CREATE ROLE learn_reporting NOLOGIN;
GRANT USAGE ON SCHEMA learn_curriculum TO learn_reporting;
GRANT SELECT ON species, occurrences TO learn_reporting;
SAVEPOINT before_drop;
-- fails: role "learn_reporting" cannot be dropped because some objects depend on it
DROP ROLE learn_reporting;
ROLLBACK TO SAVEPOINT before_drop;
SELECT table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'learn_reporting'
ORDER BY table_name;
DROP OWNED BY learn_reporting;
DROP ROLE learn_reporting;
ROLLBACK;
    -- result: d5-drop-role · 2 rows · sha256 6be36802af13
table_name | privilege_type
occurrences | SELECT
species | SELECT

    In d5.sql, with the blocks it runs after.

    The error's detail lists what holds the role: privileges for schema learn_curriculum, then each table. DROP OWNED BY revokes them all at once; listing them by hand and revoking each works too, but misses what you didn't list.

  4. A taste of row-level security. Two partners, one in Australia and one in Brazil, may each read only the records from their country; a third role has SELECT but no policy. Each role writes what it sees into a temporary table (granted INSERT for this), so one query can compare them:

    d5-rls · PostgreSQL with PostGIS 

    BEGIN;
CREATE ROLE learn_partner_au NOLOGIN;
CREATE ROLE learn_partner_br NOLOGIN;
CREATE ROLE learn_no_policy NOLOGIN;
GRANT USAGE ON SCHEMA learn_curriculum TO learn_partner_au, learn_partner_br, learn_no_policy;
GRANT SELECT ON occurrences TO learn_partner_au, learn_partner_br, learn_no_policy;
ALTER TABLE occurrences ENABLE ROW LEVEL SECURITY;
CREATE POLICY partner_au ON occurrences FOR SELECT TO learn_partner_au USING (country_code = 'AU');
CREATE POLICY partner_br ON occurrences FOR SELECT TO learn_partner_br USING (country_code = 'BR');
CREATE TEMPORARY TABLE seen (reader text PRIMARY KEY, records bigint, species bigint, countries bigint);
GRANT INSERT ON seen TO learn_partner_au, learn_partner_br, learn_no_policy;
INSERT INTO seen
SELECT '(you, the owner)', count(*), count(DISTINCT species_id), count(DISTINCT country_code) FROM occurrences;
SET ROLE learn_partner_au;
INSERT INTO seen
SELECT current_user, count(*), count(DISTINCT species_id), count(DISTINCT country_code) FROM occurrences;
SET ROLE learn_partner_br;
INSERT INTO seen
SELECT current_user, count(*), count(DISTINCT species_id), count(DISTINCT country_code) FROM occurrences;
SET ROLE learn_no_policy;
INSERT INTO seen
SELECT current_user, count(*), count(DISTINCT species_id), count(DISTINCT country_code) FROM occurrences;
RESET ROLE;
SELECT reader, records, species, countries FROM seen ORDER BY records DESC, reader;
DROP OWNED BY learn_partner_au, learn_partner_br, learn_no_policy;
ALTER TABLE occurrences DISABLE ROW LEVEL SECURITY;
DROP ROLE learn_partner_au, learn_partner_br, learn_no_policy;
ROLLBACK;
    -- result: d5-rls · 4 rows · sha256 391fc1e17327
reader | records | species | countries
(you, the owner) | 40395 | 71 | 106
learn_partner_br | 5033 | 14 | 1
learn_partner_au | 4636 | 5 | 1
learn_no_policy | 0 | 0 | 0

    In d5.sql, with the blocks it runs after.

    The same query, count(*) FROM occurrences, gives each role a different answer, and none of them asked for a country. You, the owner, skip the policies and see every record. The role with SELECT and no policy sees nothing: with RLS on, no policy means no rows. DROP OWNED BY dropped both policies, since each named only its role. A policy can also read a table, USING (country_code IN (SELECT country_code FROM partner_countries WHERE role_name = current_user)), so one policy serves every partner; index the columns it filters on, because it runs inside every query.

Result

A group role with USAGE on one schema and SELECT on its tables, and a login that is its member, reads the 40,395 records and is refused writing and making tables. Its other abilities come from PUBLIC (public and functions, plus connecting and temporary tables where the database hasn't revoked them, as critwire's test database has), so read the actual ACLs; a server you run can revoke them per database. GRANT … ON ALL TABLES covers only tables that exist; default privileges cover the tables a named role makes later. DROP ROLE fails while the role holds privileges, and DROP OWNED BY clears them. With row-level security, the Australian partner saw 4,636 records, the Brazilian 5,033, a role without a policy none, and the owner all of them.

Exercise

The Tasmanian devil project's dashboard needs a role, learn_devil_dashboard, that reads the species list and the devil's records (species 48), and nothing else: no other species' records, no other lab table, no writing. Create it, prove that reading datasets and updating a record are refused, and show what it sees in occurrences and species. Clean up as the labs do.

Show answer

d5-ex · PostgreSQL with PostGIS 

BEGIN;
CREATE ROLE learn_devil_dashboard NOLOGIN;
GRANT USAGE ON SCHEMA learn_curriculum TO learn_devil_dashboard;
GRANT SELECT ON species, occurrences TO learn_devil_dashboard;
ALTER TABLE occurrences ENABLE ROW LEVEL SECURITY;
CREATE POLICY devil_only ON occurrences FOR SELECT TO learn_devil_dashboard USING (species_id = 48);
SET ROLE learn_devil_dashboard;
SAVEPOINT try;
-- fails: permission denied for table datasets
SELECT count(*) FROM datasets;
ROLLBACK TO SAVEPOINT try;
-- fails: permission denied for table occurrences
UPDATE occurrences SET month = 7 WHERE id = 6004132305;
ROLLBACK TO SAVEPOINT try;
SELECT count(*) AS records, count(DISTINCT species_id) AS species_with_records,
       (SELECT count(*) FROM species) AS species_rows
FROM occurrences;
RESET ROLE;
DROP OWNED BY learn_devil_dashboard;
ALTER TABLE occurrences DISABLE ROW LEVEL SECURITY;
DROP ROLE learn_devil_dashboard;
ROLLBACK;
-- result: d5-ex · 1 row · sha256 bae58d1ab786
records | species_with_records | species_rows
2000 | 1 | 76

In d5.sql, with the blocks it runs after.

Check

The answer: USAGE on the schema and SELECT on the two tables; a policy limits occurrences to species 48. species has no policy and no RLS, so all its rows are readable.

As your role, occurrences shows 2,000 records of 1 species and species all 76 rows; reading datasets and the UPDATE both fail with permission denied; after your block, SELECT count(*) FROM pg_roles WHERE rolname LIKE 'learn\_%' is 0 and \dp occurrences shows no policy. If datasets was readable, you granted ON ALL TABLES; if the role saw 40,395 records, RLS isn't on (or you checked as yourself, the owner, instead of after SET ROLE).

Dialects

Lab schema: the PostGIS tables

critwire Learn lab database, PostGIS schema learn_curriculum

Every column, in words · The diagram alone