Policy Patterns and Testing

Membership and testing

Beyond owner-only, a common pattern is team membership: a members(team_id, user_id) table, and policies that allow access when a membership row exists. Put the lookup in a security definer helper function with a fixed search_path to avoid recursive policies on the members table itself. Read-only public data uses using (true) for select only. Test policies by signing in as different users from the client, or in SQL by setting the role and JWT claims inside a transaction; tools such as pgTAP can automate this.

Team access and a SQL test

Pattern plus a manual check.

create or replace function public.is_team_member(p_team_id bigint)
returns boolean
language sql stable security definer
set search_path = ''
as $$
  select exists (
    select 1 from public.members m
    where m.team_id = p_team_id and m.user_id = (select auth.uid())
  );
$$;

create policy "team members read docs" on public.docs
  for select to authenticated
  using (public.is_team_member(team_id));

-- manual test: impersonate a user inside a transaction
begin;
set local role authenticated;
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-000000000001"}';
select count(*) from public.docs;
rollback;

The dashboard SQL editor bypasses RLS by default

It runs as a privileged role, so a query working there proves nothing about what users see. Test as anon or authenticated.

Quick check: Why test policies as the authenticated role rather than in the default SQL editor session?

• Policies only exist in the client
• The SQL editor cannot run selects
• The default session is privileged and bypasses RLS
• Roles do not affect queries