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
v1-0001-Do-not-allow-removal-of-superuser-privileges-from.patch
Description: Binary data
