Use INSERT WITH CHECK and UPDATE USING to allow owner-on-create but block owner transfers

Problem and goal
You want a table where callers can create rows that name themselves as owner, may edit other fields on rows they own, and must never transfer ownership via UPDATE. The trick is enforcing different rules at creation time and at modification time.

How PostgreSQL helps
Row-level security separates selection and storage checks into USING and WITH CHECK. INSERT policies run WITH CHECK (they validate the proposed row). UPDATE policies can include both USING (which decides which existing rows the caller may target) and WITH CHECK (which validates the post-update row). That split is exactly what you need: validate the owner on INSERT, allow edits on owned rows on UPDATE, and reject any UPDATE that would leave the row owned by someone else.

Implementation shape (server will insert verified example)

• Add an INSERT policy that uses WITH CHECK to assert new_row.owner = current_user.
• Add an UPDATE policy with USING that restricts the rows the caller can touch (row.owner = current_user) and a WITH CHECK that asserts new_row.owner = current_user as well. If you omit WITH CHECK, PostgreSQL reuses USING for the update check, so owner changes are still rejected if the USING predicate requires ownership.

Expected behavior

• INSERT succeeds only when the provided owner equals the current session identity.
• UPDATE on a row you own can change non-owner columns and must leave the owner column unchanged (the updated row must still satisfy the ownership predicate).
• UPDATE attempts by non-owners cannot target the row for UPDATE because USING hides it.

Test boundary and why tests matter
A convincing fixture exercises these cases in sequence: successful owner-matching INSERT, rejected owner-claiming INSERT, permitted non-owner-field UPDATE by the owner, rejected owner-reassignment UPDATE, and rejected UPDATEs by non-owners. Testing isolated cases can miss gaps: an INSERT-only test doesn’t prove later owner changes are blocked, and an UPDATE-only test doesn’t prove inserts are constrained.

Limits and alternatives

• RLS is best when the invariant is local to the row. Policies that consult other tables or external state can introduce race conditions and need careful transaction design.
• Privileged roles (superusers, table owner, BYPASSRLS) bypass RLS — treat maintenance and admin workflows separately.
• For complex approval workflows or multi-table coordination, enforce the workflow at an appropriate layer (application logic or a constrained admin policy) rather than overloading this per-row pattern.

Why this pattern helps
Splitting policies per command makes intent explicit, easier to reason about, and safer to evolve. You can loosen inserts for a service role later without accidentally permitting owner transfers, or add an admin-specific policy when maintenance requires more power.

(Verified implementation 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