Function Search Path Mutable
Level: WARN
Rationale
In PostgreSQL, the search_path
determines the order in which schemas are searched to find unqualified objects (like tables, functions, etc.). Setting search_path
explicitly for a function is a best practice that ensures its behavior is consistent and secure, regardless of the executing user's default search_path
settings. We recommend pinning funtions' search_path
to an empty string, search_path = ''
, which forces all references within the function's body to be fully qualified. This helps prevent unexpected behavior due to changes in the search_path
and mitigates potential security vulnerabilities.
What is the Search Path?
The search path in PostgreSQL is a list of schema names that PostgreSQL checks when trying to resolve unqualified object names like profiles
. In contrast, a fully qualified name includes the schema like public.profiles
, and always resolves the same way, regardless of the user's search_path
. By default, search_path
includes the user's schema and the public
schema. However, this can lead to unexpected behavior if different users have different search_path
settings. Specifically, unqualified references will be resolved differently depending on who is executing the function.
The Issue with Not Setting the Search Path in Functions
When a function does not have its search_path
explicitly set, it inherits the search_path
of the current session when it is invoked. This behavior can lead to several problems:
- Inconsistency: The function may behave differently depending on the user's
search_path
settings. - Security Risks: Malicious users could potentially exploit the
search_path
to direct the function to use unexpected objects, such as tables or other functions, that the malicious user controls.
How to Resolve
To ensure that your functions are secure and behave consistently, set the search path explicitly to an empty string within the function's definition.
Given a function like:
1 2 3 4 5 6 |
|
You can create or replace
the function and add the search_path
setting.
1 2 3 4 5 6 7 |
|
Remember that once you set the search_path = ''
all references to tables/functions/views/etc in your function's body must be qualified with a schema name.