let's disallow ALTER ROLE bootstrap_superuser NOSUPERUSER

Hi,

Currently, it's possible to remove the rolissuper bit from the
bootstrap superuser, but this leaves that user - and the system in
general - in an odd state. The bootstrap user continues to own all of
the objects it owned before, e.g. all of the system catalogs. Direct
DML on system catalogs is blocked by pg_class_aclmask_ext(), but it's
possible to do things like rename a system function out of the way and
create a new function with the same signature. Therefore, creating a
new superuser and making the original one a non-superuser is probably
not viable from a security perspective, because anyone who gained
access to that role would likely have little difficulty mounting a
Trojan horse attack against the current superusers.

There are other problems, too. (1) pg_parameter_acl entries are
considered to be owned by the bootstrap superuser, so while the
bootstrap user loses the ability to directly ALTER SYSTEM SET
archive_command, they can still grant that ability to some other user
(possibly one they've just created, if they still have CREATEROLE)
which pretty much gives the whole show away. (2) When a trusted
extension is created, the extension objects are documented as ending
up owned by the bootstrap superuser, and the bootstrap user will end
up owning them even if they are no longer super. (3) Range
constructors end up getting owned by the bootstrap user, too. I
haven't really tried to verify whether ownership of trusted extension
objects or range constructors would allow the bootstrap
not-a-superuser to escalate back to superuser, but it seems fairly
likely. I believe these object ownership assignments were made with
the idea that the bootstrap user would always be a superuser.

pg_upgrade refers to the "install user" rather than the bootstrap
superuser, but it's talking about the same thing. If you've made the
bootstrap user non-super, pg_upgrade will fail. It is only able to
connect as the bootstrap user, and it must connect as superuser or it
can't do the things it needs to do.

All in all, it seems to me that various parts of the system are built
around the assumption that you will not try to execute ALTER ROLE
bootstrap_superuser NOSUPERUSER. I suggest that we formally prohibit
that, as per the attached patch. Otherwise, I suppose we need to
prevent privilege escalation attacks from a bootstrap ex-superuser,
which seems fairly impractical and a poor use of engineering
resources. Or I suppose we could continue with the present state of
affairs where our code and documentation assume you won't do that but
nothing actually stops you from doing it, but that doesn't seem to
have much to recommend it.

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