Check Tenant Scope in SQL Joins

A query can filter one table by tenant and still join a row from another tenant. This happens when a child key is unique only inside a tenant, when a foreign key omits tenant identity, or when a developer assumes a global ID is enough. Review the relationship, not just the WHERE clause.

Inspect each join edge

Choose one query that combines tenant-owned tables. Draw the tables and keys used in each join. Add the owner column for every table, including lookup tables that may be public or shared.

Create synthetic rows with deliberately overlapping local IDs where the schema permits it. Check permitted joins and mixed-tenant joins in an owned database fixture. Verify returned rows, aggregate counts, and update effects. A joined row that is later hidden by a serializer can still affect counts or decisions.

PostgreSQL RLS may protect the base tables, but role bypass, security-definer functions, and view behavior still matter. The PostgreSQL row security documentation describes policy application. The OWASP multi-tenant guidance supplies the isolation requirement. Record which layer actually enforces the join edge in your system.

Check schema constraints and join keys

For a schema where project IDs are unique only within a tenant, make the relationship carry both keys. This is an illustrative PostgreSQL shape, not a migration to paste into a live database:

CREATE TABLE projects (
  tenant_id text NOT NULL,
  project_id text NOT NULL,
  PRIMARY KEY (tenant_id, project_id)
);
CREATE TABLE tasks (
  tenant_id text NOT NULL,
  task_id text NOT NULL,
  project_id text NOT NULL,
  PRIMARY KEY (tenant_id, task_id),
  FOREIGN KEY (tenant_id, project_id)
    REFERENCES projects (tenant_id, project_id)
);
SELECT t.task_id, p.project_id
FROM tasks AS t
JOIN projects AS p
  ON p.tenant_id = t.tenant_id AND p.project_id = t.project_id
WHERE t.tenant_id = $1;

In a synthetic fixture, give A and B one shared project_id value. The valid A task must join only to A's project despite the duplicate ID. Give B a second project ID that A does not have. An attempt to store an A task pointing to that B-only ID must fail the composite foreign key. Check the actual schema's nullability and unique keys before adapting this pattern. PostgreSQL documents multi-column foreign keys. A query predicate still needs trusted tenant context; the constraint does not authorize a caller.

The artifact is a join-key checklist with a reproducible mixed-tenant fixture. Add it to code review for new relationships and reports. If the schema allows a child row to point to a parent in another tenant, fix the data model or add a verified constraint before relying on every query writer to remember an extra predicate. Do not use production customer rows for this test.