A PostgreSQL schema search path is the ordered list used to resolve an unqualified object name such as customers. PostgreSQL searches each listed schema in order and uses the first matching object; objects outside the path do not match. The usual default checks a schema named for the current user, then public. section 8.6
The tempting shortcut
With "$user", public, erp as the path, an unqualified current_month_sales() can resolve to public.current_month_sales() before erp.current_month_sales(). A user able to create objects in an earlier schema can accidentally shadow a trusted object or deliberately cause reads, writes, or code execution to use their object instead. section 8.6
Treat search-path order as a security boundary:
- Schema-qualify objects when the intended owner and schema matter; an unqualified name can resolve differently as path contents or ordering change. section 8.6 section 8.7
- Restrict object creation in schemas that appear in other users’ paths; for example, revoke CREATE on public from PUBLIC where appropriate. section 8.6
- Set an explicit safe path on each SECURITY DEFINER function, and place pg_temp last in that path. section 8.5
- Limit execution privileges on SECURITY DEFINER functions and use them only when necessary; unsafe resolution can otherwise become a privilege-escalation path. section 8.5 section 8.7
ALTER FUNCTION erp.current_month_sales() SET search_path = pg_catalog, erp, pg_temp;
This function-level setting fixes name resolution for a privileged function to pg_catalog, erp, then pg_temp rather than inheriting a caller-controlled or unsafe ordering. section 8.5
