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

Greetings,


* Robert Haas (robertmhaas@gmail.com) wrote:
> 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.

Right- event triggers work just the same as how regular triggers on
tables do and how RLS works.  All of these also have the possibility of
leveraging security definer functions, of course, but that doesn't
address the issue of the trigger author attempting to attack the
individual running the trigger.

I do think it'd be useful to have a pg_event_triggers or such role, so
that someone could create them without being a superuser.  A bit more
discussion about that below though..

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

As I understood Mark's suggestion, the trigger would run but would have
the privileges of the intersection of both user's permissions, which is
an interesting idea but not one we've got any way to really do today as
each privilege check would now need to check two different roles for
privilege- and if one of the privilege checks fails, then what..?
Presumably there would be an ERROR returned, meaning that the operation
would be able to be prevented from happening by the trigger author,
which was objected to as not being acceptable either, per below.

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

Regular table triggers can be used to block someone from messing with
that table, so this isn't entirely unheard of.  Deciding that someone
with event trigger access is allowed to prevent certain things from
happening in a database that they're allowed to connect and create event
triggers in may not be completely unreasonable.

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

Not sure that this is really the case- as noted above, it's the same for
table-level triggers and RLS.

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Jul 23, 2021, at 2:04 PM, Mark Dilger <mark.dilger@enterprisedb.com> wrote:
> >
> > If the GUC merely converts the event trigger into an error, then you have the problem that the customer can create
eventtriggers which the service provider will need to disable (because they cause the service providers legitimate
actionsto error rather than succeed). 
>
> I'd like to expound on this a little more.
>
> Imagine the service provider has scripts that perform actions within the database, such as physical replication, or
thecreation and removal of database users in response to actions taken at the service portal web interface, and they
don'twant the actions performed by those scripts to be leveraged by the customer to break out of the jail. 

These specific use-cases are interesting because they seem to all be
about global-level objects, right?  Surely the service provider would be
better off having a separate database that they connect to, which no one
else has access to.  Not to mention that event triggers can't be created
on global objects anyway, but even so.

On the flip side- if we create a way for roles to be created by a
non-superuser in a way that doesn't end up giving away the farm (unlike
CREATEROLE privs today), then suddenly the service provider doesn't have
any need to use a superuser role or otherwise privileged role to perform
that action and they could either punt that entirely to the client to
deal with, or have a way to log in *as* the client to perform the
action while not risking the client ending up getting the service
provider's system to run code that the client wrote.

And this last point is the most relevant to all of this, in my view.
Everything that service providers provide web forms and such for to
allow the client to perform things that they can't just GRANT directly
to the client's account is the problem- they don't write those web pages
and run things as superuser because they want to, they do it because the
database system doesn't allow them any way to do it that doesn't give up
the farm (or, perhaps in some cases, they have to do *other* things too,
that clearly they can't just give the client's account access to do,
like creating tablespaces where the volume has to also be created and
attached to the instance, but those are cases where we probably don't
need to come up with a better solution..?  though I'm not against a role
to allow creating tablespaces that isn't a superuser, since the service
provider would probably be happier running that action with that role as
a good way to reduce risk further).

> The customer has event triggers which perform no illicit activities.  They don't try to break out of the jail.  But
forcompliance with HIPAA regulations (or whatever), they need to audit log everything, and they can't just have the
serviceprovider's actions unlogged. 
>
> What to do?  If the service provider disables the event triggers, then the customer will fail their regulation audit.
If the service provider allows the event triggers to fire, the customer might create a new event trigger embedding
illicitactions.  The service provider is totally stuck. 
>
> OTOH, if there were a mechanism by which an event trigger could run with only the intersection of the privileges
enjoyedby the service provider's scripts and the customer's event trigger owner, then the service provider can allow
theirown actions to be logged, without fear that any hijacking of their privilege will occur. 

If all such actions could be performed by the client role, then the
service provider suddenly doesn't have such a concern or issue- they can
tell the client to do whatever it is and then it gets logged properly.
In my view, that's really the end goal here, we just need to build these
things in a way that granting such privileges to the client doesn't end
up giving them a way to get superuser privileges.

Thanks,

Stephen