Skip to content
AnvilBase

Row Level Security

RLS is AnvilBase’s primary data-access control. A policy is a SQL predicate Postgres evaluates for every row of a query; rows that don’t satisfy it are invisible, as if they didn’t exist. AnvilBase enables RLS on every table you create and ships a test sandbox so a policy bug never becomes a silent data breach.

The model

  • Enabled by default — on every DDL path. Every project database carries an event trigger (anvilbase_rls_default) that runs ENABLE ROW LEVEL SECURITY on every new table created in the public schema, no matter how it was created: the console table editor, db push migrations, the SQL editor / exec_sql RPC, or an ORM connected straight to Postgres. All three table-creating statements are covered — CREATE TABLE, CREATE TABLE AS, and SELECT INTO. With no policies, the table denies all access to anon/authenticated — fail-closed. You open access by adding policies.

    • Existing tables are not retroactively altered — only tables created after the trigger is installed are affected. Check rls status for the per-table flags and enable older tables explicitly.
    • Platform bookkeeping tables prefixed _anvilbase_ (e.g. _anvilbase_migrations) are skipped by the trigger.
    • service_role carries BYPASSRLS, so trusted server code keeps full access the moment a table is created.
    • The escape hatch is explicit: ALTER TABLE <table> DISABLE ROW LEVEL SECURITY (or anvilbase rls disable) — and the disabled state is surfaced by anvilbase rls status.
    • Self-hosting note: new deployments get the trigger automatically (it is pre-installed into template1, so every project database inherits it). Deployments created before this feature must apply the one-time retrofit described in docker/postgres/owned/init-anvilbase.owned.sh (run as a superuser: once against template1, once against each existing platform_* database).

  • Context injection. Before running your query, the control plane sets the session context from the request’s credential:

    SET LOCAL ROLE authenticated;                       -- or anon
    SET LOCAL app.current_user_id = '<user-uuid>';
    SET LOCAL request.jwt.claims = '{...}';

  • Policies read that context to decide what’s visible:

    using ( author_id = current_setting('app.current_user_id')::uuid )

  • service_role bypasses RLS entirely — it’s for trusted server code only.

Anatomy of a policy

create policy "<name>" on <table>
  for <SELECT|INSERT|UPDATE|DELETE|ALL>
  to <role(s)>                      -- e.g. authenticated; empty ⇒ PUBLIC
  using (<read/visibility predicate>)         -- which rows are visible / updatable / deletable
  with check (<write predicate>);             -- which rows may be inserted / updated to
  • USING filters which existing rows the command can see/affect (SELECT/UPDATE/ DELETE).
  • WITH CHECK validates the new row state on INSERT/UPDATE — it stops a user writing a row they wouldn’t be allowed to read.
  • A policy is PERMISSIVE by default (any matching policy grants access); mark it RESTRICTIVE to require all restrictive policies to pass.

Create policies

CLI

Terminal window
# Read your own rows
anvilbase rls policy create --project <id> --table todos \
  --name owner_select --command SELECT \
  --using "user_id = current_setting('app.current_user_id')::uuid"


# Only insert rows you own
anvilbase rls policy create --project <id> --table todos \
  --name owner_insert --command INSERT \
  --with-check "user_id = current_setting('app.current_user_id')::uuid"

Management API

Terminal window
curl -X POST http://localhost:39001/api/v1/projects/<id>/rls/tables/todos/policies \
  -H "Authorization: Bearer $ANVILBASE_TOKEN" -H "Content-Type: application/json" \
  -d '{
    "name": "owner_all",
    "command": "ALL",
    "using_expression": "user_id = current_setting('\''app.current_user_id'\'')::uuid",
    "with_check_expression": "user_id = current_setting('\''app.current_user_id'\'')::uuid",
    "roles": ["authenticated"],
    "permissive": true
  }'

Valid commands: ALL, SELECT, INSERT, UPDATE, DELETE. Valid roles: anon, authenticated, service_role. At least one of using_expression / with_check_expression is required. The response echoes the generated SQL.

Console

Project → RLS opens the visual policy builder with templates, a free-form expression editor, and the test sandbox below.

Policy templates (common patterns)

User owns the row

using ( user_id = current_setting('app.current_user_id')::uuid )

Public read, owner write (two policies)

-- SELECT to anon + authenticated
using ( true )
-- INSERT/UPDATE/DELETE to authenticated, owner only
using ( user_id = current_setting('app.current_user_id')::uuid )
with check ( user_id = current_setting('app.current_user_id')::uuid )

Organization / team membership

using ( org_id in (
  select org_id from memberships
  where user_id = current_setting('app.current_user_id')::uuid
) )

Role from JWT claims

using ( (current_setting('request.jwt.claims', true)::jsonb ->> 'role') = 'admin' )

Test before you trust — the sandbox

The RLS test endpoint runs a real SELECT inside a transaction with the role/user context set, reports what would be visible, and always rolls back (it never touches data):

Terminal window
anvilbase rls test --project <id> --table todos --role authenticated --user <user_id>
Terminal window
curl -X POST http://localhost:39001/api/v1/projects/<id>/rls/test \
  -H "Authorization: Bearer $ANVILBASE_TOKEN" -H "Content-Type: application/json" \
  -d '{"table":"todos","role":"authenticated","user_id":"<uuid>"}'
# → { accessible_rows, total_rows, rows, role, user_id }

accessible_rows vs total_rows tells you instantly whether a policy is too loose (accessible == total when it shouldn’t be) or too tight (accessible == 0). The console runs the same check interactively.

Enable / disable / inspect

Terminal window
anvilbase rls status   --project <id>                 # per-table enabled/forced flags
anvilbase rls enable   --project <id> --table todos
anvilbase rls disable  --project <id> --table todos
anvilbase rls policies --project <id> --table todos   # list policies
anvilbase rls policy drop --project <id> --table todos --name owner_select

Best practices

  • Always write a policy right after creating a table — until you do, the table is locked for anon/authenticated (correct, but not useful).
  • Test every policy with the sandbox, for each role you support, before relying on it.
  • Default to restrictive thinking: grant the minimum, widen deliberately.
  • Put WITH CHECK on every INSERT/UPDATE policy so users can’t write rows they couldn’t read.
  • Keep service_role server-side only; it’s the RLS bypass.
  • Capture policies as migrations so they promote across environments with your schema.

Next: API Keys & Scopes.