Re: pg_dump / restore of empty database gives errors

On Sun, 2003-02-23 at 00:36, Tom Lane wrote:
> Rod Taylor <rbt(at)rbt(dot)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(at)rbt(dot)ca>

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