almost-super-user problems that we haven't fixed yet

Due to cf5eb37c5ee0cc54c80d95c1695d7fca1f7c68cb,
e5b8a4c098ad6add39626a14475148872cd687e0, and prior commits touching
related code, it should now be possible to consider handing out
CREATEROLE as a reasonable alternative to handing out SUPERUSER. Prior
to cf5eb37c5ee0cc54c80d95c1695d7fca1f7c68cb, giving CREATEROLE meant
giving away control of pg_execute_server_programs and every other
built-in role, so it wasn't possible to give CREATEROLE to a user who
isn't completely trusted. Now, that should be OK. CREATEROLE users
will only gain control over roles they create (and any others that the
superuser grants to them). Furthermore, if you set
createrole_self_grant to 'inherit' or 'set, inherit', a CREATEROLE
user will automatically inherit the privileges of the users they
create, hopefully making them feel like they are almost a superuser
without letting them actually take over the world.

Not very surprisingly, those commits failed to solve every single
problem that anyone has ever thought about in this area.

Here is a probably-incomplete list of related problems that are so far unsolved:

1. It's still possible for a CREATEROLE user to hand out role
attributes that they don't possess. The new prohibitions in
cf5eb37c5ee0cc54c80d95c1695d7fca1f7c68cb prevent a CREATEROLE user
from handing out membership in a role on which they lack sufficient
permissions, but they don't prevent a CREATEROLE user who lacks
CREATEDB from creating a new user who does have CREATEDB. I think we
should subject the CREATEDB, REPLICATION, and BYPASSRLS attributes to
the same rule that we now use for role memberships: you've got to have
the property in order to give it to someone else. In the case of
CREATEDB, this would tighten the current rules, which allow you to
give out CREATEDB without having it. In the case of REPLICATION and
BYPASSRLS, this would liberalize the current rules: right now, a
CREATEROLE user cannot give REPLICATION or BYPASSRLS to another user
even if they possess those attributes.

This proposal doesn't address the CREATEROLE or CONNECTION LIMIT
properties. It seems possible to me that someone might want to set up
a CREATEROLE user who can't make more such users, and this proposal
doesn't manufacture any way of doing that. It also doesn't let you
constraint the ability of a CREATEROLE user to set a CONNECTION LIMIT
for some other user. I think that's OK. It might be nice to have ways
of imposing such restrictions at some point in the future, but it is
not very obvious what to do about such cases and, importantly, I don't
think there's any security impact from failing to address those cases.
If a CREATEROLE user without CREATEDB can create a new role that does
have CREATEDB, that's a privilege escalation. If they can hand out
CREATEROLE, that isn't: they already have it.

2. It's still impossible for a CREATEROLE user to execute CREATE
SUBSCRIPTION, so they can't get logical replication working. There was
a previous thread about fixing this at
https://www.postgresql.org/message-id/flat/9DFC88D3-1300-4DE8-ACBC-4CEF84399A53%40enterprisedb.com
and the corresponding CF entry is listed as committed, but
CreateSubscription() still requires superuser, so I think that maybe
that thread only got some of the preliminary permissions-check work
committed and the core problem is yet to be solved.

3. Only superusers can control event triggers. In the thread at
https://www.postgresql.org/message-id/flat/914FF898-5AC4-4E02-8A05-3876087007FB%40enterprisedb.com
it was proposed, based on an idea from Tom, to allow any user to
create event triggers but, approximately, to only have them fire for
code running as a user whose privileges the creator already has. I
don't recall the precise rule that was proposed and it might need
rethinking in view of 3d14e171e9e2236139e8976f3309a588bcc8683b, and I
think there was also some opposition to that proposal, so I'm not sure
what the way forward here is.

4. You can reserve a small number of connections for the superuser
with superuser_reserved_connections, but there's no way to do a
similar thing for any other user. As mentioned above, a CREATEROLE
user could set connection limits for every created role such that the
sum of those limits is less than max_connections by some margin, but
that restricts each of those roles individually, not all of them in
the aggregate. Maybe we could address this by inventing a new GUC
reserved_connections and a predefined role
pg_use_reserved_connections.

5. If you set createrole_self_grant = 'set, inherit' and make alice a
CREATEROLE user and she goes around and creates a bunch of other users
and they all run around and create a bunch of objects and then alice
tries to pg_dump the entire database, it will work ... provided that
there are no tables owned by any other user. If the superuser has
created any tables, or there's another CREATEROLE user wandering
around creating tables, or even a non-CREATEROLE user whose
permissions alice does not have, pg_dump will try to lock them and
die. I don't see any perfect solution to this problem: we can neither
let alice dump objects on which she does not have permission, nor can
we silently skip them in the interest of giving alice a better user
experience, because if we do that then somebody will end up with a
partial database backup that they think is a complete database backup
and that will be a really bad day. However, I think we could add a
pg_dump option that says, hey, please only try to dump tables we have
permission to dump, and skip the others. Or, of course, alice could
use -T and -N as required, but a dedicated switch for
skip-stuff-i-can't-access-quietly might be a better user experience. I
guess you could also argue that this isn't really a problem in the
first place because you could always choose to grant
pg_read_all_tables to the almost-super-user, but maybe that's not
always desirable. Not sure.

Just to be clear, there are lots of other things that a non-superuser
cannot do, such as CREATE LANGUAGE. However, I'm excluding that kind
of thing from this list because it's intrinsically unsafe to allow a
non-superuser to do that, since it's probably a gateway to arbitrary
code execution and then you can probably get superuser for real, and
control of the OS account, too. What I'm interested in is developing a
list of things that could, with the right infrastructure, be delegated
to non-superusers safely but which, as things stand today, cannot be
delegated to non-superusers. Contributions to the list are most
welcome as are thoughts on the proposals above.

Thanks,

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