N
Neon•2y ago
quickest-silver

I can't alter any table from Neon SQL Editor because I can't select the owner of this table

Hey Guys, I have had a problem that has persisted for months now: when I create multiple roles using Neon UI, connect to the database using a specific role, and create a schema and tables using that role, I cannot modify that table with another user, and I can not create any table inside this schema. When I try to change the owner of that schema, I will not be able to do that. So, I need to be logged in with all the users who created schemas or tables to change multiple tables with various roles, which is painful. I also need to be able to change the owner of a schema and all its tables. Also, when using the SQL editor in Neon, I cannot change the role from there directly. It stuck with one role, but I don't know which role. 
ALTER TABLE "dev"."Account" REPLICA IDENTITY FULL;
ALTER TABLE "dev"."Account" REPLICA IDENTITY FULL;
ERROR: must be the owner of table Account (SQLSTATE 42501)
ERROR: must be the owner of table Account (SQLSTATE 42501)
I sent the support, and they told me that everything was working when I ran the change owner of the schema using queries, but that works only if I create the role using the schema owner that I want to change from the database. 
alter schema "dev" owner to admin;
alter schema "dev" owner to admin;
ERROR: must be member of role "admin"
ERROR: must be member of role "admin"
No description
4 Replies
other-emerald
other-emerald•2y ago
Can you provide a set of precise reproduction steps so we can better understand the issue? Are you unable to GRANT permissions to the various new users/roles?
quickest-silver
quickest-silverOP•2y ago
Sure, these are the steps that you will follow to reproduce the issue. 1. Create two roles from Neon Console, for example, user1 and user2 2. Login with any PostgreSQL Client, for example, Datagrip, using the first user user1 3. Create a new schema with the user1, for example dev schema 4. Create a new table in that schema Account 5. Try to change the owner of the schema or the table to user2 using alter table "dev"."Account" owner to user2; this should produce this error ERROR: must be member of role "user2" I appreciate your expertise in this matter and hope that you can assist. If you require any additional information, please don't hesitate to let me know. Thank you for your help.
other-emerald
other-emerald•2y ago
OK, so when running these commands I received the error: 
neondb=> ALTER TABLE user1schema.accounts OWNER TO user2;
ERROR:  must be able to SET ROLE "user2
neondb=> ALTER TABLE user1schema.accounts OWNER TO user2;
ERROR:  must be able to SET ROLE "user2
This is tricky. What I found is that the users created in the Neon Console (using the Roles UI) are superusers. This made things tricky with assigning grants/roles. Bear in mind I'm not a Postgres expert! What I found worked is this. Login as neondb_owner (or another superuser) and create non-super user roles: 
-- use different usernames, and better passwords :)
CREATE ROLE usera WITH LOGIN PASSWORD 's3cur3passw0rd';

CREATE ROLE userb WITH LOGIN PASSWORD 's3cur3passw0rd';

-- grant create permissions to user1 in your chosen db (neondb is the default in Neon)
GRANT CREATE ON DATABASE neondb TO usera;

-- this is required to change ownership at a later date
GRANT userb TO usera;
-- use different usernames, and better passwords :)
CREATE ROLE usera WITH LOGIN PASSWORD 's3cur3passw0rd';

CREATE ROLE userb WITH LOGIN PASSWORD 's3cur3passw0rd';

-- grant create permissions to user1 in your chosen db (neondb is the default in Neon)
GRANT CREATE ON DATABASE neondb TO usera;

-- this is required to change ownership at a later date
GRANT userb TO usera;
Login as usera 
CREATE SCHEMA useraschema;

CREATE TABLE useraschema.accounts (
  id SERIAL PRIMARY KEY
);

-- This is critical to transfer ownership AFAICT
GRANT ALL PRIVILEGES ON SCHEMA useraschema TO userb;
CREATE SCHEMA useraschema;

CREATE TABLE useraschema.accounts (
  id SERIAL PRIMARY KEY
);

-- This is critical to transfer ownership AFAICT
GRANT ALL PRIVILEGES ON SCHEMA useraschema TO userb;
Now you can run the ALTER TABLE "useraschema".accounts OWNER TO userb; command while logged in as usera. I'll ask internally about documentation and how to better handle this.
quickest-silver
quickest-silverOP•2y ago
Thanks @ShinyPokemon, I will create only one user from the console, and the other users will be created with the first superuser. I hope that you can find a solution for this issue when you create the user from the Neon UI console.

Did you find this page helpful?