BUG #14788: `pg_restore -c` won't restore schema access privileges.

From: fujimoto(at)ceptord(dot)net
To: pgsql-bugs(at)postgresql(dot)org
Cc: fujimoto(at)ceptord(dot)net
Subject: BUG #14788: `pg_restore -c` won't restore schema access privileges.
Date: 2017-08-21 10:44:39
Message-ID: 20170821104439.1455.12525@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14788
Logged by: Fujimoto Seiji
Email address: fujimoto(at)ceptord(dot)net
PostgreSQL version: 9.6.4
Operating system: Linux 4.9.0 (Debian 9.1)
Description:

## How to reproduce

1. Create a new database:

$ createdb --template=template0 test
$ psql -c '\dn+' test
list of schemas
name | owner | access privileges | description
--------+----------+----------------------+------------------------
public | postgres | postgres=uc/postgres+| standard public schema
| | =uc/postgres |
(1 row)

2. pg_dump the database with 'custom' format:

$ pg_dump -Fc -f test.dump test

3. Restore the dump with '-c' option:

$ pg_restore -c -d test test.dump

## Bug/Problem

Now all the access privileges on the public schema are gone:

$ psql -c '\dn+' test
List of schemas
Name | Owner | Access privileges | Description
--------+----------+-------------------+------------------------
public | postgres | | standard public schema
(1 row)

## Expected behabior

Granted privileges gets restored correctly.

## Additional notes

Interestingly, if the database has been dumped with '-c' option,
pg_restore
will restore the schema privileges correctly.

$ createdb --template=template0 test
$ pg_dump -Fc -c -f test.dump
$ pg_restore -c -d test test.dump
$ psql -c '\dn+' test
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(1 row)

(I do not understand why '-c' option affects the custom format dump.
This behaviour itself contradicts what the documentation states)

> -c
> --clean
>
> ...
> This option is only meaningful for the plain-text format. For the
archive
> formats, you can specify the option when you call pg_restore.
>
> https://www.postgresql.org/docs/9.6/static/app-pgdump.html

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message John Pierce 2017-08-21 14:33:00 Re: centos 7 repository
Previous Message Abhijit Gharami 2017-08-21 10:26:57 PostgreSQL 9.4.13 is facing issue in shutting down