In this lesson, you will learn how to control the permission level of a database function by toggling between SECURITY INVOKER and SECURITY DEFINER contexts.
When you create a function in PostgreSQL, it has to decide which user's privileges to use when executing internal SQL commands. This is managed by the execution context.
SECURITY INVOKER (The Default)
By default, a function runs with the privileges of the user who is currently calling it. If user Alice calls a function to UPDATE a restricted table, the database checks if Alice has UPDATE permission. If she doesn’t, the operation fails, even if the function creator has higher privileges. This is generally the safest approach because it respects the principle of least privilege.
SECURITY DEFINER
Sometimes, you need a user to perform an action they aren't normally allowed to do, but only through a specific, controlled interface. By setting a function to SECURITY DEFINER, the function executes with the privileges of the user who created it, rather than the user calling it.
Consider a system that allows users to reset their own passwords. A regular user shouldn't have UPDATE access to the users table, but they need to change their own record.
CREATE FUNCTION reset_password(new_pass text)
RETURNS void AS $$
BEGIN
UPDATE users SET password = new_pass WHERE username = current_user;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;
In this case, because the function is SECURITY DEFINER, it acts with the authority of the administrator who created it, allowing the UPDATE to succeed.
A Critical Warning
Using SECURITY DEFINER introduces a security risk: Search Path Hijacking. Because the function runs as the owner, if the function references a table (like users) without a schema prefix (like public.users), a malicious user could create a fake users table in a schema they control and force the function to modify or read the wrong data. Always set a secure search_path when creating SECURITY DEFINER functions:
ALTER FUNCTION reset_password(text) SET search_path = public;
Key Takeaway: SECURITY INVOKER runs with the caller's permissions, while SECURITY DEFINER elevates the function to run with the owner's permissions—requiring strict search path management to prevent unauthorized access.