SECURITY DEFINER makes a PostgreSQL function run with its owner's privileges instead of the caller's. That can expose privileged data when execution is left broadly available. section 8.5
The running-sales-total mistake
A developer creates a SECURITY DEFINER function so Sales and Finance can calculate monthly sales income. Without restricting EXECUTE privileges, anyone who discovers the function can obtain confidential figures. The same mechanism can legitimately support a trigger that writes to a protected audit_log table. section 8.5
CREATE OR REPLACE FUNCTION erp.current_month_sales()
RETURNS numeric AS $$
SELECT sum(amount)
FROM erp.payments
WHERE tstamp BETWEEN date_trunc('month', now()) AND now()
$$ LANGUAGE SQL SECURITY DEFINER;
Shows a SECURITY DEFINER function that calculates the current month's payments. section 8.5
Treat SECURITY DEFINER like the Unix setuid bit: callers temporarily gain the owner's authority. Keep such functions narrow, prevent parameter-driven repurposing, and control the objects resolved through search_path. section 8.5 section 8.7
Reduce the exposure with explicit access and trusted name resolution:
- Revoke EXECUTE from PUBLIC and grant it only to required roles. section 8.5
- Set a safe search_path such as pg_catalog, the trusted application schema, and pg_temp. section 8.5 section 8.7
- Prefer SECURITY INVOKER with explicit grants when elevated execution is unnecessary. section 8.7
BEGIN;
CREATE OR REPLACE FUNCTION erp.current_month_sales()
...
REVOKE ALL ON FUNCTION erp.current_month_sales() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION erp.current_month_sales() TO sales_role;
GRANT EXECUTE ON FUNCTION erp.current_month_sales() TO finance_role;
COMMIT;
Demonstrates replacing broad execution access with grants to named roles. section 8.5
ALTER FUNCTION erp.current_month_sales() SET search_path = pg_catalog, erp, pg_temp;
Demonstrates restricting object lookup with an explicit search_path. section 8.7
