Secure Tenant Materialized Views
Check refresh and read permissions on derived tenant tables and materialized views.
Materialized views and derived tables are copies of source data. The source tables may have correct RLS policies while a refresh job writes all tenants into one new relation that has no policy. A dashboard may then read the derived relation under a broad role. Trace the copy and its readers as a separate boundary.
View lineage sheet
For each derived object, record the source tables, refresh job, execution role, owner column in the result, reader roles, refresh schedule, and expiry behavior. Include aggregate tables and search-ready denormalized rows.
| Stage | Security question |
|---|---|
| Source query | Does it include all tenants by design or one tenant? |
| Refresh role | Can it bypass source RLS, and why? |
| Derived schema | Does each row retain an owner or explicit public class? |
| Read path | What policy limits a user's view? |
| Backfill | Does an old snapshot remain after tenant deletion? |
Use Tenant A and B synthetic markers. Refresh the view as the real worker role, then read it as the app role and any internal dashboard role. Test both row contents and counts. If the view is global by design, document who may see it and whether aggregates can reveal a small tenant's activity.
A change to the source policy does not automatically change an already built snapshot. Plan a refresh or purge when ownership or deletion rules change. Verify what happens if refresh fails halfway: an old snapshot may remain available, and the team should know whether that is acceptable.
Check refresh failure
Illustrative derived row: {tenant_id:"A", metric:"open_exports", value:1}. After verifying current membership, use a reader query such as SELECT metric, value FROM tenant_metrics WHERE tenant_id = $1, with $1 bound to the authorized tenant. Read as A, B, and the dashboard's real database role; B must receive no A row or count. The database role may read broadly, so check the application query and any direct dashboard path separately. PostgreSQL documents that materialized-view reads use stored results. After deleting synthetic A, force a refresh failure. If the old row remains in the materialized view, require the reader path to deny A's deleted tenant before returning it. Record raw snapshot contents, reader-role result, and the accepted stale-data window separately. PostgreSQL source-table RLS alone does not prove the derived read path.
PostgreSQL's row security documentation covers policies on tables and role bypass; it does not make every derived object inherit a source table's protection. OWASP multi-tenant guidance frames the end-to-end goal. This lineage sheet gives a reviewer a concrete question at each copy. Hold any claim of isolation until refresh and read roles are both known.
Find the gaps in your access controls
See how an illustrative cross-workspace finding connects evidence, impact and a fix. Discuss the tenant boundaries your team needs reviewed.