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)
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 ?