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

On Thu, Jul 22, 2021 at 5:35 PM Mark Dilger
<mark.dilger@enterprisedb.com> wrote:
> I agree with the need to document the theory behind all this.  Event triggers are dangerous because they can trap a
superuserinto executing code they do not intend:
 

That's true. Regular triggers and views can also do that, and so can
operators and functions that capture queries intended to reference
system-defined objects. It's already the case that a superuser who
executes any optimizable query potentially compromises the system
unless (a) they first sanitize their search_path and (b) the query is
a SELECT that involves no views or an INSERT, UPDATE, or DELETE on a
table that has no triggers. However, event triggers owned by
non-superusers would extend the hazard to nearly all DDL commands.

Classifying event triggers as "host" security doesn't seem like the
right solution, though. I think the only way that fits into the "host"
security category is if you use some kind of definition that works by
exclusion: things that are "database" security shouldn't have
consequence X, therefore anything that does must go in some other
category. I think that kind of definition is very hard for people to
understand, though. It's much nicer to have definitions that tell you
what does go into a category than what doesn't.

I suppose one alternative we could consider is just leaving some
things uncategorized. I had hoped we could put everything in a bucket
so that the superuser role was really divided into pieces and not just
having bits chipped off of it, but maybe that's too ambitious. The
more we drift into saying that some things like "well this has to be
host security because database security breaks the model" the more
useless host security is likely to be as a concept. It's not a related
collection of things any more; it's just whatever didn't fit in the
other bucket. And nobody wants to GRANT a_bunch_of_other_stuff.

However, I also wonder whether we should think about engineering a
solution to this problem. For example, we have a row_security GUC. If
you set it to off, no RLS policies will be applied; if applying one
would have been mandatory, you will get an error instead. I don't
think that exact design will work here because there's no such thing
as permission to bypass event triggers, but we could have a
fire_event_triggers GUC (or whatever we call it) which is normally
"on" but privileged users can turn it off. Now if you're worried about
this problem, you have an easy way around it.

And I think that's a good illustration of why it's a bad idea to
categorize things according to whether or not they have a certain
consequence. Suppose we said, ah well, let's make event triggers
"host" security because it's too dangerous to make them "database"
security. Well then say someone comes along and implements the feature
I just described, reducing the danger. Do we then reclassify that
feature as "database" security? The original rationale for making it
something else is no longer valid, but on the other hand, what about
backward compatibility? Classifying things based on what they do,
rather than on the ultimate consequences that they may have, avoids
this kind of conundrum.

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