EDB is proud to present the following excerpt from Surviving Without a Superuser from EDB VP, Chief Data Scientist Robert Haas' personal blog. You can read the entire article here!
"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 rolethat 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."