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