Standard: name/description/length rules — agentskills.io standard.

PG RLS Multitenant Skill — Quick Reference (body <500 lines; full procedure in references/pg-rls-procedure.md)

The tenant convention of the platform

There is exactly one tenant GUC and one tenant column, and every Ottili repository uses them:

What	The one name	How it is set
Session setting	app.company_id	SELECT set_config('app.company_id', '<company uuid>', true) — from the verified server-side session, never from a request body
Table column	company_id	company_id <type> NOT NULL, first in every key and index
Policy	current_setting('app.company_id', true)	the same name in USING and in WITH CHECK

Both guards of a policy have to name the tenant column. USING decides which existing rows the policy applies to, WITH CHECK decides which new row may be written, and PostgreSQL falls back to USING when WITH CHECK is omitted. A policy whose USING names company_id and whose WITH CHECK does not closes the read path and leaves the write path open: no foreign row is readable and a foreign row can still be written. The leak probe cannot see that - it measures what is read. The checker can, and reports it.

app.current_company_id, ottili.company_id, app.current_company and app.current_tenant are legacy spellings. No session of the platform sets them, so a policy keyed on one isolates nobody — the checker reports it and the leak probe measures it. The name lives in one file (guc-names.json, next to this skill), so the decision is recorded once and read by every checker.

When to use this skill (trigger)

Any of these fires it:

• Adding a new tenant-isolated table or view to an Ottili schema.
• A code review mentions company_id, organization_id, or namespace columns without RLS.
• A migration adds a column that becomes the tenant discriminator.
• An incident report says "user X saw user Y's data".
• Reviewing Supabase, Hasura, Prisma, or direct SQL for missing RLS.

Disambiguation

• Fires: new tenant-isolated table/view, company_id/organization_id/namespace columns without RLS, a column becoming the tenant discriminator, 'user X saw user Y data', reviewing Supabase/Hasura/Prisma for missing RLS.
• Does NOT fire for: a single-user app with no tenants, or event delivery idempotency (use outbox-idempotency).

When NOT to use this skill

• Single-tenant databases (no tenant discriminator needed).
• Read-only analytics replicas where RLS is enforced upstream.
• Tables that intentionally share rows across tenants (e.g., global config) — mark them bypassrls explicitly instead of omitting policies.
• A database that is only ever used by one tenant — the skill still applies (the discriminator is still required), but the policy is trivially true.
• A view that is only ever queried by an owner account — that is the exact bypass the skill forbids; security_invoker = true is the fix, not an exception.
• "We encrypt the column instead of using RLS" — encryption is not access control; the skill fires when encryption is proposed as a substitute for tenant isolation.

Decision table — expand/contract migration

Step	Action	Threshold / pitfall
1. Add column	ALTER TABLE t ADD COLUMN company_id uuid	Start NULL if the table is non-empty; adding NOT NULL on a non-empty table without a default blocks
2. Backfill	UPDATE t SET company_id = <owner> WHERE company_id IS NULL	Must run in batches on large tables; record the batch size
3. Constrain	ALTER TABLE t ALTER COLUMN company_id SET NOT NULL + CHECK	CHECK must match the app tenant format (UUID v4)
4. Policy	CREATE POLICY ... USING (company_id = current_setting(...))	Policy must exist before the app depends on isolation
5. Leak test	Run scripts/check_rls_migration.py + scripts/pg-rls-leak-test.sh --server	The three measurements must be 1, 0, 0; any other value blocks the merge
6. Contract	Separate V<n>__<desc>__contract_*.sql migration dropping the old column	N = 4 weeks minimum after expand lands in production; record the date in the migration comment; rollback SQL in a -- ROLLBACK block

Decision table — leak-test outcome

Test case	Expected	If it fails
Same-tenant read	tenant A sees only its rows	Policy expression is wrong — fix the USING clause
Cross-tenant read	tenant A sees ZERO of tenant B's rows	RLS not enabled, or view bypasses it — add FORCE / security_invoker = true
INSERT wrong tenant	rejected by WITH CHECK	WITH CHECK missing or wrong — add it
UPDATE company_id	rejected when a RESTRICTIVE policy is present	RESTRICTIVE policy missing — add it
View bypass	security_invoker=false view exposes cross-tenant rows → FAIL	Set security_invoker = true on the view, or exclude it from RLS scope
Owner bypass	table without FORCE exposes all rows → FAIL	Add FORCE ROW LEVEL SECURITY
service_role bypass	service_role sees everything by design	Never use service_role client-side; restrict it in the auth layer

Worked examples

Good (permissive policy + FORCE + scoped grant):

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
CREATE POLICY orders_tenant_select ON orders
  FOR SELECT
  TO web_app
  USING (company_id = current_setting('app.company_id', true));
GRANT SELECT ON orders TO web_app;

Bad (owner bypass — table created by migration role, no FORCE):

CREATE TABLE notes (id uuid, company_id uuid, body text);
-- migration role can read every tenant's notes until FORCE is added

Bad (view bypass):

CREATE VIEW active_orders AS SELECT * FROM orders;
-- runs as view owner; RLS on orders is skipped

Bad (expand without backfill — old rows silently excluded):

ALTER TABLE orders ADD COLUMN company_id uuid NOT NULL;
-- fails: cannot add NOT NULL without a default; and if it succeeds, old rows are NULL
-- and match no policy — a silent data-loss trap, not a leak

Pitfalls from research (real incidents)

• Owner bypasses RLS unless FORCE is set — a table created by a migration role silently exposes rows to that role's sessions.
• Views are security-definer by default and bypass RLS unless security_invoker = true.
• Missing GRANT before policy → error 42501 before any policy runs.
• service_role key bypasses all RLS — never use it client-side.
• Sub-select race condition between policy evaluation and INSERT can leak rows under concurrent load.

Near-miss triggers (act before the incident)

• A table is created by a migration role and has no FORCE RLS → that role's sessions see all tenants' rows.
• A view is created without security_invoker = true → it runs as the view owner and bypasses RLS.
• GRANT ALL appears on a protected table → silently overrides the policy grant.
• service_role is used in client-side code → bypasses every RLS policy by design.
• A policy uses a sub-select on the same table → the check-then-insert race leaks rows under concurrent load.
• A migration adds the tenant column AFTER data exists, with no backfill → old rows have NULL company_id and match no policy (silent exclusion, not a leak, but a data-loss trap).
• "The leak test passed once, so the policy is fine" — re-run it after every policy change; RLS is not monotonic.

Verification checklist (all must pass before merge)

• Every row table has at least one policy matching its tenant column
• Every policy that writes names company_id in its WITH CHECK clause (or in its USING clause when there is no WITH CHECK)
• FORCE ROW LEVEL SECURITY is set on all non-dev tables
• No GRANT ALL on protected tables
• Leak test harness runs green against a real PostgreSQL (pg-rls-leak-test.sh prints PASS: a_sees_own 1, a_sees_b 0, unset_sees_rows 0, write_leaks 0)
• Migration includes rollback SQL
• The migration can be applied twice (the policy is created inside a DO block that asks pg_policies first)
• Views use security_invoker = true or are excluded from RLS scope

Verification

• Run the offline gate: python3 scripts/check_rls_migration.py <migration.sql> — must exit 0. The tenant convention is configuration, not a constant of the checker: guc-names.json (next to this skill) names the one tenant GUC (app.company_id), the one tenant column (company_id) and the legacy names it rejects. A migration that keys its policy on a legacy name is a finding, because no session of the platform sets that name. The checker reads the shapes the product repositories write: a table protected inside a DO $$ … $$ block, and a policy created with EXECUTE (which writes the setting name with doubled quotes). It also reads the two guards of a policy apart. A policy that writes has to name company_id in the guard that accepts the new row (WITH CHECK, or USING when there is no WITH CHECK), and a policy that reads has to name it in its USING clause. A policy that reads correctly and writes anything is a finding (write path of the policy is not constrained): the read probe cannot see that leak, because no foreign row is readable.
• Run the leak probe against a real PostgreSQL: scripts/pg-rls-leak-test.sh --server --job <JOB> <migration.sql> [table] — must print PASS. The harness applies the migration, seeds two companies as the superuser and probes the table as the application role, which is neither a superuser nor the owner of the table, so row level security really decides what it sees. PASS means four measurements: a_sees_own = 1 (the company sees its own row), a_sees_b = 0 (it sees no row of another company) and unset_sees_rows = 0 (nobody sees a row while the session carries no company) and write_leaks = 0 (the database refused a row of another company). A measurement that is missing is a FAIL, not a zero — a probe that did not run proves nothing. The first three measurements read, the fourth writes: the read path and the write path are guarded by two different clauses of a policy, so a probe that only measured the reads would call a migration with a wide WITH CHECK clause green. --server takes --admin-url and --app-url, or --job to take them from the truth kit; --print-probe-sql and --parse FILE run the same logic without a server, and the verdict itself lives in scripts/check_leak_probe.py. The tenant setting the probe sets comes from the same convention file; --guc NAME overrides it for a one-off probe.
• Run the standard validator of the repository (from the repository root, not from this skill folder): python3 scripts/validate_skills.py — must report OK.
• Full procedure, version table, worked examples, and pitfalls: references/pg-rls-procedure.md.
• Eval prompts with expected behaviour and failure signs: references/EVALS.md.
• Primary sources with URLs and retrieval dates: references/SOURCES.md.

Related skills

• acceptance-harness — a tenant-leak scenario is an acceptance scenario with PASS evidence.
• honest-status — the RLS migration is a status item; mark it VERIFIED only after the leak test ran green.
• long-test-discipline — the leak test is a long test that runs in the foreground.
• module-contract-first — an RLS policy is a contract: enumerate it, version it, block merge on drift.
• problem-json-contracts — a leak is reported as a problem detail, not a silent row.
• outbox-idempotency — the outbox row is tenant-scoped and must survive RLS; the leak test and the outbox leak test share the same tenant-isolation contract.
• shared-tree-git — the migration and the leak-test evidence are committed with a task-id prefix.