Surviving Without A Superuser - Coming to v16

As I've written about before, a PostgreSQL superuser always has the ability to take over the operating system account in which PostgreSQL is running, but sometimes you'd like to have a role that can administer the database but not break out of it. In existing releases, there's no good way to accomplish that. You can either make a new role so weak that it can't perform ordinary administration tasks, or you can make it so strong that it can easily break into the operating system account and thus take over the superuser role as well. Unless you hack the source code, which some people have done, there's no real way to set up an account that has enough power to usefully administer the database in meaningful ways but yet not enough power to take over everything. I've committed a number of patches to v16 to try to improve the situation, and I think that we can look forward to big improvements in this area once it is released.

One way that you can make a PostgreSQL role very powerful is to give it the CREATEROLE property, either by creating it using a command like CREATE USER alice CREATEROLE, or by adding that property later using a command like ALTER ROLE alice CREATEROLE. Unfortunately, in existing releases, that's both too much and not enough. As you would expect from the name, the CREATEROLE property allows a role to create new roles. Less obviously, it also allows a role to manage other roles, for example by using ALTER ROLE to set the CONNECTION LIMIT for other roles or even to use ALTER ROLE to change the password for other roles, provided that those roles are not superusers or users with the REPLICATION property. All of this is great: a user who is an administrator ought to be able to create new roles and managing existing roles, and they should also be prevented from breaking into superuser accounts if they're not a superuser. Unfortunately, a CREATEROLE user is also empowered to add any role as a member of any other role, including predefined roles, which means that they can create a new account and assign it the pg_execute_server_programs role, after which they can use the new account to get control of the OS account, after which they can hack superuser. Thus, in existing releases, CREATEROLE is basically useless, at least in any kind of security-sensitive environment.

In the master branch that will become v16, this is fixed. CREATEROLE users no longer have the ability to hand out membership in any role to anyone; instead, like other users, they can only hand out memberships in roles for which they possess ADMIN OPTION. Similarly, they can hand out the sensitive role properties CREATEDB, REPLICATION, and BYPASSRLS only if they possess those properties themselves -- previously, they could give CREATEDB without having it, and could never give the other two. Moreover, the ability of CREATEROLE users to manage other users has been somewhat restricted: they can still perform user management tasks, but now only for roles that they created, or for roles that they've been given the ability to manage by some other role that already has permission to manage them. Now, when a CREATEROLE user creates a new role, they are automatically granted ADMIN OPTION on that new role, which allows them to do things like change the password or connection limit for that role, or even drop it. To be clear, ADMIN OPTION on a role isn't by itself sufficient to perform user management tasks: you need that plus CREATEROLE. The upshot of all of this is that the superuser can control exactly which accounts can be managed by a CREATEROLE user. Instead of being able to manage every non-superuser, non-replication role in the system, a CREATEROLE user will just be able to manage the accounts they created or for which they have been given admin rights.

At this point, you might be wondering whether I've forgotten about the fact that superusers can do a whole lot of things other than just manage roles. Because superusers bypass all permission checks, a superuser can do everything that any other user on the system can do. They can SET ROLE to any user. Even without doing so, they can use arbitrary DML and DDL commands on any SQL object in the system. In my opinion, this is the core of the superuser experience: to be able to select from somebody else's tables, or alter them, or drop them, or whatever you want to do, without even having to SET ROLE, unless you feel like it. The master branch now has a way to make this work for CREATEROLE users, too, but with the very important restriction that it only applies to roles created by that role, and not every role in the system, else it would be insecure. It's not the default behavior and must be enabled, because some people might want to have CREATEROLE users that are just intended to create new roles and not to access the privileges of those roles e.g. a user-creation bot. However, I think for many people, the new option will be convenient.

Lest it seem a bit too magical, let me explain how it works. As I mentioned above, when a CREATEROLE user create a new role, they automatically get ADMIN OPTION on it. However, they do not automatically inherit the privileges of that role, nor do they automatically gain the ability to SET ROLE to it. Therefore, they are only able to administer the role, not use its privileges. This, however, is something that can easily be changed. One of the privileges that you get from having ADMIN OPTION on a role is the ability to add members to it. So, a CREATEROLE user could just use the new extensions to the GRANT syntax which are committed to master to GRANT the_new_account TO my_self WITH INHERIT TRUE, SET TRUE and then they'd be able to SET ROLE to the new account and would inherit all of its privileges.

However, we've also got a way to make this automatic. There is a new parameter createrole_self_grant which, if set to a non-empty value, will cause any new roles you create using the CREATEROLE privilege to also be granted back to you with the options that you specify. If you configure createrole_self_grant = 'set, inherit' and then create a bunch of new roles, you may be surprised by the degree to which you feel just like a superuser! You'll have all the privileges of those roles and can SET ROLE to all of them freely and can drop them and change their passwords and all that good stuff. The experience will be even better if you own the database that you're accessing, since that will allow you to do things like create new schemas and give them to whichever users you like, or grant CREATE privileges on the existing public schema.

Depending on the situation, the superuser might also choose to give a CREATEROLE user the REPLICATION, CREATEDB, or BYPASSRLS role properties; might choose to grant some predefined roles to them for additional privileges; or might use GRANT ALTER SYSTEM ON PARAMETER to allow them to tweak postgresql.conf.auto. This both allows for non-superuser administrators to be quite powerful, and at the same time allows the superuser to control how powerful they are.

If you've followed PostgreSQL development for a while, you know that the project believes deeply in the idea of incremental development, which is a nice way of saying that this new system is not going to solve all the problems. For example, I'm a bit concerned about the ability of such a user to take a full dump the database with pg_dump. It does basically work pretty much the same way that it would for a superuser, but if there are any tables in the database being dumped which the administrator role can't access, then it's going to fail with a permission denied error when that table is reached. That could be worked around by either giving the administrator access to all the tables or by the administrator using the -T option to pg_dump to exclude them, but maybe that won't be convenient enough to make everyone happy. Or perhaps it's fine: I'm not sure.

Also, there's currently no way to delegate the ability to issue CREATE SUBSCRIPTION or CREATE EVENT TRIGGER to a non-superuser. This won't be a problem for everyone. Many people probably don't use event triggers at all. Subscriptions are likely more heavily used, but some superusers may want control over subscriptions to remain with the superuser rather than being delegated, in which they won't care. In cases where it's desirable to delegate control over logical replication, some workaround will be needed unless and until we can get further changes made in PostgreSQL itself. There's currently a discussion on the mailing list about how to get some form of delegation up and working, but it hasn't reached any definite conclusions yet and I don't know whether it will do so before we hit feature freeze for v16.

There are, likely, some other problems as well. But there is also a lot of stuff that now does work and, I think, in a pretty usable form. I'm excited about that, and I'd love to hear what you think. Comment below!