Re: pg_dump fails to set index ownership

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073