Re: 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 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

Michael Fuhr <mike(at)fuhr(dot)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(at)fuhr(dot)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(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

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

On Fri, Sep 23, 2005 at 04:45:02PM -0400, Bruce Momjian wrote:
> Tom Lane wrote:
> > Michael Fuhr <mike(at)fuhr(dot)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