The following bug has been logged online:
Bug reference: 2085
Logged by: David Begley
Email address: d.begley@uws.edu.au
PostgreSQL version: 8.1.0
Operating system: Windows XP
Description: pg_dump incompletely dumps ACLs
Details:
Running the following command:
pg_dump -v -C -f \windows\temp\dump.sql dbname
produced a plain text dump of the named database containing various
ACL-related statements (GRANT/REVOKE), but unfortunately some information
from the original running database was lost.
This means when the dump is imported to another PostgreSQL instance, the
resultant ACLs do not match those of the original instance.
The ACLs in question are those represented as "{}" (ie., nothing between the
braces); such an ACL on a database object or language object (there may be
others affected but these are the only two I have so far confirmed) is not
properly recorded by pg_dump, but the same ACL on a function is recorded
(and recreated on the destination PostgreSQL instance) perfectly fine.
The dump generated contains no GRANT/REVOKE statements for the database
itself at all, whilst the language object includes only a REVOKE from PUBLIC
(a REVOKE from the owner is missing). For functions, a REVOKE from both
PUBLIC and the owner are correctly included by pg_dump.
The dump was generated/recreated by a user with "superuser" access to the
database.
This may sound like a puzzling ACL, but regardless the dump should include
all information from the source database so it can be reproduced correctly
when restored.
Cheers..