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" <[EMAIL PROTECTED]> To: "Hackers" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
