Thread: 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
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 >
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> 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
I don't like that solution --- pg_dump should not operate on the
assumption that it has access to a superuser account, at least not
when dumping single-owner databases.
> * 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.
Neither of these would really prevent the problem AFAICS, since you
could easily create the same situation by revoking the REFERENCE priv
afterwards.
The generic problem is that you can get into states where references
exist that should not be allowed under the current privilege setup.
It doesn't only affect foreign keys, either --- consider for example
a view that references a table in another schema, and suppose USAGE
rights on that other schema are revoked from the view owner.
Probably the only real solution is to implement DROP-CASCADE-like
checking when a privilege is revoked. Seems like rather a lot of
work :-(
regards, tom lane
On Mon, 14 Jul 2003, Tom Lane wrote: > Probably the only real solution is to implement DROP-CASCADE-like > checking when a privilege is revoked. Seems like rather a lot of > work :-( Yes and yes. That's why the SQL standard goes on for pages and pages about REVOKE. It will be looked at eventually, just make sure someone is taking notes on the failure cases. -- Peter Eisentraut peter_e@gmx.net
Is there a TODO here? --------------------------------------------------------------------------- Peter Eisentraut wrote: > On Mon, 14 Jul 2003, Tom Lane wrote: > > > Probably the only real solution is to implement DROP-CASCADE-like > > checking when a privilege is revoked. Seems like rather a lot of > > work :-( > > Yes and yes. That's why the SQL standard goes on for pages and pages > about REVOKE. It will be looked at eventually, just make sure someone is > taking notes on the failure cases. > > -- > Peter Eisentraut peter_e@gmx.net > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073