Row-level security

Decide which rows each user can read and write, with PostgreSQL policies: recipes for owners, public data, teams and guests.

Row-level security is how Plexivo Postgres keeps each user’s data their own. You write policies once, in the database, and they apply to every Data API, Realtime and GraphQL request, whichever app or client sends it.

How policies work#

Every request from your app runs inside the database as one of three roles, and PostgreSQL checks each row against the table’s policies:

anon: a request with the publishable key and nobody signed in.
authenticated: a signed-in user. Their id is auth.uid().
service_role: a request with the secret key, from your servers. It bypasses row-level security.

A table is reachable from your app only once row-level security is on and it has at least one policy. Until then, the API answers as if the table did not exist.

Helpers you can use in policies#

Function	Returns
`auth.uid()`	The signed-in user’s id (`uuid`), or null
`auth.email()`	Their email address
`auth.role()`	`anon`, `authenticated` or `service_role`
`auth.jwt()`	All the claims of their token, as `jsonb`
`auth.is_org_member(org)`	Whether they belong to that organisation
`auth.has_org_role(org, 'admin')`	Whether their role there is at least the one given: `member`, `admin` or `owner`
`auth.org_role(org)`, `auth.org_ids()`	Their role in one organisation; every organisation they belong to

Recipes#

Run policies with the secret key, as in Raw SQL, or with psql through the Plexivo CLI. The console’s SQL editor runs one statement at a time and refuses create policy.

using decides which rows a user can see, update and delete; with check decides which rows they may write.

Each user owns their rows#

Give the owner column the default auth.uid(), so your app never sends it and cannot send someone else’s:

sql

create table notes (
  id bigint generated always as identity primary key,
  owner uuid not null default auth.uid(),
  body text not null
);
alter table notes enable row level security;
create policy notes_owner on notes for all to authenticated
  using (owner = auth.uid()) with check (owner = auth.uid());

Anyone reads, owners write#

sql

create table products (
  id bigint generated always as identity primary key,
  seller uuid not null default auth.uid(),
  name text not null
);
alter table products enable row level security;
create policy products_read on products for select to anon, authenticated using (true);
create policy products_write on products for all to authenticated
  using (seller = auth.uid()) with check (seller = auth.uid());

A team’s shared data#

With organisations from Plexivo Auth, members read a project and admins change it:

sql

create table projects (
  id bigint generated always as identity primary key,
  org_id uuid not null,
  name text not null
);
alter table projects enable row level security;
create policy projects_members_read on projects for select to authenticated
  using (auth.is_org_member(org_id));
create policy projects_admins_write on projects for all to authenticated
  using (auth.has_org_role(org_id, 'admin')) with check (auth.has_org_role(org_id, 'admin'));

Guests read, accounts write#

A guest’s token says is_anonymous. Let guests look around and ask them to sign up before they post:

sql

create table reviews (
  id bigint generated always as identity primary key,
  author uuid not null default auth.uid(),
  body text not null
);
alter table reviews enable row level security;
create policy reviews_read on reviews for select to authenticated using (true);
create policy reviews_accounts_write on reviews for insert to authenticated
  with check (author = auth.uid() and not coalesce((auth.jwt() ->> 'is_anonymous')::boolean, false));

See it work#

Two users, each with their own notes, and one who tries to write a note as the other:

import { createClient } from '@plexivo/client';

const asha = createClient(PLEXIVO_DATA_URL, PLEXIVO_PUBLISHABLE_KEY, { persistSession: false });
const ben = createClient(PLEXIVO_DATA_URL, PLEXIVO_PUBLISHABLE_KEY, { persistSession: false });
const { user: ashaUser } = await asha.auth.signUp({ email: 'asha.rls@example.com', password });
await ben.auth.signUp({ email: 'ben.rls@example.com', password });

await asha.from('notes').create({ body: 'Asha’s secret' });
const bensView = await ben.from('notes');                // [] — Ben sees none of Asha’s notes
console.log(bensView.length);

await ben.from('notes').create({ owner: ashaUser.id, body: 'Pretending to be Asha' });

Members of a team see its projects; everyone else sees nothing:

const team = await asha.orgs.create({ name: 'Acme' });
await asha.from('projects').create({ org_id: team.id, name: 'Launch' });

console.log((await asha.from('projects')).length);   // 1
console.log((await ben.from('projects')).length);    // 0

A guest can read reviews, and cannot post one until they add an email:

const visitor = createClient(PLEXIVO_DATA_URL, PLEXIVO_PUBLISHABLE_KEY, { persistSession: false });
await visitor.auth.signInAnonymously();
await visitor.from('reviews');
await visitor.from('reviews').create({ body: 'Great!' });

dart

import 'package:plexivo_client/plexivo_client.dart';

final ravi = PlexivoClient(dataUrl, publishableKey);
await ravi.signUp(email: email, password: password);
final mine = await ravi.from('notes');     // only Ravi’s notes
print(mine.length);

A write that a policy refuses fails with permission_denied. A read never fails because of a policy: it returns only the rows the user may see.

Templates in the console#

On the database’s Policies tab, choose Templates, pick a Table and a template, and choose Apply template:

Template	What it allows
Owner only	Each user reads and writes only rows whose owner column is their user id.
Public read, owner write	Anyone with the publishable key can read; only the owner can insert, update or delete.
Signed-in read	Any signed-in user can read. Nobody can write through the API.
Locked	Row-level security on with no policy: the API sees nothing.

The first two ask for the Owner column, a uuid column holding the user’s id. A template forces row-level security on the table, so it applies to the table’s owner too, and replaces any template policies applied before.

See where each table stands#

The Policies view lists every table with its policies and marks it Protected (row-level security and a policy), Locked (no policy, so the API sees nothing) or Exposed (row-level security is off: turn it on). Turn on RLS and Drop work right there.

Test a policy#

Choose Tester, pick a Table, paste an Application user id and choose Run test. Plexivo runs a read-only query with that id as auth.uid() and shows how many rows the user can see, with up to five of them. Leave the user empty to test an anonymous caller. The tester matches the API for policies made from the templates. For policies you write yourself, for example to authenticated, also sign in as a test user in your app and check what they see.

For businesses Policies live in the database, not in app code, so a new mobile app, a partner integration or a script with the publishable key gets the same rules on the first day. Review them in one place, on the Policies tab.

Next steps#

Data APIRead and write the rows your policies allow. Plexivo AuthUsers, sessions and organisations.

Updated 28 October 2026