Re: Issue on public schéma with Pg_restore - Mailing list pgsql-general
| From | Thomas Poty |
|---|---|
| Subject | Re: Issue on public schéma with Pg_restore |
| Date | |
| Msg-id | CAN_ctnhp6yfQJa1ej0=GVrJbOr-kUi2OkTP7Xvwt4JuQcXW=aA@mail.gmail.com Whole thread Raw |
| In response to | Re: Issue on public schéma with Pg_restore (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Issue on public schéma with Pg_restore
(Thomas Poty <thomas.poty@gmail.com>)
Re: Issue on public schéma with Pg_restore (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-general |
Hi,
About version :
This is the same on both server
[[local]] thomasproot@serverconfig=# select version();
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ version │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)
Time: 0.183 ms
[[local]] thomasproot@postgres=# select version();
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ version │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)
Time: 4.711 ms
Command :
/usr/pgsql-9.6/bin/pg_dump --username=thomasp --host=VM65 --port=5432 --quote-all-identifiers --blobs --format=c --compress=0 --verbose serverconfig > /mnt/backupPostgreSQL/serverconfig_prod/backup_in_progress/serverconfig_prod_thomasp.sql
version :
pg_dump (PostgreSQL) 9.6.7
+ pg_restore :
command :
/bin/pg_restore --username=backup --host=VM38 --port=5432 --dbname=postgres --no-password --disable-triggers --verbose --clean --create --if-exists /mnt/backupPostgreSQL/serverconfig_prod/backup_in_progress/serverconfig_prod_thomasp.sql
version :
pg_restore (PostgreSQL) 9.6.7
[[local]] thomasproot@serverconfig=# \dn+
List of schemas
┌──────────┬────────────────────┬──────────────────────────────────────────┬────────────────────────┐
│ Name │ Owner │ Access privileges │ Description │
├──────────┼────────────────────┼──────────────────────────────────────────┼────────────────────────┤
│ public │ serverconfig_owner │ serverconfig_owner=UC/serverconfig_owner↵ │ standard public schema │
│ │ │ toolboxsysadmin=U/serverconfig_owner │ │
│ public_h │ serverconfig_owner │ serverconfig_owner=UC/serverconfig_owner↵ │ │
│ │ │ toolboxsysadmin=U/serverconfig_owner │ │
└──────────┴────────────────────┴──────────────────────────────────────────┴────────────────────────┘
(2 rows)
thomasproot@template1=# \dn+
List of schemas
┌──────┬───────┬───────────────────┬─────────────┐
│ Name │ Owner │ Access privileges │ Description │
├──────┼───────┼───────────────────┼─────────────┤
└──────┴───────┴───────────────────┴─────────────┘
(0 rows)
after restore:
[[local]] thomasproot@serverconfig=# \dn+
List of schemas
┌──────────┬────────────────────┬──────────────────────────────────────────┬────────────────────────┐
│ Name │ Owner │ Access privileges │ Description │
├──────────┼────────────────────┼──────────────────────────────────────────┼────────────────────────┤
│ public │ serverconfig_owner │ serverconfig_owner=UC/serverconfig_owner ↵│ standard public schema │
│ │ │ =UC/postgres ↵│ │
│ │ │ toolboxsysadmin=U/serverconfig_owner │ │
│ public_h │ serverconfig_owner │ serverconfig_owner=UC/serverconfig_owner↵ │ │
│ │ │ toolboxsysadmin=U/serverconfig_owner │ │
└──────────┴────────────────────┴──────────────────────────────────────────┴────────────────────────┘
(2 rows)
│ │ │ toolboxsysadmin=U/serverconfig_owner │ │
│ public_h │ serverconfig_owner │ serverconfig_owner=UC/serverconfig_owner↵ │ │
│ │ │ toolboxsysadmin=U/serverconfig_owner │ │
└──────────┴────────────────────┴──────────────────────────────────────────┴────────────────────────┘
(2 rows)
It seems pg_restore automtically add privileges usage and create for public role on schema public if it found it. Is that correct?
Regards
Thomas
2018-04-25 20:24 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 04/25/2018 11:07 AM, Thomas Poty wrote:Hello,
Here is the context :
Postgres version?I have a db db1 with a schéma public on cluster C1.
This schéma doesn't have any privileges on public role.
I have a dump of this db.
What was the dump command?On an other cluster C2, the template1 doesn't contain schema public.
I have restored db1 on cluster C2 and i saw public role had the privilege create on the schéma public.
What was the restore command?--
I cannot explain this
Thank you.
Thomas?
Adrian Klaver
adrian.klaver@aklaver.com
pgsql-general by date: