Change owner of a database

Hey guys, I'm trying to change the owner of a database so they can complete an import, but can't seem to get them permissioned correctly. All sql commands fail. Is there any way to change an owner?

14 Replies

ambitious-aquaOP•2y ago

All attempts at giving necessary role assignments end in errors like this: ERROR: permission denied to grant role "pg_write_all_data" (SQLSTATE 42501)

flat-fuchsia•2y ago

pg_write_all_data is a cluster-level role (AFAIK), so that might be the issue. Does GRANT ALL PRIVILEGES ON DATABASE thedb TO theuser; work for you instead?

xenial-black•2y ago

no luck ERROR: permission denied for schema public we also tried: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public, information_schema, pg_catalog, pg_toast TO theuser;

ambitious-aquaOP•2y ago

Are their greater privileges granted using psql than the web UI console of neon.tech?

xenial-black•2y ago

we were hoping for a simple path to crud like access for our roles

ambitious-aquaOP•2y ago

@ShinyPokemon -- @victusfate and I working on our neon.tech deployment at our firm and ran into permission snags. Your help is much appreciated

flat-fuchsia•2y ago

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public,information_schema,pg_catalog,pg_toast TO myuser; This runs fine for me. Are you using the correct user account when running the command? This would be the neondb_owner or similar account. It should work from the SQL Editor in the console, and PSQL (so long as you use the correct role to login)

ambitious-aquaOP•2y ago

From the documentation: https://neon.tech/docs/postgres/sql-createrole this should work? database=> CREATE ROLE admin WITH CREATEDB CREATEROLE; ERROR: permission denied to create role DETAIL: Only roles with the CREATEROLE attribute may create roles. database=> And further: database=> GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public,information_schema,pg_catalog,pg_toast TO william; ERROR: permission denied for schema pg_toast

xenial-black•2y ago

was able to get that to run but was unable to alter a table after doing so

ambitious-aquaOP•2y ago

well, we can change the schema public to grant all privileges, but not the other two schemas i believe

xenial-black•2y ago

sorry yeah after earlier changes I can update public but am still unable to credit/edit/delete (verifying that again now) yeah ERROR: permission denied for schema public

ambitious-aquaOP•2y ago

@ShinyPokemon do you know why this command from the documentation doesn't work? https://discord.com/channels/1176467419317940276/1219681466598953000/1219710371485585408

flat-fuchsia•2y ago

I also had some issues after running that ALL PRVILEGES command, at least I think I did, but this worked. I connected to mydatabase as a superuser, and ran GRANT CREATE ON SCHEMA public TO myuser; After this I connected as myuser and was able to CREATE / ALTER schemas

> psql 'postgresql://neondb_owner:REDACTED@ep-floral-lake-a51kg4ud.us-east-2.aws.neon.tech/mydatabase?sslmode=require'
psql (16.2 (Homebrew))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

mydatabase=> GRANT CREATE ON SCHEMA public TO myuser;
GRANT
mydatabase=> \q
[12:11:26] evanshortiss@neon-mbp /Users/evanshortiss/workspaces
> psql 'postgresql://REDACTED@ep-floral-lake-a51kg4ud.us-east-2.aws.neon.tech/mydatabase?sslmode=require'
psql (16.2 (Homebrew))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

mydatabase=> CREATE table mytable (id SERIAL PRIMARY KEY, value TEXT);
CREATE TABLE
mydatabase=> ALTER TABLE mytable ADD num INT;
ALTER TABLE
mydatabase=>

> psql 'postgresql://neondb_owner:REDACTED@ep-floral-lake-a51kg4ud.us-east-2.aws.neon.tech/mydatabase?sslmode=require'
psql (16.2 (Homebrew))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

mydatabase=> GRANT CREATE ON SCHEMA public TO myuser;
GRANT
mydatabase=> \q
[12:11:26] evanshortiss@neon-mbp /Users/evanshortiss/workspaces
> psql 'postgresql://REDACTED@ep-floral-lake-a51kg4ud.us-east-2.aws.neon.tech/mydatabase?sslmode=require'
psql (16.2 (Homebrew))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

mydatabase=> CREATE table mytable (id SERIAL PRIMARY KEY, value TEXT);
CREATE TABLE
mydatabase=> ALTER TABLE mytable ADD num INT;
ALTER TABLE
mydatabase=>

I'll look into this a bit more. Honestly I'm not well versed in permission management, and Neon has some caveats, so I'll make a note to blog/doc around this

xenial-black•2y ago

testing that now ok was able to create a new table, alter it, insert, select, and finally drop it I'll be trying this out tomorrow to cover existing and future permissions for our roles should allow a role for usage, create, and alter

GRANT ALL PRIVILEGES ON SCHEMA public TO <user>;

GRANT ALL PRIVILEGES ON SCHEMA public TO <user>;

for table level permissions

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO <user>;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO <user>;

and for future tables

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <user>;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <user>;

if you check around internally lmk if that matches what you folks recommend

Gaming

Programming

Change owner of a database

Did you find this page helpful?