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@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