101

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=>
Alexis Wilke
  • 19,179
  • 10
  • 84
  • 156
gudepier
  • 3,362
  • 6
  • 22
  • 26

5 Answers5

172

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

Marcus Campbell
  • 2,746
  • 4
  • 22
  • 36
gudepier
  • 3,362
  • 6
  • 22
  • 26
  • this issue comes when we are using wrong user to do action like update/delete.i was facing this issue coz i used wrong user to do DMLs.when i chose the right user , the issue was solved.Also if you grant the DML privileges to the current user , this issue will be solved. – Ankur Srivastava Mar 17 '17 at 14:15
  • 1
    Just want to mention that if any user is case sensitive it should be quoted. Eg: grant "UserB" to userA -> in which case the first user is case sensitive but the second isn't – gdvalderrama Apr 24 '17 at 12:00
  • This worked for me, instead of doing `alter table ` for ownership, simply running this sorted out. I simply read this command as `grant createUser to alterUser;` where `createUser` is one who created my table and `alterUser` is one trying alter it. I faced this where we had changed DB user in our configs and DB had few existing tables. – Swapnil Chincholkar May 10 '21 at 13:43
  • This is working. – Anushka Fernando Aug 11 '23 at 10:03
22

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;
vvvvv
  • 25,404
  • 19
  • 49
  • 81
dobrivoje
  • 848
  • 1
  • 9
  • 18
21

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • 4
    The doc is more nuanced: _You *must own the table* to use ALTER TABLE. [...] 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._ => the target user (aka. userB) has already the create priv. on the schema but my current user (aka. userA) wasn't a indirect member of the new role. Thanks Mike to have enlightened me! – gudepier Feb 18 '15 at 14:26
  • 1
    An RTFM answer, if ever I found one! – Michael M Jul 25 '18 at 09:53
  • Or you can do this: https://dba.stackexchange.com/questions/27380/error-creating-index-on-postgresql-9-1 – Vincent Jul 24 '20 at 19:15
0

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>;
Karam Qusai
  • 713
  • 12
  • 16
0

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
Mateus Alves de Oliveira
  • 791
  • 6
  • 12