Someone Else's Files
Logged in as one client, change a number in the address and another client's files open. One portal only checks that you're logged in. The other checks the file is yours, and the database refuses to hand over anyone else's.
01Try it
You're logged in to the client portal. Your files have a project number in the address. Open yours, then change the number by one and see what three versions of the portal show you.
Real: the database, its rules and every lookup. Pretend: the login and the portal. The files are just names, not real documents.
Every project number, tried
Brightlane tries every project number in each portal. Pink means it got files that aren't Brightlane's. These lookups ran when you opened this page.
| Project | Only checks login | Rule on, master key | Careful: first check | Careful: database alone |
|---|---|---|---|---|
| 1038Acme Tools | 2 files | 2 files | 404 | 0 rows |
| 1039Becker Freight | 2 files | 2 files | 404 | 0 rows |
| 1042Brightlane | 3 files | 3 files | 200 | 3 rows |
| 1043Kestrel Analytics | 3 files | 3 files | 404 | 0 rows |
| 1044Ortiz Studio | 2 files | 2 files | 404 | 0 rows |
| 1045Atlas HR | 2 files | 2 files | 404 | 0 rows |
| 1046Tidepool Payments | 3 files | 3 files | 404 | 0 rows |
| 1047Halden Health | 0 files | 0 files | 404 | 0 rows |
| 1048Quill Notes | 2 files | 2 files | 404 | 0 rows |
| 1049Verdant Energy | 2 files | 2 files | 404 | 0 rows |
What actually happened
Logged in doesn't mean allowed
Logged in as Brightlane, the first portal shows 18 files from 8 other clients, including a confidential one. Nobody broke in. They changed a number in the address.
The rule existed and did nothing
The second portal has the database rule switched on. It still showed 18 files, because it talks to the database with a master key that ignores the rule. Lots of real apps do exactly this.
Two checks, each one enough
The careful portal checks the project is yours: 9 of 9 other projects get “not found”. Skip that check and the database on its own returns 0 files, because it's asked as Brightlane, not with a master key.
“Not found”, not “forbidden”
“Forbidden” would tell a stranger the project exists. The careful portal says “not found” either way, so trying numbers teaches you nothing.
The code
What the two approaches do, in one sentence. The code itself is one click away.
The careless setup writes the database rule but connects with a master key that ignores it. The careful one checks in the app and asks the database as the client, so either check alone is enough.
Show the code anyway
-- migrationALTER TABLE harbor.files ENABLE ROW LEVEL SECURITY;CREATE POLICY own_files ON harbor.files USING (client = current_setting('harbor.client', true));// GET /projects/:id/filesawait q("SELECT set_config('harbor.client', $1, true)", [user.client]);return q("SELECT * FROM harbor.files WHERE project_id = $1", [id]);// connected as the app's role: rolbypassrls = trueconst [p] = await sql("SELECT client FROM harbor.projects WHERE id = $1", [id]);if (p?.client !== user.client) return notFound(); // lock 1: the appreturn withTx(async (q) => { await q("SET LOCAL ROLE harbor_client"); // lock 2: no BYPASSRLS await q("SELECT set_config('harbor.client', $1, true)", [user.client]); return q("SELECT * FROM harbor.files WHERE project_id = $1", [id]);});Still curious?
A few more questions people ask. Open what you need.
Has this happened for real?
Yes. On a ticketing platform I took over, the database rules existed but the server used a master key, so they did nothing. What actually kept customers apart was checks in the code on every request. The risk with that: someone adding a new page has to remember the check, so I made it hard to forget.
Does this apply to Supabase?
Yes. Supabase gives you a normal key that follows the rules and a master key that skips them. Server code often uses the master key because it's easier. Then the rules only work if the code checks access too.
Isn't there a switch that makes the rule apply to everyone?
There is, but it doesn't touch a master key. The fix is to ask the database as a normal client for each request, and make sure that setting ends when the request does, so the next visitor doesn't inherit it.
Wouldn't random numbers fix it?
They make guessing harder, but links get forwarded and copied. Only the check actually protects the files. Chapter 6 is about old links that keep working.
Not sure this could happen to you?
That's what the call is for. Tell me how your product works, and I'll tell you what applies to you and what I'd check first.
Ticketing: row-level security, bypassed by the service key
This article is the toy version of a problem from a real project. The case page has the real story.