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

Robert Haas:
> 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.

This hazard exists for all tables that alice has been granted the 
TRIGGER privilege on. While we prevent alice from creating tables that 
are owned by pg_read_all_settings, we do not prevent inheriting the 
TRIGGER privilege.

> 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.

Right. This will always be the case when giving out the TRIGGER 
privilege on one of your tables to somebody else.

There is two kind of TRIGGER privileges: An explicitly GRANTed privilege 
and an implicit privilege, that is given to the table owner.

I think, when WITH INHERIT TRUE, SET FALSE is set, we should:
- Inherit all explicitly granted privileges
- Not inherit any DDL privileges implicitly given through ownership: 
CREATE, REFERENCES, TRIGGER.
- Inherit all other privileges implicitly given through ownership (DML + 
others)

Those implicit DDL privileges should be considered part of WITH SET 
TRUE. When you can't do SET ROLE x, then you can't act as the owner of 
any object owned by x.

Or to put it the other way around: Only allow implicit ownership 
privileges to be executed when the CURRENT_USER is the owner. But 
provide a shortcut, when you have the WITH SET TRUE option on a role, so 
that you don't need to do SET ROLE + CREATE TRIGGER, but can just do 
CREATE TRIGGER instead. This is similar to the mental model of 
"requesting and accepting a transfer of ownership" with an implicit SET 
ROLE built-in, that I used before.

Best

Wolfgang