Re: Blocking execution of SECURITY INVOKER

Hi,

On Thu, 2023-01-12 at 19:29 -0800, Andres Freund wrote:
> superuser:
> # CREATE FUNCTION exec_su(p_sql text) RETURNS text LANGUAGE plpgsql
> SECURITY DEFINER AS $$BEGIN RAISE NOTICE 'executing %', p_sql;
> EXECUTE p_sql;RETURN 'p_sql';END;$$;
> # REVOKE ALL ON FUNCTION exec_su FROM PUBLIC ;

That can be solved by creating the function in a schema where ordinary
users don't have USAGE:

CREATE TABLE trick_superuser(value text default admin.exec_su('ALTER
USER less_privs SUPERUSER'));
ERROR: permission denied for schema admin

An interesting case, but it looks more like a gotcha (which is solvable
with best practices); not a fundamental problem.

> The point of the grant system is for a privileged user to safely
> allow a less privileged user to perform a safe subset of actions.

There is not necessarily a GRANT hierarchy like you describe. The two
users can be peers each with comparable privileges that might make
grants to each other.

> And the admin likely can switch into the user context of
> the less privileged user to perform operations in a safer context.

How would the admin do that? The malicious UDF can just "RESET SESSION
AUTHORIZATION" to pop back out of the safer context.

If there's not a good way to do this safely now, then we should
probably provide one.

> >
Regards,
--
Jeff Davis
PostgreSQL Contributor Team - AWS