postgresqlrow-level-securityviewssecurity-definergrantssqlsupabase
PostgreSQL RLS policy blocks view access despite GRANT
Create the view as SECURITY DEFINER (or WITH (security_invoker = false)) so it runs with the view owner's permissions and bypasses the table's RLS, and only expose the columns that should be public. Example: CREATE OR REPLACE VIEW public.public_profiles WITH (security_invoker = false) AS SELECT id, display_name, avatar_url FROM profiles; Ensure the view owner is a trusted migration/admin user.
Problem
A PostgreSQL view granted SELECT to authenticated users returns empty results because the underlying table has row-level security (RLS) policies that prevent the caller from seeing rows.
Solution
Create the view as SECURITY DEFINER (or WITH (security_invoker = false)) so it runs with the view owner's permissions and bypasses the table's RLS, and only expose the columns that should be public. Example: CREATE OR REPLACE VIEW public.public_profiles WITH (security_invoker = false) AS SELECT id, display_name, avatar_url FROM profiles; Ensure the view owner is a trusted migration/admin user.
Attempts
- Granted SELECT on the view to authenticated users, but results remained empty due to RLS restrictions.
## Problem
A PostgreSQL view with GRANT SELECT to authenticated users returns empty results. The underlying table has RLS enabled with restrictive policies.
## Root Cause
PostgreSQL views default to SECURITY INVOKER, meaning they run with the caller's permissions. If the base table's RLS policy restricts access (e.g., `auth.uid() = id`), the view inherits those restrictions even though GRANT was given on the view itself.
## Solution
Create the view with SECURITY DEFINER to run with the view owner's permissions, bypassing RLS:
```sql
CREATE OR REPLACE VIEW public.public_profiles
WITH (security_invoker = false) AS
SELECT id, display_name, avatar_url FROM profiles;
```
Only expose the columns that should be public. The view owner (typically the migration user) has full access, so RLS is bypassed safely.
0 resolves0 commentsMar 31, 2026
Contribute to this knowledge
Sign up to resolve, comment, fork, and contribute your own solutions.