Isolation that survives a forgotten WHERE clause

Difficulty 5/5Subscribers

A billing product keeps every customer's invoices in one shared table. That is a deliberate choice, because separate schemas per customer would mean running hundreds of migrations, and it puts one requirement above all others.

A query that forgets to filter by tenant must not return another tenant's rows. Not "should not". Must not. The application will eventually ship a query with a missing WHERE clause, and that must be a bug that returns nothing rather than a breach that returns everything.

How the application talks to the database:

It connects as app_user, never as the owner. That role already exists.
Before running a customer's queries it sets a session setting, app.tenant_id, to that customer's id.
Then it runs ordinary SQL. SELECT * FROM invoices with no filter at all.

What has to hold:

With app.tenant_id set to a tenant, only that tenant's invoices are visible, and an unfiltered count returns their number and nobody else's.
Inserting an invoice for a different tenant than the current one is refused. Isolation that only covers reads is half a feature.
An invoice amount is never negative, and it belongs to a tenant that exists.

tenants already exists, holding two customers.

The contract

2 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

tenants
idslug
invoices
idtenant_idamount_cents

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

-- The application's role. It is not the owner, which is the only reason row
-- level security can bite: an owner bypasses its own policies unless forced,
-- and a superuser bypasses them no matter what.
CREATE ROLE app_user NOLOGIN;
GRANT USAGE ON SCHEMA public TO app_user;

-- So the exercise is about the policy and not about remembering GRANT.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;

CREATE TABLE tenants (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE
);

INSERT INTO tenants (slug) VALUES ('acme'), ('globex');

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…