Governance
- Audit trail (
authz.audit): who did what, as whom (the real user during "view as"), with the reason fromSET LOCAL authz_ctx.reason = '...'. Changes to relation tables and columns are recorded too (as relate/unrelate), including TRUNCATE, but not what is written in replica mode, where no ordinary trigger runs (How it works). A TRUNCATE is one line, naming the table it was run on, a partition or a table that inherits too. A share or a custom role's permission changed by hand (anUPDATEofauthz.sharesorauthz.role_permissions) is recorded with what it was: a share's line has the old one indetail(was), and a permission changed is one removed and one added. Nobody can update or delete rows, whatever the session sets (session_replication_roletoo), not even a superuser without first dropping or disabling the trigger (DDL, which DDL logging and event triggers see). The exceptions areauthz.trim_audit(keep), which removes entries older than that and records that it did (it disables the trigger for its delete, so it holds the trail's lock until the transaction ends: run it at a quiet time), and a logical replication subscription that copies the trail, which applies the trims made where it is published (making one is DDL too). - Change feed (
authz.changes): every object whose access may have changed (but not by what is written in replica mode, as above), including everything below a moved folder, with a position to resume from. That includes what reads a changed object through a relation, and what reads those in turn, with the causedependent: the files in a moved folder; the folders and files a team is given, shared with or given a custom role on when its members change, and the teams it sits in; the folders of an org whose admins change. A custom role whose permissions change announces the objects it is given on (the causerole). A share moved to another object by hand announces both objects. A row of a type's table is announced when it is added or deleted, whether or not its columns hold relations, and when an update changes what access may read: its key, a relation's column, or a column the type's conditions name (in its permissions, its rules or its where). Where the compiler can't tell which columns those are (a condition that names the whole row, asthisalone or by its table, or calls the app's code), any column counts.{*}means every object of that type (after a TRUNCATE, and for what reads it). Past a thousand objects of a type that a change reaches through others, the feed says{*}for that type instead of their ids: a reader then reads the type again, and the write stops looking. A condition that reads other rows (a subquery) is not followed: a change to those rows announces nothing for it. No write marks the moment a share ends (expires_at) or begins (starts_at):authz.announce_timed_shares()announces the shares that ended or began since it last ran (the causeshare_endedorshare_started), with what reads their objects. Schedule it (Operations); until it runs, a reader doesn't hear of them. It keeps no time of its own, only each object's next share start or end (authz.shares_due), which each write of a share puts in: a clock set back hides no share that ends after, and a run while a share is being added to one of those objects waits for it. The feed's order is by transaction, then by position:authz.changes_since(pos)gives the entries after the one atposin that order. An entry gets its position when it is written, but other sessions see it when its transaction ends. So the feed gives an entry only once every transaction older than its own has ended, in any database of the server: a reader that goes on from the last entry it read never moves past one still to come. The newest entries show up a little late, and a long transaction holds them back until it ends (keep transactions short, as vacuum also asks:idle_in_transaction_session_timeouthelps).authz.trim_changes()removes old entries; a reader behind them gets an error fromchanges_since(AZ712) and reads everything again. So does a reader that asks from a position no entry has. A reader that starts, or starts again, first notes where the feed is,SELECT authz.changes_position(), then reads everything, then follows from that position. - Requests and approvals: people ask, those who may share the relation decide; approval makes a share that ends after the requested time. Requesters can't decide their own, and asking doesn't reveal whether a hidden object exists. A pending request goes with its requester's row and with its object's: deleting either, giving it a new id or emptying its table cancels the request (
statuscancelled, thenotesays why), so it never reaches whoever later has that id, and approving it is refused. Decided requests stay as they were. A delete finds the pending requests and the API keys that name its rows by index: it reads none of the decided requests, however many there are. Withdrawing one's own request (authz.cancel_request) is in the audit trail, as a decision is. Applying the policy cancels those of requesters whose rows went while nothing watched them. A request made while its requester is deleted waits for the delete and is refused; a delete in REPEATABLE READ or SERIALIZABLE whose snapshot is older than a request on the row fails with a serialization error (40001), to retry. - Break glass: short, audited, announced self-service access for people the policy names with
can break_glass. They pick the relation, among those the policy lets be shared with a user: ifeditoris shared, break glass can giveeditor. It gives it through a share of their own that ends with the emergency access. A share of theirs that lets them in now and lasts longer is left as it is, and one that ends sooner lasts until then. If their share of that relation starts later, or holds only while a caveat does, break glass refuses and names it (AZ717): the emergency access would replace it. Another relation, or whoever manages that share, gives the access then. - Access reviews: a snapshot of an object's shares; each is kept or revoked by someone who could unshare it; closing applies the decisions. A review takes decisions until its deadline,
start_review's third argument (two weeks after it starts unless the call says otherwise; NULL: none):review_decideafter it is refused, saying when it was (AZ718). Nothing closes a review by itself:close_reviewstill applies the decisions made in time. Reviews are for users: a service signed in is refused each call, saying so (AZ715). - Invariants run with the policy's tests and on demand.
- Previewing a change:
rowstile diff new.authz [--users 1,2]applies the new policy inside a transaction, lists every (user, object) pair that gains or loses each permission, readable row and masked column, then rolls back. A permission counts on a row the user can't select too, whereauthz.listdoesn't name it: a write rule or an action may still use it. It asks as every user, as every service or other principal (namedservice:7, also in--users) and as nobody. The app's tables stay locked until it rolls back: run it on a copy or a staging database, not on production at its busiest. A user whose id holds:or isnobodyis nameduser:<id>there, as in the audit trail. - Decision log:
SET authz_debug.log_decisions = onwrites everyauthz.candecision to the server log (for debugging: the app role can turn it on and off). Each line names who asked as the audit trail does (42,service:7).
Checking the database
authz.lint() (rowstile lint) reports what would let the app role get around row-level security, and what makes reads through the rules slow. Each finding has a severity. These are errors:
- The app role doesn't exist.
- The app role is a superuser or has BYPASSRLS, itself or through a Postgres role it is a member of.
- The app role is the policy's owner, or a member of it: who is signed in is believed without a signature, and it may read rowstile's tables and turn row-level security off. Applying refuses such an app role (AZ624), and lint finds one that became a member since.
- The app role owns a type's table, or is a member of a Postgres role that does. An owner skips row-level security and may turn it off, so
FORCE ROW LEVEL SECURITYis no way out: the app role must not own the tables. - Row-level security is off on a table with rules.
- A permissive policy on a table with rules that rowstile didn't make: Postgres joins permissive policies with OR, so it lets through what the rules don't. (A restrictive one is a note.)
- TRUNCATE on a type's table or a membership table, which skips row-level security.
- A partition of a table with rules (or a table inheriting from one) that the app role may use directly (a privilege on one of its columns is enough), or TRUNCATE.
- A partition or a table inheriting from a type's table made since the last apply: it lacks the table's row and statement triggers until the next one.
- A view over a governed table that runs with its owner's rights (without
security_invoker), which the app role may read, even one column of. - A membership table the app role may write, without rules (a privilege on one of its columns is enough).
- A masked column the app role may read from the table.
- A privilege on rowstile's own schemas (
authz,authz_gen,authz_int) or what is in them that the policy doesn't give. Applying takes it back. - A schema on the search path of rowstile's functions that run as the owner, which another Postgres role may create in. Applying refuses it (AZ612).
These are warnings:
- A setting of the SCIM endpoint (
authz.set_scim) that no longer holds: a column that was dropped, renamed or given another type, a function that is gone or was given to another role, a principal whose row is gone, a group that is no relation with one table of members in the policy now. A request that meets it fails (AZ722). - A SECURITY DEFINER function the app role may call, which seems to use a governed table.
- A column that gives a relation, which anyone who may update the row may change (no rule on the column).
- A column that moves a row under another of its type, with nothing checking where it moves to (no
afterrule). - A rule for a command the app role has no privilege for: it never applies, and the statement fails with Postgres's own "permission denied".
- A trigger that guards the audit trail but doesn't fire in every replication role (
ENABLE ALWAYS), aspg_restore --data-only --disable-triggersleaves it.rowstile reapplyputs it back. - A permission, or a select rule, that names others so many times over that every check of it is slow to plan (Speed and limits).
As performance, it names each lookup the permission checks make that no index serves (a type's key among them), with the index to add. As notes, it lists:
- The tables the app role may read in full: a type's table without rules, a table the policy reads for a relation (who is in which team), and any table in the same schemas that the policy doesn't name (password hashes, sessions: nothing filters them; fine for a lookup table).
- A unique index on a table with rules, and a primary key the app role may choose: an insert that collides with a hidden row fails, which tells the user it exists.
- A relation named like a type that signs in (
user): alone in a rule, it reads like any user. - A type that signs in but has no
manage_keyspermission, so nobody can make API keys for it.
Applying reports its errors and warnings as warnings, so nobody has to remember to run it. It also warns about a {condition} whose bare column a table it reads takes from the row (m.project_id = id, where memberships have an id too): the row's is this.id.
authz.connection_check() is the same idea for the app itself, which may call it: what is wrong with the connection it runs on (a superuser, BYPASSRLS, the policy's owner or a member of it, the owner of a table with rules or a member of it, FORCE ROW LEVEL SECURITY or not, a table with rules whose row-level security is off). A member of an owner counts whether it inherits the owner's rights or not: it may SET ROLE to it. A login that is neither the app role nor a member of it can't call it at all (permission denied for schema authz): the rules apply to the app role only. A login that is a superuser or the policy's owner and switched to the app role with SET ROLE is an error too: RESET ROLE leaves row-level security, and its sessions are believed without a signature. So is one that owns a table with rules, or is a member of its owner, and switched: RESET ROLE leaves row-level security there too. Log in as the app role. Call it when the app starts and refuse to start on an error, or its tests pass without testing anything. The clients have connection_check() / connectionCheck().