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")?...
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;
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=>
5 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...
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;
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.
For me, I had to give ownership of the complete database. and that was the case when I tried to run migrate (py manage.py migrate) in Django
how I did that, in PostgreSQL shell, please run this command:
ALTER DATABASE <name> OWNER TO <user>;
Run these commands to grant the owner permissions for all tables, views and sequences:
# Tables
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" <db_name>` ; do psql -c "alter table \"$tbl\" owner to <db_user>" <db_name> ; done
# Views
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" <db_name>` ; do psql -c "alter view \"$tbl\" owner to <db_user>" <db_name> ; done
# Sequences
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" <db_name>` ; do psql -c "alter sequence \"$tbl\" owner to <db_user>" <db_name> ; done
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
