Re: Bad permissions bug in 7.3 dump (and 7.4)?

Has anyone looked at this problem? I have delved into the source code, but
I can't for the life of me see where to make the change. I think there are
actually a few possible solutions:

* Dump all foreign key constraints as a superuser
* Prevent changing ownership of tables that have foreign keys where the new
owner does not have REFERENCE privs for all referenced tables.
* Grant REFERENCE to new owner when changing ownership of table.

Chris

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Hackers" <pgsql-hackers(at)postgresql(dot)org>
Sent: Tuesday, July 08, 2003 9:35 AM
Subject: [HACKERS] Bad permissions bug in 7.3 dump (and 7.4)?

> This:
>
> create user bob;
> create user sue;
> \c - bob
> create table parent (a int4 primary key);
> create table child(b int4 references parent);
> \c - chriskl (I'm superuser)
> alter table child owner to sue;
>
> Now, do a dump:
>
> pg_dump test > script.sql (attached)
>
> And try to restore it:
>
> bash-2.03$ psql test < script.sql
> You are now connected as new user chriskl.
> REVOKE
> GRANT
> You are now connected as new user bob.
> SET
> CREATE TABLE
> You are now connected as new user sue.
> SET
> CREATE TABLE
> You are now connected as new user bob.
> SET
> You are now connected as new user sue.
> SET
> You are now connected as new user bob.
> SET
> NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
> 'parent_pkey' for table 'parent'
> ALTER TABLE
> You are now connected as new user sue.
> SET
> NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR: parent: permission denied
>
> The solution (it seems to me) is to add all the foreign keys under the
> superuser account, NOT the owner of either table account.
>
> Chris
>

----------------------------------------------------------------------------
----

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>