# Materialized views and foreign tables are not readable through the API

- **Pattern:** `ab-002646` (`supabase-rls-keys.rls-bypass-surfaces.no-matview-or-foreign-table-in-api`)
- **Severity:** medium
- **Lifecycle:** active
- **Last modified:** 2026-10-03
- **Canonical URL:** https://auditbuffet.com/patterns/ab-002646
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-002646)

## Why it matters

Row-level security is not supported on materialized views or foreign tables, so neither can be protected by policies. A materialized view created in `public` is queried through the REST endpoint like any table, and by default `anon` and `authenticated` hold SELECT on it, so a cached aggregate or a snapshot of user data is readable by anyone and nothing in your RLS setup applies. A foreign table (a `postgres_fdw` or other wrapper to another database or an external API) exposes the remote data the same way, along with whatever credentials the wrapper uses. These objects feel internal ('just a reporting rollup') and AI tools place them in `public` because that is where the migration is already working. The advisor reports them as lints 0016 and 0017.

## Severity rationale

Medium because the exposure requires such an object to exist and to hold sensitive data, and the usual content is aggregated or derived, but when it does hold row-level data there is no policy layer to stop reads.

## Remediation

Keep these objects out of the API. Create them in a non-exposed schema, or revoke the API roles' access, and expose the data through a `security_invoker` view or a checked function when clients need it:

```sql
create materialized view private.daily_signups as
  select date_trunc('day', created_at) as day, count(*) from public.profiles group by 1;

-- or, if it must stay in public
revoke all on public.daily_signups from anon, authenticated;
```

For a foreign table, create it in a `private` schema (`create foreign table private.remote_orders ...`) and never grant it to `anon` or `authenticated`.

## Detection

**The detection method for this pattern is part of AuditBuffet Pro.**

The full method (what to look for, pass and fail conditions, and how results get reported) ships inside the [Supabase RLS & Keys audit](https://auditbuffet.com/audits/supabase-rls-keys). The pattern definition, severity rationale, and remediation remain openly licensed under CC-BY-4.0.

## External references

- cwe CWE-284
- cwe CWE-285
- owasp A01
- external 0016_materialized_view_in_api — https://supabase.com/docs/guides/database/database-advisors?lint=0016_materialized_view_in_api
- external 0017_foreign_table_in_api — https://supabase.com/docs/guides/database/database-advisors?lint=0017_foreign_table_in_api

Taxons: access-control

HTML version: https://auditbuffet.com/patterns/ab-002646