Surviving Without A Superuser - Part Two

If PostgreSQL had the ability to give to a privileged non-superuser the right to administer objects belonging to some designated group of superusers just as if the privileged account were superuser, it would get us much closer to a world in which the database can be effectively administered by a non-superuser. A highly privileged user - let's call him sauron - could be given the right to administer tables, schemas, functions, procedures, and a variety of other objects owned by dependent users witchking and khamul just as if sauron were superuser. sauron might indeed feel himself to be virtually a superuser, at least within his own domain, as long as he didn't spend too much time thinking about the users over which he had not been given administrative rights. However, sauron might notice a few irksome limitations.

First, sauron would certainly want to be able to add users to the system, and will want those users under his own full control. There is a role attribute called CREATEROLE which can be set to give sauron permission to create new roles, but in order to make sauron feel as if he were a superuser, we'll need to adjust the behavior of that role attribute so that when sauron uses it to create a new account for let's say denethor, sauron controls the objects belonging to that account just as he does for the witchking account.

Second, in existing releases, only superusers can set up logical replication. The superuser might (or might not) want to confer this privilege upon sauron. However, because sauron doesn't have administrative control of every user on the system, he can't be permitted to replicate into a table controlled by user such as gandalf who is outside his jurisdiction. In existing releases, only superusers can create subscriptions; this restriction will need to be relaxed. Because existing releases only permit superusers to create subscriptions, they also don't perform any permissions checks when data is replicated. That has just recently been fixed, as it must be to ensure that, if sauron is allowed to create subscriptions, he can't thereby mount a privilege escalation attack.

Third, in existing releases, only superuser can create event triggers. sauron might want to do that, too, and the superuser might want to allow him to do so. However, just as in the case of logical replication, sauron's considerable privileges should not entitle him to take over the entire system. It seems reasonable to allow sauron to create event triggers containing arbitrary code and to force the accounts he controls to execute that code under the circumstances sauron chooses - but sauron can't force gandalf - or the superuser - to do anything.

Fourth, sauron might want to use ALTER SYSTEM to change the values of settings on a system-wide basis. It would be inadvisable for the superuser to allow sauron to change a setting such as archive_command, because then sauron could usurp the superuser's privileges (and those of the operating system account under which PostgreSQL runs). The superuser could more reasonably decide to give sauron control over the system-wide value of some less sensitive GUC, such as work_mem or synchronous_commit. The system administrator had better think twice before giving sauron such abilities, because by nature any such changes will affect every user on the system, and some of them might not like being subject, even in part, to the dominion of sauron. At the end of the day, though, the system administrator gets to decide who gets how much control over the system, and this kind of privilege is not an unreasonable thing for the system administrator to want to delegate.

And that's all that I know about. There may be other things that a true superuser could do which in theory could be delegated to a powerful non-superuser, but as of now they are unknown to me. If you know of things I've missed, please point them out in the comments below (or on the mailing list).

Because this list of problem is relatively short, and solutions to all of them have been proposed (see links above), a world in which it's possible for PostgreSQL to permit the creation of very powerful user accounts which are nevertheless unable to usurp the superuser's privileges or the privileges of the operating system account is within reach. I doubt that we will get all the way there in PostgreSQL 15, although it would be great if we did. The fact that the superuser is equivalent to the operating system user account can be surprising to people, and it's often not what people want. If people did want those two things to be equivalent, we probably wouldn't see things like CVE-2019-9193, which is based on a misunderstanding about just this point.

It is entirely reasonable to want to grant to a user nearly full control over PostgreSQL itself, but without the ability to "break out of jail" and usurp the privileges of the OS account. It doesn't seem realistic to make that happen by circumscribing what the superuser can do, but it does seem reasonable to approach the problem by permitting enough delegation of privileges that a non-superuser has enough power to truly administer the system yet without being able to completely take it over. This blog post - together with the previous one, and following along with the mailing list discussions - suggest what might need to be done to get us there.