Hi,
On 2023-01-12 23:38:50 -0800, Jeff Davis wrote:
> 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
Doubtful. Leaving aside the practicalities of using dedicated schemas and
enforcing their use, there's plenty functions in pg_catalog that a less
privileged user can use to do bad things.
Just think of set_config(), pg_read_file(), lo_create(), binary_upgrade_*(),
pg_drop_replication_slot()...
If the default values get evaluated, this is arbitrary code exec, even if it
requires a few contortions. And the same is true for evaluating *any*
expression.
> > 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.
I thought we had a reasonably convenient way, but now I am not sure
anymore. Might have been via a C helper function. It can be hacked together,
but this is an area that should be as unhacky as possible.
> If there's not a good way to do this safely now, then we should
> probably provide one.
Yea, particularly because we do have all the infrastructure for it
(c.f. SECURITY_LOCAL_USERID_CHANGE / SECURITY_RESTRICTED_OPERATION).
Greetings,
Andres Freund