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
Tue, 8 Jul 2003 09:35:50 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
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@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
>


Wed, 9 Jul 2003 09:58:47 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Bad permissions bug in 7.3 dump (and 7.4)?
"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

Mon, 14 Jul 2003 16:13:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bad permissions bug in 7.3 dump (and 7.4)? 
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

Thu, 17 Jul 2003 10:10:49 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Bad permissions bug in 7.3 dump (and 7.4)? 

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, Pennsylvania 19073

Mon, 21 Jul 2003 11:27:31 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bad permissions bug in 7.3 dump (and 7.4)?