Postgresql – Error creating index on PostgreSQL 9.1

indexpostgresqlpostgresql-9.1

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?

Best Answer

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=>
Related Question