Artifact

Catalogue-Driven Row-Level Security Assurance and Commit-Time Audit-Chain Allocation in a Multi-Tenant PostgreSQL SaaS: A Single-System Case Study
# Artifact: Catalogue-Driven RLS Assurance and a Fork-Free Audit Chain

Companion to the technical report *Catalogue-Driven Row-Level Security Assurance and Commit-Time Audit-Chain Allocation
in a Multi-Tenant PostgreSQL SaaS: A Single-System Case Study* (Yousof Selim, v1.1, 2 November 2026).

| Report question | Experiment | Raw results |
|---|---|---|
| RQ1 catalogue assurance | Experiment 3 | `results/exp3_*` |
| RQ2 chain integrity and tamper evidence | Experiment 4 | `results/exp4_*`, `results/min_{interleave,stress,tamper}_*` |
| RQ3 availability (lock scope) | Experiment 1 | `results/exp1_*`, `results/min_lockscope_*` |
| RQ4 cost of RLS | Experiment 2 | `results/exp2_*`, `results/pgbench/` |

- **Runs:** 1-2 November 2026 (UTC+8). The timestamps are in `results/run_all.log`.
- **Database:** PostgreSQL 17.10 (Homebrew), `aarch64-apple-darwin25.6.0`, a dedicated local cluster on port 55499
  (`fsync=on`, `synchronous_commit=on`, `shared_buffers=512MB`, `max_connections=200`, everything else at default).
- **Clients:** Node.js 26.0.0 with `pg` 8.22.0, pgbench 17.10, Python 3.14 (standard library only).
- **Machine:** Apple M1 Pro (8 cores), 16 GB RAM, macOS 27.0.1. The machine was **heavily loaded by unrelated work**
  during the runs. While it was logged (from 17:13Z, during Experiment 2 and the minimal reproduction) the one-minute
  load average had a median of 84 and a range of 56-99 on 8 cores (`results/load.log`, `results/load_summary.json`).
  Lock-wait results are insensitive to this. Execution-time results are inflated by it, so the report relies only on
  ratios of two or more orders of magnitude.
- **Cloud:** none. No cloud resource was touched. WayClub's cloud database was stopped throughout.

## What is and is not here

WayClub's source code is **private** (repository `yeegz/WayClub`, studied at commit `65a18ff`, 1 November 2026).
This artifact does not contain the private source. It contains:

| Path | Contents |
|---|---|
| `sql/minimal/01_before_fix_006_excerpt.sql` | Verbatim excerpt of migration 006 (the audit table, the BEFORE INSERT chain trigger and the append-only trigger) over a stub `institution` table |
| `sql/minimal/02_after_fix_011_excerpt.sql` | Verbatim excerpt of migration 011 (the fix: `chain_index`, the deferred constraint trigger and the narrowed append-only exemption) |
| `sql/catalogue_assertions.sql` | SQL port of the project's five `RLS coverage (mechanical)` assertions (A1-A5) under the old and new tenant-scope predicates, plus the scope-free release rule R1 |
| `sql/mutations/M1..M8*.sql` | The eight seeded migration mistakes |
| `sql/catalogue_counts.sql`, `sql/definition_diff.sql`, `sql/old_vs_new_predicate.sql` | Catalogue counting queries |
| `sql/force_rls_probe.sql` | Re-measurement of the FORCE ROW LEVEL SECURITY breakage (ADR-0008), rolled back |
| `bench/setup_bench.sql`, `bench/txn_*.sql`, `bench/helper_inlining_setup.sql` | Benchmark data scaling, pgbench transactions and the helper-variant functions |
| `scripts/*.mjs`, `scripts/*.sh`, `scripts/*.py` | Experiment drivers, analysis and figure generation |
| `results/` | Every raw output (`*.jsonl`, pgbench text output, vitest logs) and the derived `summary*.json` |

**Reproducible from this artifact alone:** Experiments 1 and 4 (audit-chain lock scope, fork resistance and tamper
evidence), using the minimal excerpts. **Requires the private repository:** Experiments 2 and 3 (RLS overhead and the
catalogue assertions), because they need the full 68-migration schema and seed. Their scripts and raw results are here
so the method can be inspected and re-applied to another schema.

## Setup (once)

```bash
export LC_ALL=en_US.UTF-8
B=/opt/homebrew/opt/postgresql@17/bin
# Local only: the cluster uses trust authentication on localhost, so the roles below have no passwords.
$B/initdb -D ./pgdata -U postgres --auth=trust -E UTF8 --locale=C
cat >> ./pgdata/postgresql.conf <<'CONF'
port=55499
listen_addresses='localhost'
unix_socket_directories=''
max_connections=200
shared_buffers=512MB
CONF
$B/pg_ctl -D ./pgdata -l pg.log start -w
$B/psql -h localhost -p 55499 -U postgres -c "create role wayclub_admin login createdb" \
  -c "create role wayclub_app login" \
  -c "create role wayclub_service login bypassrls"
npm install pg@8.22.0      # in this directory, so scripts/ resolves ../node_modules/pg
```

## Experiments 1 and 4 from the public excerpts

```bash
./scripts/build_minimal.sh                       # creates wcmin_before and wcmin_after
cd scripts
for v in before after; do
  EXP_DB=wcmin_$v node audit_experiments.mjs lockscope  $v 30 5000 > ../results/min_lockscope_$v.jsonl
  EXP_DB=wcmin_$v node audit_experiments.mjs interleave $v 200      > ../results/min_interleave_$v.jsonl
  EXP_DB=wcmin_$v node audit_experiments.mjs stress     $v 100 8 5  > ../results/min_stress_$v.jsonl
  EXP_DB=wcmin_$v node audit_experiments.mjs tamper     $v          > ../results/min_tamper_$v.jsonl
done
```

`lockscope` opens a holder transaction, writes one audit row, keeps the transaction open for `hold_ms`, and times one
same-tenant and one other-tenant writer (begin, insert, commit) started 100 ms later. `interleave` replays the project's
regression interleaving on a fresh tenant chain per trial. `stress` runs 8 concurrent writers × 5 rows with random
0-15 ms pauses. Each trial verifies the chain by recomputing every SHA-256 hash in that version's own documented order
(`seq` before the fix, `chain_index` after), and counts forks (rows that share a predecessor hash).

## Experiments on the full schema (needs the private repository)

```bash
R=/path/to/WayClub-clone                                    # at commit 65a18ff
./scripts/build_db_at.sh $R 85475d7 wc_before 010           # before-fix schema
./scripts/build_db_at.sh $R 85475d7 wc_after  011           # after-fix schema
./scripts/build_db_at.sh $R 85475d7 wc_at_016                # history points
./scripts/build_db_at.sh $R 940b2ae wc_at_022
./scripts/build_db_at.sh $R 65a18ff wc_head_clean            # unseeded head, for counts
# Seeded head: in the clone, with .env DATABASE_PORT=55499 and dependencies installed
(cd $R && DATABASE_PORT=55499 node scripts/reset-db.mjs --db=test)
P="psql -h localhost -p 55499 -U postgres"
$P -d postgres -c "create database wc_head template wayclub_test owner wayclub_admin"
for s in 0 50 1000; do
  $P -d postgres -c "create database wc_bench_x$s template wayclub_test owner wayclub_admin"
  $P -d wc_bench_x$s -v scale=$s -f bench/setup_bench.sql
done

# Experiment 3: catalogue counts, scope history, mutation analysis
python3 scripts/catalogue_history.py
./scripts/run_mutations.sh wayclub_test > results/exp3_mutations.txt
./scripts/run_vitest_mutation.sh $R sql/mutations/M1_account_only_table_without_rls.sql results/exp3_vitest_M1.log
psql -h localhost -p 55499 -U postgres -d wc_bench_x0 -f sql/force_rls_probe.sql

# Experiments 1 and 4 on the real schemas
cd scripts
for v in before after head head_immediate; do node audit_experiments.mjs lockscope $v 30 5000 > ../results/exp1_lockscope_$v.jsonl; done
for v in before after head; do
  node audit_experiments.mjs interleave $v 200 > ../results/exp4_interleave_$v.jsonl
  node audit_experiments.mjs stress $v 100 8 5 > ../results/exp4_stress_$v.jsonl
  node audit_experiments.mjs tamper $v > ../results/exp4_tamper_$v.jsonl
done

# Experiment 2: RLS overhead
node rls_query_timing.mjs wc_bench_x0 5    > ../results/exp2_query_x0.jsonl
node rls_query_timing.mjs wc_bench_x50 5   > ../results/exp2_query_x50.jsonl
node rls_query_timing.mjs wc_bench_x1000 1 1,7,9,14 > ../results/exp2_query_x1000.jsonl
./run_pgbench.sh wc_bench_x0 5 20; ./run_pgbench.sh wc_bench_x50 5 20
node helper_overhead.mjs wc_bench_x1000 5 > ../results/exp2_helper_overhead.jsonl

# Plan shapes (EXPLAIN without ANALYZE) on x50
cd .. && psql -h localhost -p 55499 -U postgres -d wc_bench_x50 -f bench/explain_plans.sql > results/exp2_plans_x50.txt && cd scripts

# Analysis, headline interval and figures
python3 analyze.py && python3 finalize.py && python3 bootstrap_ci.py > ../results/exp1_bootstrap_ci.json && python3 figures.py ../results/figures
```

## Deviations from plan, stated plainly

- **x1000 query timing was cut short.** The all-persona, one-repetition run at x1000 scale was stopped after two
  measurements, because on the loaded machine one discovery read took 144 s under RLS. Those two measurements are kept in
  `results/exp2_query_x1000_aborted_partial.jsonl`. The reported x1000 figures use one persona per account (personas 1,
  7, 9 and 14), one repetition each.
- **The helper microbenchmark was run three times.** The first run failed on a type error in the set-based variant (`uuid = uuid[]`). The second showed that a bare row-independent condition becomes a one-time filter, so it did not represent a policy's OR. The reported run (5 repetitions) embeds the role check in an OR, as `event_select` does. Both fixes are in `scripts/helper_overhead.mjs`.
- **The project's whole database suite could not run in parallel** on this cluster, because its files reset a shared
  database. `infra/postgres/tests/wayclub-db.test.mjs` was run on its own: 105 passed, 2 failed at `65a18ff`
  (`results/exp3_vitest_full_db_file_head.log`; see Section 6.1 of the report). The vitest logs are trimmed to result
  lines so that they do not reproduce private test source.
- **The mutation runs through vitest left one mutant table (`mut_m7`) in the seeded template.** It was dropped from every
  derived database before Experiments 1, 2 and 4 used them (see `evidence` in the paper JSON). All catalogue counts are
  taken from `wc_head_clean`, which was built fresh from the migrations.
- **The load average was logged from 17:13Z onwards**, after Experiment 1 had finished. The 15-minute load average at
  17:13Z was about 105, which shows Experiment 1 also ran under heavy load.

- **Changes made during review (2 November 2026, report v1.1).** Role passwords were removed from this README and
  from the scripts (they were never needed: the cluster uses trust authentication). `bench/explain_plans.sql` was added
  and run once on `wc_bench_x50` to archive the plan shapes cited in Section 6.4 (`results/exp2_plans_x50.txt`); it runs
  `EXPLAIN` without `ANALYZE`, so it records plans, not timings. `scripts/bootstrap_ci.py` computes the interval for the
  headline ratio (`results/exp1_bootstrap_ci.json`). `scripts/figures.py` now draws all text at 11 px or larger.
  `scripts/build_paper.py` produced the v1.0 draft only; the report was revised by hand for v1.1, so do not use it to
  regenerate the report.

## Licence

Scripts and SQL written for this study: MIT. The two migration excerpts are © Yousof Selim and are included only so
the experiments can be reproduced.