Step 12: Configure Row-Level Security

Phase: implement

Context

You have project_id, service_key, and tables_created. Now declare which tables are reachable via the REST API and what access rules apply.

What to do

Tables are dark by default

A table you just created with CREATE TABLE is unreachable from /rest/v1/* until you declare it in the expose manifest. This eliminates the "agent created a table, forgot to set RLS, data leaked" footgun. You must explicitly list every table you want to expose.

Available RLS policies

PolicyWho can readWho can writeUse when
user_owns_rows Only the row owner (matched by owner_column) Only the row owner Personal data (my tasks, my profile, my scores). Requires owner_column.
public_read_authenticated_write Everyone (anon) Any authenticated user (any row) Shared content with sign-in (comments, public bios). NOT row-scoped writes.
public_read_write_UNRESTRICTED Everyone (anon) Everyone (anon) Open collaboration without auth (voting, anonymous shared lists). Requires i_understand_this_is_unrestricted: true.
custom Escape hatch — provide your own custom_sql with CREATE POLICY statements. Anything the built-ins can't express (e.g. anon read + owner-only writes).

One policy per table. The manifest forbids stacking multiple policies on the same table. If you need "anon reads + auth user writes", use public_read_authenticated_write. If you need anything more nuanced, use policy: "custom" with hand-rolled SQL.

Preferred: declare in unified deploy

Put the manifest under database.expose in your p.apply() call (Step 15). The gateway validates it against your migration SQL and applies it atomically with the rest of the release — schema, policies, exposure, and PostgREST reload all land together.

const p = await r.project(env.PROJECT_ID);
await p.apply({
  database: {
    expose: {
      version: "1",
      tables: [
        { name: "todos",      expose: true, policy: "user_owns_rows", owner_column: "user_id" },
        { name: "categories", expose: true, policy: "public_read_authenticated_write" },
        { name: "audit",      expose: false }
      ]
    }
  }
});

Imperative escape hatch — apply manifest standalone

For ad-hoc changes outside a deploy, POST the same manifest directly:

POST https://api.run402.com/projects/v1/admin/{project_id}/expose
Content-Type: application/json
Authorization: Bearer {service_key}

{
  "version": "1",
  "tables": [
    { "name": "todos",      "expose": true, "policy": "user_owns_rows", "owner_column": "user_id" },
    { "name": "categories", "expose": true, "policy": "public_read_authenticated_write" }
  ]
}

The MCP equivalents are validate_manifest (non-mutating check) and apply_expose (write). get_expose returns the live state.

The manifest is convergent: applying the same manifest twice is a no-op. Items removed between applies have their policies, grants, triggers, and views dropped. Always include everything you want exposed in each call — anything missing gets dark again.

How user_owns_rows works

The policy matches rows where auth.uid() = owner_column. The owner_column must be a uuid column on the table.

Set force_owner_on_insert: true to create an idempotent trigger that fills the owner column with auth.uid() when the client omits it:

{ "name": "todos", "expose": true, "policy": "user_owns_rows",
  "owner_column": "user_id", "force_owner_on_insert": true }

The trigger fires BEFORE INSERT and only fills omitted / explicit-null owner values. Authenticated clients passing a non-null owner are still subject to the WITH CHECK (owner_column = auth.uid()) rule, so they can't impersonate.

Views and RPCs

Views are dark too — list them in views[] with expose: true:

{
  "views": [
    { "name": "leaderboard", "base": "items", "select": ["user_id", "score"], "expose": true }
  ]
}

Views run with security_invoker=true — they inherit the base table's RLS.

Postgres functions / RPCs need an explicit grant — they're not reachable via PostgREST otherwise:

{
  "rpcs": [
    { "name": "compute_streak", "signature": "(user_id uuid)", "grant_to": ["authenticated"] }
  ]
}

Decision guide

  • App has auth? Use user_owns_rows for personal data, public_read_authenticated_write for shared data anyone signed-in can edit.
  • App has no auth? Use public_read_write_UNRESTRICTED (with the i_understand_this_is_unrestricted ack) for everything. Anyone with the link can read and write.
  • Anon read + owner-only writes? Use policy: "custom" with custom_sql — the built-ins can't express this in a single template.

Important notes

  • The service_key bypasses RLS. Use only for admin setup; never in frontend code.
  • The anon_key respects RLS — this is what goes in the frontend.
  • If the manifest references a table that doesn't exist in the schema, the deploy is rejected with a structured errors array.
  • Use one expose manifest for the reachable tables, views, and RPCs your app needs.

What to tell the user

"I've set up the access rules for your app. [Everyone can see everything / Each person can only see their own stuff / Some things are shared, some are private]."

Expected output

  • rls_configured — Map of table → policy applied: 
    {
  "todos":      "user_owns_rows",
  "categories": "public_read_authenticated_write"
}

Memory directive