PostgreSQL Roles and Privileges

How it works

On the isolated lab cluster, an administrator creates a temporary NOLOGIN reader role, grants schema usage and table SELECT, then adopts that role for a count. RESET ROLE returns to the original identity before the outer rollback. Role creation is cluster-wide, so this administrative example belongs only on a private learning cluster and refuses an existing role name instead of altering it.

Run in a separate PostgreSQL lab

Use PostgreSQL 18; the reviewed server and client tools are 18.6. PostgreSQL is separate from XAMPP MySQL/MariaDB and Bhylu’s SQLite playground. No PostgreSQL browser runner is available. These lessons do not change PHP, Apache or your normal database services.

1. On your own private learning instance, have its administrator create an empty bhylu_pg_lab database. This administrative step runs outside a transaction. Stop if the database already exists; do not replace it.
2. Connect explicitly to that database with its lab host, port and role. Save this download as roles.sql. With the matching client run psql -X -h 127.0.0.1 -p YOUR_LAB_PORT -U YOUR_LAB_ROLE -d bhylu_pg_lab -v ON_ERROR_STOP=1 -f roles.sql. Replace the two uppercase placeholders; never paste a password into the lesson or a committed command.
3. The complete SQL checks the database name, creates a fresh bhylu_lesson schema, sets UTC, ISO dates and bounded timeouts, and seeds all four tables. It refuses an existing schema instead of overwriting it. It then runs the lesson and rolls everything back. In pgAdmin, run the complete SQL through Query Tool; if a statement fails, issue ROLLBACK before retrying.

Download the complete roles.sql script. Each script is independent; do not import one lesson’s seed into another lesson’s transaction. Cost values are exact numeric fixtures; measurement totals are not claims about real water usage.

Review boundary: Administrator-only role exercise on a separate private PostgreSQL cluster; not a normal application query.

Example

-- Bhylu PostgreSQL 18.6 lab: Roles and Privileges
-- Use only your separate disposable database named bhylu_pg_lab.
-- Each example creates a fresh schema inside a transaction, then rolls back.
BEGIN ISOLATION LEVEL READ COMMITTED;
DO $$ BEGIN
  IF current_database() <> 'bhylu_pg_lab' THEN
    RAISE EXCEPTION 'Choose the disposable bhylu_pg_lab database';
  END IF;
END $$;
CREATE SCHEMA bhylu_lesson;
SET LOCAL search_path = bhylu_lesson, pg_catalog;
SET LOCAL timezone = 'UTC';
SET LOCAL datestyle = 'ISO, YMD';
SET LOCAL statement_timeout = '10s';
SET LOCAL lock_timeout = '2s';
CREATE TABLE tanks (
  id integer PRIMARY KEY,
  label text NOT NULL UNIQUE,
  capacity_l integer NOT NULL CHECK (capacity_l > 0),
  zone text
);
CREATE TABLE readings (
  id integer PRIMARY KEY,
  tank_id integer NOT NULL REFERENCES tanks(id),
  measured_at timestamptz NOT NULL,
  litres integer NOT NULL CHECK (litres >= 0)
);
CREATE TABLE technicians (id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE inspections (
  id integer PRIMARY KEY,
  tank_id integer NOT NULL REFERENCES tanks(id),
  technician_id integer REFERENCES technicians(id),
  status text NOT NULL CHECK (status IN ('planned','pending','done')),
  cost numeric(8,2) CHECK (cost >= 0)
);
INSERT INTO tanks VALUES
  (1,'North roof',1000,'North'),
  (2,'Library',600,'Central'),
  (3,'Workshop',800,'North'),
  (4,'Garden',400,NULL);
INSERT INTO readings VALUES
  (1,1,'2026-01-10 06:00:00+00',300),
  (2,1,'2026-01-11 06:00:00+00',450),
  (3,2,'2026-01-10 06:00:00+00',200),
  (4,3,'2026-01-10 06:00:00+00',0),
  (5,2,'2026-01-11 06:00:00+00',350);
INSERT INTO technicians VALUES (1,'Mira'),(2,'Dev'),(3,'Noor');
INSERT INTO inspections VALUES
  (11,1,1,'done',12.50),
  (12,2,2,'pending',NULL),
  (13,3,1,'done',20.00),
  (14,1,NULL,'planned',0.00);

-- Lesson commands begin here.
CREATE ROLE bhylu_pg_reader NOLOGIN;
GRANT USAGE ON SCHEMA bhylu_lesson TO bhylu_pg_reader;
GRANT SELECT ON tanks TO bhylu_pg_reader;
SET LOCAL ROLE bhylu_pg_reader;
SELECT count(*) FROM tanks;
RESET ROLE;
-- No changes from this lesson are kept.
ROLLBACK;

CREATE ROLE bhylu_pg_reader NOLOGIN;
GRANT USAGE ON SCHEMA bhylu_lesson TO bhylu_pg_reader;
GRANT SELECT ON tanks TO bhylu_pg_reader;
SET LOCAL ROLE bhylu_pg_reader;
SELECT count(*) FROM tanks;
RESET ROLE;

Expected result

4

Expected rows use a pipe between columns and <NULL> for a missing value. A normal client may display headings, spacing and NULL differently. Use the script’s explicit ORDER BY when comparing results.

The reader receives only the privileges shown. The review separately attempts an INSERT as that role and verifies permission denial plus unchanged data. A production role design also needs ownership, inherited memberships, default privileges and authentication policy. None of those is established by a single successful SELECT.

Official reference

• PostgreSQL 18 documentation