Mini-tutorial: INSERT WITH CHECK + UPDATE USING to enforce owner-on-create

Mini-tutorial: INSERT WITH CHECK + UPDATE USING to enforce owner-on-create

●2 ●48
calendar_today • schedule3 min read
— Originally published at chriseugenerodriguez.com

Prerequisites

  • PostgreSQL with row-level security enabled and permission to CREATE POLICY on the target table.
  • A session identity available as current_user (or equivalent) that you can compare against the row’s owner column.

Implementation decision
Use separate per-command policies rather than a single ALL policy. Add an INSERT policy that validates the proposed owner with WITH CHECK so callers cannot create rows claiming other owners. Add an UPDATE policy that uses USING to expose only rows the caller currently owns and WITH CHECK (or rely on USING when WITH CHECK is omitted) to ensure the updated row still meets the ownership predicate. This keeps intent explicit and lets you change insert behavior later without widening the update rule.

Expected result

  • Only sessions that match the owner value can insert rows claiming that owner.
  • Owners can update non-owner columns on their rows.
  • Attempts to change the owner column via UPDATE are rejected by the post-update check, preventing ownership transfer.
  • Non-owner sessions cannot target the row for UPDATE because USING hides it.

One limitation
Row-level policies are bypassed by privileged roles (table owner, superusers, roles with BYPASSRLS). Also, policy expressions that query other tables can introduce concurrency or race conditions — they’re not a replacement for a coordinated multi-table transaction or application-level workflow when you need cross-row invariants.

(A verified example will be inserted by the server.)

Working example

export function createTable({ primaryKey = 'id' } = {}) {
  const state = {
    rlsEnabled: false,
    rows: [],
    nextId: 1,
    policies: [],
    primaryKey,
  };

  function enableRLS() {
    state.rlsEnabled = true;
  }

  function addPolicy(policy) {
    state.policies.push({ kind: 'PERMISSIVE', roles: null, for: 'ALL', ...policy });
  }

  function matchesRole(policy, user) {
    return !policy.roles || policy.roles.includes(user);
  }

  function applicablePolicies(command) {
    return state.policies.filter((p) => p.for === 'ALL' || p.for === command);
  }

  function evalUsing(policy, row, ctx) {
    if (!matchesRole(policy, ctx.current_user)) return false;
    if (typeof policy.using !== 'function') return false;
    return Boolean(policy.using(row, ctx));
  }

  function evalWithCheck(policy, newRow, ctx) {
    if (!matchesRole(policy, ctx.current_user)) return false;
    const check = policy.withCheck || policy.using;
    if (typeof check !== 'function') return false;
    return Boolean(check(newRow, ctx));
  }

  function combine(command, row, newRow, ctx) {
    if (!state.rlsEnabled) return { visible: true, writable: true };

    const policies = applicablePolicies(command);
    if (policies.length === 0) return { visible: false, writable: false };

    const permissive = policies.filter((p) => p.kind !== 'RESTRICTIVE');
    const restrictive = policies.filter((p) => p.kind === 'RESTRICTIVE');

    const visible = (() => {
      if (command === 'INSERT') return true;
      const perm = permissive.length > 0 && permissive.some((p) => evalUsing(p, row, ctx));
      const restr = restrictive.every((p) => evalUsing(p, row, ctx));
      return perm && restr;
    })();

    const writable = (() => {
      if (newRow == null) return visible;
      if (command === 'DELETE') return visible;
      const perm = permissive.length > 0 && permissive.some((p) => evalWithCheck(p, newRow, ctx));
      const restr = restrictive.every((p) => evalWithCheck(p, newRow, ctx));
      return perm && restr;
    })();

    return { visible, writable };
  }

  function insertRow(current_user, row) {
    const ctx = { current_user };
    const newRow = { ...row };
    if (!(state.primaryKey in newRow)) newRow[state.primaryKey] = state.nextId++;

    const result = combine('INSERT', null, newRow, ctx);
    if (!result.writable) {
      const err = new Error('row-level security denied INSERT');
      err.code = 'RLS_INSERT_DENIED';
      throw err;
    }

    state.rows.push(newRow);
    return { ...newRow };
  }

  function updateRow(current_user, keyValue, changes) {
    const ctx = { current_user };
    const idx = state.rows.findIndex((r) => r[state.primaryKey] === keyValue);
    if (idx === -1) {
      const err = new Error('row not found');
      err.code = 'NOT_FOUND';
      throw err;
    }

    const oldRow = state.rows[idx];
    const checkVisible = combine('UPDATE', oldRow, null, ctx);
    if (!checkVisible.visible) {
      const err = new Error('row-level security denied UPDATE USING');
      err.code = 'RLS_UPDATE_USING_DENIED';
      throw err;
    }

    const newRow = { ...oldRow, ...changes };
    const checkWritable = combine('UPDATE', oldRow, newRow, ctx);
    if (!checkWritable.writable) {
      const err = new Error('row-level security denied UPDATE WITH CHECK');
      err.code = 'RLS_UPDATE_WITHCHECK_DENIED';
      throw err;
    }

    state.rows[idx] = newRow;
    return { ...newRow };
  }

  function getRows() {
    return state.rows.map((row) => ({ ...row }));
  }

  return Object.freeze({ enableRLS, addPolicy, insertRow, updateRow, getRows });
}

Read the full article: https://chriseugenerodriguez.com/blog/postgresql-rls-owner-per-command-policy

Want more #architecture deep dives?

Join 5,982+ developers sharing real benchmarks, code breakdowns, and projects. Free forever.

More Posts

The Sovereign Vault — A Comprehensive Guide to Protocol-Driven AI

Ken W. Algerverified - Jun 4

Short links that outlive the vendor: a newsroom URL shortener on WordPress and AWS

jaafarabazid - Nov 10

AAIF Hits 10 Projects. Its CTO on What to Adopt Now and the Security Gaps Left Open

Tom Smithverified - Oct 23

The Audit Trail of Things: Using Hashgraph as a Digital Caliper for Provenance

Ken W. Algerverified - Apr 28

Your AI Agent Doesn't Need Your Token: Edera's Ariadne Conill on Object Capabilities

Tom Smithverified - Nov 5
chevron_left
987 Points • 50 Badges
Santa Monica, CA • chriseugenerodriguez.com
37Posts
0Comments
8Connections
10 years fe

Related Jobs

Commenters (This Week)

3 comments
1 comment
1 comment

Contribute meaningful comments to climb the leaderboard and earn badges!