Guard Supabase RLS with Tests: Verify Both 'Allow' and 'Deny' with pgTAP, and Stop Authorization Regressions in CI

"We wrote RLS, so authorization is safe"—I don't take that sentence at face value.

Row-Level Security (RLS) is powerful. With a few lines of policy using auth.uid(), you can enforce "you can only read/write your own rows" at the database layer. Unlike app-side filtering such as if (user.id !== row.ownerId), no matter which client it comes from or what SQL it throws, the DB rejects it as the last line of defense. This is RLS's value.

But here's what I've seen happen over and over on the front line.

• The SELECT policy was written, but the UPDATE WITH CHECK was forgotten, and someone could rewrite another person's row to be their own.
• A test for "what should be allowed is allowed" was written, but a test for "what should be denied is denied" wasn't, so a regression that carelessly loosened a policy went unnoticed.
• Complex authorization was offloaded to a SECURITY DEFINER function, and that function became a hole that bypassed RLS and returned all rows.

The terror of RLS comes down to "you think you added a policy, but it leaks through another path" and "you can't notice a regression unless you write deny tests." So my stance is consistent: RLS isn't done when you write it. It becomes trustworthy in production only after you auto-test both 'what should be allowed is allowed' and 'what should be denied is denied' and stop regressions in CI.

This article is an implementation guide to that "guard RLS with tests" methodology. As source material, I'll weave in design decisions from a realtime collaborative match-recording app I built (an Expo + Next.js + Supabase monorepo, where multiple stakeholders—players, team admins, scorers, scouts, operators—touch the same data with different views). In this product I enabled RLS on all 69 public tables and expressed roles, teams, time-limited permissions, and field-level disclosure with 280 policies. And I tested all of them with pgTAP, stopping regressions in CI.

The rules of this article: the specs for RLS, policies, and auth helpers are based on the Supabase official documentation, RLS semantics (USING / WITH CHECK / FORCE / policy combination) on the PostgreSQL official documentation, and assertion functions on the pgTAP official reference (all as of June 2026). Specs can be revised, so always confirm the latest in the official docs before going to production (URLs at the end of the article). The code is arranged for real operation, but secrets are assumed to be in environment variables. RLS becomes trustworthy only when you test it.

Note that this article focuses on "how to test RLS and stop regressions." How to design RLS in the first place—protecting data offline-first, assuming an untrusted client—is covered in the sister article, Postgres RLS × offline-first design assuming an untrusted client. Design over there, verification over here—a complementary relationship.

0. Get this into your head first: USING and WITH CHECK are different things

Before talking about tests, leaving this ambiguous will make you design your tests wrong. An RLS policy has two "expressions," with entirely different roles.

Quoting the PostgreSQL official definitions precisely:

• USING … controls which rows are visible for SELECT, and which existing rows are accessible for UPDATE/DELETE.
• WITH CHECK … controls which rows can be inserted or updated (the constraint new row data must satisfy). If omitted, it defaults to the same as USING.

Here's the first pitfall. UPDATE needs both.

-- ❌ 危険：USING だけだと、行を「掴める」ことは制御できるが、
--    「どんな値に書き換えられるか」は無制限。
--    自分の行を掴んで、user_id を他人のIDに書き換えられてしまう。
create policy "update own profile (broken)"
on profiles for update
to authenticated
using ( (select auth.uid()) = user_id );

-- ✅ 正しい：WITH CHECK で「書き換え後の行」も縛る
create policy "update own profile"
on profiles for update
to authenticated
using ( (select auth.uid()) = user_id )       -- どの行を掴めるか
with check ( (select auth.uid()) = user_id );  -- どんな値に書き換えてよいか

And one more thing the Supabase docs make explicit: to perform an UPDATE you also need a corresponding SELECT policy. This is because UPDATE internally needs to make the target rows visible.

This "asymmetry of USING and WITH CHECK" is exactly the breeding ground for regressions you can't notice without deny tests. A using-only policy stays green with just "read tests." The hole in with check will never be detected unless you write a deny test of "attempting to rewrite into someone else's value should fail."

1. Mental model: RLS tests are "2 axes × 2 directions"

The framework I use when designing RLS tests is simple. For each table and each command, fill in these 4 quadrants.

A mediocre test only writes the left half (allow). 80% of the value of RLS tests is in the right half (deny). Because a regression that loosens a policy—carelessly leaving in using (true), widening a condition too far with OR, deleting with check—only fails on a deny test.

Keep PostgreSQL's default-deny nature in mind too. The instant you ENABLE ROW LEVEL SECURITY, if there's not a single policy, all rows become invisible and unmodifiable. In Supabase's words, "via the publishable key, you can't access any data until you create a policy." In other words, you close first, then open is the RLS way. Tests stand on this premise too and verify "is the hole you think you opened only as wide as you intended."

2. The foundation: enable RLS and write policies (conscious of allow and deny)

Let's place the minimal schema to test. Think with a matches table that simplifies the match-recording app. The rule: a team admin (team_admin) can read/write their team's matches, a scout (scout) can only read, and anyone else can't touch it.

-- 1) まずは閉じる：有効化した瞬間、ポリシーが無ければ全行不可視
alter table public.matches enable row level security;

-- 2) ロール別の GRANT（RLS とは別レイヤーの権限。両方必要）
grant select on public.matches to anon, authenticated;
grant insert, update, delete on public.matches to authenticated;

-- 3) 読みの許可：自チームの試合だけ見える（scout も team_admin も読める）
create policy "read matches of my team"
on public.matches for select
to authenticated
using (
  team_id in (
    select team_id from public.team_members
    where user_id = (select auth.uid())
  )
);

-- 4) 書きの許可：team_admin だけが自チームの試合を作れる
create policy "team_admin can insert matches"
on public.matches for insert
to authenticated
with check (
  exists (
    select 1 from public.team_members
    where user_id = (select auth.uid())
      and team_id = matches.team_id
      and role = 'team_admin'
  )
);

Here I'm also practicing Supabase's official performance know-how. Writing auth.uid() as-is evaluates it per row, but wrapping it in a subquery as (select auth.uid()) evaluates it once at initialization, and the official measurement shows a 94–99% improvement. Indexing policy columns, and making the role explicit with TO authenticated to skip evaluation for non-matching roles, also help.

And the most important defense—FORCE ROW LEVEL SECURITY.

-- テーブルオーナーは既定で RLS をバイパスする。
-- 強制適用して、オーナー権限のコネクションでも RLS を効かせる。
alter table public.matches force row level security;

As the PostgreSQL docs make explicit, a table owner bypasses RLS by default. Furthermore, a superuser and a role with the BYPASSRLS attribute always bypass it. If a migration or some admin connection runs with owner privileges, the accident of "slipping through RLS and leaking in production even though tests pass" happens. FORCE plugs that hole.

The DRY pitfall: "since read and write have the same condition," you'll be tempted to consolidate into one policy with for all. But since SELECT and INSERT are governed by different clauses (USING vs WITH CHECK), for all blurs the intent and makes it harder to separate quadrants in tests. I split read, create, fix, and delete into separate policies and test each independently (SRP: one policy = one authorization decision).

3. Install pgTAP and build the test skeleton

Here's the main topic. pgTAP is a test framework that runs on PostgreSQL, where you write assertions in SQL. In Supabase it's built into the local dev stack, and you can run it with supabase test db.

First, enable the extension. The official docs recommend creating it in a dedicated schema so it doesn't pollute public.

-- public を汚さないよう extensions スキーマに作る
create extension if not exists pgtap with schema extensions;

The skeleton of a pgTAP test always takes this form. Wrapping it in begin … rollback is meaningful—the data and role switches created during a test don't remain in the DB, and each test stays independent and idempotent.

begin;
select plan( 5 );  -- このファイルで実行するアサーション数を宣言

-- ... ここにアサーションを並べる ...

select * from finish();  -- 宣言数と実行数の齟齬を検出して終了
rollback;                -- 副作用を破棄

Declaring the number of tests you plan to run in advance with plan(N) is the pgTAP way. This lets you detect premature failure like "an exception was thrown partway and only 3 ran." select * from finish() does the final tally and TAP output.

Wrapping in begin/rollback is the most important pattern for guaranteeing test independence. Create match data, switch users, attempt writes—all of it is erased by rollback, so it doesn't depend on test order and gives the same result no matter how many times you run it in CI (reproducibility, idempotency).

4. The core: switch identity with request.jwt.claims and assert both allow and deny

The heart of RLS testing is to try policies by "impersonating another user." For requests via PostgREST, Supabase injects role (anon/authenticated) and the JWT claims as GUCs (runtime configuration parameters). In tests, you can simulate any user by setting these by hand.

4.1 The low-level API to switch identity

-- 認証済みユーザー u1 になりすます
set local role authenticated;
set local request.jwt.claims = '{ "sub": "00000000-0000-0000-0000-000000000001", "role": "authenticated" }';
-- この状態では auth.uid() が上記 sub を返す

-- 未認証（anon）に戻す
set local role anon;
set local request.jwt.claims = '';

Two points.

1. Use set local. Adding local makes it valid only within the current transaction, and rollback reliably reverts it. With set (global) it persists on the connection and contaminates between tests.
2. auth.uid() reads the sub of request.jwt.claims, so swapping claims makes you "another person." If you put authorization info in app_metadata, including it here reproduces a policy's auth.jwt() reference too.

Why app_metadata: Supabase's docs state clearly that a JWT's user_metadata (= raw_user_meta_data) can be rewritten by the user themselves, so don't use it for authorization; app_metadata (= raw_app_meta_data) cannot be rewritten by the user, so it's appropriate as the place for authorization info. Reproduce this distinction in tests too.

In a real project, writing these raw claim settings every time is tedious and error-prone, so using the helpers distributed by supabase/tests (tests.create_supabase_user(), tests.authenticate_as(), tests.authenticate_as_service_role(), tests.clear_authentication(), tests.get_supabase_uid(), etc.) makes it declarative. Below, to show the mechanism, I present the raw claims first, then the helper version.

4.2 The allow path (Allow): what should pass, passes

begin;
select plan( 6 );

-- 前提データ：チーム t1 と、その team_admin である u1、scout である u2
-- （実際は seed か insert で用意。auth.users への投入はヘルパ推奨）
insert into public.team_members (user_id, team_id, role) values
  ('00000000-0000-0000-0000-000000000001', 't1', 'team_admin'),
  ('00000000-0000-0000-0000-000000000002', 't1', 'scout');

-- === team_admin (u1) として ===
set local role authenticated;
set local request.jwt.claims = '{ "sub": "00000000-0000-0000-0000-000000000001", "role": "authenticated" }';

-- 許可：自チームの試合を作れる
select lives_ok(
  $$ insert into public.matches (id, team_id, name) values ('m1', 't1', '初戦') $$,
  'team_admin は自チームの試合を作成できる'
);

-- 許可：作った試合が読める
select results_eq(
  $$ select count(*)::int from public.matches where team_id = 't1' $$,
  $$ values (1) $$,
  'team_admin は自チームの試合が見える'
);

-- === scout (u2) として ===
set local request.jwt.claims = '{ "sub": "00000000-0000-0000-0000-000000000002", "role": "authenticated" }';

-- 許可：scout も自チームの試合は読める
select isnt_empty(
  $$ select id from public.matches where team_id = 't1' $$,
  'scout は自チームの試合を閲覧できる'
);

select * from finish();
rollback;

lives_ok(sql, desc) verifies no exception is thrown, results_eq(sql, expected, desc) verifies the result set matches exactly (content and row order), and isnt_empty / is_empty verify there are / aren't rows. The allow path is confirmation that "it passes as expected." Up to here, many people write tests.

4.3 The deny path (Deny): what should be rejected is rejected ← this is the main event

begin;
select plan( 4 );

insert into public.team_members (user_id, team_id, role) values
  ('00000000-0000-0000-0000-000000000001', 't1', 'team_admin'),
  ('00000000-0000-0000-0000-000000000003', 't2', 'team_admin');  -- 別チーム
insert into public.matches (id, team_id, name) values ('m1', 't1', '初戦');

-- === 別チーム t2 の team_admin (u3) として ===
set local role authenticated;
set local request.jwt.claims = '{ "sub": "00000000-0000-0000-0000-000000000003", "role": "authenticated" }';

-- 拒否：他チームの試合は見えない（0件）
select is_empty(
  $$ select id from public.matches where team_id = 't1' $$,
  '他チームの試合は閲覧できない'
);

-- 拒否：他チームの試合を更新しようとしても、対象行が見えないので 0 行更新（漏洩なし）
select results_eq(
  $$ with u as (update public.matches set name = 'のっとり' where id = 'm1' returning 1)
     select count(*)::int from u $$,
  $$ values (0) $$,
  '他チームの試合は更新できない（0行）'
);

-- === scout (u2) として ===
set local request.jwt.claims = '{ "sub": "00000000-0000-0000-0000-000000000002", "role": "authenticated" }';
insert into public.team_members (user_id, team_id, role) values
  ('00000000-0000-0000-0000-000000000002', 't1', 'scout');

-- 拒否：scout は試合を作れない → WITH CHECK 違反で 42501 が飛ぶべき
select throws_ok(
  $$ insert into public.matches (id, team_id, name) values ('m2', 't1', '不正作成') $$,
  '42501',
  'new row violates row-level security policy for table "matches"',
  'scout は試合を作成できない（RLS違反でエラー）'
);

-- === 未認証 (anon) として ===
set local role anon;
set local request.jwt.claims = '';

-- 拒否：anon には何も見えない
select is_empty(
  $$ select id from public.matches $$,
  '未認証ユーザーには一切見えない'
);

select * from finish();
rollback;

This is the core of RLS testing. Let me organize the points of note.

• throws_ok(sql, '42501', msg, desc) … when INSERT/UPDATE violates WITH CHECK, PostgreSQL throws SQLSTATE 42501 (insufficient_privilege) with new row violates row-level security policy .... Assert "it's rejected" precisely by error code.
• The deny of SELECT/UPDATE/DELETE manifests not as an exception but as "the target rows aren't visible" = "0 rows." So the other-team update is confirmed with "0 rows updated" via results_eq, not throws_ok. This is easy to miss—"no error = OK" is wrong; "being 0 rows" is the correct deny.
• anon's is_empty … confirm that everything disappears when unauthenticated. This is verification of the close-then-open design.

What happens if you don't write deny paths: suppose someone rewrites the read matches of my team policy to using (true). The allow tests (4.2) all stay green (they pass, since it's "more visible"). The only thing that fails is 4.3's is_empty('他チームの試合は閲覧できない'). A deny test is the sole sensor that catches a regression loosening authorization.

4.4 Write declaratively with helpers

Raw claims are useful for understanding the mechanism, but in a production test suite, make it readable with Supabase's distributed helpers.

begin;
select plan( 2 );

select tests.create_supabase_user('admin@t1.test');
select tests.create_supabase_user('scout@t1.test');

-- team_admin として
select tests.authenticate_as('admin@t1.test');
select lives_ok(
  $$ insert into public.matches (id, team_id, name) values ('m1', 't1', '初戦') $$,
  'team_admin は試合を作れる'
);

-- scout として（拒否）
select tests.authenticate_as('scout@t1.test');
select throws_ok(
  $$ insert into public.matches (id, team_id, name) values ('m2', 't1', '不正') $$,
  '42501', null,
  'scout は試合を作れない'
);

select tests.clear_authentication();
select * from finish();
rollback;

authenticate_as() internally sets role and request.jwt.claims (with the correct sub), so "as whom" is declared in one line. The readability for the reader is on another level (ETC: even adding identities, the diff stays localized).

5. Test the policy "meta": stop structural drift

In addition to row-data allow/deny, structural-level tests of "does the policy even exist, and does it apply to the intended roles and commands" are also effective. pgTAP has RLS-specific assertions.

begin;
select plan( 4 );

-- このテーブルに「このポリシー集合」が過不足なく存在することを固定
select policies_are(
  'public',
  'matches',
  array[
    'read matches of my team',
    'team_admin can insert matches'
  ],
  'matches のポリシー集合が想定どおり'
);

-- ポリシーが想定ロールに効いている
select policy_roles_are(
  'public', 'matches', 'read matches of my team', array['authenticated'],
  '読みポリシーは authenticated のみ'
);

-- ポリシーが想定コマンドに効いている
select policy_cmd_is(
  'public', 'matches', 'team_admin can insert matches', 'INSERT',
  '作成ポリシーは INSERT 用'
);

-- RLS が有効であること自体も検査（has_table 等と同系統の構造テスト）
select has_table('public', 'matches', 'matches テーブルが存在');

select * from finish();
rollback;

• policies_are(schema, table, policies[], desc) … verifies that the table's RLS policy set matches the expected set exactly. Immediately fails a regression that deleted / renamed a policy.
• policy_roles_are(...) … pins the roles a policy affects. Catches a regression that mistakenly widened TO authenticated to TO anon.
• policy_cmd_is(...) … pins the command a policy affects (SELECT/INSERT/...).

These are complementary to the "row allow/deny" tests. The former guards behavior, the latter structural drift. At a scale of 280 policies, "pinning the set" with policies_are becomes a source of reassurance during refactoring.

6. The biggest pitfall: SECURITY DEFINER functions can bypass RLS

Writing complex authorization (multi-level team hierarchies, time-limited permissions, field-level disclosure) directly into a policy expression bloats the expression and kills readability. Offloading it to a helper function is the right call—but here lies RLS's biggest landmine.

A SECURITY DEFINER function runs with the definer's (often the owner's) privileges. As the PostgreSQL docs say, the expression is evaluated with the executing user's privileges (unless you use a security-definer function). Flip that around: inside a SECURITY DEFINER function, you have the owner's privileges, not the caller's, and the owner bypasses RLS by default. In other words,

An unguarded SECURITY DEFINER function can become a hole that slips past RLS and returns all rows.

Even more notorious is search_path hijacking. If a function doesn't fix its search_path, an attacker can create a same-named table or function in another schema and make the high-privileged SECURITY DEFINER operate on an unintended target. This is a classic privilege-escalation pattern in PostgreSQL.

The countermeasures are a fixed playbook.

-- ✅ 安全な SECURITY DEFINER の書き方
create or replace function public.is_team_admin(p_team_id text)
returns boolean
language sql
stable
security definer
set search_path = ''   -- ★ search_path を空に固定（注入を封じる）
as $$
  -- すべて完全修飾（public.team_members など）で参照する
  select exists (
    select 1 from public.team_members
    where user_id = (select auth.uid())
      and team_id = p_team_id
      and role = 'team_admin'
  );
$$;

-- 実行権限は必要なロールにだけ付与（最小権限）
revoke all on function public.is_team_admin(text) from public;
grant execute on function public.is_team_admin(text) to authenticated;

The rules are these.

1. Fix search_path to empty with set search_path = ''. Even public,pg_temp is a source of carelessness, so write objects inside the function all fully-qualified (public.team_members, auth.uid()).
2. Ask whether you really need DEFINER. Use it only when there's a legitimate reason to bypass RLS (avoiding recursive policies, referencing helper tables). If you don't need it, use SECURITY INVOKER (the default) and let RLS take effect (YAGNI / least privilege).
3. revoke ... from public + grant execute limited to the necessary roles. Don't make a high-privileged function anyone can call.
4. Validate auth.uid(), the role, and MFA if needed at the function entrance. The match-recording app's RPCs are written with the SECURITY DEFINER + search_path-fixed playbook, always checking auth.uid(), role, and require_mfa() at the entrance.

And—test SECURITY DEFINER functions with pgTAP intensively.

begin;
select plan( 2 );

select tests.create_supabase_user('admin@t1.test');
select tests.create_supabase_user('outsider@t9.test');

insert into public.team_members (user_id, team_id, role)
  values (tests.get_supabase_uid('admin@t1.test'), 't1', 'team_admin');

-- 許可：team_admin に対して true を返す
select tests.authenticate_as('admin@t1.test');
select is( public.is_team_admin('t1'), true, 'team_admin には true' );

-- 拒否：無関係ユーザーに対して true を返さない（=DEFINERで漏れていない）
select tests.authenticate_as('outsider@t9.test');
select is( public.is_team_admin('t1'), false, '部外者には false（DEFINERで全行を漏らさない）' );

select * from finish();
rollback;

is(have, want, desc) compares two values with IS NOT DISTINCT FROM. The point here is to pin that an outsider gets false. If someone breaks the query inside the function and widens the RLS-bypass hole, this is(..., false, ...) fails. A deny test for a DEFINER function is the highest-value deny test.

7. Where to enforce authorization: the division of roles among app / RPC / RLS

To the question "should RLS do everything," I have a clear stance. The last line of defense is always RLS. But for UX and performance, you also place filtering on the app side. The two should be redundant and must not contradict.

Client-side filtering is not the object of trust. "Don't show the update button to a scout" is kind, but the API can be hit even without the button. So it's meaningless without RLS. Conversely, if RLS exists, the DB rejects whatever the client does. Place the center of gravity of trust at the bottom (the DB), and keep the top (the UI) for convenience—keep this principle and the test priorities decide themselves (the pgTAP deny tests are top priority).

In the match-recording app, sensitive operations (permission changes, disclosing player info) are gated by email-OTP MFA. custom_access_token_hook injects mfa_verified into the JWT, and the RPC entrance's require_mfa() enforces it. Disclosing player info to a scout is expressed with a field-level grant of "the player's own approval ∩ the team admin's approval" plus an append-only audit log. These too are of course pinned with pgTAP deny paths: "deny if MFA not verified" and "not disclosed with only one of the two approvals."

8. Stop regressions in CI: gates for RLS coverage and migration safety

Tests can't stop regressions if they only "run locally." They become a barrier only when you enforce them in CI and make it un-mergeable unless green.

8.1 Run pgTAP in CI

Stand up the Supabase local stack in GitHub Actions and run supabase test db.

# .github/workflows/db-tests.yml（要点のみ）
name: db-tests
on: [pull_request]
jobs:
  pgtap:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v1
        with: { version: latest }
      - run: supabase start          # ローカル Postgres + マイグレーション適用
      - run: supabase test db        # supabase/tests/**/*.sql の pgTAP を実行

supabase test db runs the SQL under supabase/tests/ as pgTAP and exits non-zero if even one fails. This verifies both allow and deny per PR.

8.2 Make RLS coverage a "gate"

Here's my core claim. Enforce in CI that "every table with RLS enabled has a policy test." You machine-check the existence of tests. If you enable row level security on a new table but forget to write the test, CI fails—this is the RLS-coverage gate.

#!/usr/bin/env bash
# rls-coverage-gate.sh —— RLS有効テーブルに pgTAP テストがあるか検査
set -euo pipefail

# 1) RLS を有効化している public テーブルを列挙
mapfile -t rls_tables < <(
  psql "$DATABASE_URL" -tAc "
    select tablename from pg_tables t
    join pg_class c on c.relname = t.tablename
    where t.schemaname = 'public' and c.relrowsecurity = true
    order by 1;"
)

missing=()
for tbl in "${rls_tables[@]}"; do
  # 2) その表名に言及する pgTAP テストファイルが存在するか
  if ! grep -rqlF "$tbl" supabase/tests/; then
    missing+=("$tbl")
  fi
done

if (( ${#missing[@]} > 0 )); then
  echo "❌ RLS有効だがテスト未整備のテーブル:"
  printf '   - %s\n' "${missing[@]}"
  echo "→ supabase/tests/ に許可・拒否の pgTAP を追加してください"
  exit 1
fi
echo "✅ RLS有効な全テーブルにテストが存在します"

This is a coarse heuristic (grepping the table name), but it reliably stops the worst pattern of "RLS enabled but zero tests." Since it takes tables where pg_class.relrowsecurity is true as the source of truth, the targets automatically grow as you add tables (ETC: no need to update a ledger by hand). In the match-recording app, this idea lets CI guarantee that all 69 RLS tables have tests.

8.3 Migration safety: stop dangerous DDL with squawk

RLS regressions happen not only via policies but via how migrations are written. drop policy, alter table ... disable row level security, long-locking DDL—relying on review alone will eventually let one slip. squawk is a static linter for PostgreSQL migrations that mechanically detects dangerous patterns.

  squawk:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: npx squawk@latest supabase/migrations/*.sql
        # 例：危険なロック取得、NOT NULL 追加時の全行書き換え、
        #     型変更などを警告/エラーにできる

squawk itself doesn't have RLS-specific rules, but it functions as a first barrier that "stops destructive, high-lock DDL in CI." Adding your own rule that blocks disable row level security and unguarded drop policy as forbidden patterns via grep physically prevents detaching RLS.

8.4 Guard in many layers with 11 checks

The match-recording app's CI guards in many layers with 11 GitHub Actions—type checking, pgTAP, RLS coverage, migration safety checks (squawk), schema-drift detection (detecting divergence between local migrations and the remote schema), and more. Each one is single-purpose (SRP), but combined they nearly block the paths by which an authorization regression reaches production.

Why many layers: a single check always has a hole. pgTAP guards behavior, policies_are structure, RLS coverage "the absence of tests," squawk dangerous DDL, and drift detection "divergence between local and production." Betting on "some one of multiple independent sensors going off" is the playbook of reliability design (eliminating single points of failure).

9. A collection of practices that pay off in operation

Let me summarize the fine but important points that paid off repeatedly in real combat.

• Cover the deny paths by "quadrant": mechanically fill in "self = allow / others = deny / unauthenticated = deny" per table × command. Easy to miss: UPDATE's WITH CHECK (value rewrite) and DELETE.
• Don't mix up "0 rows" and "exception": the deny of SELECT/UPDATE/DELETE is 0 rows, and a WITH CHECK violation of INSERT/UPDATE is an exception (42501). Use the right assertion (is_empty/results_eq vs throws_ok).
• FORCE ROW LEVEL SECURITY on all tables: plug the bypass on owner connections. Since tests tend to run with owner privileges, without FORCE even tests slip past RLS and turn falsely green.
• SECURITY DEFINER is minimal, search_path='', fully-qualified, least-privileged. And always add deny tests.
• Audit logs are append-only: make disclosure and permission changes INSERT-only policies, and forbid UPDATE/DELETE. Pin "can write the log / can't delete it" with pgTAP too.
• Update tests in the same PR as the migration: a rule making policy changes and test changes the same commit. RLS changes must always come with pgTAP—make this a CI gate, not a culture.
• Be thorough with begin/rollback: keep tests side-effect-free and idempotent. Use set local and forbid global set.

10. Summary: RLS testing cheat sheet

A quick reference for when you're unsure.

• Before writing: enable row level security + force row level security. Close first, then open only the necessary holes.
• Policy design: USING = rows you can read, WITH CHECK = rows you can write. UPDATE needs both + a corresponding SELECT. Wrap with (select auth.uid()).
• Tests are 2 axes × 2 directions: read/write × allow/deny. 80% of the value is the deny path.
• Impersonation: set local role + set local request.jwt.claims (or tests.authenticate_as()). Wrap in begin/rollback for independence and idempotency.
• Assertions: allow = lives_ok/results_eq/isnt_empty, deny (read) = is_empty/"0 rows", deny (write) = throws_ok(..., '42501', ...), structure = policies_are/policy_roles_are/policy_cmd_is, function = is(...).
• DEFINER functions: ask whether you really need them → if so, search_path='' + fully-qualified + least-privileged + a deny test returning false to an outsider.
• Stop in CI: make supabase test db mandatory, detect the absence of tests with an RLS-coverage gate, stop dangerous DDL with squawk, and detect schema drift. Guard in many layers.

RLS looks like "authorization is complete in a few lines of policy," but in reality it's the work of building "a mechanism that automatically re-verifies both allow and deny every time a regression occurs." In a world where leaving in one line of using (true) leaks everything, being correct when you changed it matters far more than being correct when you wrote it. What guarantees that is not human review but deny tests and CI gates.

I develop solo with generative AI (Claude Code) as my partner, but authorization is the one thing I never let through on "probably fine." In the match-recording app, I verified all 69 RLS tables and 280 policies from both allow and deny sides with pgTAP, enforced RLS coverage and migration safety in CI, and pinned the holes of SECURITY DEFINER functions with deny tests too. Guard zero-trust RLS with full-coverage pgTAP—on this I have no hesitation.

"Your Supabase—is its RLS really denying what should be denied?" From that verification, through design review and building CI gates, I'll accompany you end-to-end. First, why not put your current policies through "deny tests" together? Feel free to reach out.

References (official documentation)

• Row Level Security (Supabase official) — enable row level security, CREATE POLICY (USING/WITH CHECK), auth.uid()/auth.jwt(), app_metadata vs user_metadata, performance know-how
• pgTAP (Supabase official) — enabling the extension, the begin/plan/finish/rollback pattern, policies_are, etc.
• Advanced pgTAP Testing (Supabase official) — the tests.create_supabase_user() / authenticate_as() helpers, examples of lives_ok/results_eq/throws_ok
• Row Security Policies (PostgreSQL official) — USING/WITH CHECK semantics, PERMISSIVE/RESTRICTIVE combination (OR/AND), FORCE ROW LEVEL SECURITY, owner/superuser/BYPASSRLS bypass
• pgTAP Documentation — the function reference for plan/finish/ok/is/results_eq/throws_ok/lives_ok/is_empty/policies_are/policy_roles_are/policy_cmd_is