Row-level security stops a query from seeing another tenant’s rows; it does not stop a row from pointing at one. PostgreSQL foreign key checks bypass every policy, so with row-level security enabled and filtering correctly, an Acme task can still be assigned to a Globex member that the Acme session cannot even read. The constraint that refuses that row is a composite foreign key, (company_id, assignee_id) referencing members (company_id, id), and the two features answer different questions.

I found this reviewing a multi-company task tracker that an AI agent designed, I clicked a foreign key on tasks.assignee_member_id and company_id lit up with it, on both tables. The members table had a primary key on id and, beside it, UNIQUE (company_id, id), a constraint that adds no uniqueness at all. In my whole career I had never seen one. It turns out to be the cheapest way to make a multi-tenant database refuse cross-tenant data, and it has three traps that are easy to miss.

Does row-level security stop a row from referencing another tenant?

No: the PostgreSQL manual says foreign key references always bypass row security, and on PostgreSQL 18.3 a cross-tenant reference goes straight through.

Here is the usual setup: two companies, one member each, tasks and members both scoped by a policy on company_id, and an app role that is not the table owner.

CREATE TABLE companies (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE members (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  company_id bigint NOT NULL REFERENCES companies (id),
  name       text NOT NULL
);
CREATE TABLE tasks (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  company_id  bigint NOT NULL REFERENCES companies (id),
  assignee_id bigint REFERENCES members (id),
  title       text NOT NULL
);
INSERT INTO companies (name) VALUES ('Acme'), ('Globex');
INSERT INTO members (company_id, name) VALUES (1, 'Ann'), (2, 'Bob');

CREATE ROLE app LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON companies, members, tasks TO app;
ALTER TABLE members ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks   ENABLE ROW LEVEL SECURITY;
CREATE POLICY members_tenant ON members
  USING (company_id = current_setting('app.company_id')::bigint);
CREATE POLICY tasks_tenant ON tasks
  USING (company_id = current_setting('app.company_id')::bigint)
  WITH CHECK (company_id = current_setting('app.company_id')::bigint);

As app with SET app.company_id = '1', the policies work: SELECT count(*) FROM members returns 1, and SELECT ... FROM members WHERE id = 2 returns nothing, because Bob belongs to Globex. Writing a Globex task is refused with new row violates row-level security policy for table "tasks". And then:

INSERT INTO tasks (company_id, assignee_id, title) VALUES (1, 2, 'cross-tenant');
-- INSERT 0 1

Acme now has a task assigned to Bob. The row passes WITH CHECK, because its own company_id is Acme’s, and the foreign key finds member 2, because the check does not go through the policy. The PostgreSQL manual says so directly: referential integrity checks, foreign key references included, always bypass row security to keep data integrity. That is the right call for integrity and the wrong assumption for isolation: a bug that passes the wrong id, or a request that sends one, writes a reference no policy will ever stop.

Two more facts from the same session decide how much weight row-level security can carry. The table owner bypasses the policies unless the table is altered with FORCE ROW LEVEL SECURITY (an owner role saw both rows until then), and superusers and roles with BYPASSRLS always bypass them. And the setting is easy to get wrong on a pooled connection. A connection that never set it fails with unrecognized configuration parameter "app.company_id", but a plain SET lasts for the whole connection: after one request ran SET app.company_id = '2', the next transaction on that connection set nothing and still counted Globex’s member. Set it with SET LOCAL, or set_config('app.company_id', ..., true), inside every transaction, so it ends with the transaction; a later transaction that forgets then fails with invalid input syntax for type bigint: "" instead of reading another tenant.

How does a composite foreign key keep tenants apart?

It makes the reference carry the tenant: the child’s company_id must match the parent’s, so the pair has to exist in the parent as one row.

ALTER TABLE members ADD CONSTRAINT members_company_id_id_key UNIQUE (company_id, id);

ALTER TABLE tasks DROP CONSTRAINT tasks_assignee_id_fkey;
ALTER TABLE tasks ADD CONSTRAINT tasks_company_id_assignee_id_fkey
  FOREIGN KEY (company_id, assignee_id) REFERENCES members (company_id, id)
  ON DELETE SET NULL (assignee_id);

Now the same insert is refused: insert or update on table "tasks" violates foreign key constraint "tasks_company_id_assignee_id_fkey", while assigning Ann, who is in Acme, still works. No policy, setting or role is involved; the database proves the reference with the same machinery that proves any foreign key, and the check runs for the table owner and superusers too. The one way past it is a superuser switching triggers off: with SET session_replication_role = replica, the same cross-tenant insert went through, which is what restore and replication tools do on purpose.

The unique constraint looks redundant and is required. A foreign key may only point at columns declared as the primary key or a unique constraint, as exactly that list, and PostgreSQL does not reason that (company_id, id) is unique because id is. Without it, the key is refused with there is no unique constraint matching given keys for referenced table "members". Its only job is to be the target, plus the index the check uses. The same pattern on a junction table, so a member can only hold roles of their own tenant, is worked through in the multi-tenant RBAC post.

What breaks when you add a composite tenant key?

Three things: ON DELETE SET NULL clears the tenant column, swapped columns are accepted silently, and MySQL wants the columns in index order.

SET NULL without a column list. A plain ON DELETE SET NULL clears every column of the key. On the composite key that includes company_id, which is NOT NULL, so deleting the member fails: null value in column "company_id" ... violates not-null constraint, from an internal UPDATE ... SET "company_id" = NULL, "assignee_id" = NULL. PostgreSQL 15 added the column list, ON DELETE SET NULL (assignee_id), which clears only the member column; with it, deleting Ann leaves the task in Acme with no assignee. MySQL and SQL Server have no list, MySQL 8.4 refuses to create the plain SET NULL key at all (ERROR 1830: Column 'company_id' cannot be NOT NULL: needed in a foreign key constraint), and CockroachDB 26.3 refuses the clause, so on those use CASCADE, RESTRICT or NO ACTION for these keys. The general choice between those actions is in ON DELETE SET NULL vs CASCADE.

Columns pair by position, not by name. FOREIGN KEY (assignee_id, company_id) REFERENCES members (company_id, id) is created without complaint, because both columns are integers, and then compares assignee_id with company_id. With a third company, Initech, and a third member, Cy (id 3, in Acme), that key refused the correct row (company 1, assignee 3) with Key (assignee_id, company_id)=(3, 1) is not present and accepted an Initech task assigned to Ann of Acme (company 3, assignee 1). Nothing fails at creation; it fails on the first real rows, and some of the rows it lets through cross tenants.

MySQL wants index order. PostgreSQL accepts the referenced columns in any order as long as some unique constraint covers that set. MySQL 8.4.11 accepted REFERENCES members (company_id, id) against UNIQUE (company_id, id) and refused REFERENCES members (id, company_id) with error 6125, Missing unique key for constraint ... in the referenced table. Writing the tenant first everywhere avoids it.

The index is less of a trap than it looks. The check that runs when a member is deleted searches for company_id = ? AND assignee_id = ?, and an index leading with assignee_id alone serves it: on 100,000 rows, PostgreSQL 18.3 used ix_tasks_assignee for that lookup, because the member id already names one parent row.

Composite foreign key vs row-level security: which should you use?

Use composite foreign keys wherever tenant tables reference each other, and add row-level security when queries need a second guard; they close different holes.

The composite key is a structural guarantee: once it exists, no ordinary write can store a cross-tenant reference, including migrations, admin scripts and the next service someone writes. Row-level security is a query guarantee: it catches the missing WHERE company_id = ? that every multi-tenant codebase eventually ships. A schema that has only the policies looks isolated and can still hold Acme tasks assigned to Globex people; a schema with only composite keys can still leak a list of every tenant’s members to a query that forgot its filter.

The cost of the keys is real but bounded. Every table that references a tenant table carries company_id, which it usually has anyway, and every referenced table gets one extra unique constraint. Tables outside the tenancy, such as a global country list, keep ordinary single-column keys.

How do you design a composite tenant key in Schemity?

Draw the relationship as usual: when the parent has a unique constraint holding its primary key plus a tenant column, Schemity makes the composite key for you.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Since v2.14.5, dragging a relationship from members to tasks when members has UNIQUE (company_id, id) creates (company_id, member_id) referencing members (company_id, id). If tasks already has company_id, it is shared; if not, it is added as NOT NULL, and the relation dialog shows that row locked so you can see which. A checkbox keeps the plain key for a table outside the tenancy. The details are in Relationships.

The traps above are handled where you design the next one, not after the migration runs. SET NULL on such a key is written as ON DELETE SET NULL (member_id) on PostgreSQL and refused with the reason on the databases that cannot limit it. A key read from a database keeps its column list instead of losing it on the next migration. And schema lint catches both mistakes before they ship: redundant-fk-path flags tasks when it and members both reference companies but the key from tasks to members leaves company_id out, the key that let an Acme task go to a Globex member, and names the composite key that fixes it; fk-columns-crossed flags a key whose columns are paired crosswise, the swapped key that is created and then refuses correct rows.

If an AI agent designs your schema, the open-source schema design skill makes it decide ON DELETE per key, name every constraint, and check its design with Schemity’s lint before handing it back. It was one of those agent-designed schemas that put this key in front of me.