Supabase row-level security for video: scoping media, uploads and stream keys

Supabase row-level security is usually taught on a table the signed-in user writes into, so the policy compares auth.uid() against an owner_id that same session inserted. The fastpix schema our Supabase integration creates inverts that, because a backend worker writes every row from a FastPix webhook and authenticated should hold select and nothing else.

One of those five tables also holds a live credential. fastpix.live_streams carries streamKey, srtSecret, srtPlaybackStreamId and srtPlaybackSecret, plus keys inside simulcastResponses, any of which lets a stranger broadcast into or pull from your account on your bill, and a policy filters rows rather than columns, so it will not hide either value however carefully you write it.

So the order below is grant, then enable, then one policy at a time. Our docs ship the first half, the service-role grants and the enable statements, and stop. The revoke, the grants to signed-in users and every policy here are yours to write. You get a policy per table, the view that keeps the stream key server-side, the quoting camelCase needs, and pgTAP tests that fail for the right reason.

Supabase row-level security starts with grants

Grants and policies are two separate layers and Postgres checks the grant first, so a policy on a table the role cannot select from raises 42501 before the policy is ever evaluated. Supabase's own order is revoke, grant, enable, then policy, and following it stops the two failures in the troubleshooting section below from arriving as unrelated mysteries.

-- the worker's role. The service-role key bypasses RLS, but it still needs grants.
grant usage on schema fastpix to service_role;
grant all on all tables in schema fastpix to service_role;
alter default privileges in schema fastpix grant all on tables to service_role;

-- take everything back from the two roles a browser can reach.
-- The first line covers today's tables, the second covers the ones a
-- future package version adds, which would otherwise arrive unprotected.
revoke all on all tables in schema fastpix from anon, authenticated;
alter default privileges in schema fastpix revoke all on tables from anon, authenticated;

-- deny by default on all five, including the ones nobody will ever open
alter table fastpix.webhook_events enable row level security;
alter table fastpix.media          enable row level security;
alter table fastpix.live_streams   enable row level security;
alter table fastpix.uploads        enable row level security;
alter table fastpix.sync_state     enable row level security;

-- hand back exactly the read the policies below will then filter
grant usage on schema fastpix to authenticated;
grant select on fastpix.media, fastpix.uploads to authenticated;
grant select on public.media_owners to authenticated;

-- your own mapping table needs a policy too, or every signed-in user
-- reads the whole ownership list for every tenant
alter table public.media_owners enable row level security;

create policy "read own ownership rows"
  on public.media_owners
  for select
  to authenticated
  using (owner_id = (select auth.uid()));

The grant on public.media_owners is the one we forget most often: the policy below subqueries it as the calling role, and a role with no select on it gets 42501 from inside a policy it cannot see. Granting it without a policy is the opposite mistake and a quieter one, because every signed-in user then reads which user owns which asset across every tenant. The policy above closes that, and it cannot recurse, because media_owners never looks back at fastpix.media.

The default-privileges line matters more than it looks. revoke all on all tables only reaches the tables that exist when you run it, and the integration is still at 0.1, so the next version can add a table that arrives fully readable with nobody noticing. Re-run the block after any upgrade regardless, because default privileges only cover objects created by the role that set them.

Enabling row-level security with no policies denies every role except service_role, which reads like an outage and is the correct starting point. Our worker keeps writing throughout, because the service-role key bypasses policies, and a browser gets nothing until you open one table deliberately. FastPix's Supabase setup guide ships the enable block as the last step before production.

Before that block runs the tables are not quite wide open. A custom schema is not on Supabase's Data API until somebody adds it under Exposed schemas (Supabase documents the setting), so an anon key hitting PostgREST gets nothing out of fastpix by default. One toggle stands between you and five unprotected tables, and it never applied to a direct connection anyway.

Supabase is also retiring the legacy anon and service_role API keys by the end of 2026, in favour of sb_publishable_* and sb_secret_*. The database roles keep their names, so nothing in these policies moves. Keep whichever secret key you hold in server-side code only, because a key that ignores every policy you write turns the rest of this into decoration.

Which tables a browser reads

Two of the five, and only through a policy. fastpix.media and fastpix.uploads carry what an interface renders, and the other three hold credentials or bookkeeping that should never leave your server.

We write no insert, update or delete policy anywhere in this schema and neither should you, because the worker needs none and a client that can update fastpix.media can rename someone else's video the first time a select policy has a gap.

Map ownership in your own table

Keep the mapping in a table you control. public.media_owners holds one row per media asset and the user who owns it, with a real foreign key into auth.users, and it is the version that survives a retrofit.

create table public.media_owners (
  media_id text primary key,
  owner_id uuid not null references auth.users (id)
);
create index media_owners_owner_idx on public.media_owners (owner_id);

create policy "read own media"
  on fastpix.media
  for select
  to authenticated
  using (exists (
    select 1 from public.media_owners o
    where o.media_id = fastpix.media."mediaId"
      and o.owner_id = (select auth.uid())
  ));

Wrapping the helper in (select auth.uid()) makes Postgres treat it as an initPlan, evaluated once per statement rather than once per row. Index both join columns, because an unindexed policy lookup turns each read into a sequential scan. Our Realtime companion subscribes a browser to the same table through the same mapping, so the two policies are deliberately identical: live upload and encode status with Supabase Realtime.

You can skip the join by matching "creatorId" instead, an optional string on the create media request that our sync engine writes into fastpix.media. We do not lead with it: it is free text in somebody else's schema with no foreign key behind it, and it is null on everything uploaded before you started setting it, so a retrofit makes the back catalogue vanish with no error.

fastpix.uploads cannot use that shape, and this one cost us a bug report. Its mediaId stays empty until FastPix creates the media, so a policy joining on it shows the user nothing for the whole upload, which is the window a progress screen exists for, and deleting the media later orphans the row. Key your own table on the upload's own id instead, which exists from the moment you create the session:

create table public.upload_owners (
  upload_id text primary key,
  owner_id  uuid not null references auth.users (id)
);

create policy "read own uploads"
  on fastpix.uploads
  for select
  to authenticated
  using (exists (
    select 1 from public.upload_owners o
    where o.upload_id = fastpix.uploads."uploadId"
      and o.owner_id = (select auth.uid())
  ));

Give it the same treatment as media_owners. A progress screen needs status and error, nothing else.

Quote camelCase columns in policies

Every row-level security example you have read uses snake_case, so this never comes up until it does. The fastpix column names match the FastPix API, so they are camelCase, and Postgres folds an unquoted identifier to lower case.

-- Fails: Postgres looks for a column named mediaid
create policy "read own media" on fastpix.media
  for select to authenticated
  using (mediaId in (
    select media_id from public.media_owners
    where owner_id = (select auth.uid())));

-- Works
create policy "read own media" on fastpix.media
  for select to authenticated
  using ("mediaId" in (
    select media_id from public.media_owners
    where owner_id = (select auth.uid())));

The first statement raises a column-not-found error at creation time, which is the kind way to fail. The unkind version is a lower-case column beside the camelCase one, where the policy quietly filters on the wrong thing. Keep your own tables snake_case, which is why media_owners uses media_id and owner_id, so only the fastpix side ever needs quoting.

Can row-level security hide the stream key?

No. Row-level security answers which rows and has no opinion about which columns, so a role allowed to see one row of fastpix.live_streams reads streamKey, srtSecret, srtPlaybackSecret and the keys inside simulcastResponses with it. Anyone holding a stream key can broadcast into your account, and the bill is yours.

Two mechanisms sit outside row-level security and fix this. Column-level privileges let you revoke select and grant it back per column. The other is what the FastPix docs recommend: a view in public naming only the safe columns.

create view public.live_stream_status as
  select "streamId", "status", "lowLatency", "enableRecording", "createdAt"
  from fastpix.live_streams;

grant select on public.live_stream_status to authenticated;

Leave that view on the Postgres default, a security-definer view owned by postgres, so it reads the base table with the owner's rights and returns the five columns you named.

Adding with (security_invoker = on) gets you the opposite. Invoker semantics apply the caller's permissions and policies to fastpix.live_streams, and the grant block above gives authenticated nothing there, so the call fails with permission denied for schema fastpix. Invoker views are right when the base table carries a policy you want enforced through it. This one carries none for authenticated on purpose, and reading a table the caller cannot is the whole point.

Name every column, because a view written with select * starts returning whatever the next migration adds. The same reasoning covers webhook_events.payload and uploads.url, a short-lived signed upload target. A playback ID is the exception: it is not a secret on its own, and access is decided by the token you mint, which we cover in how to protect video content with signed URLs.

Test each policy with pgTAP

A policy that reads correctly and a policy that behaves correctly are different claims, and only one survives the next migration. pgTAP runs inside the database, so a test can set the role the way your API does.

create extension if not exists pgtap;

begin;
select plan(3);

-- A grant check reads the catalogue, so it does not care which role runs it.
select ok(
  not has_table_privilege('authenticated', 'fastpix.media', 'update'),
  'authenticated holds no update grant on media');

-- Everything below is an RLS assertion, so set the role BEFORE the first one.
-- Run these as the table owner and RLS is bypassed: they pass on nothing.
set local role authenticated;
set local request.jwt.claims = '{"sub":"11111111-1111-1111-1111-111111111111"}';

select is_empty(
  $$ select 1 from fastpix.live_streams limit 1 $$,
  'authenticated cannot read live_streams');

select results_eq(
  $$ select count(*)::int from fastpix.media $$,
  $$ values (2) $$,
  'authenticated sees only its own two media rows');

select * from finish();
rollback;

Both comments mark a bug we shipped in an earlier version of this page. The is_empty assertion sat above set local role, so it ran as the table owner, and a table owner bypasses row-level security, which means it passed while proving nothing.

The write assertion was worse: a throws_ok on 42501 labelled "authenticated cannot write media". An UPDATE blocked by a policy affects zero rows and raises no error, so the only thing that code could ever catch was the missing grant. Assert the grant directly, the shape Supabase's own pgTAP example uses, and the test says what it means.

Run it in CI against a seeded branch. The first pass is not the value: we wrote it for the failure three months later, when somebody adds a table and forgets the enable statement.

Why your policy returns zero rows

Check which role ran the query before you touch the policy: the two commonest causes are outside it.

Zero rows and the policy looks right. The Supabase SQL editor runs as postgres, your tables' owner, and an owner bypasses row-level security unless the table forces it. That is ownership rather than superuser, so a query that works in the editor proves nothing about authenticated. Set the role and the JWT claim explicitly, the way the pgTAP block does. Then check the cast, since auth.uid() returns uuid and creatorId is text, and then the value, because a creatorId you never set is null and null equals nothing.

`42501` before the policy runs. 42501 is insufficient privilege, a grant failure rather than a policy failure, because Postgres checks the grant first. The role needs usage on fastpix and select on the table, plus select on public.media_owners if that is what the policy subquery reads. A policy cannot grant access the role does not already hold.

Recursion, reported as 42P17. A policy that queries a table which itself carries a policy pointing back can loop. Move the lookup into a security definer function with a pinned search_path. public.media_owners avoids the shape, because it carries a grant and no policy.

A column-not-found error out of nowhere. That is the camelCase fold again, and the integration troubleshooting list names it as its own entry for the same reason.

Run the block on a branch

Order matters here more than any single policy. Run the grant and enable block on a Supabase branch, confirm the worker still writes, then open media and uploads one at a time and leave the other three closed. One query says where you stand:

select table_schema, table_name, privilege_type
from information_schema.table_privileges
where grantee = 'authenticated'
  and table_schema in ('fastpix', 'public')
order by table_schema, table_name;

Populate public.media_owners on every upload from today, because retrofitting ownership is the slow version of this job. The Supabase integration guide carries the grant block, the schema layout and the sync engine for non-Supabase Postgres, and running the whole setup against a real webhook first needs nothing beyond the free plan.