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