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=>
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.
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.
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 ;-)
Another requirement is that the object has to be owned by user userA before altering it...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With