Exposed Materialized Views

What “Exposed Materialized Views” means (plain English)

Materialized views store query results; any client with SELECT can read them even if base tables are locked down.

How Exposed Materialized Views works in Supabase/Postgres (technical)

Because they cache rows, materialized views bypass some RLS protections when granted to PUBLIC/anon, so you must treat them like separate tables.

Attack paths & failure modes for Exposed Materialized Views

• Exposed Materialized Views: direct API bypass: The dashboard relies on a materialized view for quick metrics and grants SELECT to authenticated.
• Exposed Materialized Views: migration drift regression: A new materialized view ships with default grants, undoing earlier hardening.
• Exposed Materialized Views: direct API bypass: Clients could query the view directly and read aggregated data that the UI never intended to leak.
• Exposed Materialized Views: migration drift regression: The migration granted SELECT to authenticated, so the view became publicly readable.
• The configuration doesn’t match what the UI implies (direct API access bypasses the app).
• Policies/grants drift over time and widen access without anyone noticing.
• Fixes are applied without verification, leading to false confidence.

Why Exposed Materialized Views matters for Supabase security

Clients can retrieve sensitive data faster than through the base tables, so these views become attractive exposure points.

Common Exposed Materialized Views mistakes that lead to leaks

• Granting SELECT on materialized views to clients for dashboard speed.
• Assuming materialized views inherit base table security.
• Not rechecking view grants after schema migrations.
• Exposed Materialized Views: direct API bypass: Clients could query the view directly and read aggregated data that the UI never intended to leak.
• Exposed Materialized Views: migration drift regression: The migration granted SELECT to authenticated, so the view became publicly readable.

Where to look for Exposed Materialized Views in Supabase

• Your grants, policies, and any direct client access paths.
• Storage and RPC settings (common blind spots).

How to detect Exposed Materialized Views issues (signals + checks)

Use this as a quick checklist to validate your current state:

• Try the same queries your frontend can run (anon/authenticated). If sensitive rows come back, you have exposure.
• Verify RLS is enabled and (for sensitive tables) forced.
• List policies and look for conditions that don’t bind rows to a user or tenant.
• Audit grants to anon / authenticated on sensitive tables and functions.
• Exposed Materialized Views: direct API bypass: Materialized views are API surfaces when granted to clients.
• Exposed Materialized Views: direct API bypass: Refresh and expose them through backend code.
• Exposed Materialized Views: direct API bypass: Backend endpoints make authorization explicit.
• Re-test after every migration that touches security-critical tables or functions.

How to fix Exposed Materialized Views (backend-only + zero-policy posture)

Mockly’s safest default is backend-only access: the browser should not query tables, call RPC, or access Storage directly.

1. Decide which operations must remain client-side (often: none for sensitive resources).
2. Create server endpoints (API routes or server actions) for required reads/writes.
3. Apply hardening SQL: enable+force RLS where relevant, remove broad policies, and revoke grants from client roles.
4. Generate signed URLs for private Storage downloads on the server only.
5. Re-run a scan and confirm the issue disappears.
6. Add a regression check to your release process so drift doesn’t reintroduce exposure. Fixes that worked in linked incidents:

• Exposed Materialized Views: direct API bypass: Revoke client grants, refresh the view server-side, and expose the data through a backend endpoint.
• Exposed Materialized Views: migration drift regression: Restrict grants, tie refreshes to backend jobs, and include grant validation in migration processes.

Verification checklist for Exposed Materialized Views

1. Attempt direct access using client credentials and confirm it fails.
2. Apply a backend-only fix pattern and verify end-to-end behavior.
3. Re-run a scan after changes and after the next migration.
4. Exposed Materialized Views: direct API bypass: Materialized views are API surfaces when granted to clients.
5. Exposed Materialized Views: direct API bypass: Refresh and expose them through backend code.
6. Exposed Materialized Views: direct API bypass: Backend endpoints make authorization explicit.
7. Exposed Materialized Views: direct API bypass: Test direct access to catch leaks.
8. Exposed Materialized Views: migration drift regression: Migrations can reintroduce exposed views.

SQL sanity checks for Exposed Materialized Views (optional, but high signal)

If you prefer evidence over intuition, run a small set of SQL checks after each fix.

The goal is not to memorize catalog tables — it’s to make sure the access boundary you intended is the one Postgres actually enforces:

• Confirm RLS is enabled (and forced where appropriate) for tables tied to this term.
• List policies and read them as plain language: who can do what, under what condition?
• Audit grants for anon/authenticated and PUBLIC on the tables, views, and functions involved.
• If Storage is involved: review bucket privacy and policies for listing/reads.
• If RPC is involved: review EXECUTE grants for functions and whether privileged functions are server-only.

Pair these checks with a direct API access test using client credentials. When both agree, you can ship the fix with confidence.

Over time, keep a small “query pack” for the checks you trust and run it after every migration. That’s how you prevent quiet regressions.

Prevent Exposed Materialized Views drift (so it doesn’t come back)

• Add a repeatable checklist and re-run it after schema changes.
• Prefer backend-only access for sensitive resources.
• Keep one reusable verification test for “Exposed Materialized Views: direct API bypass” and rerun it after every migration that touches this surface.
• Keep one reusable verification test for “Exposed Materialized Views: migration drift regression” and rerun it after every migration that touches this surface.

Rollout plan for Exposed Materialized Views fixes (without breaking production)

Many hardening changes fail because teams revoke direct access first and only later discover missing backend paths.

Use this sequence to reduce both risk and outage pressure:

1. Implement and verify the backend endpoint or server action before permission changes.
2. Switch clients to that backend path behind a feature flag when possible.
3. Then revoke direct client access (broad grants, permissive policies, public bucket reads, or broad EXECUTE).
4. Run direct-access denial tests and confirm authorized backend flows still succeed.
5. Re-scan after deployment and again after the next migration.

This turns security fixes into repeatable rollout mechanics instead of one-off emergency changes.

Incident breakdowns for Exposed Materialized Views (real scenarios)

Exposed Materialized Views: direct API bypass

Scenario: The dashboard relies on a materialized view for quick metrics and grants SELECT to authenticated.

What failed: Clients could query the view directly and read aggregated data that the UI never intended to leak.

What fixed it: Revoke client grants, refresh the view server-side, and expose the data through a backend endpoint.

Why the fix worked: Backend access keeps the view hidden from untrusted clients and lets you control when it refreshes.

Key takeaways:

• Materialized views are API surfaces when granted to clients.
• Refresh and expose them through backend code.
• Backend endpoints make authorization explicit.
• Test direct access to catch leaks.

Read full example: Exposed Materialized Views: direct API bypass

Exposed Materialized Views: migration drift regression

Scenario: A new materialized view ships with default grants, undoing earlier hardening.

What failed: The migration granted SELECT to authenticated, so the view became publicly readable.

What fixed it: Restrict grants, tie refreshes to backend jobs, and include grant validation in migration processes.

Why the fix worked: Restricted privileges keep the view hidden and automated checks stop future drift.

Key takeaways:

• Migrations can reintroduce exposed views.
• CI should validate view grants.
• Backends control refresh and exposure.
• Track security before and after schema changes.

Read full example: Exposed Materialized Views: migration drift regression

Real-world examples of Exposed Materialized Views (and why they work)

• Exposed Materialized Views: direct API bypass — An exposed materialized view leaked data via direct API calls.
• Exposed Materialized Views: migration drift regression — A migration reintroduced an exposed materialized view.

Related terms

• Schema USAGE Granted to PUBLIC → /glossary/schema-usage-granted-to-public
• Insecure SECURITY DEFINER Functions → /glossary/insecure-security-definer-functions