Skip to content
Codex
Documentation GitHub
Infrastructure

Supabase Advisor Warning Resolution Patterns

Supabase Advisor Warning Resolution Patterns

Problem

The Supabase Dashboard advisor reported 7 warnings on the production instance, spanning RLS policy structure, MFA configuration, and schema drift. Additionally, a CHECK constraint on workspace_participants.capabilities listed only 11 values while the Rust domain defined 19, meaning any participant granted newer capabilities would be silently rejected by PostgreSQL.

Symptoms:

  • Dashboard advisor shows “Multiple Permissive Policies” for 4 roles on workspace_participants
  • Dashboard advisor shows “Auth RLS Initplan” on analytics_events
  • Dashboard advisor shows “Insufficient MFA Options”
  • Application inserts with newer capabilities (attachments_read, types_write, etc.) rejected by CHECK constraint with no clear error path back to the developer
  • Dashboard info panel shows “Unindexed Foreign Keys” on analytics_events.user_id

Root Cause

Multiple Permissive Policies (advisories 3–6)

PostgreSQL evaluates permissive RLS policies with OR logic. When workspace_participants had both a FOR ALL policy (implicitly covers SELECT, INSERT, UPDATE, DELETE) and a separate FOR SELECT policy, any SELECT query matched two permissive policies OR’d together. While semantically correct in this case, Supabase flags it because overlapping permissive policies are a common source of unintended access escalation.

CHECK Constraint Drift

The capabilities_valid constraint was written once with 11 capabilities and never updated as the Rust Capability enum grew to 19 variants. PostgreSQL enforces CHECK constraints on INSERT and UPDATE — any row with a capability not in the array is rejected. Since the constraint uses the <@ (contained-by) operator, the failure is silent from the application’s perspective until an insert actually includes a newer capability.

Auth RLS Initplan (advisory 2)

RLS policies using bare auth.uid() cause PostgreSQL to re-evaluate the function per row. Wrapping in (select auth.uid()) converts it to an initplan — evaluated once per query. The baseline already had this fix but the deployed schema predated it.

MFA (advisory 7)

No MFA factors were configured in supabase/config.toml. The hosted instance had no factors enabled.

Unindexed FK

analytics_events.user_id references users(id) ON DELETE SET NULL but had no index. On user deletion, PostgreSQL must scan analytics_events to find rows to nullify — a full table scan on what will be a high-volume append-only table.

Solution

1. Replace overlapping RLS policies with per-operation policies

-- BEFORE: Two overlapping permissive policies on SELECT
CREATE POLICY "owner_all_participants" ON workspace_participants
    FOR ALL
    USING (EXISTS (SELECT 1 FROM workspaces w WHERE w.id = workspace_id AND w.owner_id = (select auth.uid())));


CREATE POLICY "participant_read_own" ON workspace_participants
    FOR SELECT
    USING (participant_id = (select auth.uid()));


-- AFTER: One policy per operation, no ambiguity
CREATE POLICY "participants_select" ON workspace_participants
    FOR SELECT
    USING (
        EXISTS (SELECT 1 FROM workspaces w WHERE w.id = workspace_id AND w.owner_id = (select auth.uid()))
        OR participant_id = (select auth.uid())
    );


CREATE POLICY "owner_insert_participants" ON workspace_participants
    FOR INSERT
    WITH CHECK (EXISTS (SELECT 1 FROM workspaces w WHERE w.id = workspace_id AND w.owner_id = (select auth.uid())));


CREATE POLICY "owner_update_participants" ON workspace_participants
    FOR UPDATE
    USING (EXISTS (SELECT 1 FROM workspaces w WHERE w.id = workspace_id AND w.owner_id = (select auth.uid())));


CREATE POLICY "owner_delete_participants" ON workspace_participants
    FOR DELETE
    USING (EXISTS (SELECT 1 FROM workspaces w WHERE w.id = workspace_id AND w.owner_id = (select auth.uid())));

The FOR SELECT policy combines owner-check and self-check via OR, preserving the original semantics (owner sees all participants, non-owner sees only their own row) in a single policy.

2. Sync CHECK constraint with Rust domain

ALTER TABLE workspace_participants ADD CONSTRAINT capabilities_valid CHECK (
    capabilities <@ ARRAY[
        'pages_read', 'pages_write', 'pages_organize', 'pages_delete',
        'search_use', 'history_read',
        'bookmarks_read', 'bookmarks_manage',
        'workspace_manage', 'import_execute', 'sync_manage',
        -- Added to match Capability enum (19 variants)
        'attachments_read', 'attachments_write',
        'types_read', 'types_write',
        'tags_read', 'tags_write',
        'properties_read', 'properties_write'
    ]::TEXT[]
);

3. Add MFA configuration

In supabase/config.toml:

[auth.mfa]
max_enrolled_factors = 10


[auth.mfa.totp]
enroll_enabled = true
verify_enabled = true


[auth.mfa.web_authn]
enroll_enabled = true
verify_enabled = true

TOTP is universally supported. WebAuthn fits desktop apps well (Touch ID, Windows Hello, FIDO2 keys) with zero ongoing cost (no SMS provider needed). Phone MFA deferred until user demand.

Dashboard must also be configured: Authentication → MFA → Enable TOTP (set to “Enabled”, not “Verify Enabled” — opt-in enrollment without forcing verification at every sign-in).

4. Add missing FK index

CREATE INDEX IF NOT EXISTS idx_analytics_events_user_id ON analytics_events (user_id);

5. Hot-apply to hosted databases without reset

For changes that can be expressed as idempotent SQL, apply directly via psql against the branch connection URL rather than waiting for a full CI pipeline + database reset cycle:

Terminal window
psql "$STAGING_DB_URL" -c "CREATE INDEX IF NOT EXISTS idx_analytics_events_user_id ON analytics_events (user_id);"
psql "$PRODUCTION_DB_URL" -c "CREATE INDEX IF NOT EXISTS idx_analytics_events_user_id ON analytics_events (user_id);"

For non-idempotent changes (RLS policy restructure, CHECK constraint replacement), a database reset is required when modifying the baseline rather than adding an incremental migration.

Prevention

Best Practices

  • Audit CHECK constraints when adding enum variants. Any time a Rust enum that maps to a PostgreSQL CHECK constraint gains new variants, update the constraint in the baseline. Consider adding a compile-time or CI test that compares the two.
  • One RLS policy per operation. Never combine FOR ALL with operation-specific policies on the same table. Use explicit FOR SELECT, FOR INSERT, FOR UPDATE, FOR DELETE policies.
  • Always wrap auth.uid() in (select ...) in RLS policies to enable PostgreSQL initplan optimization.
  • Index every FK column. PostgreSQL does not auto-index foreign keys. Every FK needs an explicit index for efficient CASCADE/SET NULL operations.
  • Dashboard settings are not in code. Some Supabase features (leaked password protection, MFA factor enablement on hosted instance) require Dashboard action in addition to config.toml changes. Document these manual steps alongside code changes.

Warning Signs

  • Supabase advisor showing “Multiple Permissive Policies” — you have overlapping FOR ALL + FOR <operation> policies
  • Application silently failing to insert rows with valid-looking data — CHECK constraint may be out of sync with application enum
  • ON DELETE CASCADE or ON DELETE SET NULL operations becoming slow — missing FK index causing full table scans

References

  • crates/domain/src/capability.rs — Canonical Capability enum (19 variants)
  • supabase/migrations/00000000000000_baseline.sql — Baseline schema with all fixes applied
  • supabase/config.toml — MFA configuration
  • Supabase MFA docs: TOTP “Enabled” = opt-in enrollment; “Verify Enabled” = enforced at sign-in

Was this page helpful?