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

PostgresqlSql Grant

Postgresql Problem Overview


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=>

Postgresql Solutions


Solution 1 - Postgresql

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...

Solution 2 - Postgresql

From the fine manual.

> You must own the table to use ALTER TABLE.

Or be a database superuser.

> ERROR: must be owner of relation contact

PostgreSQL error messages are usually spot on. This one is spot on.

Solution 3 - Postgresql

This solved my problem: an ALTER TABLE statement to change the ownership.

ALTER TABLE databasechangelog OWNER TO arwin_ash;
ALTER TABLE databasechangeloglock OWNER TO arwin_ash;

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestiongudepierView Question on Stackoverflow
Solution 1 - PostgresqlgudepierView Answer on Stackoverflow
Solution 2 - PostgresqlMike Sherrill 'Cat Recall'View Answer on Stackoverflow
Solution 3 - PostgresqldobrivojeView Answer on Stackoverflow