Understanding PostgreSQL Function Security: Definer vs Invoker Explained

Function Security in PostgreSQL

PostgreSQL functions can be defined with SECURITY DEFINER or SECURITY INVOKER. The chosen option determines which role is used when the function executes.

Syntax

A CREATE FUNCTION statement may include:

CREATE FUNCTION schema_name.func_name(...)
RETURNS return_type
LANGUAGE plpgsql
SECURITY DEFINER   -- or SECURITY INVOKER (default)
SET search_path = 'public';

Setting search_path inside the function overrides the caller’s path.

Role Concepts: session_user vs current_user

session_user

: the role used to log in or the role set by SET SESSION AUTHORIZATION. current_user: the role active after SET ROLE or the role defined by a security‑definer function.

Source reference:

src/backend/utils/init/miscinit.c

Example: Test Function

The following function is created by the superuser postgres with SECURITY DEFINER and an explicit search_path:

CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = 'public'
AS $$
BEGIN
    RAISE NOTICE 'search_path=%', current_setting('search_path');
    RAISE NOTICE 'current_role=%', current_user;
END;
$$;

When a regular user digoal calls public.f1(), the notices show: search_path = public (instead of the default "$user",public) current_role = postgres (the function owner)

Changing the definition to SECURITY INVOKER makes the same call report current_role = digoal.

Security Risks of SECURITY DEFINER

If a security‑definer function accesses objects without schema qualification, a non‑privileged user can manipulate the search_path or create objects in a schema that appears earlier in the path (including temporary schemas). This can bypass intended checks and lead to privilege escalation.

Mitigation Strategies

Always qualify object names with their schema (e.g., public.my_table).

Explicitly set a safe search_path inside the function, such as SET search_path = 'public', to prevent user‑controlled schemas from being searched.

Optionally, revoke CREATE privileges on schemas that appear before the safe schema.

Applying these measures causes the exploit attempts to fail, as demonstrated by the test runs.

References

PostgreSQL Documentation – CREATE FUNCTION:

http://www.postgresql.org/docs/9.5/static/sql-createfunction.html

Source code reference:

src/backend/utils/init/miscinit.c