How Row Level Security in PostgreSQL makes it impossible — not merely unlikely — for one company to see another's data, the real code of the policies, and the genuine tension between GDPR erasure and the invoice you are legally required to keep.
by Nadia Maar ·
Every multi-company SaaS portal hides the same uncomfortable question: what happens the day somebody forgets a check? In a normal application, “company A cannot see company B's data” is a line of code — a `WHERE org_id = ?` — repeated in every endpoint, every query, every report somebody will write three years from now. Forget it ONCE, in ONE endpoint, and a customer sees another customer's data. It is not hypothetical: it is the single most common cause of the data breaches that make trade-press headlines, and it has nothing to do with sophisticated hackers.
Row Level Security moves that check from the application to the database: the wrong row never arrives, whatever code queries for it, whatever endpoint somebody writes tomorrow without remembering the rule. It is no longer merely well-defended: it is impossible by construction.
This piece shows how to design a multi-company database with RLS, the real code of the policies — the ones that decide who sees what — and the part almost no RLS article covers: what happens when GDPR asks you to delete a company that still has invoices you are legally required to keep.
Picture a portal where several client companies manage their own documents. The `documents` table has an `org_id` column. The normal way to isolate the data is for every query in the application to add `WHERE org_id = $1`, using the logged-in user's organisation id. It works, as long as it always works — and that is the problem: it depends on the discipline of whoever writes code against that table, forever.
None of these cases is gross negligence. They are the ordinary way code grows over three years, with different people working on it. RLS does not ask anyone to remember anything: the rule lives once, in the database, and applies to every query that arrives — from the application, from a script, from an administrator with direct access, from an endpoint written six months from now by someone who never read this article.
Row Level Security is a PostgreSQL feature that automatically filters the ROWS a query is allowed to see or modify, based on who is running the query — not on what the application asked to do. It is switched on per table and defined with POLICIES: rules written in SQL that return true or false for each row.
Here is the minimal schema and the policies that protect it. `memberships` links every Supabase user (`auth.uid()`) to the organisations they belong to; `documents` carries an `org_id`. The policy is not written in the application: it is written once, on the database, and it holds forever.
-- Turns RLS on: from this point, with no explicit policies,
-- NO row is visible to anyone. It is the safe default:
-- you forget a policy, not a filter.
alter table documents enable row level security;
-- A member only sees documents belonging to their own organisation.
create policy "members_read_own_org"
on documents for select
using (
org_id in (
select org_id from memberships
where user_id = auth.uid()
)
);
-- Only "editor" or higher can insert, and only inside
-- their own organisation — being logged in is not enough,
-- you need the right role on the right row.
create policy "editors_insert_own_org"
on documents for insert
with check (
org_id in (
select org_id from memberships
where user_id = auth.uid() and role in ('editor', 'admin')
)
);
-- Internal staff see EVERY organisation: an additional policy,
-- not an exception carved into the application code.
create policy "staff_read_all"
on documents for select
using (
exists (select 1 from staff where user_id = auth.uid())
);The most important line is not in the policies: it is enable row level security. From that point on, the default behaviour is to deny everything — a table with RLS on and zero policies is invisible to everyone, database admin included. It flips the usual software risk on its head, where forgetting a check leaves things too permissive: here, forgetting it leaves things too restrictive, and an overly restrictive bug shows up in the first test, not in the first breach.
337 rows belonging to other companies. Not hidden by the interface: never returned by the database.
338 rows belonging to other companies, including Company A's three. Same query, no hand-written WHERE.
Same query, same instant: SELECT * FROM documents run by two different users returns two different results — with not a single line of application code aware that two companies exist.
This is the point worth the whole article: the query is identical, byte for byte. No `WHERE` written by hand, no parameter passed by the application. PostgreSQL rewrites the query for each user before executing it, based on the active policies — one level below any bug the application will ever have.
Most RLS articles stop here, and this is where the problem clients actually run into begins. A company ends its contract and asks for its data to be deleted, as GDPR allows. But the invoices issued to that company must be kept under tax law — in most of the EU, several years. The two requests are both legitimate and opposite, and a `DELETE FROM organizations WHERE id = ?` treats them as if they were the same thing.
1,000 deletion requests a year
180 carry an active constraint; 820 real deletion.
Most of a company's data carries no retention duty at all: profile, preferences, messages, project attachments. For these, GDPR wins without exception, and the deletion is the real thing — a DELETE cascading through every linked table, not a hidden flag that leaves the data where it is.
156 carry an active constraint; 24 real deletion.
Twenty-four requests concern invoices now past their retention period: no law protects them any more, and they should be genuinely deleted like the rest. It is the check almost no system performs, and the reason some archives keep data forever that could have gone years ago.
For these, tax law overrides the right to erasure — an explicit exception in the GDPR text itself (Article 17(3)(b)). Company name and address become “Removed customer #4471”; amount, VAT rate and date remain, because those are what the tax authority can ask to verify. When the period expires, a scheduled function genuinely deletes these too.
The technical point is that this distinction belongs in the schema, not in the head of whoever writes the deletion script. A `subject_to_retention boolean` column on the `documents` table, populated at creation based on the document type, turns the deletion function into a query that explains itself instead of a hidden `if` buried in a service somewhere.
create or replace function gdpr_delete_organization(org uuid)
returns void
language plpgsql
security definer -- runs with the function's own permissions, not the
-- caller's: a GDPR deletion should not go through the
-- caller's own RLS filter
as $
begin
-- Data with no tax constraint: real deletion.
delete from documents
where org_id = org and subject_to_retention = false;
delete from messages where org_id = org;
delete from attachments where org_id = org;
-- Invoices: anonymisation, not deletion.
-- Amount, date and VAT rate stay; the recipient's identity goes.
update invoices
set company_name = 'Removed customer #' || org::text,
address = null,
vat_number = null,
anonymized_at = now()
where org_id = org;
-- The organisation itself: soft-delete with a timestamp,
-- not a DELETE — this is what proves the request WAS
-- fulfilled, if anyone ever asks.
update organizations
set deleted_at = now(), name = 'Removed organisation'
where id = org;
end;
$;"security definer" is the most delicate line in the function: it means the GDPR deletion is not subject to the same RLS policies that protect the data day to day. That is correct — a deletion request arrives AFTER identity has already been verified, it should not pass through the filter meant for everyday use — but it is also the one place in the system where a mistake bypasses the protection described in section two, which is why this function deserves more testing than any other line in the portal.
A badly written policy is invisible in the application's normal tests, because the application always calls with the right user. The way you find a leak is by impersonating each role and trying to read what that role should not see — not once when the policy is written, but on every migration that touches that table.
| Check | Command | Expected result |
|---|---|---|
| Member A reads their own documents | SET request.jwt.claim.sub = user_A; SELECT * FROM documents | Only rows with company A's org_id |
| Member A tries to read company B's | SET request.jwt.claim.sub = user_A; SELECT * FROM documents WHERE org_id = org_B | Zero rows — not an error, zero results |
| A non-editor member tries to insert | SET request.jwt.claim.sub = user_viewer; INSERT INTO documents (...) | Rejected by the with check policy |
| No user at all (anonymous query) | RESET request.jwt.claim.sub; SELECT * FROM documents | Zero rows: RLS denies everything without a valid identity |
An application test checks that the endpoint calls the right query. A test like this checks that IF someone called the wrong query — today or in two years, from an endpoint or a script — the database would still reject the misplaced row. It is the only layer of testing that also protects against code that has not been written yet.
If you are building — or already built without RLS — a portal where several companies share the same database, bring the schema: in half an hour we look at where the boundary between one company and another sits today, and how much work it takes to move it from the application to the database.
If your portal runs a separate database per customer, I will tell you: you probably do not need RLS, you need the discipline you already have. RLS solves a specific problem — a database shared across tenants — it is not a universal security requirement.
Custom web application development
It is a feature that automatically filters the rows a query is allowed to read or modify, based on who is running it — not on what the application asked to do. On a database shared across companies, it moves the “this company only sees its own data” check from the application — where it must be repeated in every endpoint and can be forgotten — to the database, where it is written once and holds forever.
In practice, no, as long as indexes on the columns used in the policies (typically org_id) are already in place: PostgreSQL folds the policy condition into the query's execution plan rather than applying it as a separate filter afterwards. The added cost is comparable to a hand-written WHERE clause using the same index.
With anonymisation instead of deletion, for the specific data under a tax retention duty: the customer's name and address get replaced with an identifier, while the amount, date and VAT rate stay because those are what the tax authority can ask to verify. It is an explicit exception in GDPR itself (Article 17(3)(b)), not a workaround for the deletion request.
By impersonating each role with SET request.jwt.claim.sub and explicitly trying to read what that role should not see: the query must return zero rows, not an error. It needs repeating on every migration that touches the table, not just when the policy is first written — it is the only test that also protects against code that has not been written yet.