PostgreSQL:SecurityContext

lastmod 2026-02-26created 2026-02-089 edits

In PostgreSQL, Views and Functions have two security modes depending on whose privileges they run with.

SECURITY DEFINER

Privileges are applied as the object owner (owner). So RLS is ❌ bypassed.

SECURITY INVOKER

Privileges are applied as the caller (caller). So RLS is ✅ applied.

SECURITY DEFINER vs SECURITY INVOKER

Applying to Views

Views run as SECURITY DEFINER by default. The security_invoker option was added in PostgreSQL 15+.

-- SECURITY DEFINER (기본값): owner 권한으로 실행, RLS 우회
CREATE VIEW my_view AS
SELECT * FROM my_table;

-- SECURITY INVOKER: 호출자 권한으로 실행, RLS 적용
CREATE VIEW my_view
WITH (security_invoker = true)
AS
SELECT * FROM my_table;

-- 기존 View 변경
ALTER VIEW my_view SET (security_invoker = true);

Applying to Functions

Functions run as SECURITY INVOKER by default. Note that the default is the opposite of Views.

-- SECURITY INVOKER (기본값): 호출자 권한으로 실행
CREATE FUNCTION get_users()
RETURNS SETOF users
LANGUAGE sql
AS $$
    SELECT * FROM users;
$$;

-- SECURITY DEFINER: 함수 소유자 권한으로 실행 (권한 상승)
CREATE FUNCTION admin_get_all_users()
RETURNS SETOF users
LANGUAGE sql
SECURITY DEFINER
AS $$
    SELECT * FROM users;
$$;

Caveats when using SECURITY DEFINER

SECURITY DEFINER allows privilege escalation, so it must be used carefully.

Recommendations:

• Set search_path explicitly to prevent schema injection. See the #search_path section below.

CREATE FUNCTION admin_action()
RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
    -- ...
$$;

• Validate inputs thoroughly — the caller can indirectly abuse the owner's privileges
• Prefer SECURITY INVOKER where possible, and use DEFINER only when truly necessary

search_path

Preventing schema injection

Use SET search_path = '' to prevent schema injection.

This setting prevents security vulnerabilities in SECURITY DEFINER functions.

SECURITY DEFINER functions run with the privileges of the function owner (usually a superuser). If a malicious user manipulates search_path:

-- 공격자가 자신의 스키마에 가짜 admins 테이블을 만듦
CREATE SCHEMA attacker;
CREATE TABLE attacker.admins AS SELECT auth.uid() AS user_id;
SET search_path = attacker, public;

-- 이제 is_admin()이 attacker.admins를 참조하게 됨 → 항상 TRUE

Solution:

SET search_path = ''

With an empty search_path, the function body must always qualify schemas explicitly, like public.admins and auth.uid(), so no matter how search_path is changed from outside, it always references the intended table.

In short, SECURITY DEFINER + SET search_path = '' is a security pattern used as a pair.

Use in Supabase

Because Supabase uses an RLS-based security model:

• View: RLS applies only if you set security_invoker = true
• Function (SECURITY DEFINER): used for "service-level" tasks that bypass RLS (e.g. admin-only functions, logic inside triggers)
• Function (SECURITY INVOKER): used for functions aimed at regular users; RLS applies naturally

See Supabase:Database:RLS for details.

See also

• PostgreSQL