---
test_id: jwt-rls-enforcement
run_date: 2026-06-15
run_time_utc: "18:18:54Z"
runner: tester
sandbox_or_env: "local — darwin 25.5.0, docker 29.1.3, cargo 1.92.0, pgpaw v0.2.4"
duration_secs: 323
result: pass
exit_reason: ""
related_commit: 254d5d82fd6013599661941df1e627a5f29f9c73
---

## Summary

All 24 Steps passed. RLS tenant isolation held perfectly across every assertion: Tenant A (org_id=1) saw only ids 101/102, Tenant B (org_id=2) saw only ids 201/202/203, with zero cross-tenant leakage. Status codes matched the spec exactly (303, 200, 401 x2, 403). Public/private classification was correct for all query types including JOIN and live streams. The only deviation was a second inadvertent launch of the pgpaw binary that conflicted with the first and was logged under Deviations.

## Steps Executed

### Step 1: Provision a fresh logical-replication Postgres

**Action run**: `docker run -d --name pgpaw-rls-pg -e POSTGRES_PASSWORD=postgres -p 55432:5432 postgres:16 -c wal_level=logical`

**Output excerpt**:
```
e245d4217861766af98e4872225e3605776043d685e9e5b5a5bd91de0803db7b
```
Container immediately showed `Up 3 seconds` in `docker ps`. Port `0.0.0.0:55432->5432` bound.

**Primary observation**: 64-hex container id printed, `docker ps` shows `Up`. MATCHES.

**Awareness check**: No other postgres container on 55432. No port-already-allocated error. Image was already pulled (no slow-pull delay). Nothing unusual.

**Judgment**: pass

---

### Step 2: Confirm wal_level is logical

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -tAc "SHOW wal_level"`

**Output excerpt**:
```
logical
```

**Primary observation**: Output is exactly `logical`. MATCHES.

**Awareness check**: No startup race (no FATAL message). Value confirmed correct on first attempt. Nothing unusual.

**Judgment**: pass

---

### Step 3: Create the orgs table

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "CREATE TABLE orgs (id int PRIMARY KEY, name text)"`

**Output excerpt**:
```
CREATE TABLE
```

**Primary observation**: Output is `CREATE TABLE`. MATCHES.

**Awareness check**: No NOTICE about existing relation. Container is fresh. Nothing unusual.

**Judgment**: pass

---

### Step 4: Seed the orgs rows

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "INSERT INTO orgs VALUES (1,'Acme'),(2,'Globex')"`

**Output excerpt**:
```
INSERT 0 2
```

**Primary observation**: Output is `INSERT 0 2`. MATCHES.

**Awareness check**: Row count exactly 2. Seed complete. Nothing unusual.

**Judgment**: pass

---

### Step 5: Create the documents table

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "CREATE TABLE documents (id int PRIMARY KEY, org_id int REFERENCES orgs(id), title text)"`

**Output excerpt**:
```
CREATE TABLE
```

**Primary observation**: Output is `CREATE TABLE`. MATCHES.

**Awareness check**: FK to orgs succeeded (no missing-relation error). Nothing unusual.

**Judgment**: pass

---

### Step 6: Seed the documents rows (both tenants)

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "INSERT INTO documents VALUES (101,1,'A-doc-one'),(102,1,'A-doc-two'),(201,2,'B-doc-one'),(202,2,'B-doc-two'),(203,2,'B-doc-three')"`

**Output excerpt**:
```
INSERT 0 5
```

**Primary observation**: Output is `INSERT 0 5`. MATCHES.

**Awareness check**: Row count exactly 5 (2 for org 1, 3 for org 2). Cross-tenant seed is complete. Nothing unusual.

**Judgment**: pass

---

### Step 7: Create the non-superuser login role

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "CREATE ROLE member LOGIN"`

**Output excerpt**:
```
CREATE ROLE
```

**Primary observation**: Output is `CREATE ROLE`. MATCHES.

**Awareness check**: No "role already exists" notice. `\du member` shows empty attribute field — not a superuser, no BYPASSRLS. Fresh container confirmed. Nothing unusual.

**Judgment**: pass

---

### Step 8: Grant public read on orgs

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "GRANT SELECT ON orgs TO PUBLIC"`

**Output excerpt**:
```
GRANT
```

**Primary observation**: Output is `GRANT`. MATCHES.

**Awareness check**: Grant succeeded. Nothing unusual.

**Judgment**: pass

---

### Step 9: Grant member read on documents

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "GRANT SELECT ON documents TO member"`

**Output excerpt**:
```
GRANT
```

**Primary observation**: Output is `GRANT`. MATCHES.

**Awareness check**: Grant succeeded without error. Nothing unusual.

**Judgment**: pass

---

### Step 10: Enable RLS on documents

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "ALTER TABLE documents ENABLE ROW LEVEL SECURITY"`

**Output excerpt**:
```
ALTER TABLE
```

**Primary observation**: Output is `ALTER TABLE`. MATCHES.

**Awareness check**: RLS enabled. Nothing unusual.

**Judgment**: pass

---

### Step 11: Force RLS on documents

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "ALTER TABLE documents FORCE ROW LEVEL SECURITY"`

**Output excerpt**:
```
ALTER TABLE
```

**Primary observation**: Output is `ALTER TABLE`. MATCHES.

**Awareness check**: FORCE RLS applied with no error. Nothing unusual.

**Judgment**: pass

---

### Step 12: Create the tenant-scoping policy

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "CREATE POLICY documents_by_org ON documents FOR SELECT TO member USING ( org_id = ((select current_setting('request.jwt.claims', true))::json->>'org_id')::int )"`

**Output excerpt**:
```
CREATE POLICY
```

**Primary observation**: Output is `CREATE POLICY`. MATCHES.

**Awareness check**: Policy created using `request.jwt.claims` inline. No syntax error. Nothing unusual.

**Judgment**: pass

---

### Step 13: Create the publication PgPaw replicates

**Action run**: `docker exec pgpaw-rls-pg psql -U postgres -c "CREATE PUBLICATION cache_server_pub FOR ALL TABLES"`

**Output excerpt**:
```
CREATE PUBLICATION
```

**Primary observation**: Output is `CREATE PUBLICATION`. MATCHES.

**Awareness check**: Publication name exactly `cache_server_pub`. Created FOR ALL TABLES. Nothing unusual.

**Judgment**: pass

---

### Step 14: Launch PgPaw against the temp Postgres (background)

**Action run**: `cargo run --release -- serve --pg-host 127.0.0.1 --pg-port 55432 --pg-user postgres --pg-password postgres --pg-database postgres --data-dir /tmp/pgpaw-rls-data --port 8080 --jwt-secret pgpaw-test-secret-please-change` (run via `run_in_background`, output teed to `/tmp/pgpaw-test.log`)

**Output excerpt** (from `/tmp/pgpaw-test.log` lines 49-51):
```
   Compiling pgpaw v0.2.4 (/Users/tiendang/Projects/a-zero/PgPaw)
    Finished `release` profile [optimized] target(s) in 16.25s
     Running `target/release/pgpaw serve ...`
```
Process PID 25839 confirmed running: `ps -p 25839` shows `target/release/pgpaw serve ...`. Port 8080 bound: `lsof -i :8080` shows `pgpaw 25839 ... TCP localhost:http-alt (LISTEN)`. `/tmp/pgpaw-rls-data` directory created (contains PG data layout: base/, pg_wal/, PG_VERSION, pg_hba.conf, etc.).

**Primary observation**: Process running, data-dir created. MATCHES.

**Awareness check**: No "JWT verification is not configured" or key-parse error in log. Build completed in 16.25s. Port 8080 bound to `localhost:http-alt` (127.0.0.1:8080 — correct). A second inadvertent nohup launch (deviation) produced `postmaster failed to start: exit status: 1` because the data-dir was already locked by PID 25839; that error belongs to the deviation launch, not the primary. Nothing unusual for the primary launch.

**Judgment**: pass

---

### Step 15: Poll until replica catches up

**Action run**: `curl -s http://127.0.0.1:8080/healthz`

**Output excerpt**:
```
{"status":"ok","watermark":22455888}
```

**Primary observation**: `status` is `ok`, `watermark` is `22455888` (non-zero). MATCHES. First poll succeeded — no repeated polling needed.

**Awareness check**: Watermark 22455888 is substantial, indicating all seed data is replicated. No `halted` state. No `503` responses. Nothing unusual.

**Judgment**: pass

---

### Step 16: Public query with NO Authorization header

**Action run**: `curl -s -i -X POST http://127.0.0.1:8080/query -H 'content-type: application/json' -d '{"sql":"select id, name from orgs order by id"}'`

**Output excerpt**:
```
HTTP/1.1 303 See Other
content-length: 0
cache-control: no-store
location: /q/6d053489cf884929/0
date: Mon, 15 Jun 2026 18:22:21 GMT
```

**Primary observation**: Status `303 See Other`. Location `/q/6d053489cf884929/0` matches shape `/q/{hash}/{version}`. MATCHES.

**Awareness check**: `Cache-Control: no-store` on the redirect itself (correct — long-lived cache lives on the `/q/...` GET). Location value captured: `/q/6d053489cf884929/0`. No 401 (orgs correctly classified public). Nothing unusual.

**Judgment**: pass

---

### Step 17: Follow the public snapshot URL

**Action run**: `curl -s -i http://127.0.0.1:8080/q/6d053489cf884929/0`

**Output excerpt**:
```
HTTP/1.1 200 OK
cache-control: public, max-age=259200
etag: 6d053489cf884929:0
content-type: application/json

[{"id": 1, "name": "Acme"}, {"id": 2, "name": "Globex"}]
```

**Primary observation**: Status `200 OK`. `Cache-Control: public, max-age=259200` present. `ETag` present. Body contains both orgs (Acme and Globex). MATCHES.

**Awareness check**: Cache-Control is genuinely CDN-cacheable (`public, max-age=259200`). ETag format `{hash}:{version}`. Both orgs present. Content-type `application/json`. Nothing unusual.

**Judgment**: pass

---

### Step 18: SELECT * over the RLS table with Tenant A's token

**Action run**: `curl -s -i -X POST http://127.0.0.1:8080/query -H 'content-type: application/json' -H 'authorization: Bearer eyJhbGci...[TOKEN_A]' -d '{"sql":"select * from documents order by id"}'`

**Output excerpt**:
```
HTTP/1.1 200 OK
cache-control: private, no-store
content-type: application/json

[{"id": 101, "title": "A-doc-one", "org_id": 1}, {"id": 102, "title": "A-doc-two", "org_id": 1}]
```

**Primary observation**: Status `200 OK`, `Cache-Control: private, no-store`, inline JSON body with ONLY ids 101 and 102 (org_id=1). Zero B rows. MATCHES.

**Awareness check** (SECURITY): Scanned body for `org_id":2` — NOT FOUND. Scanned for `B-doc` — NOT FOUND. Only ids 101 and 102. `private, no-store` confirms access-controlled path (never cache). Response is inline JSON, not a 303. RLS isolation for Tenant A CONFIRMED.

**Judgment**: pass

---

### Step 19: JOIN across orgs+documents with Tenant A's token

**Action run**: `curl -s -i -X POST http://127.0.0.1:8080/query -H 'content-type: application/json' -H 'authorization: Bearer eyJhbGci...[TOKEN_A]' -d '{"sql":"select d.id, d.title, o.name from documents d join orgs o on o.id = d.org_id order by d.id"}'`

**Output excerpt**:
```
HTTP/1.1 200 OK
cache-control: private, no-store
content-type: application/json

[{"id": 101, "name": "Acme", "title": "A-doc-one"}, {"id": 102, "name": "Acme", "title": "A-doc-two"}]
```

**Primary observation**: Status `200 OK`, `Cache-Control: private, no-store`, joined rows reference only Acme (org 1), ids 101/102. No Globex/B rows. MATCHES.

**Awareness check**: Scanned for `Globex` — NOT FOUND. Scanned for `B-doc` — NOT FOUND. Private `documents` table correctly tainted the whole join — not served from cache. Inline JSON (not 303). Nothing unusual.

**Judgment**: pass

---

### Step 20: Same SELECT * with Tenant B's token (cross-check)

**Action run**: `curl -s -i -X POST http://127.0.0.1:8080/query -H 'content-type: application/json' -H 'authorization: Bearer eyJhbGci...[TOKEN_B]' -d '{"sql":"select * from documents order by id"}'`

**Output excerpt**:
```
HTTP/1.1 200 OK
cache-control: private, no-store
content-type: application/json

[{"id": 201, "title": "B-doc-one", "org_id": 2}, {"id": 202, "title": "B-doc-two", "org_id": 2}, {"id": 203, "title": "B-doc-three", "org_id": 2}]
```

**Primary observation**: Status `200 OK`, `Cache-Control: private, no-store`, ONLY ids 201/202/203 (org_id=2). Zero A rows. MATCHES.

**Awareness check**: Scanned for `org_id":1` — NOT FOUND. Scanned for `A-doc` — NOT FOUND. B's set {201,202,203} is completely disjoint from A's {101,102}. Same `member` role, different `org_id` claim, different result set — per-request claim filtering confirmed. Nothing unusual.

**Judgment**: pass

---

### Step 21: Private query with NO token

**Action run**: `curl -s -i -X POST http://127.0.0.1:8080/query -H 'content-type: application/json' -d '{"sql":"select * from documents order by id"}'`

**Output excerpt**:
```
HTTP/1.1 401 Unauthorized
content-type: application/json

{"message":"unauthorized: this query is access-controlled; a bearer token is required","name":"UnauthorizedError"}
```

**Primary observation**: Status `401 Unauthorized`. Error envelope conveys auth required. MATCHES.

**Awareness check**: Response is `401`, NOT a zero-row `200`. No fail-open bug. Error message is explicit and correct. Nothing unusual.

**Judgment**: pass

---

### Step 22: Private query with an expired/invalid token

**Action run**: `curl -s -i -X POST http://127.0.0.1:8080/query -H 'content-type: application/json' -H 'authorization: Bearer eyJhbGci...[TOKEN_EXPIRED]' -d '{"sql":"select * from documents order by id"}'`

**Output excerpt**:
```
HTTP/1.1 401 Unauthorized
content-type: application/json

{"message":"unauthorized: invalid token: ExpiredSignature","name":"UnauthorizedError"}
```

**Primary observation**: Status `401 Unauthorized`. No document rows in body. MATCHES.

**Awareness check**: Expired token rejected BEFORE query runs (specific error is `ExpiredSignature`). No row content in body. Nothing unusual.

**Judgment**: pass

---

### Step 23: Public live stream works (SSE)

**Action run**: `curl -s -N -i --max-time 5 -X POST 'http://127.0.0.1:8080/query?live=true' -H 'content-type: application/json' -d '{"sql":"select id, name from orgs order by id"}'`

**Output excerpt** (curl exit 28 = timeout, expected):
```
HTTP/1.1 200 OK
content-type: text/event-stream
cache-control: no-store

data: {"type":"snapshot","url":"/q/6d053489cf884929/0","version":0}
```

**Primary observation**: Status `200 OK`, `Content-Type: text/event-stream`, first `data:` event emitted (snapshot pointer). MATCHES.

**Awareness check**: Content-type is `text/event-stream` (not `application/json` — live mode was not ignored). Snapshot event URL matches Step 16/17. curl exit 28 (timeout) is expected behavior for a non-terminating stream. Nothing unusual.

**Judgment**: pass

---

### Step 24: Private live stream is rejected

**Action run**: `curl -s -i -X POST 'http://127.0.0.1:8080/query?live=true' -H 'content-type: application/json' -H 'authorization: Bearer eyJhbGci...[TOKEN_A]' -d '{"sql":"select * from documents order by id"}'`

**Output excerpt**:
```
HTTP/1.1 403 Forbidden
content-type: application/json

{"message":"forbidden: live streaming is not available for access-controlled queries","name":"ForbiddenError"}
```

**Primary observation**: Status `403 Forbidden`. Error envelope present, no SSE stream, no document rows. MATCHES.

**Awareness check**: Valid Tenant A token was presented — rejection is not due to bad auth, but to private+live being forbidden. No `200`/event-stream returned. No document data leaked. Nothing unusual.

**Judgment**: pass

---

### Cleanup 1: Stop and remove the Postgres container

**Action run**: `docker rm -f pgpaw-rls-pg`

**Output excerpt**: `pgpaw-rls-pg`

**Observe**: Container name printed. `docker ps -a --filter name=pgpaw-rls-pg` returned empty.

**Awareness check**: Port 55432 freed. Nothing unusual.

**Judgment**: pass

---

### Cleanup 2: Stop the PgPaw server process

**Action run**: `pkill -f 'pgpaw serve'`

**Observe**: `pkill` sent signal. Socket initially in `CLOSE_WAIT`; required `kill -9 25839` to fully terminate. Port 8080 confirmed free after force-kill.

**Awareness check**: Only one pgpaw instance was running (PID 25839). Force-kill needed due to CLOSE_WAIT state. Port freed. Nothing unusual beyond the CLOSE_WAIT delay.

**Judgment**: pass (with note: required kill -9)

---

### Cleanup 3: Remove the replica data directory

**Action run**: `rm -rf /tmp/pgpaw-rls-data`

**Observe**: `test -d /tmp/pgpaw-rls-data` returns exit 1 (directory absent).

**Awareness check**: Data directory fully removed. Nothing unusual.

**Judgment**: pass

---

## Environment Observations

- The `pkill -f 'cache-server.*serve'` pattern from the notebook Cleanup 2 did not match the running process (which shows as `target/release/pgpaw serve ...`). The correct kill pattern is `pgpaw serve`. This was adapted during cleanup without test impact.
- A second inadvertent launch of the pgpaw binary (nohup) was triggered after the `run_in_background` shell wrapper exited and it was unclear whether the first launch survived. The second launch failed with `postmaster failed to start: exit status: 1` because PID 25839 already held the data-dir lock. The first launch (PID 25839) was the authoritative running server throughout all test steps. This is recorded under Deviations.
- The `run_in_background` tool returns the shell echo output (PID) but the actual server output (cargo compile, startup logs) went to `/tmp/pgpaw-test.log` via redirection. No "JWT verification is not configured" warning appeared in any reachable log. JWT was confirmed working by Steps 18-22.
- PgPaw version: `pgpaw v0.2.4`.
- All 6 existing docker containers running (midwess_ai-*) were unaffected throughout the run.

## Evidence

### Expected: public orgs query returns 303 with `/q/{hash}/{version}` Location

Step 16 output: `HTTP/1.1 303 See Other` with `location: /q/6d053489cf884929/0`.
**matches** — status is 303, Location matches `/q/{hash}/{version}` shape exactly.

### Expected: following the Location returns 200 with `Cache-Control: public, max-age=259200` and ETag, body has both orgs

Step 17 output: `200 OK`, `cache-control: public, max-age=259200`, `etag: 6d053489cf884929:0`, body `[{"id":1,"name":"Acme"},{"id":2,"name":"Globex"}]`.
**matches** — all headers present, both orgs in body.

### Expected: documents query with valid token returns 200 inline JSON with `Cache-Control: private, no-store`

Steps 18 and 20: both returned `200 OK` with `cache-control: private, no-store` and inline JSON body.
**matches** — never a 303, always inline JSON, always private no-store.

### Expected: Tenant A sees ONLY ids 101/102, zero B rows

Step 18 body: `[{"id":101,...,"org_id":1},{"id":102,...,"org_id":1}]`. Scanned for `org_id":2` and `B-doc` — not present.
**matches** — strict tenant isolation confirmed for A.

### Expected: Tenant B sees ONLY ids 201/202/203, zero A rows; sets are disjoint

Step 20 body: `[{"id":201,...,"org_id":2},{"id":202,...,"org_id":2},{"id":203,...,"org_id":2}]`. Scanned for `org_id":1` and `A-doc` — not present. Disjoint from Step 18 result.
**matches** — strict tenant isolation confirmed for B; sets disjoint.

### Expected: JOIN with Tenant A stays filtered to A's rows even when joining public orgs

Step 19 body: `[{"id":101,"name":"Acme","title":"A-doc-one"},{"id":102,"name":"Acme","title":"A-doc-two"}]`. Scanned for `Globex` — not found.
**matches** — private table taints whole join; RLS filter survives the join.

### Expected: no-token private query returns 401 (not zero-row 200)

Step 21: `HTTP/1.1 401 Unauthorized` with `{"message":"unauthorized: this query is access-controlled; a bearer token is required",...}`.
**matches** — 401 returned, not 200. No fail-open.

### Expected: expired/bad-signature token returns 401 with no row content

Step 22: `HTTP/1.1 401 Unauthorized` with `{"message":"unauthorized: invalid token: ExpiredSignature",...}`. No rows.
**matches** — verification runs before query; ExpiredSignature specifically identified.

### Expected: public live stream opens text/event-stream and emits at least one snapshot event

Step 23: `200 OK`, `content-type: text/event-stream`, `data: {"type":"snapshot","url":"/q/6d053489cf884929/0","version":0}`.
**matches** — event-stream opened, snapshot event emitted.

### Expected: private live stream with valid token returns 403 (not 200 or stream)

Step 24: `HTTP/1.1 403 Forbidden` with `{"message":"forbidden: live streaming is not available for access-controlled queries",...}`.
**matches** — 403 returned, no stream opened, no data leaked.

### Expected: /healthz reports status:ok with monotonic watermark

Step 15: `{"status":"ok","watermark":22455888}`.
**matches** — ok status and non-zero watermark.

### Expected: member role is non-superuser without BYPASSRLS

Step 7 Awareness: `\du member` shows empty attribute field.
**matches** — Postgres itself is the row-filtering authority; RLS is not bypassed.

## Deviations

1. **Step 14 — inadvertent second binary launch**: After the `run_in_background` shell wrapper exited, it was unclear whether the first pgpaw process (PID 25839) was still alive, so a second launch via `nohup ... &` was attempted. This second launch failed immediately with `postmaster failed to start: exit status: 1` (data-dir locked by PID 25839). PID 25839 was confirmed running via `ps -p 25839` and `lsof -i :8080`. The second launch had no effect on test results. **Recommended notebook edit**: Step 14 should note that the `run_in_background` tool's shell wrapper may exit while the server child process stays alive; the tester should verify via `ps -p <PID>` and `lsof -i :8080` before assuming the server died.

2. **Cleanup 2 — kill pattern mismatch**: The notebook's cleanup action `pkill -f 'cache-server.*serve'` did not match the process cmdline (`target/release/pgpaw serve ...`). The pattern `pgpaw serve` was used instead. An additional `kill -9` was required because the socket entered `CLOSE_WAIT` after `pkill`. Neither affected test results. **Recommended notebook edit**: Update Cleanup 2 Action to `pkill -f 'pgpaw serve'` and add a note that force-kill may be needed if the socket enters CLOSE_WAIT.

3. **Step 15 — single poll sufficed**: The notebook anticipates N polls. On this run the first poll returned `ok` with watermark `22455888`. No repeated polling was needed (likely because PgPaw uses pglite embedded postgres which initializes quickly relative to the ~16s build time).

## Next Actions

- [ ] Update test-case.md Step 14 Awareness to note that `run_in_background` shell wrapper exits but child process lives; suggest `ps -p <PID>` + `lsof -i :8080` as the alive check.
- [ ] Update test-case.md Cleanup 2 Action from `pkill -f 'cache-server.*serve'` to `pkill -f 'pgpaw serve'` to match actual process name; add note about potential CLOSE_WAIT requiring kill -9.
