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@familyhealth.com.au>
To: "Hackers" <pgsql-hackers@postgresql.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@postgresql.org so that your
> message can get through to the mailing list cleanly
>
