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. No route handler checks ownership, no query is filtered for security reasons in the server process. Postgres decides, every time, for every path — REST, storage, realtime and the client SDK all end up 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":"…"}', 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.

Which role you get:

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 (`/admin/v1`)	`service_role`	Bypassed

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.

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. Validated as a uuid before the cast, so a malformed `sub` is `NULL` rather than an error.
`auth.role()`	`text`	`'anon'`, `'authenticated'` or `'service_role'`.
`auth.email()`	`text`	The `email` claim, `NULL` if absent.
`auth.jwt()`	`jsonb`	The whole claim set, including `app_metadata` and `user_metadata`.

auth.is_admin() no longer exists. It was removed along with the auth.users.is_admin column, because operators are not application users (see auth.md). 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');

Set it with the service key:

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

user_metadata is different: the user can change it themselves through PUT /auth/v1/user. Never make an authorisation decision from user_metadata. It is a display name and a theme preference, not a claim.

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, both 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:

A new table in public with RLS off is readable, writable and deletable by anyone holding your anon key — which is a public string in your frontend.

Not "readable by logged-in users". By anyone, on the internet, with curl.

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;

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.

Two ways to accidentally bypass your own policies

Views. In Postgres 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). A view over an RLS-protected table therefore returns every row to whoever can select from the view — and /rest/v1 exposes views. Always:

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

security_invoker = true makes the view run as the caller, so the underlying policies apply. Retrofit an existing view with 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 through /rest/v1/rpc/. Default to security invoker (the default) 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).

Multiple permissive policies for the same command OR together: adding one widens access. To narrow it, add a policy as restrictive, which ANDs.

Two habits worth adopting from the start:

-- 1. Index every column a policy filters on. A policy is a WHERE clause that
--    runs on every row of every query.
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 (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

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()));

Add 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');

A moderator's token carries the claim from the moment it is issued, so promoting someone takes effect on their next sign-in or token refresh.

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. Postgres raises infinite recursion detected in policy for relation "team_members" and every query against the table fails.

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 room creator's.
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.

One exception, and it is worth knowing: a DELETE event is not RLS-checked, because the row no longer exists to be re-read. Deletes go to every subscriber of that table whose filter matches, carrying the old row. If a table's contents are confidential, soft-delete instead (update … set deleted_at = now()), which goes through the normal check. See realtime.md.

5. Storage: one folder per user

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):

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);

Sharing one file with one person

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

update storage.objects
   set metadata = jsonb_set(metadata, '{shared_with}', '["<uuid>"]'::jsonb)
 where bucket_id = 'user-files' and name = '<uid>/report.pdf';

Or hand out a signed URL, which needs no account at all:

const { data } = await bl.storage.from('user-files').createSignedUrl(path, 60 * 15);

A signed URL is an HMAC over bucket, key and expiry. It is the authorisation for that one object until it expires, so treat it as a bearer credential.

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;

Do this in one migration together with your replacements. Between the drop and the create, every bucket denies everything, including your public ones.

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"}',
    true);

  select * from public.notes;    -- exactly what that user would see over REST
rollback;

The SQL editor itself runs as service_role, which bypasses RLS — so a table that looks fine in the Studio grid may still be denying every request from your app. The block above is how you check what your users actually see.

Checklist before you ship

Every table in public has relrowsecurity = true (run the audit query).
Every table with RLS on has at least one policy, or it 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 above for at least one policy.
Any realtime-enabled table whose rows are confidential uses soft deletes.

Navigation Menu

Repository navigation

Row level security

The model

Helper functions

What happens if you leave RLS off

Audit what you have

Two ways to accidentally bypass your own policies

Writing policies

1. Private per-user data

2. Public read, owner write

3. Team membership

The recursion trap

The policies

The chicken-and-egg

4. A chat application

5. Storage: one folder per user

Sharing one file with one person

Replacing the defaults instead

Testing a policy without a client

Checklist before you ship

FilesExpand file tree

row-level-security.md

Latest commit

History

row-level-security.md

File metadata and controls

Row level security

The model

Helper functions

What happens if you leave RLS off

Audit what you have

Two ways to accidentally bypass your own policies

Writing policies

1. Private per-user data

2. Public read, owner write

3. Team membership

The recursion trap

The policies

The chicken-and-egg

4. A chat application

5. Storage: one folder per user

Sharing one file with one person

Replacing the defaults instead

Testing a policy without a client

Checklist before you ship