Обсуждение: BUG #8545: pg_dump fails to backup database level grants

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)

On 21-10-2013 19:17, lalbin@fhcrc.org wrote:
> 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.
>
Lloyd, this is not a bug. pg_dump doesn't dump global objects such as
databases, roles and tablespaces. Hence grants are not presented in your
dump. If you want global objects use pg_dumpall.


--
   Euler Taveira                   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento