Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgresql: error "must be owner of relation" when changing a owner object

What is the grant option/trick I need to give to the current user ("userA") to allow him to change a object's owner which belongs by another user ("userC")?

More precisely, the contact table is owned by the userC and when I perform the following query for changing the owner to the userB, connected with the userA:

alter table contact owner to userB; 

I get this error:

ERROR:  must be owner of relation contact 

But userA has all needed rights to do that normally (the "create on schema" grant option should be enough):

grant select,insert,update,delete on all tables in schema public to userA;  grant select,usage,update on all sequences in schema public to userA; grant execute on all functions in schema public to userA; grant references, trigger on all tables in schema public to userA; grant create on schema public to userA; grant usage on schema public to userA; 

Thks


Command line output:

root@server:~# psql -U userA myDatabase myDataBase=>\dt contact     List of relations Schema |  Name   |   Type   |  Owner -------+---------+----------+--------- public | contact | table    | userC (1 row) myDataBase=> myDataBase=>alter table contact owner to userB; ERROR:  must be owner of relation public.contact myDataBase=> 
like image 748
gudepier Avatar asked Feb 18 '15 13:02

gudepier


People also ask

Can we change the owner of table in PostgreSQL?

You must own the table to use ALTER TABLE. To change the schema of a table, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema.

How do I give superuser permissions in PostgreSQL?

Log into PostgreSQL and run the following ALTER USER command to change user test_user to superuser. Replace test_user with username as per your requirement. postgres-# ALTER USER test_user WITH SUPERUSER; In the above command, we use WITH SUPERUSER clause to change user to superuser.


1 Answers

Thanks to Mike's comment, I've re-read the doc and I've realised that my current user (i.e. userA that already has the create privilege) wasn't a direct/indirect member of the new owning role...

So the solution was quite simple - I've just done this grant:

grant userB to userA; 

That's all folks ;-)


Update:

Another requirement is that the object has to be owned by user userA before altering it...

like image 114
gudepier Avatar answered Oct 14 '22 18:10

gudepier