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

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

Get started free