Don't build sharing with row-level security

The rest of this post is the deep dive: why the single-equality policy is worth protecting, the four concrete ways an OR clause damages it, the publish-projection shape we'd build instead, and the trade-offs that shape actually costs. The question came up while we were designing shared workspaces for teams — a feature that is in development and has not shipped — but nothing here is specific to Dhaga.

The setup: one predicate, everywhere

Tenant isolation in a shared database is usually one rule repeated on every table. Each table carries a user_id (or tenant_id), and each has one policy:

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY notes_tenant_isolation ON notes
  USING (user_id = current_setting('app.current_user_id', true));

The request sets app.current_user_id before it runs any query, and the database does the filtering. What makes this design good is not that it is clever — it is that it is boring in exactly the same way on every table:

• It is provable. One equality has one meaning. You can read every policy in the schema in a minute and convince yourself no row escapes.
• It is fast. An equality on an indexed column is the cheapest predicate Postgres has, and the planner can use that index to satisfy the policy rather than filtering after the fact.
• It is uniform. Every table's policy is the same shape, so a new table is correct by copy-paste and a reviewer only has to check one thing.

That uniformity is the entire value. Anything that makes one table's isolation predicate different from the others spends it.

The tempting move: one OR away

Then the sharing feature arrives. A user wants a colleague to see one record. RLS already decides who sees which row — so the change looks like it belongs right there:

-- the trap
CREATE POLICY notes_tenant_isolation ON notes
  USING (
    user_id = current_setting('app.current_user_id', true)
    OR EXISTS (
      SELECT 1 FROM shares s
      WHERE s.record_id = notes.id
        AND s.shared_with = current_setting('app.current_user_id', true)
    )
  );

One clause, and sharing works. No new table to keep in sync, no application code to write, no chance of a read path that forgets to check the grant. It is genuinely the smallest diff that makes the feature work, which is exactly why it is worth arguing against carefully.

Why the OR clause is the wrong place

RLS is row-level, and there is no per-row column masking

This is the reason that matters most, and it is structural rather than a bug you can test your way out of.

A policy decides whether a row is visible. It does not decide which columns of that row are visible. There is no "return only these columns" in a policy body. So the moment the predicate above returns true, the recipient can SELECT * and get the name, the email, the phone number, the address, the private note body, and every column you add to that table next year.

Product asked to share the company name. The policy shared the record.

Postgres does have column-level privileges — GRANT SELECT (col) ON … — but those are per-role and static; they cannot say "this column, for this row, for this grantee." A view with a fixed column list helps only if nobody can reach the base table, and in a codebase where the ORM queries tables directly, they can. Unless you already run a redaction layer that every read passes through — most codebases don't — widening the policy quietly turns a narrow share into a full record disclosure, and it will not show up in any test that only asserts "the colleague can see the shared item."

You just made the hottest predicate in the database non-trivial

The isolation policy runs against every row of every query on that table. It is the single most-evaluated expression you own. Putting a correlated EXISTS there is a per-row lookup on your most-read table.

The per-row cost is the smaller half of the problem: OR short-circuits, so for rows the tenant already owns the subquery is never evaluated. The real damage is to the scan. a = $1 can be answered by an index on a; a = $1 OR <correlated subquery> cannot, because rows the index would have excluded might still qualify through the second branch. The planner has to consider rows the equality alone would never have touched, and on a large table under unhelpful statistics that becomes a sequential scan with a subplan attached.

Sometimes the planner finds a good shape anyway. The point is that you now have to prove it — on every query against that table, at every data size, after every statistics change — where before the answer was obviously "index scan." Note too that RLS makes the table behave as a security-barrier subquery: your own WHERE clauses are not freely pushed below the policy, so the policy's cost is paid first by design. That is correct behaviour, and it is exactly why the policy is the worst place in the schema to put something expensive.

Policies are not re-runnable

If your schema is expressed as idempotent DDL — the pattern where the app brings any database up to the current shape on startup — policies are the awkward exception. CREATE POLICY has no IF NOT EXISTS form, so idempotent schema code guards it with a catalogue check:

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'notes' AND policyname = 'notes_tenant_isolation'
  ) THEN
    CREATE POLICY notes_tenant_isolation ON notes USING (…);
  END IF;
END $$;

That guard is create-if-absent, not converge-to-this-definition. Editing the policy body in your template changes what a brand-new database gets and does nothing to any database that already has a policy by that name. A dev box seeded from scratch is then running a different security predicate from production, and nothing reports it.

Converging an existing database means an explicit ALTER POLICY, or a DROP POLICY followed by CREATE POLICY — a one-shot, ordered migration step, which is precisely the machinery the idempotent template was chosen to avoid. To be fair to Postgres: DDL is transactional, so a drop-and-create inside one transaction has no window where the table is readable unprotected — it just holds an ACCESS EXCLUSIVE lock on your busiest table for the duration. And a table with RLS enabled (plus FORCE ROW LEVEL SECURITY, so the owner is covered too) and no policy denies everything, so a migration that dies between the drop and the create fails closed, not open. That is the good news. The bad news is that failing closed on your most sensitive table is still an outage, and you have taken on hand-ordered migration machinery to buy a product feature.

It cannot be feature-flagged

Application code can hide behind a flag. You ship it dark, enable it for yourself, then for a cohort, then for everyone, and you can switch it off in seconds.

A policy applies the instant the DDL runs. There is no "off" position — the widened predicate is live for every query on that table from the moment of deploy, whether or not a single user can see a share button. So the riskiest part of the change ships unconditionally, and the staged rollout that normally contains a mistake contains nothing at all. Rolling back means another DDL change under the same lock.

The alternative: publish a projection

Sharing writes a new row in a new table, containing exactly the columns the recipient is allowed to see. The base table's policy is never touched.

CREATE TABLE shared_record_projections (
  id                  uuid PRIMARY KEY,
  source_record_id    uuid NOT NULL,
  owner_user_id       text NOT NULL,
  shared_with_user_id text NOT NULL,
  -- exactly the fields the recipient may see, and nothing else
  display_name        text NOT NULL,
  organisation        text,
  last_interaction    date
);

ALTER TABLE shared_record_projections ENABLE ROW LEVEL SECURITY;

CREATE POLICY shared_projection_read ON shared_record_projections
  USING (shared_with_user_id = current_setting('app.current_user_id', true));

The properties that matter:

• The recipient's read never reaches the base table. There is nothing to mask, because the columns you didn't copy do not exist in the row they can reach. Adding a sensitive column to the base table later cannot silently widen an existing share.
• The new table's policy is the same single equality as everything else — shared_with_user_id = <current user> for a direct share, or org_id = current_setting('app.current_org_id', true) for an org-wide one. The uniformity property survives intact.
• The projection is written under the owner's scope, at share time, by code that already has legitimate access to the source row. Authorization is decided once, on the write path, rather than re-decided on every read by a subquery.
• Revocation is a DELETE, not a schema change. It is immediate, per-share, and needs no lock on the base table.

This is not an exotic design. It is the access-grant / derived-index shape large systems settle on: a table of materialized grants, authorization resolved in the application or in a view over that table, and RLS kept underneath purely as the tenant-isolation backstop that catches an application bug. The database still guarantees "you cannot read another tenant's rows." It is simply no longer asked to express what your product means by shared.

What it costs — honestly

This is not free, and it is worth being straight about the bill.

It duplicates data, so it can go stale. A copy is only as good as its refresh. The projection has to be updated on the owner's write path — every mutation touching a projected column must fan out to the projections derived from that row — and that fan-out is code you own and can forget. Budget for a periodic reconciliation job that rebuilds projections from source and reports drift, and treat a stale share as a bug with an owner rather than acceptable eventual consistency.

It is more code than one OR. A grant table, a writer, a refresh hook on every mutation path, a reconciler, a revoke path, and tests for all of it. If your honest answer is "we are never going to build the refresh job," the OR clause is still not the safer option — read the separate tables alternative below instead.

You are copying some sensitive fields into a second table. That is real, and it should be said plainly rather than glossed over. It is still strictly less exposure than widening the policy, for two reasons: the copy contains only the fields you deliberately chose, so the blast radius is bounded by a column list a reviewer can read in one screen; and unsharing hard-deletes the row, so revocation removes the data instead of merely removing permission to see data that is still sitting there. But it is a second place your users' data lives, and it belongs in your deletion cascade and your export path from day one.

Two other options worth naming

A SECURITY DEFINER function, or a security-barrier view with a fixed column list. This solves the masking problem by construction: the caller can only reach the columns the function returns, and there is no copy to keep fresh. It fits well when the shared view is cheap to compute and must always be current. The caution is that a SECURITY DEFINER function is itself a privilege-escalation primitive — it runs with its owner's rights, so a mistake inside it bypasses RLS entirely. If you use one, pin its search_path, own it with a dedicated role holding only the privileges it needs, and review it as security code, because that is what it is.

Fully separate org-tenanted tables. The record is copied into an organisation-scoped table whose policy keys on org_id, and the two then live independent lives. This is the cleanest model conceptually — a personal graph and a shared workspace are genuinely different objects, with different lifecycles and different owners — and it is the most duplication. Where a share is really a handoff rather than a window, this is usually the right answer.

The takeaways

1. Keep the isolation predicate a single equality, forever. Its value comes from being identical and trivial on every table. Every branch you add spends that, on the one expression that runs against every row of every query.
2. RLS filters rows, not columns. If a policy admits the row, it admits every column on it, including the ones you add next year. A feature phrased as "share only these fields" cannot be built in a policy.
3. Express sharing one level up, over a projection you control. Write the allowed columns into their own table at share time, under the owner's scope, with its own single-equality policy. The recipient never touches the base table.
4. A copy is a sync path you own. Refresh on the owner's writes, reconcile periodically, hard-delete on revoke. If you won't build that, pick separate tables — not an OR clause.
5. Prefer changes you can switch off. Application authorization ships dark and rolls back in seconds; a policy is live the moment the DDL runs and rolls back under an exclusive lock on your most sensitive table.
6. RLS is the floor you cannot fall through, not the mechanism for expressing product features. Its job is to make an application bug non-catastrophic. Give it that job and nothing else.