institution_id only) and new (institution_id or account_id) predicates, by schema version.A multi-tenant SaaS product that keeps every customer in one shared PostgreSQL schema has exactly one thing standing between two customers' data: the predicate that says which rows belong to whom. Putting that predicate in the application means one forgotten WHERE clause is a breach. Putting it in the database, as PostgreSQL row-level security (RLS) policies, makes a forgotten filter fail closed{c('chong2006', 'pgrls')}. It does not remove the problem, though. It moves it: now the risk is a table created without RLS, a policy that keys on the wrong column, a helper function that runs with more privilege than its caller, or an audit log whose integrity mechanism quietly fails under concurrency.
This report studies those risks in WayClub, a group-management product for clubs, societies and communities that the author is building. It is in development: it has no live site and no real tenants, and its cloud database is stopped. That is a real constraint on what can be claimed, and it is also why the database can be studied in detail. Every schema state in its history can be rebuilt from migrations and examined on a laptop at no cost.
Two engineering episodes from that history motivate the paper. First, the project's security documents once said that RLS was "enabled and forced" and that a test asserted coverage. Neither was true. When the claim was checked, turning on FORCE ROW LEVEL SECURITY broke the policy helpers. The response was to replace prose with five tests that query PostgreSQL's catalogue directly. One of those tests later turned out to have a blind spot of its own: its tenant-scope predicate looked only for institution_id, so it ignored tables added once account_id became the tenant root. Second, the hash-chained audit log forked under concurrency, and its tenant lock serialised every audited write in a tenant behind the slowest open request. A serial test suite had reported the chain correct throughout.
This report re-examines both episodes with new, reproducible measurements, and adds one finding that was not known before it was written: the policy-helper style the design depends on is very expensive per row.
account_id. Neither catches a person-scoped or foreign-key-derived table, which only a scope-free release check catches (Section 5.3).SET clause or the definer property barely helps. A set-based or hoisted formulation removes most of the cost (Section 5.6).Chong, Carraro and Wolter describe a spectrum running from a separate database per tenant, through a separate schema per tenant, to a shared schema in which every row carries a tenant identifier{c('chong2006')}. The shared schema is the cheapest to run and the easiest to scale to many small tenants. Its isolation is only as good as the code that filters by tenant. WayClub sits at the shared end. Its tenants are many small clubs and a few institutions, so per-tenant databases would cost more than the product can carry before launch.
PostgreSQL RLS attaches policies to a table. Each policy is a USING expression that filters visible rows and a WITH CHECK expression that constrains written rows, and permissive policies combine by OR{c('pgrls')}. Enabling RLS on a table with no policies denies all access. Three exemptions matter here. Superusers and roles with BYPASSRLS skip policies. The table owner skips them too, unless the table is marked FORCE ROW LEVEL SECURITY. And a SECURITY DEFINER function runs with its owner's privileges, so it is subject to whatever RLS applies to that owner. PostgreSQL's guidance for definer functions is to pin search_path with a SET clause{c('pgfunc')}. A pinned SET clause, and the definer property on its own, both stop the planner inlining a SQL function into the calling query{c('pginline')}. Vendor guidance for RLS-heavy platforms gives two remedies for per-row function cost: wrap row-independent calls in a scalar sub-select so they run once per statement, and use definer functions for membership lookups{c('supabase')}. Section 5.6 measures both.
Schneier and Kelsey showed how to make log entries written before a machine's compromise impossible for the attacker to read, or to alter or delete without detection, using hash chains and evolving keys{c('schneier1999')}. Crosby and Wallach observed that auditing a hash chain costs linear time. They proposed a Merkle-tree history structure that gives logarithmic membership and consistency proofs, with an untrusted logger and auditors who hold commitments{c('crosby2009')}. Certificate Transparency put that design into production for public certificate logs{c('rfc6962')}. WayClub's chain is much more modest. It is a per-tenant SHA-256 hash chain inside the same database it protects, with no external commitment. Section 5.5 measures exactly what that does and does not detect.
Berenson et al. showed that the ANSI isolation levels do not capture several anomalies that real implementations allow, and they defined snapshot isolation{c('berenson1995')}. Fekete et al. characterised when applications under snapshot isolation stay serializable{c('fekete2005')}. Cahill, Röhm and Fekete made snapshot isolation serializable at run time{c('cahill2008')}, a method PostgreSQL adopted as its SERIALIZABLE level{c('ports2012')}. Warszawski and Bailis found 22 concurrency attacks in 12 web applications and traced them to developers reasoning about one request at a time{c('warszawski2017')}. The audit-chain fork in Section 4.4 has the same shape at a smaller scale. A sequence number was allocated outside the lock that was meant to order it, the defect showed only with two open transactions, and a serial test suite reported the property as holding. PostgreSQL's advisory locks{c('pglocks')} and deferred constraint triggers{c('pgtrigger')} are the two primitives the fix uses.
Mutation testing judges a test suite by seeding faults and counting how many the suite detects{c('jia2011')}. Section 5.3 applies the same idea to schema-level security assertions. Each mutant is a plausible migration mistake, and a good assertion set should fail on it.
""") # ---------------------------------------------------------------- 3 sec("system-and-threat-model", "3 System and threat model", f"""System. WayClub is a modular monolith: a NestJS API, a Next.js web client and a Flutter mobile client over one PostgreSQL 17 database. The database has {chead['migration_files']} migration files at the studied commit (65a18ff, 1 November 2026). The tenant root is the account, either an institution that governs many clubs or a standalone club. Every tenant-scoped row carries account_id. Institution-governed rows also carry institution_id, and a composite foreign key ties it to the row's own account. Three database roles exist. wayclub_admin owns every object and runs migrations and seeds. wayclub_app serves every request, owns nothing and lacks BYPASSRLS. wayclub_service has BYPASSRLS and is reserved for narrow scheduled jobs. The API sets the caller's identity per transaction with set_config('request.jwt.claims', $1, true), and policies read it through auth.uid()-style helper functions.
Assets. Tenant data (members, events, registrations, money records, handovers) and the audit log, which records approvals, role grants and state changes and has to stand up as evidence in a dispute between club officers.
Adversaries in scope. (A1) An authenticated user of one tenant who controls their own requests, including a forged or replayed token for their own identity, and who tries to read or write another tenant's rows. (A2) A developer, human or AI assistant, who writes a migration that adds a table or policy and forgets a protection. This is the adversary the catalogue assertions target. (A3) Concurrent honest traffic, which is no adversary but has historically broken integrity properties that serial tests reported as holding. (A4) An attacker with database-owner or superuser access who edits the audit log afterwards.
Out of scope. A4 is in scope for detection only. The chain cannot prevent an owner from rewriting it, and Section 5.5 shows the cases it cannot even detect. Also out of scope: compromise of the API container (which, as the project's security document records, can read the service-role credential), SQL injection (the codebase uses parameterised queries, and this report does not test that), side channels, and denial of service beyond the lock-scope effect in Section 5.4.
""") # ---------------------------------------------------------------- 4 sec("design", "4 Design", f"""The original architecture decision called for every tenant table to have RLS enabled and forced. That was never implemented. When the gap was found, the obvious fix was to turn FORCE on, and doing so broke the system. FORCE changes exactly one thing: whether policies also bind the table owner. Here the owner is the role that owns the SECURITY DEFINER helpers the policies call (app.is_member_of, app.current_account_id and about thirty more), and those helpers are definer-owned precisely so that evaluating a policy does not recurse into RLS. We re-ran the project's measurement on a seeded copy of today's schema, inside a transaction that was then rolled back. With RLS forced on public.membership, app.is_member_of() returned false for a genuine member, where it returned true before. The owner's count(*) over membership dropped from 16 rows to 0.
The design that was adopted puts the trust boundary in role separation, not in FORCE. The request role owns nothing and has no BYPASSRLS, so RLS binds it unconditionally. Rules that must bind the owner as well are written as constraints and triggers, not policies: the composite tenant foreign key, the append-only audit trigger, the unique (account_id, chain_index) backstop and several immutability triggers. A catalogue test asserts that no table is forced, and it carries the measurement in a comment, so that nobody reverses the decision without seeing the reason.
Definer functions are the audited exceptions to the boundary. They run as the owner, so RLS cannot protect anything a definer function writes. An adversarial review found two of them authorising on less than they should. app.write_audit accepted a cross-tenant forged audit row, and app.promote_waitlist_head authorised on tenancy alone. Migrations 012 to 015 made each definer check its caller's claims itself. The request-role audit writer now pins account, institution and actor to the caller's own claims. The unvalidated writer is granted to no role at all and is reachable only from owner-definer trigger functions. Both regressions are covered by passing tests in the project's database suite. At head there are {chead['definer_functions']} SECURITY DEFINER functions in the application schemas, all with a pinned SET search_path, and {chead['policies_calling_app_helpers']} of the {chead['public_policies']} public-schema policies call at least one app.* helper. That ratio becomes important in Section 5.6.
The five assertions in the project's RLS coverage (mechanical) test block read pg_class, pg_policy and has_table_privilege instead of a hand-kept list. They first compute a tenant scope, the set of public tables that name a tenant. Then:
relrowsecurity set;relforcerowsecurity set (Section 4.1);audit_event;current_account_id.The tenant-scope predicate is the weak point. It was first written as "the institution table plus every table with an institution_id column" (commit 85475d7). Migration 017 then made account_id the tenant root. Tables added after it, including entitlements, club affiliation, institution claims and club migration, carry account_id and no institution_id, so the predicate silently stopped selecting them. Commit 5096bcd widened it to "institution or account, or any table with institution_id or account_id". Section 5.2 measures what the old predicate would have missed at each point.
The deployment pipeline also runs a release-time role check, database-checks.mjs. Among other things, it fails on any privilege the request role holds on any relation without RLS, in any non-system schema. That rule needs no tenant predicate. We call it R1 and include it in the evaluation because it covers exactly the cases the scoped assertions cannot.
audit_event is append-only. A BEFORE UPDATE OR DELETE trigger rejects changes from every role, owner included. Each row's hash is SHA-256 over the previous row's hash and the row's own fields. Migration 006 computed the hash in a BEFORE INSERT trigger. That trigger took pg_advisory_xact_lock on the tenant, read the latest row by seq (a generated always as identity column) and chained onto it. This had two defects.
Integrity. seq is allocated while the tuple is formed, before the trigger runs, and so before the lock is taken. Allocation order and lock order can differ. Suppose a transaction T1 writes a row and holds the lock. T2 then allocates seq = n and blocks. T1 writes a second row with seq = n+1 and commits. T2 then chains its lower-seq row onto T1's higher-seq row. A verifier that walks seq order sees two breaks. With several rows per transaction it gets worse. A transaction's second row looks up "the latest row by seq", which can be another transaction's committed row rather than its own first row, so two rows end up with the same predecessor: a real fork (Section 5.5).
Availability. An advisory transaction lock lasts until commit. The lock was taken at the first audited write, and API transactions span whole request handlers, so one slow request blocked every audited write in its tenant.
Migration 011 fixed both. A new chain_index column carries the authoritative position. Chaining moved into a DEFERRABLE INITIALLY DEFERRED constraint trigger, which fires during COMMIT. That trigger takes the lock, reads the tenant's highest chain_index, and writes chain_index, hash_prev and hash onto the new row in one narrowly exempted update. Chain order and lock order are now the same by construction, and the critical section is the commit, not the rest of the handler. A unique (account_id, chain_index) index is the structural backstop: if serialisation were ever defeated, two rows would collide and the commit would fail loudly instead of forking silently. Migration 019 re-keyed the chain from institution to account, so that standalone clubs, which have no institution, get a chain too. Before re-deriving hashes under the new formula, it verified every existing chain under the old one, because re-hashing an unverified chain would launder a forgery. One trade-off remains. A session that runs SET CONSTRAINTS ALL IMMEDIATE makes the trigger fire per insert again, which brings the availability problem back. Integrity is unaffected, because the position is still allocated inside the lock.
| Quantity | Documented (2026-08-03, at migration 022) | Measured at migration 022 | Measured at head (65a18ff) |
|---|---|---|---|
| Tables | 79 | {c22['tables']} | {chead['tables']} |
| Tables with RLS enabled | 78 | {c22['rls_enabled']} | {chead['rls_enabled']} |
| Tables without RLS | 1 (role) | {c22['rls_disabled']} ({c22['rls_disabled_names']}) | {chead['rls_disabled']} ({chead['rls_disabled_names']}) |
| Tables with RLS forced | 0 | {c22['rls_forced']} | {chead['rls_forced']} |
| Policies | 126 across 47 tables | {c22['policies']} across {c22['tables_with_policies']} | {chead['policies']} across {chead['tables_with_policies']} ({chead['request_policies']} for the request role, {chead['service_policies']} for the service role) |
Tables reachable by wayclub_app | 48 | {c22['app_reachable']} | {chead['app_reachable']} |
| Deny-all tables (RLS on, no policy, no grant) | 31 | {c22['deny_all']} | {chead['deny_all']} |
| Mutant | Seeded mistake | A1-A5, new predicate | A1-A5, old predicate | R1 (scope-free release check) |
|---|
| Schema | Same tenant p50 | Same tenant p95 | Other tenant p50 | Other tenant p95 |
|---|---|---|---|---|
| Before fix (migrations 001-010) | {ms(b_same['p50'])} | {ms(b_same['p95'])} | {ms(b_other['p50'])} | {ms(b_other['p95'])} |
| After fix (001-011) | {ms(a_same['p50'])} | {ms(a_same['p95'])} | {ms(a_other['p50'])} | {ms(a_other['p95'])} |
| Head, per-account chain | {ms(h_same['p50'])} | {ms(h_same['p95'])} | {ms(h_other['p50'])} | {ms(h_other['p95'])} |
Head, with SET CONSTRAINTS ALL IMMEDIATE | {ms(i_same['p50'])} | {ms(i_same['p95'])} | {ms(i_other['p50'])} | {ms(i_other['p95'])} |
| Public minimal reproduction, before (excerpt of 006) | {ms(MIN['before']['same_tenant_ms']['p50'])} | {ms(MIN['before']['same_tenant_ms']['p95'])} | {ms(MIN['before']['other_tenant_ms']['p50'])} | {ms(MIN['before']['other_tenant_ms']['p95'])} |
| Public minimal reproduction, after (excerpt of 006 + 011) | {ms(MIN['after']['same_tenant_ms']['p50'])} | {ms(MIN['after']['same_tenant_ms']['p95'])} | {ms(MIN['after']['other_tenant_ms']['p50'])} | {ms(MIN['after']['other_tenant_ms']['p95'])} |
| Schema and workload | Trials | Chains failing verification | Trials with a fork | Trials with writer errors |
|---|
| Tampering | Verifier result | Signal |
|---|---|---|
Owner role issues UPDATE (no trigger bypass) | refused, SQLSTATE WC403 | append-only trigger |
Edit one interior row's detail | {yes(tam['edit_detail_of_interior_row']['detected'])} | {tam['edit_detail_of_interior_row']['after']['breaks']} hash mismatch |
| Delete one interior row | {yes(tam['delete_interior_row']['detected'])} | link break and chain_index gap |
| Delete the newest row (tail truncation) | {yes(tam['delete_last_row (tail truncation)']['detected'])} | the shorter chain is internally consistent |
| Edit one row and recompute every later hash | {yes(tam['edit_and_rehash_suffix (full suffix rewrite)']['detected'])} | the formula is public and unkeyed |
| Query, scale | Filter p50 | RLS p50 | Filter min | RLS min | Ratio | Same rows |
|---|
| Scale, mode | Throughput (tps) | Mean latency (ms) |
|---|---|---|
| Seed, explicit filter (owner) | {ci_txt(tps0_o)} | {ci_txt(lat0_o)} |
| Seed, RLS | {ci_txt(tps0_r)} | {ci_txt(lat0_r)} |
| Seed, RLS plus explicit filter | {ci_txt(tps0_p)} | {ci_txt(lat0_p)} |
| x50, explicit filter (owner) | {ci_txt(tps50_o)} | {ci_txt(lat50_o)} |
| x50, RLS | {ci_txt(tps50_r)} | {ci_txt(lat50_r)} |
| x50, RLS plus explicit filter | {ci_txt(tps50_p)} | {ci_txt(lat50_p)} |
SET search_path (as shipped)",
"definer_without_set": "SECURITY DEFINER, no SET",
"invoker_inlinable": "SECURITY INVOKER, no SET (claims read inline)",
"set_based_definer_once": "definer called once, returns the caller's club ids",
"no_membership_test": "no membership test (floor)",
"university_admin_per_row": "app.is_university_admin(), per row",
"university_admin_hoisted": "(select app.is_university_admin())"}
tbl_h = "| Formulation | p50 | min | count |
|---|---|---|---|
| {hn[k]} | {ms(H[k]['exec_ms']['p50'])} | {ms(H[k]['exec_ms']['min'])} | {H[k]['count'][0]:,} |
Six questions are asked. RQ1: do the documented catalogue counts reproduce, and what are they today? RQ2: how large was, and is, the old tenant-scope predicate's blind spot? RQ3: which plausible migration mistakes do the catalogue assertions catch? RQ4: how far does the lock-scope fix restore availability? RQ5: does commit-time allocation prevent forks, and what tampering does the chain detect? RQ6: what does RLS cost compared with an explicit filter, and why?
""" sec("evaluation", "5 Evaluation", f""" {rq}All experiments ran locally on {S.get('machine', 'an Apple M1 Pro laptop (8 cores, 16 GB) running macOS 27.0.1')}, against a dedicated PostgreSQL 17.10 (Homebrew) cluster created for this study: port 55499, fsync and synchronous_commit on, shared_buffers 512 MB, everything else at defaults. No cloud resource was touched. Schemas were rebuilt from the private repository's migrations exactly as they stood at three commits: 85475d7 (migrations up to 010, the "before" schema, and up to 011, the "after" schema), 940b2ae (up to 022) and 65a18ff (head, 68 migration files, 1 November 2026). The head schema was seeded with the project's deterministic seed (32 users, 22 events, four accounts). Clients were Node.js 26 with pg 8.22.0 and pgbench 17.10. The audit-chain experiments were also run against a public minimal reproduction, consisting of verbatim excerpts of migrations 006 and 011 over a stub table, which needs none of the private source. The two agree (Tables 3 and 4).
Load caveat. The laptop was shared with other work throughout. The one-minute load average sampled during the timed runs had a median of {LOAD['median']:.0f} (range {LOAD['min']:.0f}-{LOAD['max']:.0f}) on 8 cores. Lock-wait results are dominated by a deliberate 5-second hold and are insensitive to this. Execution-time results are inflated by it, so Section 5.6 reports minima next to medians and draws conclusions only from ratios of two orders of magnitude or more.
Table 1 shows the documented figures reproduced exactly when the schema is rebuilt at migration 022: 78 RLS tables, 126 policies across 47 tables, 48 tables reachable by the request role and 31 deny-all tables. The document counts 79 tables. The rebuilt public schema has 80, the extra one being the migration runner's own schema_migrations ledger, which also has no RLS and no grant. At head the schema is larger: {chead['rls_enabled']} of {chead['tables']} tables have RLS, with {chead['policies']} policies across {chead['tables_with_policies']} tables. No table is forced, and only role, a global read-only catalogue of role definitions, lacks RLS. The documented counts are therefore correct for the date they give and out of date today.
Figure 2 applies the old and new tenant-scope predicates to each rebuilt schema. At migration 016, before account tenancy, they select the same {next(p for p in SCOPE if p['label'].startswith('016'))['old_scope']} tables. At migration 022 the old predicate misses {blind22['new_scope'] - blind22['old_scope']} ({blind22['blind_spot_tables']}). At head it misses {blindhead['new_scope'] - blindhead['old_scope']} ({blindhead['blind_spot_tables']}). In every case, every table in the blind spot does have RLS enabled. The gap was latent: no table escaped protection, only the assertions' attention. One claim in the project's documents needs correcting. The continuity-domain tables (handover, external tool and so on) carry institution_id as well as account_id, so contrary to the security document they were never in the blind spot.
institution_id only) and new (institution_id or account_id) predicates, by schema version.The project's own test file also runs red at head. Assertion A5 fails on four policies added by migrations 066 and 067 (legal_version, reserved_handle, person_handle, person_avatar), none of which mention current_account_id. Two of these tables hold global reference data. The other two are person-scoped and reach the account boundary only through a helper. Whether each is safe is a design question, and the assertion did its job by forcing that question to be asked. The developer's uncommitted working tree, which this study did not use, answers it with an explicit allow-list that checks each exemption's exact shape.
Table 2 lists eight mutants, each a mistake a migration author could plausibly make. Under the new predicate, the five assertions catch all six mutants that fall inside a tenant scope. Under the old predicate they miss M1 and M2, the two whose only tenant column is account_id: a table created without RLS but granted to the request role, and a table with RLS on, granted, and no policy. This is the migration-017 gap made concrete. Neither predicate catches M7 (a person-scoped table with only a user_id) or M8 (a child table whose tenancy is derivable only through a foreign key). Both are granted to the request role without RLS, which is the worst case, and both are caught only by the scope-free release rule R1. In turn, R1 misses M2, M4, M5 and M6, which need the scoped assertions. Neither family catches everything on its own, and together they catch all eight. The SQL port's verdicts match the project's own vitest block on the baseline and on M1, M2 and M7, the three that were also run through the real suite.
Each run opens a holder transaction, writes one audit row for tenant A, and keeps the transaction open for 5 s. After 100 ms, a writer for tenant A and a writer for tenant B each run begin, insert, commit, and are timed. Table 3 and Figure 1 show the result. Before the fix, the same-tenant writer waits out the holder: median {ms(b_same['p50'])}, against {ms(b_other['p50'])} for the other tenant. This agrees with the project's original single measurement of 4,780 and 4,792 ms against 43 ms. After the fix the two writers are indistinguishable (medians {ms(a_same['p50'])} and {ms(a_other['p50'])}), a {speedup:,.0f}-fold reduction in the median same-tenant wait. The head schema behaves the same way ({ms(h_same['p50'])} and {ms(h_other['p50'])}; its absolute times are higher because the head schema runs more triggers per insert and the machine was loaded). With SET CONSTRAINTS ALL IMMEDIATE the old behaviour comes straight back (median {ms(i_same['p50'])}), confirming that commit-time firing is what restores availability, as ADR-0009 predicts.
Table 4 shows that before the fix, every one of {pre_inter['trials']} replays of the regression interleaving produced a chain that failed verification in seq order, with a median of {pre_inter['breaks_per_trial']['p50']:.0f} broken links per 3-row chain. In those replays the hash links still formed a single path; it simply did not match seq order. The multi-row stress workload is worse. All {pre_stress['trials']} trials produced real forks, in which two rows share a predecessor (median {pre_stress['breaks_per_trial']['p50']:.0f} broken links per 40-row chain), so no ordering of the rows can make them verify. After the fix, {post_fail} of {post_trials} trials across the after-fix and head schemas failed verification, and none had a fork or a writer error. The unique (account_id, chain_index) backstop never fired, which is consistent with the lock serialising every allocation. A test that runs one transaction at a time cannot observe any of this. The original suite was serial and green while the property was false.
Table 5 records what the chain detects. An ordinary owner-role update is refused outright by the append-only trigger. With that trigger bypassed by a superuser, an edited interior row and a deleted interior row are both detected. Two attacks are not: deleting the newest row, and editing a row and recomputing every later hash. Both follow directly from an unkeyed chain held in the same database it protects. The chain is tamper-evident only against an attacker who does not recompute. Closing that gap needs what Schneier and Kelsey, and Crosby and Wallach, prescribe: a commitment held outside the attacker's reach, such as a periodically published chain head, a keyed MAC with an externally held key, or a Merkle-tree log with external auditors{c('schneier1999', 'crosby2009', 'rfc6962')}. The project's documents list "externally anchored checkpoints" as planned, not built, and this measurement shows why it matters.
Table 6 compares two tenant-scoped reads that the product actually issues. One lists a club's latest 20 events, the other lists the latest 50 events open for discovery. Each runs under RLS as the request role with a persona's claims, and again as the owner with the explicit filter account_id = $1 that RLS replaces. Both modes returned the same number of rows in every measurement. On the 22-event seed the uncontended difference is small: the minima are {ms(qx0['club']['rls_exec_ms']['min'])} under RLS and {ms(qx0['club']['owner_filter_exec_ms']['min'])} with the filter. The median ratio of {x(qx0['club']['ratio_median'])}× at this scale is mostly load noise acting on very short queries, so we do not rely on it. The cost grows with the number of rows the policy has to inspect. At x50 scale even the minima differ by {x(qx50['club']['rls_exec_ms']['min'] / qx50['club']['owner_filter_exec_ms']['min'])}× for the club read. At x1000 scale the discovery read takes a median of {ms(qx1k['discovery']['rls_exec_ms']['p50'])} under RLS, against {ms(qx1k['discovery']['owner_filter_exec_ms']['p50'])} with the explicit filter. Even the minima, the least-contended observations, differ by {x(qx1k['discovery']['rls_exec_ms']['min'] / max(qx1k['discovery']['owner_filter_exec_ms']['min'], 1e-9))}×. pgbench tells the same story at the level of whole transactions (Table 7). At seed scale the mixed transaction completes {tps0_o['mean']:,.0f} tps with the explicit filter and {tps0_r['mean']:,.0f} tps under RLS. At x50 scale the figures are {tps50_o['mean']:,.0f} and {tps50_r['mean']:,.1f} tps. Adding the explicit filter on top of RLS changes little ({tps0_p['mean']:,.0f} and {tps50_p['mean']:,.1f} tps), because RLS already restricts to the account through an indexed condition, and the cost lies in the remaining policy terms.
The query plans locate the cost. In event_select, the conditions after the account test are an OR of calls such as app.is_committee_of(club_id), app.is_member_of(club_id), app.is_reviewer_for_club(club_id), app.is_university_admin() and app.club_is_suspended(club_id). The planner evaluates them per row as an opaque filter. Every helper is a SECURITY DEFINER SQL function with a SET search_path clause, and some call further definers (is_university_admin calls has_institution_role, which calls active_assignment). Such functions cannot be inlined{c('pginline')}, and each call also saves and restores a configuration setting. Our hypothesis was that this call overhead was the main cost. Table 8 and Figure 4 test that on one account's 14,014 events, and the data mostly refutes it. Dropping the SET clause (median {ms(hs['definer_without_set'])}) or making it a plain invoker function with no SET that reads the claims inline ({ms(hs['invoker_inlinable'])}) gives little improvement on the shipped {ms(hs['shipped_definer_with_set'])}, because the membership sub-query still runs once per row. What works is changing how often the helper runs. A definer called once per statement that returns the caller's club ids, tested with club_id = any(...), takes the median to {ms(hs['set_based_definer_once'])} (minimum {ms(hmin['shipped_definer_with_set'])} down to {ms(hmin['set_based_definer_once'])}), while keeping the definer property the design needs to avoid RLS recursion. A row-independent helper inside an OR is evaluated per row as well. app.is_university_admin() costs {ms(hs['university_admin_per_row'])} over these rows, and wrapping it in a scalar sub-select makes it an InitPlan evaluated once, at {ms(hs['university_admin_hoisted'])}. This is the vendor guidance cited in Section 2.2{c('supabase')}. On its own, outside an OR, PostgreSQL already treats such a call as a one-time filter; we hit this in a first, discarded version of the microbenchmark. These rewrites are measured here as microbenchmarks only. They have not been applied to WayClub's policies, and a policy rewrite would need its own equivalence tests before it shipped.
FORCE and a coverage test, and none of them agreed with the database. A query over pg_class and pg_policy cannot drift that way.institution_id predicate was correct until the tenant root moved, then quietly covered fewer tables after every migration. At head it is stale again, now for person-scoped tables. Pair scoped assertions with at least one scope-free invariant, such as "the request role holds no privilege on any relation without RLS". In our mutation study, neither family caught every mutant on its own, and together they caught all eight.BEFORE triggers run. A deferred constraint trigger moves both the allocation and the lock into COMMIT. A unique index on the position turns any remaining race into a loud failure instead of a silent fork.SECURITY DEFINER" (removing SET or the definer property barely helped) but "call it once": hoist row-independent checks into (select ...), and turn per-row membership calls into a single definer call that returns the caller's ids.Internal validity. The timed runs shared a heavily loaded laptop (median one-minute load average {LOAD['median']:.0f} on 8 cores). Lock-wait measurements are dominated by a fixed 5 s hold, and their conclusions survive the noise: the before-fix same-tenant minimum ({ms(b_same['min'])}) exceeds the after-fix maximum ({ms(L['after']['same_tenant_ms']['max'])}). The RLS execution times are inflated and noisy, with large spread across repetitions of the same query, so the report relies only on ratios of two or more orders of magnitude and gives minima alongside medians. The x1000 query timings have one repetition per persona. The comparison baseline for RLS is the explicit tenant filter, not a full reimplementation of the policies' visibility rules. Both modes returned identical row counts in every measurement, but on other data they could differ. The tamper experiment uses a superuser trigger bypass to stand in for an owner-level attacker. The SQL port of the assertions was checked against the project's own vitest block on four schema states, not all nine.
Construct validity. "Fork" means two rows that share a predecessor hash, and "verification failure" means any recomputed hash or link that does not match in the version's own documented order. The pre-fix interleaving produces failures without forks, and the stress workload produces both. The two are reported separately.
External validity. One system, one developer, one PostgreSQL version and synthetic data. The x1000 scale replicates the seed's 22 events a thousand times. That preserves the seed's mix of status and visibility but not a real tenant's distribution. The mutants are hand-chosen and stand in for plausible mistakes, not a sample of real ones.
""") # ---------------------------------------------------------------- 8 sec("limitations", "8 Limitations", """Shared-schema multi-tenancy on PostgreSQL RLS is only as strong as the assurance around it, and that assurance has to be a query, not a sentence. Catalogue assertions caught every mutant inside their scope. Their scope predicate is itself a model of tenancy that drifts, so a scope-free invariant has to run beside them. A hash-chained audit log needs its position allocated inside the same lock that orders it, held for the commit and no longer. Done that way, {post_trials} concurrent trials produced no forks, and a same-tenant writer behind a 5-second holder waited a median of {ms(a_same['p50'])} instead of {ms(b_same['p50'])}. The same study found the cost the design was carrying unnoticed: definer-helper policies that are cheap on a seed and expensive at realistic row counts. The fixes are known, and none of them has yet been applied to the product.
""") sec("artifact-availability", "Artifact availability", f"""The artifact is at /research/{SLUG}/artifact/. It contains the experiment drivers (Node.js and shell), the SQL ports of the catalogue assertions and of the release check, the eight mutation files, the benchmark transactions, every raw result file, the analysis and figure scripts, and a README with exact commands, the PostgreSQL version and the run date (1-2 November 2026). WayClub's source is private. The artifact includes only verbatim excerpts of the two audit-chain migrations, enough to reproduce Experiments 1 and 4 on an empty database, together with the catalogue and benchmark SQL. Experiments 2 and 3 need the private schema and are provided as scripts and raw outputs.
""") # ---------------------------------------------------------------- citations order = [] for s in sections: for k in re.findall(r"\[\[([a-z0-9]+)\]\]", s["html"]): if k not in order: order.append(k) for s in sections: s["html"] = re.sub(r"\[\[([a-z0-9]+)\]\]", lambda m: f'[{order.index(m.group(1)) + 1}]', s["html"]) s["html"] = re.sub(r"\n\s*", "\n", s["html"]).strip() unused = [k for k in REFS if k not in order] assert not unused, unused references = [{"id": k, "text": REFS[k][0], "url": REFS[k][1]} for k in order] words = len(re.sub(r"<[^>]+>", " ", " ".join(s["html"] for s in sections)).split()) abstract = ( f"Moving the audit chain's lock from the first audited write to commit time cut the median wait of a same-tenant write behind a slow transaction from {plain_ms(b_same['p50'])} to {plain_ms(a_same['p50'])}, and in {post_trials} concurrent trials the chain never forked; before the fix it failed verification in all {pre_inter['trials'] + pre_stress['trials']}. " "This technical report studies a pre-launch, multi-tenant PostgreSQL SaaS (WayClub) that isolates tenants with row-level security (RLS) in one shared schema. " "RLS is enabled but deliberately not forced, because forcing it made an owner-owned SECURITY DEFINER policy helper deny a genuine member; we re-measured that. " "Instead of a hand-kept table list, five tests assert coverage from PostgreSQL's catalogue. " f"Rebuilding the schema from its migration history reproduces the documented 78 RLS tables and 126 policies exactly at migration 022 and finds {chead['rls_enabled']} and {chead['policies']} today. " f"The tests' original institution-only scope predicate would have ignored {blindhead['new_scope'] - blindhead['old_scope']} tenant tables today. " "In a mutation study the widened assertions catch all six in-scope mistakes, and a scope-free release check catches the two they cannot see. " f"The study also found a cost nobody had measured: because policies call definer helpers once per row, tenant reads ran {x(qx1k['discovery']['ratio_median'])} to {x(qx1k['club']['ratio_median'])} times slower than an explicit filter at 22,000 events. Set-based or hoisted helpers remove most of that cost. " "All experiments ran locally and are reproducible from the artifact." ) paper = { "slug": SLUG, "title": "Catalogue-Driven Row-Level Security Assurance and a Fork-Free, Tamper-Evident Audit Chain for a Multi-Tenant PostgreSQL SaaS", "short": "Proving Tenant Isolation", "authors": [{"name": "Yousof Selim", "affiliation": "Sunway University (Lancaster University dual degree), Malaysia", "email": "yousofselim2@gmail.com"}], "date": "2026-11-02", "version": "v1.0", "status": "Technical report", "area": "Software engineering · Database security · Multi-tenancy", "keywords": ["PostgreSQL", "row-level security", "multi-tenancy", "SECURITY DEFINER", "catalogue assertions", "mutation analysis", "tamper-evident logging", "hash chain", "concurrency", "advisory locks", "deferred constraint triggers"], "projects": ["wayclub"], "pitch": f"I found why one slow request froze a customer's audit-log writes, cut that wait {speedup:,.0f}-fold, and showed my security checks catch forgotten protections.", "keyResult": {"value": f"{speedup:,.0f}×", "label": "shorter wait for a customer's writes behind slow requests"}, "heroFigure": {"src": f"{IMG}/lock-scope.svg", "alt": "Strip plot: before the fix, writes in the same tenant as a slow transaction wait about 4.9 seconds while other tenants wait about 20 ms; after the fix both wait well under a second."}, "findings": [ f"Allocating the audit-chain position at commit cut the median same-tenant wait behind a 5-second transaction from {plain_ms(b_same['p50'])} to {plain_ms(a_same['p50'])}, a {speedup:,.0f}-fold reduction.", f"Before the fix all {pre_inter['trials'] + pre_stress['trials']} concurrent trials broke the audit chain, including {pre_stress['trials_with_shared_prev_hash']} with true forks; after it, 0 of {post_trials} did.", f"The catalogue assertions' original scope predicate would have ignored {blindhead['new_scope'] - blindhead['old_scope']} tenant tables at today's head, and widened assertions plus a scope-free check caught 8 of 8 seeded mistakes.", f"Row-level security policies that call helper functions once per row made tenant reads {x(qx1k['discovery']['ratio_median'])} to {x(qx1k['club']['ratio_median'])} times slower than an explicit filter at 22,000 events; calling the helper once per query cut one test from {plain_ms(hs['shipped_definer_with_set'])} to {plain_ms(hs['set_based_definer_once'])}.", ], "abstract": abstract, "abstractParts": [ {"label": "The problem", "text": "When every customer shares one database, a single forgotten protection can expose one customer's data to another, and an audit log can be quietly broken by requests that run at the same time. WayClub's own documents had once claimed protections the database did not have."}, {"label": "What I did", "text": "I rebuilt WayClub's database at several points in its history and ran four local experiments: seeded security mistakes against its catalogue-reading tests, timed audited writes before and after a lock fix, hammered the audit chain with concurrent writers, and benchmarked row-level security against plain filters."}, {"label": "What I found", "text": f"The fix cut a same-customer write's median wait behind a slow request from {plain_ms(b_same['p50'])} to {plain_ms(a_same['p50'])}, and the chain went from failing all {pre_inter['trials'] + pre_stress['trials']} concurrent trials to failing 0 of {post_trials}. The tests' original scope would have ignored {blindhead['new_scope'] - blindhead['old_scope']} tenant tables today. Row-level security made reads {x(qx1k['discovery']['ratio_median'])} to {x(qx1k['club']['ratio_median'])} times slower at 22,000 events."}, {"label": "Why it matters", "text": "Security claims about a database should be queries that can fail, not sentences, and concurrency properties need concurrent tests. The same measurements found a performance cost that would have arrived with the first large customer, while it is still cheap to fix."}, ], "contributions": [ "A mutation analysis of catalogue-driven RLS assertions under old and new tenant-scope predicates, plus a scope-free release check, over eight seeded migration mistakes.", "A rebuilt-from-history measurement of the coverage gap: documented counts reproduced exactly at migration 022, and the old predicate's blind spot traced from 0 to 6 to 10 tables.", f"Evidence that commit-time chain allocation removes audit-chain forks (0 failures in {post_trials} concurrent trials) and restores per-tenant availability ({speedup:,.0f}-fold lower median same-tenant wait).", f"A measured, explained cost of definer-helper RLS policies (median {x(qx1k['discovery']['ratio_median'])} to {x(qx1k['club']['ratio_median'])} times an explicit filter at 22,000 events), with set-based and hoisted formulations that remove most of it.", ], "limitationsLine": "Pre-launch system with no real tenants or load, one developer, a loaded laptop, synthetic data, and a private codebase; one access-control gap remains enforced but unproven.", "aiStatement": "Yousof Selim designed the system and its security model and directed the work; much of the implementation and parts of this report were produced with AI coding assistants (Claude, Codex) under his direction and review. The experiments are reproducible from the artifact. This is a technical report and has not been peer reviewed.", "artifact": {"label": "Scripts, SQL, raw results and README (WayClub source is private)", "href": f"/research/{SLUG}/artifact/"}, "sections": sections, "references": references, "evidence": json.load(open(os.path.join(ART, "results", "evidence.json"))), } json.dump(paper, open(OUT, "w"), indent=2, ensure_ascii=False) print("words in sections:", words, "| abstract words:", len(abstract.split()), "| refs:", len(references))