Re: Blocking execution of SECURITY INVOKER - Mailing list pgsql-hackers

From Andres Freund
Subject Re: Blocking execution of SECURITY INVOKER
Date
Msg-id 20230113081641.fylfgkrpgmf4gp3q@awork3.anarazel.de
Whole thread Raw
In response to Re: Blocking execution of SECURITY INVOKER  (Jeff Davis <pgsql@j-davis.com>)
Responses Re: Blocking execution of SECURITY INVOKER
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: David Geier
Date:
Subject: Re: Sampling-based timing for EXPLAIN ANALYZE
Next
From: Jelte Fennema
Date:
Subject: Re: [EXTERNAL] Re: [PATCH] Support using "all" for the db user in pg_ident.conf