A Supabase project gives you two ways into the same Postgres: a connection
string, and a public REST API that anyone holding the project's public key can
call from a browser. Nakodo only uses the first one. This
is the census of what that means in the schema, and the part that surprised me
when I checked it.
The census
$ grep -c "enableRLS" src/db/schema.ts 25 $ grep -ric "create policy" drizzle/ 0 $ grep -ri "pgPolicy" src/db/schema.ts | wc -l 0 25 tables, 25 calls, no policies anywhere in the schema or in any of the 32
migrations. In the generated SQL the calls show up as one statement per table:
ALTER TABLE "campaigns" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint There are 28 of those statements, spread over 9 of the 32 migration files.
Comparing the table names in them with the current snapshot explains the extra
three:
print('in migrations but gone:', [t for t in historic if t not in current]) # ['contact_unlocks', 'outreach_messages', 'quota_days'] print('current with no RLS statement:', [t for t in current if t not in historic]) # [] Three tables were dropped along the way and nothing current was ever created
without the statement. That second line is the one I wanted to see, and the
only way to see it was to ask.
What "on, with no policies" actually does
This is the bit people assume is half-configured, so here it is in a throwaway
Postgres you can start in a minute:
create table demo (id int primary key, secret text); insert into demo values (1, 'a'), (2, 'b'); alter table demo enable row level security; create role visitor login; grant select on demo to visitor; As the owner:
owner_sees ------------ 2 As visitor, which has been granted select on the table:
visitor_sees -------------- 0 insert into demo values (3,'c'); ERROR: permission denied for table demo Row level security with no policies is not a door left ajar. It is a closed
door: a role subject to RLS matches no rows, because no policy says any row is
visible to it. The grant is still necessary and no longer sufficient.
The nuance worth knowing is in pg_class:
relname | relrowsecurity | relforcerowsecurity ---------+----------------+--------------------- demo | t | f RLS is on, and forcing is off, which means the table's owner is not subject to
it. That is exactly why our app still works. Our connection is the one that
owns these tables, so it reads and writes normally, while a request arriving at
the project's public API as the anon or authenticated role is not the owner
and sees nothing at all.
So RLS here is not our authorisation model. Authorisation is TypeScript: a
helper that resolves the signed-in user, every row read by owner, and a
deliberate 404 rather than a 403 when a row belongs to somebody else. RLS is
the answer to a different question, which is "what happens if something talks
to this database through the other door".
The other half: the key never leaves the server
The reason the other door is not worth attacking is that nothing we serve
carries its key. The project URL and public key are a module constant, and the
constant is imported by exactly two files:
$ grep -rn "supabase/project" src src/proxy.ts:5:import { SUPABASE_ANON_KEY, SUPABASE_URL } from "@/lib/supabase/project"; src/lib/supabase/server.ts:3:import { SUPABASE_ANON_KEY, SUPABASE_URL } from "./project"; Both run on the server. There is no browser Supabase client in the app at all:
sign-in is a Server Action, the session lives in a cookie the server sets, and
the repository contains zero NEXT_PUBLIC_ variables. You can check the result
of that from outside:
$ curl -s https://nakodo.app/ | grep -c supabase 0 $ curl -s https://nakodo.app/login | grep -o 'eyJ[A-Za-z0-9_-]\{10,\}' | wc -l 0 No project reference, no JWT, nothing to copy out of
the home page or
the sign-in page. Running the same grep over every
URL in the sitemap gives 0 pages with a hit
out of 76, which is less of an achievement than it sounds: it follows from
never having written a client-side query.
What it does buy is honest. Our privacy notice
lists what we hold and promises an export and a deletion; the thing that makes
those promises keepable is that there is exactly one code path to the data, in
one language, with one authorisation helper in front of it.
Three gaps the census found
Nothing tests it. Not one of the repository's test files connects to a
database, so the assertion "every table has RLS on" exists only as the Python
comparison above, which I ran by hand while writing this. A real check would be
a query against pg_class in a seeded database, and we do not have one.
.enableRLS() is a convention, not a gate. Add a table without it and
drizzle generates a CREATE TABLE with no ALTER TABLE, nothing fails, and
the omission shows up only in a snapshot nobody reads. For a project where the
public API is permanently one policy away from being usable, the default should
be the other way round.
Forcing is off. relforcerowsecurity is false on all 25 tables, which is
the right call today, because our own connection owns them and would otherwise
need policies to read anything. It also means RLS can never catch a bug of
ours. It is a wall around the door we do not use, and the door we do use has a
TypeScript lock on it. Knowing which is which is the whole point of writing the
census down.
