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

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"}' | 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"}' | 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.