Thread: pg_dump / restore of empty database gives errors

bash-2.05b$ ./psql newempty < file.txt
SET
You are now connected as new user rbt.
SET
REVOKE
GRANT
ERROR:  dependent privileges exist (use CASCADE to revoke them too)


The above is from the result of loading the attached file (empty
database) into "newempty" which is a different empty database.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Rod Taylor <rbt@rbt.ca> writes:
> ERROR:  dependent privileges exist (use CASCADE to revoke them too)

I saw that a couple weeks ago, and then was unable to reproduce it later
(and still can't today).  I suspect there may be some kind of
uninitialized-variable bug, or something else with not-very-consistent
behavior.  Can you dig into it while you're seeing it?
        regards, tom lane

On Sun, 2003-02-23 at 00:36, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > ERROR:  dependent privileges exist (use CASCADE to revoke them too)
>
> I saw that a couple weeks ago, and then was unable to reproduce it later
> (and still can't today).  I suspect there may be some kind of
> uninitialized-variable bug, or something else with not-very-consistent
> behavior.  Can you dig into it while you're seeing it?

Seems to be by design.  Create an empty schema with no permissions.
First REVOKE sees lack of permissions, and adds them for the owner.  The
GRANT makes PUBLIC dependent on the owner for permissions.  The second
REVOKE attempts to remove the permissions of the owner (replace
CURRENT_USER with the current user) which PUBLIC is now dependent upon.

The fix appears to be making it either an error to revoke permissions
from the owner, or to quietly ignore the request.  If we CASCADE the
second REVOKE, PUBLIC will lose their abilities.


create schema schema;

select * from pg_namespace where nspname = 'schema';

REVOKE ALL ON SCHEMA schema FROM PUBLIC;

select * from pg_namespace where nspname = 'schema';

GRANT ALL ON SCHEMA schema TO PUBLIC;

select * from pg_namespace where nspname = 'schema';

REVOKE ALL ON SCHEMA schema FROM CURRENT_USER;

select * from pg_namespace where nspname = 'schema';

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Rod Taylor <rbt@rbt.ca> writes:
>>> ERROR:  dependent privileges exist (use CASCADE to revoke them too)
>>
>> I saw that a couple weeks ago, and then was unable to reproduce it later

> Seems to be by design.  Create an empty schema with no permissions.
> First REVOKE sees lack of permissions, and adds them for the owner.  The
> GRANT makes PUBLIC dependent on the owner for permissions.  The second
> REVOKE attempts to remove the permissions of the owner (replace
> CURRENT_USER with the current user) which PUBLIC is now dependent upon.

Hmm.  So the real story here is that the permissions set up by initdb
for PUBLIC are actually an illegal state: postgres has granted
permissions to public that it isn't allowed to.  When pg_dump tries to
reproduce that state, it can't.  (There may also be an issue with the
order in which pg_dump issues its revoke/grant operations, ie, there
might be legal combinations that it can't reproduce.)  Peter, what
do you think?
        regards, tom lane

Tom Lane writes:

> Hmm.  So the real story here is that the permissions set up by initdb
> for PUBLIC are actually an illegal state: postgres has granted
> permissions to public that it isn't allowed to.

Yes, the way the permissions are initialized in the catalog templates

DATA(insert OID = 11 ( "pg_catalog" PGUID "{=U}" ));
DATA(insert OID = 99 ( "pg_toast" PGUID "{=}" ));
DATA(insert OID = 2200 ( "public" PGUID "{=UC}" ));

produce an invalid state.  I hadn't thought that this would create a
problem for pg_dump, but I will hurry up fixing it.  (I will probably put
explicit GRANT commands into initdb.)

> (There may also be an issue with the order in which pg_dump issues its
> revoke/grant operations, ie, there might be legal combinations that it
> can't reproduce.)

If you don't do manual surgery on aclitem's then I am convinced that it is
not possible to arrive at an undumpable state.  This is a consequence of
the way it's implemented.

-- 
Peter Eisentraut   peter_e@gmx.net

Has this been addressed?  I don't see any changes in initdb.sh or
pg_namespace.h.

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > Hmm.  So the real story here is that the permissions set up by initdb
> > for PUBLIC are actually an illegal state: postgres has granted
> > permissions to public that it isn't allowed to.
> 
> Yes, the way the permissions are initialized in the catalog templates
> 
> DATA(insert OID = 11 ( "pg_catalog" PGUID "{=U}" ));
> DATA(insert OID = 99 ( "pg_toast" PGUID "{=}" ));
> DATA(insert OID = 2200 ( "public" PGUID "{=UC}" ));
> 
> produce an invalid state.  I hadn't thought that this would create a
> problem for pg_dump, but I will hurry up fixing it.  (I will probably put
> explicit GRANT commands into initdb.)
> 
> > (There may also be an issue with the order in which pg_dump issues its
> > revoke/grant operations, ie, there might be legal combinations that it
> > can't reproduce.)
> 
> If you don't do manual surgery on aclitem's then I am convinced that it is
> not possible to arrive at an undumpable state.  This is a consequence of
> the way it's implemented.
> 
> -- 
> Peter Eisentraut   peter_e@gmx.net
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

--  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