Skip to content
PerSQL PerSQL PerSQL

Row-Level Security

A policy is a SQL boolean expression attached to a table. When a policy is enabled, every /v1 query that references the table is rewritten so the predicate is AND-ed onto the read. Tokens see only the rows the policy admits.

WITH "orders" AS (SELECT * FROM "orders" WHERE user_id = ?)
SELECT * FROM orders WHERE total > 100

The CTE shadows the base table — every reference in the user’s SQL resolves to the filtered version, including subqueries and joins.

Tokens carry claims

Section titled “Tokens carry claims”

Policies wouldn’t be useful without per-call context. API tokens can carry an arbitrary JSON object of claims that bind into the predicate. Each :auth_<key> reference in the expression is replaced with the corresponding value at request time.

Terminal window
curl -X POST https://api.persql.com/api/namespaces/acme/tokens \
  -H "Cookie: …" \
  -d '{
    "name": "user-42",
    "role": "readonly",
    "claims": { "userId": "u_42", "tier": "pro" }
  }'

Then a policy on orders like:

user_id = :auth_userId

routes through the token’s userId claim. A query made with that token only sees rows where user_id = 'u_42'. A different token with userId = "u_99" sees a different slice.

Claims are user-defined — there’s no schema. Mint a token per user (or per session, if you prefer).

Creating a policy

Section titled “Creating a policy”

In the console: Database → Policies → New policy. Pick a table, write the expression, save.

REST equivalent:

POST /api/namespaces/:ns/databases/:db/policies
{
  "name": "orders-by-customer",
  "tableName": "orders",
  "expression": "user_id = :auth_userId",
  "enabled": true
}

Manage permission required.

Rules

Section titled “Rules”
  • SELECT-only. CTEs aren’t writeable in SQLite, so INSERT, UPDATE, and DELETE bypass policies. Lock writes with token role (readonly) or table scope.
  • Single line, no ;. The expression is spliced into a CTE; a semicolon would terminate the statement.
  • Unknown claims become NULL. A reference to :auth_userId on a token that doesn’t carry userId substitutes NULL. The predicate becomes ... = NULL, which is never true — fail closed by default.
  • Multiple policies on one table AND together implicitly: any row that fails any enabled policy is excluded.
  • Best-effort table detection. We use the same regex as table scoping. Aliases and CTEs that don’t match the reference pattern may not pick up the policy — verify with the Insights tab if you rely on filtering for compliance.

Examples

Section titled “Examples”

Multi-tenant SaaS. Every user sees only their own orders:

user_id = :auth_userId

Tenant + role override. Admins see everything, users see their own:

:auth_role = 'admin' OR user_id = :auth_userId

Soft-delete hiding. Read-only views that hide deleted rows:

deleted_at IS NULL

Tier gating. Free-tier rows hidden from pro-tier tokens:

:auth_tier = 'free' OR is_premium = 1

See also

Section titled “See also”