Postgres, RLS and the Data API
1. How does a request from supabase-js reach Postgres, and what decides what it can see?
The client sends an HTTP request to the project's Data API, which is PostgREST.
PostgREST reads the API key from the apikey header and the user's session JWT from Authorization, switches to the matching Postgres role (anon, authenticated or service_role), sets the JWT claims for the transaction, and runs the query as that role.
Two layers then decide access. Table privileges (GRANT) decide whether the role can touch the table at all. Row Level Security policies decide which rows it can read or change.
A strong candidate names both layers and says that the browser is a direct database client, so the policies are the backend. A weak answer treats the client library as an ORM sitting behind some server they wrote.
2. What does enabling RLS on a table do before you write any policy?
It denies everything. With RLS enabled and no policies, anon and authenticated get zero rows on select and every write fails, because Postgres treats a missing policy as "no rows pass".
Roles with BYPASSRLS, such as service_role and the table owner postgres, are not affected.
The mistake interviewers want candidates to spot is the opposite case: a table in an exposed schema with RLS disabled is readable and writable by anyone holding the publishable key, as long as the role has table grants.
The Security Advisor in the dashboard flags these tables. A senior candidate enables RLS in the same migration that creates the table.
create table public.notes (
id bigint generated always as identity primary key,
user_id uuid not null references auth.users (id) on delete cascade,
body text not null,
created_at timestamptz not null default now()
);
alter table public.notes enable row level security;
grant select, insert, update, delete on public.notes to authenticated;
create policy "Owners read their notes"
on public.notes for select
to authenticated
using ( (select auth.uid()) = user_id );
3. What is the difference between USING and WITH CHECK?
USING filters the existing rows a statement can see or target. WITH CHECK validates the new row a statement is trying to write.
Select and delete policies take only USING, insert policies take only WITH CHECK, and update policies take both: USING picks which rows the user may update, WITH CHECK decides what those rows may become.
An update policy with USING and no WITH CHECK makes Postgres reuse the USING expression for the new row.
That default is safe for ownership checks, but a candidate should still write both out, because question 7 shows how an update policy without a proper check lets a user move a row to another owner.
4. Your RLS-protected list endpoint got slow as the table grew. What do you check?
Run explain analyze on the query with RLS active and look for a sequential scan with a large "Rows Removed by Filter" count. The RLS guide lists the fixes a candidate should name:
- Index the policy columns. A
btree index on user_id lets the policy filter use an index scan.
- Wrap function calls in a select.
(select auth.uid()) lets Postgres evaluate it once per statement as an initPlan instead of once per row. This only works when the result does not depend on the row.
- Name the role with
to. A policy scoped to authenticated is skipped for anon requests.
- Move lookups into a
security definer function. A policy that joins a membership table re-runs that table's own RLS; a definer function in an unexposed schema avoids it.
- Filter in the query too. Adding
.eq('user_id', userId) on the client gives the planner a condition it can use, even though the policy enforces the same rule.
A strong candidate also says that a policy is a WHERE clause added to every query, so its cost multiplies with the number of rows the query touches.
5. How do you write a team-membership policy without killing performance?
Put the membership check in a security definer function in a schema the Data API does not expose, pin its search_path, and call it from the policy wrapped in a select.
Flip the check so the function returns the set of teams for the current user, which Postgres can evaluate once, instead of checking membership per row.
create schema if not exists private;
create function private.user_team_ids()
returns setof uuid
language sql
security definer
stable
set search_path = ''
as $$
select team_id
from public.team_members
where user_id = (select auth.uid())
$$;
create policy "Team members read projects"
on public.projects for select
to authenticated
using ( team_id in (select private.user_team_ids()) );
A weak answer misses two traps. Creating the function in public makes it callable as an RPC endpoint, and a definer function without set search_path = '' can be hijacked by objects a user creates in another schema.
Interviewers should ask why every name inside is schema-qualified.
6. Find the bug: an admin policy built on JWT metadata.
create policy "Admins can read all orders"
on public.orders for select
to authenticated
using ( (auth.jwt() -> 'user_metadata' ->> 'role') = 'admin' );
Any signed-in user can make themselves an admin. user_metadata comes from raw_user_meta_data, which the user can change with supabase.auth.updateUser({ data: { role: 'admin' } }).
After their next token refresh, the claim is in the JWT and the policy passes. The Supabase docs say not to use raw_user_meta_data for authorization.
The fix is to read app_metadata, which only the server can set, or a roles table looked up through a definer function.
A senior candidate adds that JWT claims are stale until the token refreshes, so revoking admin access takes effect only after the access token expires, and a roles table checked at query time does not have that delay.
A custom access token hook is the third option when the role must be in the token.
7. Find the bug: a post update policy.
create policy "Users update their own posts"
on public.posts for update
to authenticated
using ( (select auth.uid()) = author_id )
with check ( true );
The USING clause limits which rows a user can target, but with check (true) accepts any new row.
A user can run an update that sets author_id to another user's ID and hand their post to someone else, or set it to a value that makes the row disappear from their own view.
On a table with a published or approved flag, the same hole lets users approve their own content.
The fix is with check ( (select auth.uid()) = author_id ).
For columns users should never change, the stronger fix is column privileges: revoke update on public.posts from authenticated, then grant update (title, body) on public.posts to authenticated.
Candidates who reach for column grants have usually been burned by this before.
8. A view returns rows that the underlying table's RLS should hide. Why?
Views run with the privileges of their owner by default, and the owner is usually postgres, which bypasses RLS. A view in public over an RLS-protected table therefore exposes every row through the Data API.
On Postgres 15 and later, create it with (security_invoker = true) so the view checks permissions and policies as the calling role.
create view public.my_open_tasks
with (security_invoker = true)
as
select id, title, due_at
from public.tasks
where done = false;
The same thinking applies to functions: a security definer function in an exposed schema is an RPC endpoint that runs as its owner.
A strong candidate audits views, materialized views (which have no RLS at all) and definer functions when reviewing a schema, not only tables.
