More

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

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

    Recent Articles

    spot_img

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox