FAQ
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

Search Discussions

  • Christopher Kings-Lynne at Jul 9, 2003 at 1:57 am
    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
  • Tom Lane at Jul 14, 2003 at 8:12 pm

    "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
  • Peter Eisentraut at Jul 17, 2003 at 8:01 am

    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
  • Bruce Momjian at Jul 21, 2003 at 3:28 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 8, '03 at 1:35a
activeJul 21, '03 at 3:28p
posts5
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase