The following bug has been logged on the website:
Bug reference: 8545
Logged by: Lloyd Albin
Email address: lalbin@fhcrc.org
PostgreSQL version: 9.3.1
Operating system: SUSE Linux (64-bit)
Description:
Most people I know, including myself, use pg_dumpall -g to dump out the
globals such as user accounts and then use pg_dump -Fc or -Fd to dump out
their databases in the compressed formats. When doing this method the
database level grants have been missed, although if people used the
pg_dumpall to dump the entire server they will be dumped, but not in a
compressed format that they can use with pg_restores multi-job.
I have tested this issue with Postgres Versions:
9.0.12
9.2.4
9.3.0
9.3.1
You may easily test this by doing:
CREATE DATABASE new_db
WITH OWNER = postgres
ENCODING = 'UTF8'
TEMPLATE = template0;
GRANT CREATE
ON DATABASE new_db TO dw;
CREATE ROLE dw WITH LOGIN PASSWORD 'test';
pg_dump -Fc new_db -f new_db.pgdump
pg_restore -Fc new_db.pgdump -C > new_db.sql
We can now look at the new_db.sql and can see that there is no database
level grants such as granting the CREATE to dw.
Lloyd Albin
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Vaccine and Infectious Disease Division (VIDD)
Fred Hutchinson Cancer Research Center (FHCRC)