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