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

On Fri, Jul 23, 2021 at 4:57 PM Mark Dilger
<mark.dilger@enterprisedb.com> wrote:
> > On Jul 23, 2021, at 1:54 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> > Yeah, but you're inventing a system for allowing the restriction on a
> > GUC to be something other than is-superuser in the very patch we're
> > talking about. So it could be something like is-database-security.
> > Therefore I don't grok the objection.
>
> I'm not objecting to how hard it would be to implement.  I'm objecting to the semantics.  If the only non-superuser
whocan set the GUC is pg_database_security, then it is absolutely worthless in preventing pg_database_security from
trappingactions performed by pg_network_security members.  On the other hand, if pg_network_security can also set the
GUC,then pg_network_security can circumvent audit logging that pg_database_security put in place.  What's the point in
havingthese as separate roles if they can circumvent each other's authority? 

Right, that would be bad. I had forgotten how this worked, but it
seems that event triggers are called with the privileges of the user
whose action caused the event trigger to be fired, not the privileges
of the user who owns the trigger. So as you say, if you can get
somebody to do something that causes an event trigger to be fired, you
can do anything they can do. As far as I can see, the only reasonable
conclusion is that, unless we change the security model, doing
anything with event triggers will have to remain superuser-only. In
other words I don't think we can give it to any of
pg_database_security or pg_host_security or pg_network_security, or
any similar role. We could have a pg_event_triggers role that is
documented as able to usurp superuser, but I don't see the point.

Now, the other alternative is changing the security model for event
triggers, but I am not sure that really fixes anything. You proposed
having a new mode where someone could only do things that could be
done by either user, but that troubles me for a number of reasons. One
is that it often makes a difference who actually did a particular
operation. For example it might be that alice and bob both have the
ability to give charlie permission on some table, but the ACL for that
table will record who actually issued the grant. It might be that both
alice and bob have the ability to create a table, but the table will
be owned by whoever actually does. Suppose bob is about to be
terminated but can arrange for alice (who is a star performer) to
grant permissions to his accomplice charlie, thus arranging for those
permissions to survive his impending termination. That's bad.

Also, what about just throwing an ERROR? Anybody's allowed to do that,
but that doesn't mean that it's OK for one user to block everything
some other user wants to do. If seward and bates respectively have
pg_database_security and pg_network_security, it's OK for seward to
interfere with attempts by bates to access database objects, but it's
not OK for seward to prevent bates from reconfiguring network access
to PostgreSQL. Because event triggers don't fire for ALTER SYSTEM or
DDL commands on global objects, we might almost be OK here, but I'm
not sure if it's completely OK.

I'm pretty sure that the reason we set this up the way we did was
because we assumed that the person creating the event trigger would
always have maximum privileges i.e. superuser. Therefore, it seemed
"safer" to run the code under the less-privileged account. If we'd
thought about this from the perspective of having non-superuser-owned
event triggers, I think we would have made the opposite decision,
since running code as yourself in somebody else's session is less
dangerous than running code as somebody else straight up.

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