Re: BUG #2085: pg_dump incompletely dumps ACLs

On Fri, 2 Dec 2005, Alvaro Herrera wrote:

> Tom Lane wrote:
>
> > Given that -C overlaps pg_dumpall functionality anyway, maybe it should
> > dump GRANT/REVOKE commands for the database too? Any thoughts pro or
> > con out there?

See below - I hadn't tried it previously, but having now tried pg_dumpall I've
found it also has an ACL-loss bug.

> I agree. Why have only half a funcionality if we can have the whole
> thing? Maybe we can take that part of of pg_dumpall if at all possible,
> and make it use pg_dump's.

Speaking as just a dumb end-user, I get the impression that pg_dump is used to
extract a single database, not just part of a database; sayeth the manual:

"pg_dump is a utility for backing up a PostgreSQL database. [...] Script
dumps are plain-text files containing the SQL commands required to
reconstruct the database to the state it was in at the time it was
saved."

Unless I'm missing something, that means pg_dump should include (when told to
do so, as in this case) _all_ commands required to restore the database "to
the state it was in at the time it was saved". What's happening at the moment
is that some GRANT/REVOKE commands are included but others are missing,
meaning any transfer of a database is potentially losing (at least some) ACLs
along the way.

I have verified that _no_ GRANT/REVOKE commands are dumped for the database,
and only some GRANT/REVOKE commands are dumped for "language" objects (see
below); near as I can tell, all GRANT/REVOKE commands are dumped for
"function" and "table" objects, but it's early in the morning so I may be
missing something.

When I say that "only some" commands are dumped for "language" objects,
remember my original bug report - I tripped over this whilst verifying whether
or not "{}" ACLs (ie., not even owner or PUBLIC) are correctly
dumped/restored. For functions, I find this in the pg_dump output:

REVOKE ALL ON FUNCTION function_check() FROM PUBLIC;
REVOKE ALL ON FUNCTION function_check() FROM postgres;

In other words, dump/restore appears to do its job. For the database, there
are no GRANT/REVOKE statements at all. For "language" objects, pg_dump
provides:

REVOKE ALL ON LANGUAGE plpgsql FROM PUBLIC;

Here, a second statement revoking rights from the owner (in order to achieve
the "{}" ACL in the original database) is missing (yet it revoked rights
from PUBLIC - why only half an ACL?).

> Does pg_dump -C include the database comment already? If not, maybe
> it's worth to add it as well.

A very basic test for me shows the database comment is already included.

Bearing in mind Tom's statement regarding pg_dump versus pg_dumpall, I ran the
latter to check its output (though in reality it's not the entire cluster I'm
trying to dump here):

- pg_dumpall includes the two missing REVOKE statements on the database
that pg_dump is mising;

- both pg_dump and pg_dumpall include the database comment; and,

- both pg_dump and pg_dumpall are missing the second REVOKE statement (for
the owner) on the "language" object (ie., bug in both).

I don't know what other objects may be affected (either in pg_dump or
pg_dumpall).

Cheers..