PostgreSQL:View

lastmod 2026-02-08created 2026-02-083 edits

security_invoker

In PostgreSQL 15+, the security_invoker = true option makes a View run with the privileges of the user calling it, so the source table's RLS policies apply as is.

-- 새 View 생성 시
CREATE VIEW my_view
WITH (security_invoker = true)
AS
SELECT * FROM my_table;

-- 기존 View에 적용
ALTER VIEW my_view SET (security_invoker = true);

Caveats:

• You can't apply ENABLE ROW LEVEL SECURITY directly to a View itself. It always relies on the RLS of the source tables.
• For a View that JOINs multiple tables, the RLS of every table applies.
• This option is easy to miss when creating a View in the Supabase Dashboard, so it is safer to write it directly in the SQL Editor.

Views and RLS

In PostgreSQL, Views run with the View owner's privileges (security_definer) by default, so even if RLS is set on the source table, querying through the View bypasses the RLS policies. See #security_invoker.

Example: my_orgs View

Example View that lists the organizations the currently logged-in user belongs to:

-- VIEW: my_orgs
-- Current user's organization list with plan, role, and member count

CREATE OR REPLACE VIEW public.my_orgs
WITH (security_invoker = true)
AS
SELECT
    o.id,
    o.icon,
    o.name,
    o.slug,
    COALESCE(s.plan_code, 'free'::public.plan_code) AS plan,
    r.name AS role,
    (SELECT COUNT(*)::int FROM public.org_members m WHERE m.org_id = o.id) AS member_count,
    o.created_at
FROM public.orgs o
JOIN public.org_members om ON om.org_id = o.id
JOIN public.org_roles r ON r.id = om.role_id
LEFT JOIN public.subscriptions s ON s.org_id = o.id
    AND s.status IN ('active', 'trialing')
WHERE om.user_id = auth.uid();

-- Grant access to authenticated users
GRANT SELECT ON public.my_orgs TO authenticated;

Design points:

• security_invoker = true — RLS of the source tables applies normally
• WHERE om.user_id = auth.uid() — also filters to the current user at the View level (double security)
• COALESCE(..., 'free') — organizations without a subscription default to the free plan
• Subscription status filter — JOINs only active and trialing to exclude expired/canceled subscriptions

Performance considerations:

• The member_count subquery is a correlated subquery that runs per row, but since a user usually belongs to few organizations, it is good enough in practice.
• Because of security_invoker, each table's RLS conditions and the View's WHERE clause both apply, so if performance issues arise, check the query plan with EXPLAIN ANALYZE.

Guarding against duplicate subscriptions: If an organization might have active/trialing subscriptions at the same time, consider fetching only the latest one with a LATERAL subquery:

LEFT JOIN LATERAL (
    SELECT plan_code
    FROM public.subscriptions
    WHERE org_id = o.id AND status IN ('active', 'trialing')
    ORDER BY created_at DESC
    LIMIT 1
) s ON true

See also

• PostgreSQL
• Supabase
• Supabase:Database
• Supabase:Database:View