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
Attachments:
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 deniedThe solution (it seems to me) is to add all the foreign keys under the
superuser account, NOT the owner of either table account.Chris
----------------------------------------------------------------------------
----
Show quoted text
---------------------------(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, Pennsylvania 19073