1

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.

3 Answers 3

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.

3
  • Thank you, but I need a workaround with earlier versions of postgres. some colleges of mine suggested using trigger events, but I struggle to get it working.
    – imans77
    Feb 5 at 20:00
  • I have added a potential workaround to the answer. Feb 6 at 7:28
  • I want to mimic all functionalities of CREATE ROLE and that user be able to create new users, alter or drop them, with all arguments, etc. Just prevent granting certain predefined roles like "pg_execute_server_program". I'll be glad if you could give me the function along with a working example.
    – imans77
    Feb 6 at 22:02
1

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.

1
  • I want to mimic all functionalities of CREATE ROLE and that user be able to create new users, alter or drop them, with all arguments, etc. Just prevent granting certain predefined roles like "pg_execute_server_program". I'll be glad if you could give me the function along with a working example.
    – imans77
    Feb 6 at 22:02
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.