Postgres and Row Level Security on Supabase

What Row Level Security Does for You

Your customer list, your jobs and your invoices sit in one Postgres database. Many apps talk to it. Staff sign in from a phone. A public page shows open hours. A report runs at night.

Row level security is a rule that lives inside the database itself. It says who may see or change each row. It does not matter which app asks. If the rule says no, the row does not come back.

That matters because Supabase hands out a public key to web pages. Anyone can find it in the page source. That is by design. The key is only safe if the database refuses to show that key anything private. Row level security is the thing that refuses.

How We Wire Every Table

Every new table gets three things before any data goes in. Security on. The public role shut out. A written rule for who can read and write.

alter table public.jobs enable row level security;
revoke all on public.jobs from anon;

create policy "members read their shop's jobs"
  on public.jobs for select
  to authenticated
  using (
    shop_id in (
      select shop_id from public.shop_members
      where user_id = (select auth.uid())
    )
  );

The (select auth.uid()) wrapper is on purpose. Postgres runs it once per query instead of once per row. On a big table that is the gap between a fast page and a timeout.

We also shut the door for tables nobody has made yet. By default, a new table can come with grants the public role can use. We change the default so new tables start closed.

alter default privileges in schema public
  revoke all on tables from anon;

Test as a Real User, Not by Reading

This is the lesson that cost us the most. A migration file shows what someone meant to do. It does not show what the database now allows. Another migration may have changed it. A grant may have slipped in. A view may skip the rules entirely.

So we ask the database directly, as the person in question. Inside a transaction, we take on the role of a signed-in user and run the query their app would run.

begin;
set local role authenticated;
select set_config(
  'request.jwt.claims',
  '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}',
  true
);
select count(*) from public.jobs;
rollback;

Swap in a real user's id. If a tech at one shop can count rows from another shop, the rule is wrong, however clean the migration looked. Then we run the same test as anon and expect zero.

We also ask the catalog which tables have security off. This takes one query and never lies.

select c.relname
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
  and c.relkind = 'r'
  and not c.relrowsecurity;

Why We Chose Postgres on Supabase

Postgres is the database a large share of the world's apps already trust. It has been around for decades. It is open source, so your data is never locked in a format only one company can read.

Supabase runs Postgres for us and adds the parts an app needs around it. Sign in. File storage. An API that pages can call. Backups. We do not have to run any of that on our own server, and you do not have to pay someone to watch it.

The best part is that security lives in the database, not in each app. Shop Desk, a report job and a public booking page all hit the same rules. We write a rule once. Every door into the data has to pass it. If we add a new app next year, it inherits the same locks on day one.

That is also why the testing matters so much. One wrong rule is wrong for every app at once.

What It Saves the Owner

You do not have to wonder if a stranger can read your customer list. You do not have to trust that a developer remembered a setting. The rule sits in the database and we test it with a real login.

It also saves you from the worst kind of bug. That is the one where nothing looks broken. The page loads. The save button works. And someone who should not see a row can see it. No error ever tells you. Only a test does.

This is the database under Shop Desk, which runs a working auto repair shop, Supercanic, every day. Real staff, real customers, real money. That is why we treat every table as private until proven otherwise.

What We Learned the Hard Way

A migration that applies with no errors tells you nothing about what the database now allows. We once found a batch of reporting tables open to anyone with the public key. Nothing had failed. The tables were made after the default grants had been left open, and the grants came along for the ride.

Views are the next trap. A view can run with the rights of the person who made it, not the person asking. That skips row level security without a word. We check every view the same way we check tables, by acting as a user.

Last, a save that matches no rows is not an error in Postgres. If a rule blocks an update, the app can get back a success with zero rows changed. The screen says saved and nothing saved. We check the row count on every write that matters.

Read the full method in the Supabase row level security guide, and the quiet save problem in Supabase silent failures. Next up the stack is app hosting on Railway.