Neon•2y ago

zesty-coffee

Issue with roles

When I started our database it added my full name as the owner role, and a while back we switched it to another role so a bunch of tables were created with both role. the issue is that our prisma migrations are failing on it, I believe this is because the role needs to match the one that made the table? the thing that changed is that the vercel integration wont let me add the company role now, only the owner role (I just reinstalled the plugin and this seems to have changed from before) Ideally we'd be using the other role for everything, and have it be the owner, not my personal name one... is there a way to resolve this?

25 Replies

conscious-sapphire•2y ago

I'm pretty confused by your issue. You have two users in your database?

zesty-coffeeOP•2y ago

Roles but yeah, i guess users but everywhere in neon it refers to them as roles

conscious-sapphire•2y ago

Ok, so 2 roles. The first role was the original owner of the database and created tables in that database?

zesty-coffeeOP•2y ago

the database owner is myname , i added companyname when I created the database back in October we've been using that in our connection string this whole time, but the vercel integration recently updated to only let me select myname , so now it's connecting with myname and our prisma migrations are failing

conscious-sapphire•2y ago

Then you created another role and created more tables in the same database? I see

zesty-coffeeOP•2y ago

i actually think all of the tables were made with companyname, i am looking now and seeing that I set it up like an hour after creating the database, so pretty sure they are (idk how to check)

conscious-sapphire•2y ago

I wonder what is going on internally with the integration. Are you a pro tier user?

zesty-coffeeOP•2y ago

yeah

conscious-sapphire•2y ago

I would submit a support request via the Console you will get prioritized help there. Discord is a place for all of us to talk Neon, but if you need serious support, take advantage of what you pay for.

zesty-coffeeOP•2y ago

ahh ok cool will do

conscious-sapphire•2y ago

cc @Mike J. Is this a regression in the vercel integration?

zesty-coffeeOP•2y ago

for reference, just seeing the owner in this dropdown now

conscious-sapphire•2y ago

That's weird Can you connect to your database through psql and run \dg that will list all postgres roles in your database

flat-fuchsia•2y ago

This wouldn''t be ideal for you, but can create the workflow yourself. Here's the template https://github.com/neondatabase/preview-branches-with-vercel Also do check the permissions the non owner role has.

GitHub

GitHub - neondatabase/preview-branches-with-vercel: Example project...

Example project that shows how you can create a branch for every preview deployment on Vercel using GitHub actions - neondatabase/preview-branches-with-vercel

generous-apricot•2y ago

@jaboto I think we have two different issues at play here. 1) the prisma migration, which I suspect fails reporting "Error: db error: ERROR: must be owner of table <table_modified_by_your_migration>" 2) The vercel integration no longer showing one of the two roles created. The first problem is actually pretty frequent when using multiple roles. The schema public and/or tables in that schema are probably still owned by 'jasondonnette', in which case it's expected that the prisma migration fails. Only the owner of the schema public/child tables is allowed to modify the structure of the schema/tables. To resolve this issue you would either need to grant ownership for the public schema/all children tables to the "company role", or you would need to run your prisma migration using "jasondonnette". I looked at our Support case queue and couldn't see any case raised for this issue. If you still need help with this, feel free to DM me: 1) your email address 2) the endpoint/project impacted 3) if you allow me to connect to your endpoint (If you don't want me to connect on your endpoint, I would need a detailled output of the roles present and the ownership of the schema and tables used) I would be happy to raise a support case on your behalf and dig into this deeper. Regarding the Vercel integration, I'm honestly not sure what the issue could be, but same as above: happy to raise a support case on your behalf and dig into this if you give me further details regarding your account/project/endpoint 🙂

zesty-coffeeOP•2y ago

ahh thanks for the reply, I must have gotten distracted before clicking submit on the ticket 😅 . I just submitted it through the portal, and I checked the box to say you can connect to the DB

generous-apricot•2y ago

Thanks 🙂 I'm having a look at this right now @jaboto I connected to your database and could identify where the problem comes from regarding the permissions: the neondb is owned by the original user ("jasondonnette"), while all tables but one are owned by the second role, which is why your prisma migration fails. Changing the owner of the database is not very difficult, here is an example from a test project I used to reproduce your problem:

postgres=# select datname,
postgres-#      datdba::regrole::text,
postgres-#      datdba::regrole::text = current_user as is_owner
postgres-# from pg_database;
datname  |   datdba    | is_owner
-----------+-------------+----------
neondb    | yanic       | f
(4 rows)
 
postgres=# alter database neondb owner to new_owner;
ALTER DATABASE
postgres=# select datname,
   datdba::regrole::text,
   datdba::regrole::text = current_user as is_owner
from pg_database;
datname  |   datdba    | is_owner
-----------+-------------+----------
neondb    | new_owner   | f
(4 rows)
 
postgres=#

postgres=# select datname,
postgres-#      datdba::regrole::text,
postgres-#      datdba::regrole::text = current_user as is_owner
postgres-# from pg_database;
datname  |   datdba    | is_owner
-----------+-------------+----------
neondb    | yanic       | f
(4 rows)
 
postgres=# alter database neondb owner to new_owner;
ALTER DATABASE
postgres=# select datname,
   datdba::regrole::text,
   datdba::regrole::text = current_user as is_owner
from pg_database;
datname  |   datdba    | is_owner
-----------+-------------+----------
neondb    | new_owner   | f
(4 rows)
 
postgres=#

But.... I cannot reproduce the problem that you're facing with the Vercel integration

generous-apricot•2y ago

In my case, when configuring the integration, I can select both the original owner ("Yanic") and the new one ("new_owner")

generous-apricot•2y ago

I would thus discourage any ownership change till we figured out where it hurts in the Vercel integration May I ask why did you reinstall the integration? Did you face any issue? errors? I'm sorry to ask such an obvious question but.... have you tried to uninstall the integration and then reconfigure it again? 😕

generous-apricot•2y ago

If we can make the integration work as expected, allowing you to select the correct role, I'm happy to help you change the ownership of what needs to be, but we must ensure first that the integration is working as expected If the integration only allows you to select the original owner, altering the ownership of your db won't resolve your problem (I sent you an email via the case and shared a calendly link with you. please try to uninstall and reinstall the integration, if the issue persists after this, let's have a remote session together 🙂 )

wise-white•2y ago

I created a couple of roles for a db. I want a migration role as my db owner so that it can alter table schema. I am trying to alter the owner of the DB but I don't seem to have permissions despite the role I am using having neon_superuser privileges. I get the error ERROR: must be able to SET ROLE "bomello-migration-prod" is it possible to change owners without being a postgres superuser? I checked my permissions like so: SELECT r.rolname AS role_name, r.rolsuper AS is_superuser, CASE WHEN pg_has_role(r.rolname, 'neon_superuser', 'member') THEN 'Yes' ELSE 'No' END AS is_member_of_neon_superuser FROM pg_roles r WHERE r.rolname = current_user; outputs role_name | is_superuser | is_member_of_neon_superuser -----------+--------------+----------------------------- roberttod | f | Yes and my set role command which I don't have permssion for is ALTER DATABASE "bomello-app-db" OWNER to "bomello-migration-prod"; bomello-migration-prod is also a neon_superuser @Yanic would you be able to help me with this?

conscious-sapphire•2y ago

@Robert Tod use the user that Neon initially created

generous-apricot•2y ago

@Robert Tod My apologises, I was OOO. Can you please ping me your endpoint details in MP? If it helps, and if you give me your consent, I can connect to your database to review this in depth and change the permissions for you.

wise-white•2y ago

No problem! I was wondering if there was a way to configure it in the UI/CLI. I am using my owner account now anyway. It's my name, so not ideal! but it works. Are y'all planning to make some of these things configurable in the future?

Gaming

Programming

Issue with roles

Did you find this page helpful?