Обсуждение: pg_dump fails to set index ownership

PostgreSQL 8.0.0rc4

pg_dump fails to set ownership on indexes.  When a database superuser
restores a dump, indexes will be owned by the superuser instead of
by the table's owner.  The table owner will then be unable to drop
or alter the index.

Here's how to reproduce:

createuser -Upostgres -PAD testuser
createdb -Upostgres test
psql -Utestuser test
CREATE TABLE foo (id serial PRIMARY KEY, name text);
CREATE INDEX foo_name_idx ON foo (name);
\q
pg_dump -Upostgres test > test.sql
dropdb -Upostgres test
createdb -Upostgres test
psql -Upostgres test < test.sql
psql -Utestuser test
\di
                List of relations
 Schema |     Name     | Type  |  Owner   | Table
--------+--------------+-------+----------+-------
 public | foo_name_idx | index | postgres | foo
 public | foo_pkey     | index | postgres | foo
(2 rows)

DROP index foo_name_idx;
ERROR:  must be owner of relation foo_name_idx

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, Jan 10, 2005 at 07:28:52PM -0700, Michael Fuhr wrote:

> pg_dump fails to set ownership on indexes.

Is this a bug in pg_dump, or is it perhaps a bug in CREATE INDEX?
Is there any reason CREATE INDEX shouldn't set the index owner to
be the same as the table owner?

For pg_dump's part, it's issuing ALTER TABLE OWNER TO immediately
after creating the table but before populating it and creating the
indexes.  If ALTER TABLE OWNER TO were issued after creating the
indexes then the ownership would cascade to the indexes.  Or pg_dump
could issue ALTER INDEX OWNER TO statements after creating the
indexes.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike@fuhr.org> writes:
> On Mon, Jan 10, 2005 at 07:28:52PM -0700, Michael Fuhr wrote:
>> pg_dump fails to set ownership on indexes.

> Is this a bug in pg_dump, or is it perhaps a bug in CREATE INDEX?
> Is there any reason CREATE INDEX shouldn't set the index owner to
> be the same as the table owner?

Hm.  CREATE INDEX never did so in the past, and I suspect that at some
point along the line we explicitly decided that that was a good idea.
But it'd be worth thinking about some more.  A related point is that
ALTER TABLE ... OWNER does not recurse to the table's indexes.

The behavior of pg_dump definitely is a bug because it is specifically
associated with the change to emit ALTER ... OWNER commands instead of
SET SESSION AUTHORIZATION commands --- if you select the latter option
then the indexes are created with the right ownership.  So I went in and
fixed it to make the ALTER OWNER path behave the same as the historical
behavior has been.

            regards, tom lane

On Tue, Jan 11, 2005 at 12:25:31AM -0500, Tom Lane wrote:

> A related point is that ALTER TABLE ... OWNER does not recurse to
> the table's indexes.

Eh?  ALTER TABLE ... OWNER won't touch the indexes if the table
owner doesn't change, but if the table owner changes then so do
the index owners.  I don't know what behavior is intended, but
that's what currently happens:

SELECT relname, relkind, relowner FROM pg_class WHERE relname LIKE 'foo%';
  relname   | relkind | relowner
------------+---------+----------
 foo        | r       |      100
 foo_id_seq | S       |      100
 foo_pkey   | i       |        1
(3 rows)

ALTER TABLE foo OWNER TO mfuhr;  -- mfuhr = 100, so no table owner change
SELECT relname, relkind, relowner FROM pg_class WHERE relname LIKE 'foo%';
  relname   | relkind | relowner
------------+---------+----------
 foo        | r       |      100
 foo_id_seq | S       |      100
 foo_pkey   | i       |        1
(3 rows)

ALTER TABLE foo OWNER TO postgres;  -- table owner change
SELECT relname, relkind, relowner FROM pg_class WHERE relname LIKE 'foo%';
  relname   | relkind | relowner
------------+---------+----------
 foo        | r       |        1
 foo_id_seq | S       |        1
 foo_pkey   | i       |        1
(3 rows)

ALTER TABLE foo OWNER TO mfuhr;  -- table owner change
SELECT relname, relkind, relowner FROM pg_class WHERE relname LIKE 'foo%';
  relname   | relkind | relowner
------------+---------+----------
 foo        | r       |      100
 foo_id_seq | S       |      100
 foo_pkey   | i       |      100
(3 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike@fuhr.org> writes:
> On Tue, Jan 11, 2005 at 12:25:31AM -0500, Tom Lane wrote:
>> A related point is that ALTER TABLE ... OWNER does not recurse to
>> the table's indexes.

> Eh?  ALTER TABLE ... OWNER won't touch the indexes if the table
> owner doesn't change, but if the table owner changes then so do
> the index owners.

[ scratches head ... ]  Looking at the code, you're right.  I'm not sure
what I did wrong in the quick test that led me to conclude otherwise.

            regards, tom lane