Re: Delegating superuser tasks to new security roles (Was: Granting control of SUSET gucs to non-superusers)

> On Jul 22, 2021, at 11:21 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> When we come to the
> third thing the patch includes in this category, creating and dropping
> event triggers, I *really* don't understand why that one is considered
> host security. That one isn't touching the filesystem even to the
> extent that the extension stuff is; it seems to me to be purely
> internal to the database. Yeah, OK, that could involve writing files
> because we make catalog entries, but so could any DDL. Now, maybe
> there's a theory of operation that you have in mind that makes this
> all make more sense the way you have it, but if so, it seems not to be
> spelled out anywhere in the patch itself or the commit message you
> wrote for it, so I'm in the dark.

I agree with the need to document the theory behind all this. Event triggers are dangerous because they can trap a superuser into executing code they do not intend:

create table super_special (big_darn_secret integer);
revoke all privileges on super_special from public;
insert into super_special values (42);
-- imagine that "untrustworth_bob" is a member of as-yet unimplemented role
-- pg_database_security, and has the ability to create event triggers; to simulate
-- that, we'll put bob into superuser temporarily while the event trigger is
-- created, then remove superuser.
create role untrustworthy_bob superuser;
set session authorization untrustworthy_bob;
create function update_super_special_big_darn_secret() returns event_trigger as $$
begin
-- note that non-superusers should draw an error if they try this
update super_special set big_darn_secret = big_darn_secret + 1;

-- note that non-pg_host_security roles should draw an error if they try this
perform pg_rotate_logfile();
end;
$$ language plpgsql;
create event trigger secret_sauce on sql_drop
execute procedure update_super_special_big_darn_secret();
reset session authorization;
alter role untrustworthy_bob nosuperuser;
set session authorization untrustworthy_bob;
update super_special set big_darn_secret = big_darn_secret + 1;
ERROR: permission denied for table super_special
select pg_rotate_logfile();
ERROR: permission denied for function pg_rotate_logfile
reset session authorization;
select * from super_special;
big_darn_secret
-----------------
42
(1 row)

create table foo_tmp (t integer);
drop table foo_tmp;
WARNING: rotation not possible because log collection not active
select * from super_special;
big_darn_secret
-----------------
43
(1 row)

When the superuser dropped table foo_tmp, pg_rotate_logfile() got called, as did an update of table super_special. Any other function could have been called from there instead. That's a big deal. If creating event triggers is delegated to nonsuperuser members of pg_database_security, I think it means that pg_database_security has a privilege escalation path to become superuser. Since pg_host_security already has such an escalation path, it makes more sense to me to use this role for event trigger creation. The argument for dropping event triggers is less clear, but it seems that event triggers may be used to implement an auditing system, and we wouldn't want pg_database_security to be enough privilege to circumvent the auditing system.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company