db advisors --local misses API-exposure lints (rls_disabled_in_public, etc.)

[stefan-girlich]

Affected area

Local development

Supabase CLI version

v2.109.0 (also confirmed absent in v2.109.1 and main)

Operating system

macOS

Installation method

brew

Command

supabase db advisors --local --type security

Actual output

Connecting to local database...
No issues found

Expected behavior

db advisors --local should report rls_disabled_in_public for an RLS-disabled,API-reachable public table, matching the hosted Security Advisor so it can be trusted as a pre-deploy CI gate. Right now it gives a false all-clear for the entire class of API-exposure lints.

Steps to reproduce

1. supabase init && supabase start
2. In the local DB, create an exposed, RLS-off table:

   create table public.leaky (id int);
   grant select on public.leaky to anon, authenticated;

3. Run the security advisors locally and list the lint names:

   supabase db advisors --local --type security --output-format json \
     | jq -r '.results[].name' | sort | uniq -c
   

   => rls_disabled_in_public is absent (only rls_enabled_no_policy appears).
4. Prove the cause is the unset GUC:

   alter role postgres set pgrst.db_schemas = 'public';   -- then reconnect

   Re-run step 3 => rls_disabled_in_public (ERROR) now appears.

Crash report ID

No response

Docker and service versions

Additional context

Actual behavior

The only security lint ever returned locally is rls_enabled_no_policy. rls_disabled_in_public - and every other API-exposure lint (auth_users_exposed, security_definer_view, materialized_view_in_api, foreign_table_in_api, sensitive_columns_exposed, the pgmq queue lint) - is never emitted, even for a public table that clearly qualifies (RLS disabled AND granted to anon/authenticated).
No error is printed; the findings are silently absent.

rls_disabled_in_public was correctly detected only on Supabase cloud, not in my CI steps before.

Root cause (traced to source):

• The --local path runs an embedded splinter snapshot via a direct connection as
  postgres: apps/cli-go/internal/db/advisors/advisors.go (//go:embed templates/lints.sql).
• In that snapshot, rls_disabled_in_public (and ~6 other API-exposure lints) gate on:

  and n.nspname = any(array(select trim(unnest(
    string_to_array(current_setting('pgrst.db_schemas', 't'), ',')))))

  https://github.com/supabase/cli/blob/v2.109.0/apps/cli-go/internal/db/advisors/templates/lints.sql (line 708)
• The advisors setup SQL does not set pgrst.db_schemas, and a directly-connected
  postgres session has it unset → string_to_array(NULL,',') → empty array →
  nspname = any('{}') is false for every table. So the lints silently match nothing.
• Upstream splinter already fixed exactly this in PR supabase db remote commit fails with error Error: ERROR: prepared statement "lrupsc_1_0" already exists (SQLSTATE 42P05) #168 (2026-06-17) with
  coalesce(current_setting('pgrst.db_schemas','t'), 'public'):
  fix: default API-exposure lints to public schema when pgrst.db_schemas is unset splinter#168
  But the CLI's vendored snapshot has not been updated - the coalesce fallback is
  absent in v2.109.0, v2.109.1, and main (grep of the embedded lints.sql).

Suggested fix (either):

• (a) re-vendor splinter >= PR supabase db remote commit fails with error Error: ERROR: prepared statement "lrupsc_1_0" already exists (SQLSTATE 42P05) #168, or
• (b) have the advisors setup SQL set pgrst.db_schemas from the project's
  [api].schemas before running the lints.

Note: db advisors is currently undocumented in the CLI reference
(https://supabase.com/docs/reference/cli/supabase-db-advisors has no content), and
#3839 requested this capability - so this may just be an unfinished edge.