Error creating index on PostgreSQL 9.1

Question

I have a process where user A creates a table and user B tries to create an index on the new table, owned by A. Users A and B are from the same group but user B is an automated process.

Is it possible to create an index from a user that is not the owner of the table, but is in the table's owner group?

Answer 1

You will need to assign ownership of the table to the group the two users have in common.

Demo:

Setup:

CREATE ROLE thegroup;
CREATE USER user1 IN ROLE thegroup;
CREATE USER user2 IN ROLE thegroup;
CREATE TABLE t1 ( x integer not null );
ALTER TABLE t1 OWNER TO user1;

Without further changes, here's what happens if user2 tries to add an index on t1.x:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
ERROR: must be owner of relation t1

The solution is to grant the table ownership to the shared role. As user1 or a superuser:

ALTER TABLE t1 OWNER TO thegroup;

now:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
CREATE INDEX
regress=> 

Answer 2

You can grant as below instead of altering each table's owner.

I faced below error while updating similar table,

PG::InsufficientPrivilege: ERROR: must be owner of relation <my table name>

Suppose a table was created by a DB user named "createU" and you are trying to perform other operation using another DB user named "updateU" then simply run below.

grant createU to updateU;

To check owner of table you can connect to DB console and run \d.