compile_json.py's CATEGORIES_RE corrupts the manifest categories field for lints with an earlier array[...] literal (0023/0024/0025 hidden from any manifest-driven consumer)

[BrianGBuurWork]

Summary

bin/compile_json.py's CATEGORIES_RE regex extracts the wrong substring for any lint whose SQL body contains an array[...] literal before the actual array['SECURITY'] as categories / array['PERFORMANCE'] as categories line. The regex is .search() with DOTALL and a non-greedy quantifier, so it matches from the first array[ in the file to the first subsequent ] as categories — which, for a handful of lints, is not the categories literal at all.

Confirmed against the published splinter.json manifest for releases 2026.07.0, 2026.08.0, and 2026.09.0 (unchanged across all three): the categories field is malformed for exactly these lints, because each has an earlier array[...] in its own body:

Lint	Level	What comes before the real categories literal
rls_policy_always_true (0024)	WARN	role_oids = array[0::oid]
sensitive_columns_exposed (0023)	ERROR	unnest(array['password', ...]) (sensitive-column name list)
public_bucket_allows_listing (0025)	WARN	array['public'::name, 'anon'::name, ...]

The lint files themselves are correct and unchanged (rls_policy_always_true hasn't changed since April) — this is purely a manifest-generation bug.

Repro (3 lines)

git clone https://github.com/supabase/splinter && cd splinter
python3 bin/compile_json.py --output /tmp/s.json
jq '.lints[] | select(.name=="rls_policy_always_true") | .categories' /tmp/s.json

Expected: ["SECURITY"]. Actual: an array of SQL-fragment strings (the text between the first array[ and the first ] as categories), never ["SECURITY"] as a clean single-element array.

Real-world impact

We noticed on our own hosted project that the Management API (GET /v1/projects/{ref}/advisors/security — the same code path used by MCP get_advisors and the CLI's supabase db advisors --linked) never reports rls_policy_always_true findings, while:

• Supabase Studio's Security Advisor tab shows them correctly, and
• executing the lint's own SQL directly against the same database returns the expected rows.

Studio isn't affected because it filters lints by each result row's own, Postgres-evaluated categories column (see apps/studio/hooks/misc/useLints.ts: data.filter((lint) => lint.categories.includes('SECURITY'))) — that value is computed at query execution time and is never touched by the manifest bug. We can't inspect the Management API's closed-source implementation directly, but per #175's own description ("supabase/platform's advisor consumes this manifest instead of scraping splinter.sql"), a consumer that uses the manifest's categories field to decide which lint queries to even run for a category-scoped request would silently skip these three for any /advisors/security-style call — which matches the observed behavior exactly.

This may also be relevant to #181 (public_bucket_allows_listing disappearing from the hosted advisor) — that issue proposes a different mechanism (a transaction-local GUC not being set), but public_bucket_allows_listing is one of the three lints with a corrupted manifest categories field here, so both causes may be in play, or this one may fully explain that report's hosted-advisor symptom instead. Worth checking against this bug before assuming the GUC theory is the whole story.

Since sensitive_columns_exposed is an ERROR-level lint, any consumer that relies on the manifest for lint selection would be silently blind to it — not just missing a warning, but missing a data-exposure-severity finding.

Suggested fix

Anchor the regex to quoted category literals only, rather than matching greedily from the first array[, e.g.:

CATEGORIES_RE = re.compile(
    r"array\[((?:\s*'[A-Z_]+'\s*,?)+)\]\s+as\s+categories", re.IGNORECASE
)

and/or add a CI assertion in this repo that every generated manifest entry's categories is a subset of {SECURITY, PERFORMANCE} (a malformed value should fail the build loudly rather than ship silently, which is what let this go unnoticed across three releases).

Happy to test a fix against the reproduction above.

[BrianGBuurWork]

One more, minor and unrelated to the manifest bug above: public_bucket_allows_listing's own predicate only matches a single-literal bucket_id = '<id>' policy shape. A policy written as bucket_id = ANY(ARRAY['a','b','c']) (or IN (...)) grants the same public listing but isn't flagged — confirmed on our own project, verified 0 false positives, just a coverage gap in the predicate's pattern matching. Mentioning it here since it's the same lint and may be worth a look in the same pass, not filing separately.

[utkarash2991]

@BrianGBuurWork It should be fixed through: #187 . I saw this issue today and created a fix PR and coincidentally you opened the issue :-)