Build

Row policies

A policy says who may read and who may write a table. The secret key always has full access. Policies decide what the publishable key and signed-in users can do.

Levels

LevelReadWrite
secretsecret key only (the default)secret key only (the default)
publicanyone with the publishable key, signed in or notnot allowed for writes
authenticatedany signed-in user of the appany signed-in user of the app
ownera signed-in user sees only their own rowsa signed-in user inserts, updates, and deletes only their own rows

Read and write are set separately, so a table can be public to read and owner to write (a public profile page that only its owner edits).

Owner tables

Setting owner adds an owner_id uuid column that references the app's users and defaults to the signed-in user. Clients never send it.

berth tables create --app "$APP" notes title:text:notnull --read owner --write owner
berth tables show --app "$APP" notes

The same with curl:

curl -sS -X POST https://api.atberth.com/v1/apps/$APP/tables \
  -H "Authorization: Bearer $SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"name":"profiles","columns":[{"name":"display_name","type":"text"}],"policy":{"read":"public","write":"owner"}}'

Try it with two users

Sign up Ada and Grace:

ACCESS_TOKEN=$(curl -sS -X POST https://api.atberth.com/v1/apps/$APP/auth/signup \
  -H "Authorization: Bearer $PUBLISHABLE_KEY" \
  -H "Content-Type: application/json" \
  -d '{"email":"ada@example.com","password":"correct horse battery 1"}' | jq -er .access_token)
GRACE_TOKEN=$(curl -sS -X POST https://api.atberth.com/v1/apps/$APP/auth/signup \
  -H "Authorization: Bearer $PUBLISHABLE_KEY" \
  -H "Content-Type: application/json" \
  -d '{"email":"grace@example.com","password":"another long password 2"}' | jq -er .access_token)

Ada writes a note. owner_id is filled in from her token:

ROW_ID=$(curl -sS -X POST https://api.atberth.com/v1/apps/$APP/tables/notes/rows \
  -H "apikey: $PUBLISHABLE_KEY" \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"title":"Ada only"}' | jq -er .row.id)

Ada sees one row. Grace sees none:

curl -sS https://api.atberth.com/v1/apps/$APP/tables/notes/rows \
  -H "Authorization: Bearer $ACCESS_TOKEN" | jq '.rows | length'
curl -sS https://api.atberth.com/v1/apps/$APP/tables/notes/rows \
  -H "Authorization: Bearer $GRACE_TOKEN" | jq '.rows | length'

Grace cannot read, change, or delete Ada's row even with its id. To her it does not exist:

curl -sS -X PATCH https://api.atberth.com/v1/apps/$APP/tables/notes/rows/$ROW_ID \
  -H "Authorization: Bearer $GRACE_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"title":"Grace was here"}'

A publishable key without a user is refused, because owner needs a signed-in user:

curl -sS https://api.atberth.com/v1/apps/$APP/tables/notes/rows \
  -H "Authorization: Bearer $PUBLISHABLE_KEY"

How it is enforced

Berth does not only filter in the API. owner turns on Postgres row level security for the table, with policies equivalent to:

alter table notes enable row level security;
create policy owner_read on notes for select using (owner_id = auth.uid());
create policy owner_write on notes for all using (owner_id = auth.uid()) with check (owner_id = auth.uid());

Requests from signed-in users run as the app's Postgres role with the user id set for the transaction, so filters, cursors, counts, and the realtime stream all see only that user's rows. The secret key bypasses policies, which is why it stays on your server.

You can use auth.uid() as a column default too: berth columns add --app "$APP" notes author:uuid --default 'auth.uid()'.

Changing a policy

Policies can change at any time. Make notes readable by everyone while writes stay owner only:

berth tables policy --app "$APP" notes --read public --write owner
curl -sS https://api.atberth.com/v1/apps/$APP/tables/notes/rows \
  -H "Authorization: Bearer $PUBLISHABLE_KEY" | jq '.rows | length'
curl -sS -X PATCH https://api.atberth.com/v1/apps/$APP/tables/notes \
  -H "Authorization: Bearer $SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"policy":{"read":"owner","write":"owner"}}'

Buckets use the same levels. See Storage.

Custom rules

When the four levels are not enough, give read or write a SQL rule. The rule is one boolean expression over the row, and Postgres checks it for every row, just like the built-in levels. This is how you build teams, organizations and shared workspaces.

curl -sS -X PATCH https://api.atberth.com/v1/apps/$APP/tables/documents \
  -H "Authorization: Bearer $SECRET_KEY" -H "Content-Type: application/json" \
  -d '{"policy":{
        "read":  {"sql": "org_id = _berth.claim('"'"'org_id'"'"')"},
        "write": {"sql": "org_id = _berth.claim('"'"'org_id'"'"') AND _berth.has_role('"'"'editor'"'"')"}}}'

A rule can use the row's columns, subqueries on other tables, and these helpers:

HelperReturns
_berth.uid()the signed-in user's id, or null
_berth.role()user, anon (publishable key only) or service
_berth.claim('key')one of the user's claims, as text
_berth.has_role('name')true if the claims list that role in roles (or role)
_berth.claims()all of the user's claims, as jsonb

A membership table works too: {"sql": "EXISTS (SELECT 1 FROM members m WHERE m.org_id = documents.org_id AND m.user_id = _berth.uid())"}. If members has its own policy, give it one that lets a user see their own memberships. A rule is checked when you save it, and a bad column or a typo is refused. Rules can't contain semicolons or comments. The secret key still sees and changes everything, and realtime streams only send a user the rows their rule allows.

Claims: roles and organizations

Claims are facts about a user that only your server can set: their roles, organization, plan or team. Policies and functions read them with _berth.claim(). A user can see their own claims (GET /auth/me) but never change them.

curl -sS -X PATCH https://api.atberth.com/v1/apps/$APP/auth/users/$USER_ID \
  -H "Authorization: Bearer $SECRET_KEY" -H "Content-Type: application/json" \
  -d '{"claims":{"org_id":"acme","roles":["editor"]}}'

Changes apply on the user's next request, with no new sign-in. Claims can also be set when you create or import a user. Up to 8 KB per user.

Functions users can call

Postgres functions are callable with the secret key by default. To let your app's users call one, allow it. Their call then runs as them: _berth.uid() and _berth.claim() work inside it, and every table's policy still applies.

berth sql --app "$APP" "create function my_open_tasks() returns bigint language sql stable as \$\$ select count(*) from tasks where not done \$\$"
curl -sS -X PUT https://api.atberth.com/v1/apps/$APP/rpc/my_open_tasks/access \
  -H "Authorization: Bearer $SECRET_KEY" -H "Content-Type: application/json" -d '{"allow":"user"}'
curl -sS -X POST https://api.atberth.com/v1/apps/$APP/rpc/my_open_tasks \
  -H "apikey: $PUBLISHABLE_KEY" -H "Authorization: Bearer $USER_TOKEN" -d '{}'

allow is secret (the default), user (any signed-in user) or anon (anyone with the publishable key). GET /v1/apps/$APP/rpc lists every function and who may call it. A security definer function runs with the app's own rights, so only allow those if they check _berth.uid() themselves.