Abusing SECURITY DEFINER functions in PostgreSQL

05.2019 / Category: / Tags:

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:

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:

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.


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.

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
not the river I stand in
not the river I stand in
4 years ago

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!

4 years ago

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.

not the river I stand in
not the river I stand in
4 years ago
Reply to  laurenz

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 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 function?

5 years ago

ALTER ROLE username SET search_path = pg_catalog,pg_temp
provide the same security as
SET search_path = pg_catalog,pg_temp; ?

5 years ago
Reply to  Joris

Absolutely not.
If you do not set the search_path on the function itself, then every user can just run
SET search_path = whatever;
before calling the function.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram