Thread: pg_dump is broken by recent privileges changes

In CVS tip, create an empty database.  pg_dump it.  Try to restore the
dump.  The first thing it does is

REVOKE ALL ON SCHEMA public FROM PUBLIC;

which fails with

ERROR:  dependent privileges exist (use CASCADE to revoke them too)

This message seems incorrect --- what is a dependent privilege, and
why would PUBLIC have any?  All I see in pg_namespace is
public             |        1 | {=UC/postgres}

Also, pg_dump itself seems confused --- the full text of a dump from
an empty DB is (omitting comment lines)

\connect - postgres
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;

which is not only inefficient but wrong, since public surely should
have privileges when the dust settles.
        regards, tom lane

Tom Lane writes:

> REVOKE ALL ON SCHEMA public FROM PUBLIC;
>
> which fails with
>
> ERROR:  dependent privileges exist (use CASCADE to revoke them too)

Not here.

> This message seems incorrect --- what is a dependent privilege, and
> why would PUBLIC have any?

The term "dependent privilege" is explained on the REVOKE reference page.
And no, PUBLIC wouldn't ever have any.

> Also, pg_dump itself seems confused --- the full text of a dump from
> an empty DB is (omitting comment lines)
>
> \connect - postgres
> REVOKE ALL ON SCHEMA public FROM PUBLIC;
> GRANT ALL ON SCHEMA public TO PUBLIC;
> GRANT ALL ON SCHEMA public TO PUBLIC;
> REVOKE ALL ON SCHEMA public FROM postgres;
>
> which is not only inefficient but wrong, since public surely should
> have privileges when the dust settles.

The second GRANT is a bug because the buffer wasn't cleared.  The other
commands are correct as far as pg_dump is concerned.  At the end the
privileges are exactly "=UC/postgres", which is what they are by default.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> REVOKE ALL ON SCHEMA public FROM PUBLIC;
>> 
>> which fails with
>> 
>> ERROR:  dependent privileges exist (use CASCADE to revoke them too)

> Not here.

[ scratches head ]  Not here either; but it was definitely failing when
I wrote that message.  I'll dig into it and see if I can figure out
what changed.
        regards, tom lane