Re: has_privs_of_role vs. is_member_of_role, redux - Mailing list pgsql-hackers

On Sun, Sep 25, 2022 at 5:08 AM Wolfgang Walther
<walther@technowledgy.de> wrote:
> Robert Haas:
> > Well, maybe. Suppose that role A has been granted pg_read_all_settings
> > WITH INHERIT TRUE, SET TRUE and role B has been granted
> > pg_read_all_settings WITH INHERIT TRUE, SET FALSE. A can create a
> > table owned by pg_read_all_settings. If A does that, then B can now
> > create a trigger on that table and usurp the privileges of
> > pg_read_all_settings, after which B can now create any number of
> > objects owned by pg_read_all_settings.
>
> I'm not seeing how this is possible. A trigger function would run with
> the invoking user's privileges by default, right? So B would have to
> create a trigger with a SECURITY DEFINER function, which is owned by
> pg_read_all_settings to actually usurp the privileges of that role. But
> creating objects with that owner is exactly the thing B can't do.

Yeah, my statement before wasn't correct. It appears that alice can't
just usurp the privileges of pg_read_all_settings trivially, but she
can create a trigger on any preexisting table owned by
pg_read_all_settings and then anyone who performs an operation that
causes that trigger to fire is at risk:

rhaas=# create role alice;
CREATE ROLE
rhaas=# create table foo (a int, b text);
CREATE TABLE
rhaas=# alter table foo owner to pg_read_all_settings;
ALTER TABLE
rhaas=# grant pg_read_all_settings to alice;
GRANT ROLE
rhaas=# grant create on schema public to alice;
GRANT
rhaas=# set session authorization alice;
SET
rhaas=> create or replace function alice_function () returns trigger
as $$begin raise notice 'this trigger is running as %', current_user;
return null; end$$ language plpgsql;
CREATE FUNCTION
rhaas=> create trigger t1 before insert or update or delete on foo for
each row execute function alice_function();
CREATE TRIGGER
rhaas=> begin;
BEGIN
rhaas=*> insert into foo values (1, 'stuff');
NOTICE:  this trigger is running as alice
INSERT 0 0
rhaas=*> rollback;
ROLLBACK
rhaas=> reset session authorization;
RESET
rhaas=# begin;
BEGIN
rhaas=*# insert into foo values (1, 'stuff');
NOTICE:  this trigger is running as rhaas
INSERT 0 0
rhaas=*# rollback;
ROLLBACK

This shows that if rhaas (or whoever) performs DML on a table owned by
pg_read_all_settings, he might trigger arbitrary code written by alice
to run under his own user ID. Now, that hazard would exist anyway for
tables owned by alice, but now it also exists for any tables owned by
pg_read_all_settings. I'm not really sure how significant that is. If
you can create triggers as some other user and that user ever does
stuff as themselves, you can probably steal their privileges, because
they will probably eventually do DML on one of their own tables and
thereby execute your Trojan trigger. However, in the particular case
of pg_read_all_settings, the intent is probably that nobody would ever
run as that user, and there is probably also no reason to create
tables or other objects owned by that user. So maybe we really can say
that just blocking SET ROLE is enough.

I'm slightly skeptical of that conclusion because the whole thing just
feels a bit flimsy. Like, the whole idea that you can compromise your
account by inserting a row into somebody else's table feels a little
nuts to me. Triggers and row-level security policies make it easy to
do things that look safe and are actually very dangerous. I think
anyone would reasonably expect that calling a function owned by some
other user might be risky, because who knows what that function might
do, but it seems less obvious that accessing a table could execute
arbitrary code, yet it can. And it is even less obvious that creating
a table owned by one role might give some other role who inherits that
user's privileges to booby-trap that table in a way that might fool a
third user into doing something unsafe. But I have no idea what we
could reasonably do to improve the situation.

-- 
Robert Haas
EDB: http://www.enterprisedb.com