Security, roles and backup

Until now you have worked as postgres, the superuser, who may do anything. Nobody should work like that in production. Today you decide who can see and change what (see Data, privacy and regulation).

Roles

A role is a user or a group: CREATE ROLE name LOGIN for a person or program that connects, NOLOGIN for a group whose rights others inherit (GRANT group_role TO person;). A new role can do almost nothing until it is granted rights.

CREATE ROLE reporting NOLOGIN;

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

REVOKE takes a right away. Rights can be as fine as single columns: GRANT SELECT (claim_id, status) ON claims TO someone;.

Testing as another role

SET ROLE name makes the rest of the session act as that role (the superuser may become anyone); RESET ROLE comes back. In this lab your role is reset after every run, so put SET ROLE and the queries to test in the same run.

SET ROLE reporting;

SELECT COUNT(*) FROM claims;

SET ROLE reporting;

UPDATE claims SET reserve = 0;

Views that minimise

A view runs with its owner's rights. Grant a role access to a view and not to its table, and the role sees only the columns and rows the view exposes: the database's way of doing data minimisation.

Row-level security

Row-level security (RLS) filters rows per role. Enable it on a table, then add policies: each says which rows a role may see (USING) or write (WITH CHECK). A role with no policy on the table sees no rows at all. The superuser and the table's owner are not filtered.

ALTER TABLE policies ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_clients ON policies
    FOR SELECT TO broker_portal
    USING (broker_id = CURRENT_SETTING('app.broker_id')::INT);

Backups and snapshots

On a server, pg_dump writes a database, or chosen tables, to a file that psql or pg_restore can load again: pg_dump --format=custom --file=harbour.dump harbour. It runs outside SQL, so not in this browser. Inside SQL, before a risky change, take a snapshot: CREATE TABLE name AS SELECT ... keeps a copy you can compare with, or restore from. And wrap the change in a transaction: BEGIN; ... check ... COMMIT; or ROLLBACK;.