neon: Cannot create or grant roles

Running the following query:

BEGIN;
    create role test_role;
    create user test_user with encrypted password '***';
    grant test_role to test_user:
COMMIT;

Results in the following error:

ERROR: permission denied to create role (SQLSTATE 42501)

The query was performed from the UI and also programmatically with the DB owner credentials.

I tried to workaround this by creating two users through the neon UI console and then granting one user to the other (just a workaround so I can test the rest of my migration), but I was not able to grant one user to the other:

grant one_existing_user to another_existing_users;

Results in:

ERROR: must have admin option on role "one_existing_user" (SQLSTATE 42501)

The use case: My existing migrations depend on test_role to exist and expect test_role to be granted to test_user. test_role is used in the migration scripts whilst test_user is used by the application to query the database. The migrations grant limited table access to test_role. In the future, test_role can be granted to other users to be used by other applications.

Upon further investigation, it seems the only user that has the ability to create roles is the zenith_admin user.

Is it possible to give this permission to the DB owner as well? Seems that this is locking me out of my own DB a bit.

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Reactions: 1
  • Comments: 17 (5 by maintainers)

Most upvoted comments

Hey, @Ranguna

Now we started some preparatory work for this =) High chances that it will be shipped in Q1.

As a side note – probably we can make our roadmap more public to make it easier to track. WDYT @stepashka? Should we just open neon roadmap?

Hello @kelvich, I saw that the #2104 was moved to draft, are there any updates that can be shared other than that?

Hey! You can create roles now: https://neon.tech/docs/manage/roles#manage-roles-with-sql

Forgot to close this issue. Closing it now.

Any updates @kelvich ?