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
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 |