Need a Role that can CREATE USER but not allowed to GRANT Predefined Roles in PostgreSQL <16

Question

We are administrating DB in kubernetes to our customers. For each new cluster, we create a new user with CREATE ROLE and CREATE DB roles given to them, so that they can create their own databases and new users with stricter roles.

The problem is, with CREATE ROLE permission, the user can GRANT himself pg_execute_server_program role, and then use reverse shell attack and then get shell from our pod and read the environment variables, which is not desired. E.g. we have several secrets in env vars that the customer can take advantage of and then increase their attack range and take over more things.

In short, I want to have a user that can create new users, but can't grant himself specific predefined roles.

Update: I looked and found that ADMIN_OPTION has been added in PostgreSQL 16 to resolve such issue. My problem is we're using PostgreSQL version 13,14,15 and we can't just force upgrade all clusters.

Answer 1

You have to use at least PostgreSQL v16. In that version, CREATEROLE has been modified so that it doesn't allow you to grant membership in arbitrary roles. You can only grant membership in roles for which you have the ADMIN privilege.

With older PostgreSQL versions, you cannot safely give a user CREATEROLE.

One way around that could be a SECURITY DEFINER function owned by a superuser that allows the caller to create a role. Make sure to set search_path to something safe like pg_catalog on that function.

Answer 2

If you aren't stuck with only executing CREATE ROLE then create a custom function / procedure to allow your customers to create new users. That function would need to be created with SECURITY DEFINER permissions and restrict what options could be provided. Obviously the customer's user account wouldn't be able to create users itself.

Answer 3

I was able to figure out a workaround with supautils library which exists just to solve the problem I have here.

As I'm doing this to resolve a security issue, creating functions with SECURITY DEFINER may just add another issue.