A SECURITY DEFINER function is safe only if you set its search_path to trusted schemas, list pg_temp last, and limit who can execute it. The function runs with the privileges of its owner, so an attacker who can shadow a table or operator by name can make it do the owner's work for them.
What the function actually does
In PostgreSQL, a normal function runs with the rights of the caller. A function marked SECURITY DEFINER runs with the rights of the role that owns it. Teams use this to expose one narrow action, such as checking a password or writing an audit row, without granting access to the underlying table.
The PostgreSQL documentation warns that care is needed because the function can be misused. Its advice is that search_path should exclude any schemas writable by untrusted users. That prevents someone from creating tables, functions or operators that mask the objects the function means to use.
The temporary schema trap
By default the temporary schema is searched first, and it is normally writable by anyone. The documentation says a secure arrangement is to force it to be searched last, by writing pg_temp as the final entry in search_path.
CREATE FUNCTION check_login(uname text, pass text) RETURNS boolean AS $$
DECLARE passed boolean;
BEGIN
SELECT (pwd = pass) INTO passed FROM admin.pwds WHERE username = uname;
RETURN passed;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = admin, pg_temp;
Without the SET clause, a caller could create a temporary object with the same name as something the function touches. With it, the lookup is pinned to the schemas you named.
Execute privilege is open by default
The same documentation notes that execute privilege is granted to PUBLIC for newly created functions. For a definer function that is usually wrong. Revoke the default and grant to specific roles, and do both in one transaction so there is no window where everyone can call it.
BEGIN;
CREATE FUNCTION audit_write(msg text) RETURNS void AS $$
INSERT INTO admin.audit(msg) VALUES (msg)
$$ LANGUAGE sql SECURITY DEFINER SET search_path = admin, pg_temp;
REVOKE ALL ON FUNCTION audit_write(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION audit_write(text) TO app_runtime;
COMMIT;
A review checklist
| Check | Why it matters |
|---|---|
| search_path is set on the function | Stops name shadowing |
| pg_temp is last in the list | Temporary objects cannot win a lookup |
| Owner is a low-privilege role, not a superuser | Limits what a bypass can reach |
| EXECUTE revoked from PUBLIC | Not every role should call it |
| Arguments are used as values, never built into dynamic SQL | Avoids injection inside the function |
The owner choice is easy to forget. A definer function owned by a superuser turns a small bug into full control. Create a dedicated owner with only the table rights the function needs.
Safeguard's dependency scanning can run on the repository that holds your migration files, and policy gates can hold a pull request for review. A free scan is at gold.safeguard.sh/scan.
Try it yourself
Run the code scan in the demo and open a finding. Look for how a data flow from user input to a query is traced, which is the same habit you need when reviewing database functions.
Click through it at your own pace, or open it full screen.
Quick check
Does SET search_path on the function change the caller's setting?
The setting applies while the function runs. The PostgreSQL docs describe attaching it to the function so the lookup path is fixed for that call.
Is SECURITY INVOKER safer?
It runs with the caller's rights, so it cannot escalate. It also cannot give access the caller lacks, which is the reason people choose definer.
Do I need this for every function?
No. It matters for functions that run with elevated rights.
Sources
Last reviewed: 19 October 2026.
