> On Jul 23, 2021, at 6:22 AM, Robert Haas <robertmh...@gmail.com> wrote:
>
> 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.
Well, one very big reason for wanting to break superuser into separate roles is
to make postgres-as-a-service providers comfortable granting more privileges to
customers. If we design any privilege escalations into one of those roles,
then no sensible service provider is ever going to grant it to anybody, which
fairly much defeats the purpose of this work. The privilege escalations we
need to prevent are not just escalations to superuser, but also escalations to
other privileged roles. Contrary to this design goal, the "pg_host_security"
role is a bit of a synonym for "superuser", since being able to write files or
execute shell commands is a path to superuser, and we can't do too much about
that. "pg_database_security", "pg_network_security", and
"pg_logical_replication" are not synonyms for "superuser".
I like your idea of designing some extra security around event triggers to
resolve their privilege escalation problems. A GUC seems the wrong approach to
me.
I think a superuser-only GUC to suppress firing event triggers won't quite cut
it, because the other privileged roles would still be in danger of being
trapped by a clever pg_database_security event trigger author; but extending
permissions on the GUC to include the other roles would mean that they, and not
just superuser, could evade event trigger based auditing solutions. That is
odd, because you wouldn't expect granting pg_network_security or
pg_logical_replication to have anything to do with granting privilege to defeat
audit logging.
A superuser-only GUC for this is also a bit too heavy handed. The superuser
may not want to circumvent all event triggers, just those put in place by the
pg_database_security role. If that sounds arbitrary, just consider the
postgres-as-a-service case. The superuser wants to be able to grant
pg_database_security to the customer, but doesn't want the customer to be able
to use that to trap the service provider.
Instead of a GUC, how about checking permissions inside event triggers for both
the user firing the trigger *and* the trigger owner. That's a backward
compatibility break, but maybe not a bad one, since until now only superusers
have been allowed to create event triggers. Systems which create an event
trigger using a role that later has superuser revoked, or which change
ownership to a non-superuser, will see a behavior change. I'm not super happy
with that, but I think it is better than the GUC based solution. Event
triggers owned by a superuser continue to work as they do now. Event triggers
owned by a non-superuser cannot be used to force a privileged user to run a
command that the event trigger owner could not have run for themself.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
