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