Skip site navigation (1) Skip section navigation (2)

Re: pg_dump / restore of empty database gives errors

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump / restore of empty database gives errors
Date: 2003-02-23 17:03:30
Message-ID: 1046019810.84130.209.camel@jester (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2003-02-23 18:00:36
Subject: Re: pg_dump / restore of empty database gives errors
Previous:From: Rod TaylorDate: 2003-02-23 14:58:41
Subject: Re: [HACKERS] Allow pg_dump to dump sequences using NO_MAXVALUE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group