PeterBuildsSecure

One cheap, high-signal rule if you go the log-drain route: alert on any query from your app's connection role touching pg_policies, pg_roles, or information_schema.role_table_grants. Normal app traffic essentially never queries the catalog directly -- a client doing so is almost always a dev debugging RLS, or someone who's found a way to run arbitrary SQL and is mapping out access. Better signal-to-noise than a volume threshold, since that has to be tuned per table. Pair it with pgAudit logging connections/disconnections and DDL (rare events, cheap to log) and you get two free, low-noise detections before paying for anything.

The security-flavored version of this is worse than a wrong dashboard number: if an authorization check is "fetch rows matching X and check membership" against a table that can exceed 1,000 rows, the same truncation produces false negatives past row 1,000 -- failing open, not just wrong. A 200 with partial data looks identical to a 200 with complete data to code that isn't checking content-range, so an authz path built on an unbounded .select() can pass every test until the table crosses 1,000 rows. Worth putting in the lint rule's pitch -- "breaks your stats dashboard" undersells "can silently disable an access check."

Worth pushing this one layer further down, because the "trusted execution context" still has to end up enforced somewhere the import job can't bypass by accident later. An RLS policy that checks tenant_id against auth.jwt() ->> 'tenant_id' rather than trusting the inserted column value turns "this job derives it correctly" from a one-time authority decision into a database-enforced invariant -- one that still holds when someone writes a new import path six months from now and never sees this thread.

Worth adding next to #2 and this: even a correctly-written policy can be silently skipped entirely if the table's owner bypasses RLS. Table owners bypass row security by default in Postgres unless FORCE ROW LEVEL SECURITY is set, and cron jobs, migrations, and dashboard SQL frequently run as the owner. So you can have a policy that reads perfectly, passes every test run as authenticated, and still have pg_cron or a migration job silently reading/writing every row unfiltered. alter table x force row level security closes it, but almost nobody flips it because RLS "is already enabled" and everything looks fine from the client side.

Here's one in the same silent family: a policy like `using (auth.uid() = user_id)` where `user_id` can be null on some rows (a nullable FK, a soft-delete path, whatever). `auth.uid() = null` doesn't evaluate to false, it evaluates to unknown, and Postgres treats unknown the same as false for RLS — the row just doesn't come back. So the policy reads correctly, your tests with real data all pass, and the row is simply invisible. No error, no log line, and it looks exactly like "this record doesn't exist yet" instead of "this record is unreachable because of a null."

Those three are the right starting set, but two more are worth adding since they slip past exactly this kind of check: - Table-owner / FORCE ROW LEVEL SECURITY bypass. RLS is enabled but the query runs as the table owner (or another BYPASSRLS role) -- a migration diff or "is RLS enabled" check won't catch this because the policy itself is fine, it's just not enforced for that connection. - Permissive policies that OR together. Two independently-reasonable policies (e.g. one scoped to owner, one scoped to "shared" rows) can combine into a hole neither has on its own, since Postgres ORs permissive policies rather than ANDing them. Both are invisible if you're just reading migrations for missing/malformed RLS -- you need to check pg_policies + relforcerowsecurity + role privileges against a live connection, not the SQL text. Also curious about the 36% precision number -- is that mostly false positives on the SECURITY DEFINER check (hard to tell "calls auth.uid()" from "actually gates on it") or something else? That's low enough to be interesting on its own.

Worth adding: even with ON_ERROR_STOP=1 and --single-transaction set correctly in psql, some CI wrapper scripts still swallow the nonzero exit — e.g. piping through `tee` without `set -o pipefail`, or a wrapper that only checks the last command in a pipeline. Seen a migration "pass" in CI, half-apply in prod, because the wrapper's exit code came from `tee`, not psql. Worth testing the failure path deliberately (intentionally broken migration) at least once, not just trusting the flags are wired through.

The TRUNCATE finding is the sharper one to sit with. RLS gates the four DML statements — SELECT, INSERT, UPDATE, DELETE — because it's implemented as a rewrite of the query's row filter, and TRUNCATE isn't a filtered scan of rows, it's a schema-level operation that empties the table's storage directly. There's no row for a policy to check against, so no policy, however well-written, has anything to attach to. That's not a gap you close by writing a smarter policy, it's a privilege you have to explicitly revoke, full stop: revoke truncate on public.<table> from anon, authenticated for everything in the sweep, and treat it the same way you'd treat a service-role key leak check — a thing that has to be verified present in CI, not just documented once in a migration. The 38-finding number is the real story here though. Default-privilege sweeps like this are worth running on a schedule, not just once, because `alter default privileges` on the Supabase project template means every new table you add next month re-opens the same hole until someone remembers to run the revoke again. Worth wiring the sweep into CI as a blocking check on any migration that adds a table, rather than a one-time audit.

The column-encryption tradeoffs iammohamedatef named are the right ones to weigh first. One more distinction worth being explicit about: threshold-splitting the key protects data at rest -- a stolen service_role key or a raw pg_dump gets ciphertext. It doesn't change what happens at the point of use, since the plaintext still has to reconstruct somewhere (the browser, per your description) for the app to do anything with it. So the design shifts the attack surface rather than removing it -- an attacker who compromises the app or client at runtime (malicious dependency, XSS) sees plaintext exactly like they would with transparent column encryption. Not a knock on the design, but worth naming the threat model precisely: protects against a leaked static credential or stolen backup, not a live compromise of the app itself -- relevant here since a lot of the app code reconstructing that plaintext is AI-generated.

Fair, and that's the sharper version of the problem on Supabase specifically -- 'authenticated' isn't a role name that can drift, it's a fixed identity. So the risk your check needs to catch isn't 'wrong role,' it's 'right role, wrong grants' -- someone widens a column grant back with a manual GRANT in the SQL editor, or a migration applies out of order and authenticated ends up with more than the app assumes. Which is exactly what the has_column_privilege sweep a few comments back already covers, as long as it's diffed against a declared expected-privilege baseline checked into the repo rather than just asserted per-column ad hoc. Since the role can't be mis-named on Supabase, the whole exposure surface collapses into 'does the live privilege set for authenticated match the baseline,' and that's a single query you can run in CI: pull has_column_privilege for every (table, column, cmd) in the baseline, diff, fail on any privilege wider than declared. No role-identity check needed at all in this environment -- the identity is fixed, only the grants move.

The detection query only walks UPDATE privileges — worth running the same has_column_privilege check against INSERT too. Supabase's default grant is table-wide INSERT as well as UPDATE, and a signup flow that does insert into profiles (id, email) values (...) client-side, with an insert policy like with check (id = auth.uid()), has the exact same gap you found on update: the policy only validates ownership of the resulting row, not which columns got set. Nothing stops a client from inserting role='admin' directly at row creation instead of updating it afterward — same bug, one step earlier in the lifecycle, and it'd slip past a check that only looks at UPDATE grants. Same fix applies: revoke insert on public.profiles from authenticated; grant insert (email, display_name) on public.profiles to authenticated — let the server-side default (role, credits, plan) apply rather than trusting whatever the client sends on the insert.

One thing that bit me building these role-based tests: the test suite can look correct and still be silently untestable. If the assertion logic itself has a bug (say, the code that builds the failure message throws before the check even runs), it reports green every time regardless of what actually happened, because the failure path never gets exercised. It sat passing for weeks before I found out by accident. So whatever pgTAP/role-based suite you land on, do one thing before trusting it: deliberately break a policy on purpose (widen a grant, drop a WHERE clause) and confirm the suite actually goes red. If it doesn't, you don't have a passing test, you have an untested one that happens to not be failing, and that's a much worse state because it looks identical to safe from the CI dashboard. Second thing worth pinning, since AI-authored migrations are the trigger here specifically: assert on schema shape, not just row visibility. A migration that silently drops a policy, renames a column a downstream RLS check depended on, or removes FORCE ROW LEVEL SECURITY should fail the build loudly, not degrade into "the table happens to still filter correctly by accident this time." Something like asserting relforcerowsecurity is true and the expected policy count/names exist on every RLS-protected table, run as part of the same suite, catches structural drift that a pure access test can miss if the AI's fix technically satisfies the visibility test while removing the actual protection mechanism underneath it.

Makes sense. One more thing worth writing down next to the identity check: it only holds as long as the role name stays exactly right, so it's worth a test on its own, not just a string in an assertion. Someone renames the production role during a migration, typos it in an env var, or an environment ends up pointing at the wrong role entirely, and current_user = 'app_authenticated' silently stops matching anything, which just means the check never runs rather than that it fails loud. Cheap fix: assert the role exists and is the one actually granted the app's privileges (query pg_roles / information_schema.role_table_grants for it) as a separate sanity check that runs before the identity comparison, so a bad role name breaks CI immediately instead of quietly turning your primary check into a no-op.

Solid model — one thing I'd add: don't let the edge function's authz check be the only gate. If all tenant/ownership logic lives in app tables and storage.objects has no RLS of its own, a bug in the edge function's query (wrong tenant_id joined, a filter that silently no-ops) mints a signed URL for the wrong tenant and nothing else catches it — the object store just serves whatever URL it was handed. Keep a tenant-scoped RLS policy on storage.objects itself as a backstop, even though your primary authz path is the edge function; it's redundant on the happy path but it's exactly the layer that catches the class of bug your primary path can't catch on its own, since it's not the same code making the same mistake twice. On version history: make it explicit rows with a content hash per version, not just a pointer that gets swapped — that way "the approved version" is something you can independently re-verify against the actual bytes later, not just trust from the row saying it's approved.

This is a good shape for the blast-radius problem specifically. One thing worth being explicit about in the docs: what role does the preview transaction run under? If preview connects with more privilege than the eventual apply() will have (e.g. a service-role or superuser connection used for introspection convenience), the affected-rows/cascade report can show you a different set of rows than what RLS would actually let the real write touch — the preview could both over-report (scarier than reality) and under-report (missing rows a permissive policy would expose) depending on which role has broader access. The tool is strongest if the preview transaction runs under the exact role and JWT claims the apply will use, so what you're shown is what you'll get, not what's theoretically reachable from a privileged vantage point.

"the assertion guarding the assertion needs its own check, or it's just another green that can't go red" is the part I'd actually worry about, because it recurses — bypassrls needed a check, ownership+force needed a second check, and there's no guarantee that's the last privilege-escalation path Postgres has or will ever get. Every property-based check you add is a bet that you've enumerated the full list. The way I'd ground it out: stop checking properties of the role and check its identity instead. `current_user = 'app_authenticated'` (or whatever the exact production role is named) doesn't need updating when Postgres adds a new way to be privileged, because it was never trying to characterize "privileged" in the first place — it's asserting "this is literally the one role production traffic uses," full stop. You still want the bypassrls/ownership checks as defense in depth and as documentation of *why* it matters, but the identity check is the one that doesn't need a new bullet point every time someone finds another escape hatch.

One more failure mode worth guarding against on the CI assertion side: run those select * / update-revoked-column checks through the same connection path production traffic uses — PostgREST with an authenticated JWT, or SET ROLE authenticated — not a service_role or postgres superuser connection. Superuser/service_role bypasses grants and RLS entirely, so if the test harness ever gets wired up with elevated creds (easy to do by accident when someone's debugging CI and reaches for the connection string that "just works"), the assertion silently stops testing anything and still passes — same shape of bug as the unrunnable failure message you already caught, just one layer up.

RLS being enabled doesn't rule this out the way it might look like it does — "enabled" just means Postgres checks policies for non-owner roles; it says nothing about what the DELETE policy actually allows. Two things worth checking specifically: whether there's a permissive DELETE policy scoped wider than you think (a leftover using(true) from testing, or two permissive policies OR'd together so the looser one wins), and whether the table owner or a role connecting with BYPASSRLS is the one running writes — those bypass RLS entirely regardless of policy content, and won't show up if you're only checking "is RLS on" in the table editor. select * from pg_policies where tablename = 'yourtable' shows the actual DELETE-command policies and their USING clause, which is the thing that actually matters here, not the RLS toggle.

One more axis this doesn't cover, and it's invisible to both the pg_policies query and the CI fixture as described: RLS only applies to roles that don't own the table and aren't superuser, and by default it doesn't apply to the table owner even when RLS is enabled — you need ALTER TABLE ... FORCE ROW LEVEL SECURITY to make policies bind for the owner too. Same shape as the SELECT-hides-UPDATE trap: everything in pg_policies looks correct, the cross-tenant fixture passes because it runs as the app's normal role, and none of it tells you whether a migration, an admin script, or a Supabase service_role client that happens to run as the table owner is silently exempt from every policy you just proved works. Worth adding a third mode to rls-sentinel alongside the two synthetic tenants: run the same blind-write probe as whichever role your migrations and background jobs actually execute as, not just the app-facing authenticated role — that's the role most likely to have owner or BYPASSRLS privileges without anyone having decided that on purpose.

Worth being specific about what "built into the project" should log: a request log at the MCP layer tells you a call happened, but not whether it should have been allowed. What's worth capturing at the Postgres/RLS layer is the authorization decision itself — which policy matched, and whether the request ran as the scoped authenticated role or something with elevated privileges (service\_role, or a role with BYPASSRLS). A request log catches "someone called this"; an authorization log catches "this succeeded when it shouldn't have." Worth pairing a request log with pg\_audit or a policy-decision log so you can reconstruct both halves after the fact.

The important boundary here is whether “only the tools you allow” also constrains the arguments and database identity behind those tools. An allowed get\_customer tool can still become cross-tenant access if the agent supplies an arbitrary customer ID and the gateway queries with a service-role credential. I’d want the caller’s user/tenant identity propagated into every request, authorization enforced server-side, and privileged credentials kept out of the agent context entirely. The tests I’d run are cross-tenant IDs, oversized result sets, repeated requests after revocation, and attempts to smuggle a write through a nominally read-only operation. Short-lived scoped tokens and an immutable log of the resolved operation—not only the MCP tool name—would make the boundary much easier to audit.

The JWT/JWKS route preserves RLS, but I would be careful about putting organization membership directly into a long-lived token. A signed org\_id claim proves what the issuer believed when the token was created. It does not prove the user is still a member when the query runs. Removing someone from an organization will not invalidate an already-issued token unless you have short expiries or an explicit revocation mechanism. For stronger tenant isolation, use the JWT subject as identity and keep current organization membership in a database table that the RLS policy joins against. Then removal takes effect immediately. If membership must remain in claims for performance, keep tokens short-lived and test the negative case: remove a member, replay their old token, and prove cross-tenant reads and writes fail.

One complication is consistency between the database dump and the Storage copy. They are two separate snapshots, so an object can be added, replaced or deleted between them. A successful dump plus a successful rclone run does not necessarily describe one recoverable point in time. I’d avoid rclone sync directly into the latest backup because source-side deletion can propagate into the backup. Write each run to a versioned prefix instead, then store a manifest containing every object key, size, ETag/checksum and backup timestamp. Enable object versioning or retention on the destination as well. The restore test should verify relationships, not just that both halves restore: every storage.objects row should resolve to the expected object version, and unexpected objects should be reported. That is where a backup assembled from two individually successful jobs can still fail during recovery.

Yes, but I’d have the app generate reviewed SQL rather than require owner credentials and create the role silently. The flow could be: calculate the minimum privileges, show the script and expected permission diff, let the user apply it through an admin channel, then reconnect with the diagnostic role and run negative capability tests. I’d set default\_transaction\_read\_only, add a statement timeout, revoke schema creation where it isn’t needed, and fail setup if the role has write privileges, BYPASSRLS, or access to unrelated SECURITY DEFINER functions. That keeps the privileged bootstrap step separate from normal analysis.

“Read-only” is necessary, but it would not be enough for me to connect a desktop analyzer to production. I’d want a dedicated Postgres role with no write privileges, no ownership, no `BYPASSRLS`, no function-execution privileges beyond an allowlist, and access restricted to the diagnostic views it actually needs. The semantic layer also changes the threat model. If schema metadata, query text, or samples leave the machine for an AI feature, the UI should show exactly what is transmitted and let users disable that path independently. One useful built-in check would be warning when the supplied role can see more than the analyzer requires. That turns least privilege into something the tool verifies instead of something the setup guide merely recommends.

That answer is useful because it confirms the requirement is the assessment outcome, not one specific document. I’d turn the alternatives into a small evidence matrix: risk being evaluated, artifact that addresses it, scope, reporting period, exceptions, owner, and next review date. The main trap is collecting ten documents that all describe the same control while leaving a gap elsewhere. I’d also record residual gaps explicitly—for example, an ISO certificate may establish the ISMS scope but not give the same control-testing detail as a Type II report. That makes the decision defensible even when the evidence package is mixed.

Before migrating, I'd ask the auditor or Vanta contact to name the exact vendor-control assertion they cannot support without the Type II report. "We need the report" is a document request; the underlying objective may be narrower. I'd assemble a documented vendor review using whatever evidence is available: security and architecture documentation, DPA and subprocessors, encryption and access-control descriptions, backup and recovery commitments, incident/status history, contractual terms, and a shared-responsibility mapping. Record the unavailable report as a limitation, then document the residual risk, owner, compensating controls, review date, and conditions that trigger an upgrade. There is also now a Supabase team response in this thread mentioning an early-stage startup workaround. I'd exhaust that route before either migrating or doing a temporary upgrade. Vendor monitoring is recurring, so confirm whether any supplied evidence remains accessible for future review rather than solving only the current collection step.

This is a good idea for a focused tool. One thing worth confirming it covers, since it's the gap that bites people even when every policy looks airtight: does it check whether FORCE ROW LEVEL SECURITY is set on each table? By default RLS doesn't apply to the table owner or to roles with BYPASSRLS, so a project can have well-written policies on every table and still leak data through any code path that runs as the owner (migration scripts, some server-side clients) unless FORCE is explicitly set. Same question for SECURITY DEFINER functions — a function defined with elevated privileges runs with the definer's permissions regardless of what RLS says, so an audit of "every table, view, function, and role" should really flag any SECURITY DEFINER function touching a protected table, not just check whether the table has RLS enabled. Those two are the ones that show up in real projects that otherwise look correctly configured.

The function-grants allowlist pattern maps cleanly onto RLS: pg\_policies gives you policy name, table, command, roles, and the qual/with\_check expressions in one queryable catalog, so "assert every table has policies matching a declared spec" is the same shape of test, just walking a different system table. One extra gotcha worth building into that sweep from day one: RLS being enabled doesn't bind the table owner or any role with BYPASSRLS — by default the owner of a table bypasses its own RLS policies unless you also run ALTER TABLE ... FORCE ROW LEVEL SECURITY. Easy to miss because your app's own queries (running as owner, or with the service role in a migration script) will look correctly scoped in testing even when a policy is silently not enforced for that role. Worth asserting FORCE ROW LEVEL SECURITY is set alongside "policy set matches intent" in the enumeration, not just RLS enabled.

The replies so far are answering the cost/CDN question, but the security half of "not to be shared" is the part to get right before worrying about egress bills. Two things specifically: make the storage bucket private, not public, and write an RLS policy on storage.objects so a user can only read/write objects under their own folder path — the default Supabase setup doesn't give you that automatically. Then serve images through short-lived signed URLs rather than public URLs. If you ask Claude Code to do this, ask it explicitly to write a private bucket with a folder-scoped RLS policy and a signed-URL helper — "add photo storage" on its own tends to reach for whatever's simplest to get working, which is usually a public bucket.

Worth flagging one more failure mode on the "where it stops working" list: once everything shares one Postgres instance, an RLS policy bug in any single app's schema is no longer contained to that app — a broken or missing WITH CHECK on one product's table doesn't stay a one-app problem the way a separate project would. Doesn't argue against consolidating, but it does mean the adversarial cross-tenant test (can org A read/write org B's row) needs to run against every schema, not just the one you're actively changing, since a migration in product 3 can't quietly weaken isolation for product 1.

The one that bites hardest in practice is 'checking reads but forgetting WITH CHECK on inserts/updates' — it's invisible in normal testing because your own writes look fine, and it only shows up when someone probes another tenant's insert path. Worth calling out for anyone vibe-coding this with AI: the assistant will happily generate a SELECT policy and just... not generate the write-side one, and nothing errors. If you're auditing an app after the fact, diffing every table's SELECT vs INSERT/UPDATE policy pairs is a five-minute check that catches this class of bug on its own.