Thread: pg_dump fails to set index ownership
Regarding the removal of ALTER INDEX OWNER commands from pg_dump, indexes are now restored with the wrong ownership if the user doing the restore is different than the user who owned the original index (if this sounds familiar, I reported the same problem for 8.0.0rc4 in January). ALTER INDEX OWNER no longer works, and ALTER TABLE OWNER won't change the index ownership if the table ownership doesn't actually change (i.e., nothing happens if the new owner and the old owner are the same). Should CREATE INDEX automatically set index ownership to be the same as the table ownership? Or did I miss past discussion about that? Seems like this ought to be fixed before beta1 is announced so it doesn't bite people who are trying 8.1 for the first time. postgres=# CREATE ROLE test LOGIN PASSWORD 'test'; CREATE ROLE postgres=# CREATE DATABASE test1; CREATE DATABASE postgres=# CREATE DATABASE test2; CREATE DATABASE postgres=# \c test1 test Password for user test: You are now connected to database "test1" as user "test". test1=> CREATE TABLE foo (id serial PRIMARY KEY, val text); NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE test1=> CREATE INDEX foo_val_idx ON foo (val); CREATE INDEX test1=> \q % pg_dump -U postgres test1 | psql -U postgres test2 SET SET SET COMMENT SET SET SET CREATE TABLE ALTER TABLEsetval -------- 1 (1 row) ALTER TABLE CREATE INDEX REVOKE REVOKE GRANT GRANT % psql -q -U test test2 Password for user test: test2=> \d List of relationsSchema | Name | Type | Owner --------+------------+----------+-------public | foo | table | testpublic | foo_id_seq | sequence | test (2 rows) test2=> \di List of relationsSchema | Name | Type | Owner | Table --------+-------------+-------+----------+-------public | foo_pkey | index | postgres | foopublic | foo_val_idx | index| postgres | foo (2 rows) test2=> DROP INDEX foo_val_idx; ERROR: must be owner of relation foo_val_idx test2=> \c test2 postgres Password for user postgres: You are now connected to database "test2" as user "postgres". test2=# ALTER INDEX foo_val_idx OWNER TO test; WARNING: cannot change owner of index "foo_val_idx" HINT: Change the ownership of the index's table, instead. ALTER INDEX test2=# ALTER TABLE foo OWNER TO test; ALTER TABLE test2=# \di List of relationsSchema | Name | Type | Owner | Table --------+-------------+-------+----------+-------public | foo_pkey | index | postgres | foopublic | foo_val_idx | index| postgres | foo (2 rows) test2=# ALTER TABLE foo OWNER TO postgres; ALTER TABLE test2=# ALTER TABLE foo OWNER TO test; ALTER TABLE test2=# \di List of relationsSchema | Name | Type | Owner | Table --------+-------------+-------+-------+-------public | foo_pkey | index | test | foopublic | foo_val_idx | index | test | foo (2 rows) -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes: > Regarding the removal of ALTER INDEX OWNER commands from pg_dump, > indexes are now restored with the wrong ownership if the user doing > the restore is different than the user who owned the original index pg_dump is not the source of the problem. We should instead arrange that an index's relowner value is copied directly from the parent table during CREATE INDEX. This is probably more important now with roles, since GetUserId() might not have a lot to do with the table's owner ID. regards, tom lane
Tom Lane wrote: > Michael Fuhr <mike@fuhr.org> writes: > > Regarding the removal of ALTER INDEX OWNER commands from pg_dump, > > indexes are now restored with the wrong ownership if the user doing > > the restore is different than the user who owned the original index > > pg_dump is not the source of the problem. We should instead arrange > that an index's relowner value is copied directly from the parent table > during CREATE INDEX. This is probably more important now with roles, > since GetUserId() might not have a lot to do with the table's owner ID. My testing indicated this is fixed. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Yep, testing confirms this is fixed. Thanks for the report. --------------------------------------------------------------------------- Michael Fuhr wrote: > Regarding the removal of ALTER INDEX OWNER commands from pg_dump, > indexes are now restored with the wrong ownership if the user doing > the restore is different than the user who owned the original index > (if this sounds familiar, I reported the same problem for 8.0.0rc4 > in January). ALTER INDEX OWNER no longer works, and ALTER TABLE > OWNER won't change the index ownership if the table ownership doesn't > actually change (i.e., nothing happens if the new owner and the old > owner are the same). Should CREATE INDEX automatically set index > ownership to be the same as the table ownership? Or did I miss > past discussion about that? > > Seems like this ought to be fixed before beta1 is announced so it > doesn't bite people who are trying 8.1 for the first time. > > postgres=# CREATE ROLE test LOGIN PASSWORD 'test'; > CREATE ROLE > postgres=# CREATE DATABASE test1; > CREATE DATABASE > postgres=# CREATE DATABASE test2; > CREATE DATABASE > postgres=# \c test1 test > Password for user test: > You are now connected to database "test1" as user "test". > test1=> CREATE TABLE foo (id serial PRIMARY KEY, val text); > NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" > CREATE TABLE > test1=> CREATE INDEX foo_val_idx ON foo (val); > CREATE INDEX > test1=> \q > > % pg_dump -U postgres test1 | psql -U postgres test2 > SET > SET > SET > COMMENT > SET > SET > SET > CREATE TABLE > ALTER TABLE > setval > -------- > 1 > (1 row) > > ALTER TABLE > CREATE INDEX > REVOKE > REVOKE > GRANT > GRANT > > % psql -q -U test test2 > Password for user test: > test2=> \d > List of relations > Schema | Name | Type | Owner > --------+------------+----------+------- > public | foo | table | test > public | foo_id_seq | sequence | test > (2 rows) > > test2=> \di > List of relations > Schema | Name | Type | Owner | Table > --------+-------------+-------+----------+------- > public | foo_pkey | index | postgres | foo > public | foo_val_idx | index | postgres | foo > (2 rows) > > test2=> DROP INDEX foo_val_idx; > ERROR: must be owner of relation foo_val_idx > test2=> \c test2 postgres > Password for user postgres: > You are now connected to database "test2" as user "postgres". > test2=# ALTER INDEX foo_val_idx OWNER TO test; > WARNING: cannot change owner of index "foo_val_idx" > HINT: Change the ownership of the index's table, instead. > ALTER INDEX > test2=# ALTER TABLE foo OWNER TO test; > ALTER TABLE > test2=# \di > List of relations > Schema | Name | Type | Owner | Table > --------+-------------+-------+----------+------- > public | foo_pkey | index | postgres | foo > public | foo_val_idx | index | postgres | foo > (2 rows) > > test2=# ALTER TABLE foo OWNER TO postgres; > ALTER TABLE > test2=# ALTER TABLE foo OWNER TO test; > ALTER TABLE > test2=# \di > List of relations > Schema | Name | Type | Owner | Table > --------+-------------+-------+-------+------- > public | foo_pkey | index | test | foo > public | foo_val_idx | index | test | foo > (2 rows) > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Fri, Sep 23, 2005 at 04:45:02PM -0400, Bruce Momjian wrote: > Tom Lane wrote: > > Michael Fuhr <mike@fuhr.org> writes: > > > Regarding the removal of ALTER INDEX OWNER commands from pg_dump, > > > indexes are now restored with the wrong ownership if the user doing > > > the restore is different than the user who owned the original index > > > > pg_dump is not the source of the problem. We should instead arrange > > that an index's relowner value is copied directly from the parent table > > during CREATE INDEX. This is probably more important now with roles, > > since GetUserId() might not have a lot to do with the table's owner ID. > > My testing indicated this is fixed. Tom fixed it shortly after making that post: http://archives.postgresql.org/pgsql-committers/2005-08/msg00347.php -- Michael Fuhr