Search documentation

Search for a topic, a control, or an event type.

    Docs / Databases / Row policies

    Row policies

    Choose which rows an account can read or change with PostgreSQL policies based on OAIZ identity.

    Rows owned by the acting account

    To keep customer rows with the account named by owner_id, run this migration as a database Owner. Start without an existing customers table. The acting account is the account making the database request. A job uses its assigned account.

    This migration creates customers, indexes owner_id, removes the four default permissive rules, and keeps a row whose owner_id is the acting account. A database Owner still sees every row. An account with View can read its own rows and cannot insert. An update cannot give the row to another account. An update of a hidden row changes nothing.

    Replace the default rules with the acting account
    CREATE TABLE customers (
      id uuid PRIMARY KEY,
      name text NOT NULL,
      status text NOT NULL,
      owner_id uuid NOT NULL
    );
    CREATE INDEX customers_owner_id_idx ON customers (owner_id);
    DROP POLICY everyone_can_see ON customers;
    DROP POLICY everyone_can_add ON customers;
    DROP POLICY everyone_can_change ON customers;
    DROP POLICY everyone_can_delete ON customers;
    CREATE POLICY customers_select_own ON customers FOR SELECT
      USING (owner_id = (SELECT oaiz_private.oaiz_subject()));
    CREATE POLICY customers_insert_own ON customers FOR INSERT
      WITH CHECK (owner_id = (SELECT oaiz_private.oaiz_subject()));
    CREATE POLICY customers_update_own ON customers FOR UPDATE
      USING (owner_id = (SELECT oaiz_private.oaiz_subject()))
      WITH CHECK (owner_id = (SELECT oaiz_private.oaiz_subject()));
    CREATE POLICY customers_delete_own ON customers FOR DELETE
      USING (owner_id = (SELECT oaiz_private.oaiz_subject()));

    Row policies

    A new table starts with four permissive rules: everyone_can_see, everyone_can_add, everyone_can_change and everyone_can_delete. Permissive rules combine with OR, so adding a narrower permissive rule leaves the broad access in place. Restrictive rules combine with AND. A restrictive rule hides rows, including from a database Owner, and it grants nothing on its own. The platform Owner rule is permissive, so a permissive customer rule does not hide rows from a database Owner.

    A policy can compare columns and literals, use IN, ANY and ALL on an array, combine conditions with AND, OR and NOT, and test IS NULL. It can cast to text, varchar, character, uuid, boolean, integers, numeric, floating point, date, time, timestamp and timestamp with time zone, including arrays of those types. It can call one of the identity functions listed here with no arguments, or read that function from a scalar SELECT with no FROM, WHERE, ORDER BY, LIMIT or extra column. It can also compare pg_trigger_depth() with a whole number, such as pg_trigger_depth() > 0, to tell a row a trigger writes from a direct write.

    oaiz_private.oaiz_subject()
    UUID of the acting account.
    oaiz_private.oaiz_organisation()
    UUID of the organisation.
    oaiz_private.oaiz_database()
    Text identity of this physical database.
    oaiz_private.oaiz_capability()
    Text capability for this database: view, edit or owner.
    oaiz_private.oaiz_organisation_role()
    Text organisation role of the acting account.
    oaiz_private.oaiz_teams()
    UUID array of the acting account’s teams. The array is empty when the account is in none.
    oaiz_private.oaiz_app()
    UUID of the app when the caller is an app token. Null for every other caller.
    A function with arguments
    Refused, including aggregates, window functions, functions in the data schema, and any function outside oaiz_private other than pg_trigger_depth(). Use the identity functions above with no arguments. A computed predicate has no policy form.
    A lookup subquery
    Joins, EXISTS, and IN or ANY against a set of rows are refused. The message says the expression uses a subquery other than (SELECT oaiz_private.<identity function>()). A rule that has to look up another table has no policy form.
    Another operator
    Arithmetic, concatenation, JSON tests, LIKE, regular expressions, CASE, COALESCE, subscripts, whole-row references and placeholders are refused. Use a direct comparison when the rule can be written that way. Otherwise that rule has no policy form.
    A cast outside the list above
    Enum, custom, JSON and object-resolving casts are refused, as are casts with a modifier or a type from another schema. Compare an enum column as text. PostgreSQL can store a literal comparison as a cast to the enum, and OAIZ then refuses that stored rule.
    A qualified operator or a long name
    Use an ordinary comparison and a column of the current table. A column name with more than two parts is refused.

    USING decides which existing rows the account can see or change. WITH CHECK decides which inserted or updated rows the account can write. The account that owns a row is the value in a column such as owner_id. That account is not the same thing as a database Owner, whose grant covers the whole database.

    The policy examples add a B-tree index on the owner, team or status column the rule compares. That is ordinary indexing for a column used in a comparison. It does not promise that every plan uses the index.

    Rows owned by a team

    This migration creates deals, indexes owner_team, removes the four default rules, and keeps a row whose owner_team is one of the acting account’s teams. An account in none of those teams sees no rows. A database Owner still sees the rows. An update cannot move the deal to another team. An account with View that belongs to the team can read the row and cannot insert.

    Match owner_team to the acting teams
    CREATE TABLE deals (
      id bigint PRIMARY KEY,
      title text NOT NULL,
      owner_team uuid NOT NULL
    );
    CREATE INDEX deals_owner_team_idx ON deals (owner_team);
    DROP POLICY everyone_can_see ON deals;
    DROP POLICY everyone_can_add ON deals;
    DROP POLICY everyone_can_change ON deals;
    DROP POLICY everyone_can_delete ON deals;
    CREATE POLICY deals_select_team ON deals FOR SELECT
      USING (owner_team = ANY (oaiz_private.oaiz_teams()));
    CREATE POLICY deals_insert_team ON deals FOR INSERT
      WITH CHECK (owner_team = ANY (oaiz_private.oaiz_teams()));
    CREATE POLICY deals_update_team ON deals FOR UPDATE
      USING (owner_team = ANY (oaiz_private.oaiz_teams()))
      WITH CHECK (owner_team = ANY (oaiz_private.oaiz_teams()));
    CREATE POLICY deals_delete_team ON deals FOR DELETE
      USING (owner_team = ANY (oaiz_private.oaiz_teams()));

    The same team rule with a cast

    The cast to uuid[] keeps the subquery a single array value. The rows each account can see and change are the same as in the direct team rule.

    Cast the team array
    CREATE TABLE deals (
      id bigint PRIMARY KEY,
      title text NOT NULL,
      owner_team uuid NOT NULL
    );
    CREATE INDEX deals_owner_team_idx ON deals (owner_team);
    DROP POLICY everyone_can_see ON deals;
    DROP POLICY everyone_can_add ON deals;
    DROP POLICY everyone_can_change ON deals;
    DROP POLICY everyone_can_delete ON deals;
    CREATE POLICY deals_select_team ON deals FOR SELECT
      USING (owner_team = ANY ((SELECT oaiz_private.oaiz_teams())::uuid[]));
    CREATE POLICY deals_insert_team ON deals FOR INSERT
      WITH CHECK (owner_team = ANY ((SELECT oaiz_private.oaiz_teams())::uuid[]));
    CREATE POLICY deals_update_team ON deals FOR UPDATE
      USING (owner_team = ANY ((SELECT oaiz_private.oaiz_teams())::uuid[]))
      WITH CHECK (owner_team = ANY ((SELECT oaiz_private.oaiz_teams())::uuid[]));
    CREATE POLICY deals_delete_team ON deals FOR DELETE
      USING (owner_team = ANY ((SELECT oaiz_private.oaiz_teams())::uuid[]));

    Only one team adds, changes and deletes

    This migration creates prices, keeps everyone_can_see, removes the three default write rules, and lets only the members of one team add, change and delete rows. Put that team’s UUID in each rule: oaiz team list shows it. Every account with access still sees every row. An update or delete by an account outside the team changes no row.

    Compare the team UUID with the acting teams, not a team column. A rule such as owner_team = ANY (oaiz_private.oaiz_teams()) lets a member of any team add a row for their own team.

    Let one team write
    CREATE TABLE prices (
      id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      product text NOT NULL,
      amount numeric NOT NULL CHECK (amount >= 0)
    );
    DROP POLICY everyone_can_add ON prices;
    DROP POLICY everyone_can_change ON prices;
    DROP POLICY everyone_can_delete ON prices;
    CREATE POLICY pricing_team_adds ON prices FOR INSERT
      WITH CHECK ('33333333-3333-4333-8333-333333333333'::uuid = ANY (oaiz_private.oaiz_teams()));
    CREATE POLICY pricing_team_changes ON prices FOR UPDATE
      USING ('33333333-3333-4333-8333-333333333333'::uuid = ANY (oaiz_private.oaiz_teams()))
      WITH CHECK ('33333333-3333-4333-8333-333333333333'::uuid = ANY (oaiz_private.oaiz_teams()));
    CREATE POLICY pricing_team_deletes ON prices FOR DELETE
      USING ('33333333-3333-4333-8333-333333333333'::uuid = ANY (oaiz_private.oaiz_teams()));

    Hide lost deals from every account

    This migration keeps the default permissive rules and adds a restrictive select rule. A lost deal is hidden from every account, including a database Owner. View still cannot update a visible row. The restrictive rule grants nothing by itself. If everyone_can_see is removed, an editor sees no rows, and lost deals stay hidden from the Owner.

    A restrictive status rule
    CREATE TABLE deals (
      id bigint PRIMARY KEY,
      title text NOT NULL,
      status text NOT NULL
    );
    CREATE INDEX deals_status_idx ON deals (status);
    CREATE POLICY hide_lost_deals ON deals AS RESTRICTIVE FOR SELECT
      USING (status <> 'lost');

    Let a database Owner see lost deals

    The same restrictive rule also allows the row when the acting capability is owner. A database Owner then sees lost deals, and an editor still does not. If everyone_can_see is removed, the editor sees nothing and the Owner still sees both rows.

    Exempt the owner capability
    CREATE TABLE deals (
      id bigint PRIMARY KEY,
      title text NOT NULL,
      status text NOT NULL
    );
    CREATE INDEX deals_status_idx ON deals (status);
    CREATE POLICY hide_lost_deals ON deals AS RESTRICTIVE FOR SELECT
      USING (status <> 'lost' OR (SELECT oaiz_private.oaiz_capability()) = 'owner');

    A narrower rule left beside the default

    This migration adds a select rule for the acting account and leaves everyone_can_see in place. The migration succeeds. Another account can still read and update the row, because permissive rules combine with OR. Replace the broad rules, or add a restrictive rule, to limit that access.

    Add a select rule without removing the default
    CREATE TABLE notes (
      id bigint PRIMARY KEY,
      owner_id uuid NOT NULL,
      body text NOT NULL
    );
    CREATE INDEX notes_owner_id_idx ON notes (owner_id);
    CREATE POLICY notes_select_own ON notes FOR SELECT
      USING (owner_id = (SELECT oaiz_private.oaiz_subject()));

    Keep who a row belongs to

    An update rule checks the row before the change with USING and the row after it with WITH CHECK, but it cannot compare the two. An account that can change a row it does not own can therefore also change owner_id. A trigger that runs before each update can compare OLD with NEW.

    This migration creates tickets, lets each account add tickets only in its own name, and keeps everyone_can_change, so every editor can change every ticket. The trigger refuses a change to owner_id unless the acting account is in one team. The refused update raises the trigger’s message and changes nothing.

    Refuse a change to the owner
    CREATE TABLE tickets (
      id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      title text NOT NULL,
      owner_id uuid NOT NULL
    );
    CREATE INDEX tickets_owner_id_idx ON tickets (owner_id);
    DROP POLICY everyone_can_add ON tickets;
    CREATE POLICY tickets_add_own ON tickets FOR INSERT
      WITH CHECK (owner_id = (SELECT oaiz_private.oaiz_subject()));
    
    CREATE FUNCTION data.tickets_keep_owner() RETURNS trigger LANGUAGE plpgsql
        SET search_path = data, extensions, pg_temp AS $$
    BEGIN
        IF NEW.owner_id IS DISTINCT FROM OLD.owner_id
            AND NOT ('33333333-3333-4333-8333-333333333333'::uuid = ANY (oaiz_private.oaiz_teams())) THEN
            RAISE EXCEPTION 'only the support leads change who a ticket belongs to';
        END IF;
        RETURN NEW;
    END
    $$;
    
    CREATE TRIGGER tickets_keep_owner BEFORE UPDATE ON data.tickets
        FOR EACH ROW EXECUTE FUNCTION data.tickets_keep_owner();