Row level security

This is the page to read properly. Everything else in Baselyra is convenience; this is the part that decides whether your data is safe. Baselyra performs no authorisation in JavaScript — Postgres decides, every time, for every path, and REST, storage, realtime and the client SDK all end at the same policies.

The model

Every request that touches your data goes through one function, asRole(), which opens a transaction and does three things before running anything:

BEGIN;
SELECT set_config('role', 'authenticated', true);                  -- or 'anon', or 'service_role'
SELECT set_config('request.jwt.claims', '{"sub":"6c1f…","role":"authenticated", …}', true);
SELECT set_config('request.jwt.claim.sub',  '6c1f…', true);
SELECT set_config('request.jwt.claim.role', 'authenticated', true);
-- your query runs here
COMMIT;

Both settings are LOCAL, so the commit restores the pooled connection. There is no path by which one request's identity survives into the next.

What the caller sent	Postgres role	RLS
Nothing, or the anon key	`anon`	Enforced
`Authorization: Bearer <user access token>`	`authenticated`	Enforced
The service key	`service_role`	Bypassed — `BYPASSRLS`
A Studio session on `/admin/v1`	`service_role`	Bypassed

The three roles are NOLOGIN NOINHERIT, so membership never leaks privileges implicitly: a connection only gets one by an explicit SET ROLE, which is what asRole() does.

The anon key is public and is meant to be public. It is not a password; it only names the role a request runs as. The service key is the opposite: it bypasses every policy on the instance, so it belongs on a server and nowhere else — never in a bundle, a mobile binary, or a NEXT_PUBLIC_* variable.

Grants and policies are two different gates

Two gates, two failure shapes. Knowing which one you hit tells you which thing to fix.

Helper functions

These read request.jwt.claims and are executable by anon, authenticated and service_role. They never raise — a policy that errors turns a denied row into a failed query, and an anonymous request legitimately has no claims at all.

Function	Returns	Notes
`auth.uid()`	`uuid`	The signed-in user's id, `NULL` for anon. The `sub` claim is matched against a uuid regex before the cast, so a malformed one is `NULL` rather than an error inside your policy.
`auth.role()`	`text`	`anon`, `authenticated` or `service_role`. Falls back to the `request.jwt.claim.role` GUC, then to `anon`.
`auth.email()`	`text`	The `email` claim, `NULL` if absent. A phone-only user has none.
`auth.jwt()`	`jsonb`	The whole verified claim set, including `app_metadata` and `user_metadata`.

An application admin role

If your application needs its own admin role, keep it in the user's app_metadata — a field only the service key can write — and read it from the claims:

create policy invoices_admin_read on public.invoices
  for select to authenticated
  using ((auth.jwt() -> 'app_metadata' ->> 'role') = 'admin');

curl -s -X PUT "$URL/auth/v1/admin/users/$USER_ID" \
  -H "authorization: Bearer $SERVICE_KEY" \
  -H 'content-type: application/json' \
  -d '{"app_metadata":{"role":"admin","plan":"pro"}}'

The claim is inside the signature, so it takes effect on that user's next sign-in or token refresh — within an hour at the default TTL, or immediately if the client calls bl.auth.refreshSession().

What happens if you leave RLS off

This is the failure mode that matters, so it gets its own section.

db/project/001_schema.sql grants table privileges in public to anon and authenticated — for existing tables, and, through ALTER DEFAULT PRIVILEGES, for every table created later:

alter default privileges in schema public
  grant select, insert, update, delete on tables to anon, authenticated;

That is what makes a table work over /rest/v1 the moment you create it, with no grant step. It also means:

Reproduce it in ten seconds on a fresh install, and then never forget it:

curl -s -X DELETE "$URL/rest/v1/unprotected?id=gt.0" -H "apikey: $ANON_KEY"

So a new table is exactly two statements from safe:

alter table public.thing enable row level security;
-- then at least one policy, or nothing can read it at all

With RLS enabled and no policies, the table denies everything to anon and authenticated — which is the correct, safe default. An empty result from REST usually means "RLS is on and no policy admits you", not "no rows".

Audit what you have

select c.relname                        as table,
       c.relrowsecurity                 as rls_enabled,
       count(p.polname)                 as policies
  from pg_catalog.pg_class c
  join pg_catalog.pg_namespace n on n.oid = c.relnamespace
  left join pg_catalog.pg_policy p on p.polrelid = c.oid
 where n.nspname = 'public' and c.relkind in ('r', 'p')
 group by 1, 2
 order by rls_enabled, 1;

      table       | rls_enabled | policies
------------------+-------------+----------
 signup_leads     | f           |        0     <-- world-writable
 audit_scratch    | f           |        0     <-- world-writable
 invoices         | t           |        0     <-- inert: nobody can read it
 articles         | t           |        4
 notes            | t           |        4

Anything with rls_enabled = false is world-writable. Anything with RLS on and zero policies is inert. The Studio's Database page shows the same thing per table, and a table with RLS off is badged; so does the VS Code extension, in the warning colour.

Two ways to bypass your own policies

Views. A view runs with its owner's privileges by default, and the table owner is exempt from RLS — Baselyra enables RLS without FORCE, which is what lets the auth module manage sessions and tokens on the owner connection. A view over an RLS-protected table therefore returns every row to whoever can select from the view, and /rest/v1 exposes views.

create view public.recent_notes with (security_invoker = true) as
  select id, title, created_at from public.notes order by created_at desc;

-- retrofit
alter view public.recent_notes set (security_invoker = true);

security definer functions. A function marked security definer runs as its owner and bypasses RLS on everything it touches. That is exactly what you want for the membership helpers below, and exactly what you do not want for a function you expose over /rest/v1/rpc/. Default to security invoker and pin search_path on anything you do mark definer.

Writing policies

A policy has a name, a command, a list of roles, and one or two expressions:

using (…) — which existing rows this command may see or touch (SELECT, UPDATE, DELETE).
with check (…) — which rows may result from a write (INSERT, UPDATE).

How the policies on a table combine. Permissive policies OR; a restrictive policy ANDs on top of all of them.

Two habits worth adopting from the first policy you write:

-- 1. Index every column a policy filters on. A policy is a WHERE clause that
--    runs on every row of every query against that table.
create index notes_user_id_idx on public.notes (user_id);

-- 2. Wrap the helper in a scalar subquery so the planner evaluates it once per
--    statement instead of once per row.
using (user_id = (select auth.uid()))

The five sets below are complete and copy-pasteable. Run them in the Studio SQL editor with the mode set to Write.

1. Private per-user data

A table only its owner can see. The default shape for notes, settings, documents, anything personal.

create table public.notes (
  id         uuid primary key default gen_random_uuid(),
  user_id    uuid not null default auth.uid()
             references auth.users(id) on delete cascade,
  title      text not null check (length(title) between 1 and 200),
  body       text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index notes_user_id_idx on public.notes (user_id);

alter table public.notes enable row level security;

create policy notes_select_own on public.notes
  for select to authenticated
  using (user_id = (select auth.uid()));

create policy notes_insert_own on public.notes
  for insert to authenticated
  with check (user_id = (select auth.uid()));

create policy notes_update_own on public.notes
  for update to authenticated
  using (user_id = (select auth.uid()))
  with check (user_id = (select auth.uid()));

create policy notes_delete_own on public.notes
  for delete to authenticated
  using (user_id = (select auth.uid()));

Why it is safe:

No policy names anon, so anonymous callers get nothing — not an error, an empty result, which is also what you want, because existence is information.
user_id defaults to auth.uid(), so clients never send it, and the with check refuses it if they try to send someone else's.
The update policy repeats the condition in with check. Without that, a user could UPDATE … SET user_id = <someone else> and hand their row away. Always write both.

await bl.from('notes').insert({ title: 'Groceries' });   // user_id fills itself
const { data } = await bl.from('notes').select('*');     // only yours, no filter needed

And the proof, from the shell, with two different users' tokens against one URL:

curl -s "$URL/rest/v1/notes?select=id,title" -H "apikey: $ANON_KEY" -H "authorization: Bearer $ADA"
[{"id":"3f2a…","title":"Groceries"}]

curl -s "$URL/rest/v1/notes?select=id,title" -H "apikey: $ANON_KEY" -H "authorization: Bearer $GRACE"
[]

2. Public read, owner write

The blog shape: everyone reads what is published, only the author writes, and the author can also see their own drafts.

create table public.articles (
  id           bigint generated always as identity primary key,
  author       uuid not null default auth.uid()
               references auth.users(id) on delete cascade,
  slug         text not null unique,
  title        text not null,
  body         text not null default '',
  published_at timestamptz,
  created_at   timestamptz not null default now()
);

create index articles_author_idx on public.articles (author);
create index articles_published_idx on public.articles (published_at desc)
  where published_at is not null;

alter table public.articles enable row level security;

-- Anyone, signed in or not, sees published articles.
create policy articles_select_published on public.articles
  for select to anon, authenticated
  using (published_at is not null and published_at <= now());

-- The author additionally sees their own drafts. Permissive policies OR, so this
-- widens the rule above rather than replacing it.
create policy articles_select_own on public.articles
  for select to authenticated
  using (author = (select auth.uid()));

create policy articles_insert_own on public.articles
  for insert to authenticated
  with check (author = (select auth.uid()));

create policy articles_update_own on public.articles
  for update to authenticated
  using (author = (select auth.uid()))
  with check (author = (select auth.uid()));

create policy articles_delete_own on public.articles
  for delete to authenticated
  using (author = (select auth.uid()));

Moderators without a schema change, using the claim idiom:

create policy articles_moderate on public.articles
  for all to authenticated
  using ((auth.jwt() -> 'app_metadata' ->> 'role') = 'moderator')
  with check ((auth.jwt() -> 'app_metadata' ->> 'role') = 'moderator');

3. Team membership

The multi-tenant shape, and the one where people most often write an infinite loop by accident.

create table public.teams (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  created_by uuid not null default auth.uid() references auth.users(id),
  created_at timestamptz not null default now()
);

create table public.team_members (
  team_id   uuid not null references public.teams(id) on delete cascade,
  user_id   uuid not null references auth.users(id) on delete cascade,
  role      text not null default 'member'
            check (role in ('owner', 'admin', 'member')),
  joined_at timestamptz not null default now(),
  primary key (team_id, user_id)
);

create index team_members_user_idx on public.team_members (user_id);

create table public.projects (
  id         bigint generated always as identity primary key,
  team_id    uuid not null references public.teams(id) on delete cascade,
  name       text not null,
  created_at timestamptz not null default now()
);

create index projects_team_idx on public.projects (team_id);

The recursion trap

The obvious policy on team_members is:

-- DO NOT DO THIS
create policy members_read on public.team_members
  for select to authenticated
  using (team_id in (select team_id from public.team_members where user_id = auth.uid()));

Reading team_members invokes the policy, which reads team_members, which invokes the policy:

{"error":{"code":"internal_error",
  "message":"infinite recursion detected in policy for relation \"team_members\"","details":null}}

Every query against the table fails — including the ones that were working before you added it. The fix is one security definer function: it runs as its owner, so the policy does not re-enter, and search_path is pinned so nothing a caller creates can resolve ahead of the objects it means to touch.

create or replace function public.team_role(target uuid) returns text
language sql stable security definer
set search_path = pg_catalog, public
as $$
  select m.role
    from public.team_members m
   where m.team_id = target
     and m.user_id = auth.uid();
$$;

create or replace function public.is_team_member(target uuid) returns boolean
language sql stable security definer
set search_path = pg_catalog, public
as $$
  select exists (
    select 1 from public.team_members m
     where m.team_id = target and m.user_id = auth.uid()
  );
$$;

revoke all on function public.team_role(uuid), public.is_team_member(uuid) from public;
grant execute on function public.team_role(uuid), public.is_team_member(uuid)
  to anon, authenticated;

The policies

alter table public.teams        enable row level security;
alter table public.team_members enable row level security;
alter table public.projects     enable row level security;

-- Teams: members see their teams; admins and owners rename them; owners delete.
create policy teams_select_member on public.teams
  for select to authenticated
  using (public.is_team_member(id));

create policy teams_insert_any on public.teams
  for insert to authenticated
  with check (created_by = (select auth.uid()));

create policy teams_update_admin on public.teams
  for update to authenticated
  using (public.team_role(id) in ('owner', 'admin'))
  with check (public.team_role(id) in ('owner', 'admin'));

create policy teams_delete_owner on public.teams
  for delete to authenticated
  using (public.team_role(id) = 'owner');

-- Membership: everyone in a team sees the roster; admins and owners change it.
create policy members_select on public.team_members
  for select to authenticated
  using (public.is_team_member(team_id));

create policy members_write_admin on public.team_members
  for insert to authenticated
  with check (public.team_role(team_id) in ('owner', 'admin'));

create policy members_update_admin on public.team_members
  for update to authenticated
  using (public.team_role(team_id) in ('owner', 'admin'))
  with check (public.team_role(team_id) in ('owner', 'admin'));

-- Leaving is your own business; removing someone else needs a role.
create policy members_delete on public.team_members
  for delete to authenticated
  using (user_id = (select auth.uid())
         or public.team_role(team_id) in ('owner', 'admin'));

-- Everything owned by a team inherits the team's membership rule.
create policy projects_all_member on public.projects
  for all to authenticated
  using (public.is_team_member(team_id))
  with check (public.is_team_member(team_id));

The chicken and egg

Creating a team leaves you outside it: you are not a member yet, so members_write_admin refuses to let you add yourself. Solve it in the database, where the rule cannot be forgotten by a client.

create or replace function public.add_team_creator() returns trigger
language plpgsql security definer
set search_path = pg_catalog, public
as $$
begin
  insert into public.team_members (team_id, user_id, role)
  values (new.id, new.created_by, 'owner')
  on conflict do nothing;
  return new;
end;
$$;

create trigger teams_add_creator
  after insert on public.teams
  for each row execute function public.add_team_creator();

const { data: team } = await bl.from('teams').insert({ name: 'Acme' }).select().single();
// You are already the owner — the trigger did it inside the same transaction.
await bl.from('projects').insert({ team_id: team.id, name: 'Website' });

4. A chat application

Room members read that room's messages. Nobody else can, including through a realtime subscription.

create table public.rooms (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  is_public  boolean not null default false,
  created_by uuid not null default auth.uid() references auth.users(id),
  created_at timestamptz not null default now()
);

create table public.room_members (
  room_id   uuid not null references public.rooms(id) on delete cascade,
  user_id   uuid not null references auth.users(id) on delete cascade,
  joined_at timestamptz not null default now(),
  primary key (room_id, user_id)
);

create index room_members_user_idx on public.room_members (user_id);

create table public.messages (
  id         bigint generated always as identity primary key,
  room_id    uuid not null references public.rooms(id) on delete cascade,
  author     uuid not null default auth.uid()
             references auth.users(id) on delete cascade,
  body       text not null check (length(body) between 1 and 4000),
  created_at timestamptz not null default now()
);

-- The index the read policy and the message list both need.
create index messages_room_created_idx on public.messages (room_id, created_at desc);

create or replace function public.in_room(target uuid) returns boolean
language sql stable security definer
set search_path = pg_catalog, public
as $$
  select exists (
    select 1 from public.room_members m
     where m.room_id = target and m.user_id = auth.uid()
  );
$$;

revoke all on function public.in_room(uuid) from public;
grant execute on function public.in_room(uuid) to anon, authenticated;

alter table public.rooms        enable row level security;
alter table public.room_members enable row level security;
alter table public.messages     enable row level security;

-- Rooms: public ones are discoverable; private ones only to their members.
create policy rooms_select on public.rooms
  for select to anon, authenticated
  using (is_public or public.in_room(id));

create policy rooms_insert on public.rooms
  for insert to authenticated
  with check (created_by = (select auth.uid()));

-- Membership: members see the roster; you may join a public room yourself and
-- leave any room. Adding someone else to a private room is the creator's job.
create policy room_members_select on public.room_members
  for select to authenticated
  using (public.in_room(room_id));

create policy room_members_join on public.room_members
  for insert to authenticated
  with check (
    user_id = (select auth.uid())
    and exists (select 1 from public.rooms r where r.id = room_id and r.is_public)
  );

create policy room_members_leave on public.room_members
  for delete to authenticated
  using (user_id = (select auth.uid()));

-- Messages: read what your rooms contain, write as yourself into a room you are
-- in, edit and delete only your own.
create policy messages_select_member on public.messages
  for select to authenticated
  using (public.in_room(room_id));

create policy messages_insert_member on public.messages
  for insert to authenticated
  with check (author = (select auth.uid()) and public.in_room(room_id));

create policy messages_update_own on public.messages
  for update to authenticated
  using (author = (select auth.uid()))
  with check (author = (select auth.uid()));

create policy messages_delete_own on public.messages
  for delete to authenticated
  using (author = (select auth.uid()));

Turn on the change feed:

select baselyra.enable_realtime('public.messages');

const channel = bl.channel('public:messages');
channel.on('postgres_changes',
  { event: 'INSERT', schema: 'public', table: 'messages', filter: 'room_id=eq.' + roomId },
  ({ new: row }) => append(row));
await channel.subscribe();

The filter is a convenience, not a boundary. Before any change is delivered, the server re-reads the row as the subscriber's own Postgres role; messages_select_member decides, exactly as it does for a select. Remove the filter and a non-member still receives nothing. A subscription can never show more than a query would.

5. A per-user storage folder

Files are rows in storage.objects, so the same policy machinery applies. The convention is a key prefixed with the owner's id: user-files/<uid>/report.pdf.

Create the bucket with the service key or from the Studio — bucket rows are service-only by design, because storage.buckets has no INSERT, UPDATE or DELETE policy at all:

insert into storage.buckets (id, name, "public", file_size_limit)
values ('user-files', 'user-files', false, 26214400)
on conflict (id) do nothing;

db/project/002_rls.sql ships four permissive default policies on storage.objects: read if the bucket is public, you own the object, or the owner listed you in metadata.shared_with; write only if you own it. Those already stop one user reading another's file. They do not stop a user uploading into another user's folder, because the uploader would still be the owner.

Add a restrictive policy, which ANDs with the defaults and is a no-op for every other bucket:

create policy user_files_own_folder on storage.objects
  as restrictive
  for all to anon, authenticated
  using (
    bucket_id <> 'user-files'
    or split_part(name, '/', 1) = (select auth.uid())::text
  )
  with check (
    bucket_id <> 'user-files'
    or split_part(name, '/', 1) = (select auth.uid())::text
  );

For anon, auth.uid() is NULL, the comparison is NULL, the restrictive policy fails, and the bucket is closed to anonymous callers entirely.

const path = user.id + '/' + file.name;
await bl.storage.from('user-files').upload(path, file);
await bl.storage.from('user-files').list(user.id + '/');
const { data } = await bl.storage.from('user-files').createSignedUrl(path, 3600);

Trying to write into someone else's folder is refused by the policy, and the storage route reports that as a 403 rather than a silent no-op:

{"error":{"code":"forbidden","message":"Not allowed to write this object","details":null}}

The default read policy already honours metadata.shared_with, an array of user ids:

update storage.objects
   set metadata = jsonb_set(metadata, '{shared_with}', '["6c1f5c62-8e4c-4a6b-9a54-1c9d0a0d1a11"]'::jsonb)
 where bucket_id = 'user-files' and name = '3f2a…/report.pdf';

Or hand out a signed URL, which needs no account at all. A signed URL is an HMAC over bucket, key and expiry — treat it as a bearer credential and keep the expiry short.

Replacing the defaults instead

If the shipped rules do not suit you, drop them and write your own — nothing in Baselyra depends on their names:

drop policy objects_select on storage.objects;
drop policy objects_insert on storage.objects;
drop policy objects_update on storage.objects;
drop policy objects_delete on storage.objects;

Testing a policy without a client

Impersonate a role in the SQL editor. Roll it back so the pooled connection is not left holding a role:

begin;
  select set_config('role', 'authenticated', true);
  select set_config('request.jwt.claims',
    '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated","email":"ada@example.com",
      "app_metadata":{"role":"admin"}}',
    true);

  select * from public.notes;              -- exactly what that user would see over REST
  insert into public.notes (title) values ('probe');   -- and what they may write
rollback;

To check the anonymous case, set the role to anon and the claims to the empty string:

begin;
  select set_config('role', 'anon', true);
  select set_config('request.jwt.claims', '', true);
  select * from public.articles;           -- what a logged-out visitor sees
rollback;

Performance

Habit	Why
Index every column a policy filters on	A policy is a `WHERE` clause evaluated for every row the query would otherwise touch. An unindexed `user_id` turns every read into a sequential scan.
Wrap helpers in `(select …)`	`using (user_id = (select auth.uid()))` lets the planner treat the call as a one-time filter instead of re-evaluating it per row.
Prefer a `security definer` helper over a correlated subquery	One indexed lookup per statement beats a subquery per row — and it is also what avoids the recursion trap.
Watch `EXPLAIN` under the right role	A plan taken as the owner does not include the policy. Wrap `explain (analyze, buffers)` in the impersonation block above.

Failure modes

What you see	What it means	What to do
`200` with `[]` from a table you know has rows	RLS is on and no policy admits your role. The commonest of all.	Run the audit query, then the impersonation block
`403 insufficient_privilege`	The grant is missing, not the policy. SQLSTATE `42501`.	`grant select, insert, update, delete on public.t to anon, authenticated`
`500` — `infinite recursion detected in policy for relation …`	A policy on a table reads that same table	Move the lookup into a `security definer` function
`500` — `function auth.is_admin() does not exist`	A policy carried over from an older instance or an import	Replace it with `(auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'`
An insert succeeds but the row is invisible afterwards	The `with check` allowed it and the `using` of the select policy does not	Make the two agree, or add `Prefer: return=representation` and read what came back
A user can move their row to another owner	The update policy has a `using` and no `with check`	Add the `with check`
Everything works in the Studio and nothing works in the app	The Studio bypasses RLS	Test with a real user token, or with the impersonation block
A view returns rows the underlying policy hides	The view is not `security_invoker`	`alter view … set (security_invoker = true)`
Realtime delivers deletes for rows a user cannot read	Deletes are not RLS-checked — the row is gone	Soft-delete, or do not enable realtime on that table

Checklist before you ship

Every table in public has relrowsecurity = true.
Every table with RLS on has at least one policy, or is deliberately inert.
Every update policy has a with check, not just a using.
Every column a policy filters on is indexed.
Every view in public is security_invoker = true.
No policy reads user_metadata — users write that field themselves.
The service key is not in any client bundle, mobile app or public env var.
You have run the impersonation block for at least one policy per table.
Any realtime-enabled table whose rows are confidential uses soft deletes.

Edit this page Report a problem