Home PostgreSQL Postgresql: error “must be owner of relation” when altering a table

Postgresql: error “must be owner of relation” when altering a table

0
178

We often face an issue while performing “ALTER TABLE” in postgresql even though the desired user has all privileges like ‘CREATE, ALTER, INSERT, DELETE, SELECT’ .


dvdrental=# alter table abc_test ADD COLUMN birth_date timestamp without time zone;

ERROR: must be owner of relation abc_test
SQL state: 42501

 

Issue

Suppose login “user1” had created table “abc_test” in the “dvdrental” database. Now we have created one login say “user2″and gave all privileges like ‘CREATE, ALTER, INSERT, DELETE, SELECT’ . Now if we login as “user2” and then try to alter the table “abc_test”, we will get an error as mentioned above.

1. CREATE ROLE user2 WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD 'XXXXXX';

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

3. Login with user2 and perform.
dvdrental=# alter table abc_test ADD COLUMN birth_date timestamp without time zone;

ERROR: must be owner of relation abc_test
SQL state: 42501

 

CAUSE:

We are facing above issue because of the following reasons.

  1. “user2” has not created the table “abc_test” and “user1” is the creator or owner of the table.
  2. “user2” is not superuser even though it has all the privileges like ‘CREATE, ALTER, INSERT, DELETE, SELECT’ .
  3. PostgreSQL does not allow a user to change object structure(ALTER TABLE) of other user without owning that object. It can perform INSERT, UPDATE,DELETE operations as per the privileges.

Resolution:

  1. Change the ownership of the table to “user2” using following command.
dvdrental=# alter table abc_test owner to user2;

2. Grant role “user1” to role “user2” using following command.

dvdrental=# grant user1 to user2;

3. Perform the alter table operation with a login having superuser role(say postgres).