Row-level security

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#

Write your own policy#

Run your policies over a direct connection, such as psql through the Plexivo CLI, your migration tool, or a migration with the secret key (see SQL editor, “For everything else”). The console’s SQL editor runs one statement at a time and refuses create policy. For example, notes that each user owns:

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 "Each user owns their notes" on notes
  for all to authenticated
  using (owner = auth.uid())
  with check (owner = auth.uid());

using decides which rows a user can see, update and delete; with check decides which rows they may write. Because owner defaults to auth.uid(), your app never sends it.

A team’s shared data#

With organisations from Plexivo Auth, members read a project’s rows and admins change them:

alter table projects enable row level security;

create policy "Members read" on projects
  for select to authenticated
  using (auth.is_org_member(org_id));

create policy "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'));

Public catalogue, private orders#

alter table products enable row level security;
create policy "Anyone can read products" on products
  for select to anon, authenticated using (true);

Templates in the console#

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

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.

Next steps#