Home > mailing lists

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	April 26, 2018 14:30:23
Msg-id	CAN_ctnjxD7YkqvtD6XFyGrHte7shv9Uk7QZEtQkvMRjTyz0+Hw@mail.gmail.com Whole thread Raw
In response to	Re: Issue on public schéma with Pg_restore (Thomas Poty <thomas.poty@gmail.com>)
List	pgsql-general

Tree view

I have run this command (same of previous one without -d and with -f argument :

/bin/pg_restore --username=backup --host=VM38 --port=5432 --no-password --disable-triggers --verbose --clean --create --if-exists -f /tmp/thomasp.log /mnt/backupPostgreSQL/serverconfig_prod/backup_in_progress/serverconfig_prod_thomasp.sql

Here is partial content of the file /tmp/thomasp.log
I don't see any "create schema public"... :

BUT I see create database ... TEMPLATE=template0

CREATE DATABASE "serverconfig" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_GB.UTF-8' LC_CTYPE = 'en_GB.UTF-8';
ALTER DATABASE serverconfig OWNER TO serverconfig_owner;
\connect serverconfig

At this time for my issue, I see solutions :

- never use schema public (so rename it)=>

dropping it after a create database or a pg_restore with --create

- use a postscript to revoke all privileges from public

According to me, pg_dump/pg_restore could add new features :

- An other solution could be a new argument in order to specify a template (like --template) only relevent with --create of pg_restore

- An other solution could be :

with pg_dump : include create statement for all schema

with --create of pg_restore, have this behavior : create the database, drop all in the new db , and create all schema included in the dump.

- An other solution could be : a new argument in order to specify an other template than template0 (like --template) only relevent with --create of pg_restore.

Is it possible to discuss about these potentiel features? with whom? Is there a specific canal?

i would like to thank Adrian.

Regards Thomas

2018-04-26 9:03 GMT+02:00 Thomas Poty <thomas.poty@gmail.com>:

Hi,

About version :

This is the same on both server

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

+ target server

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

+ pg_dump :

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

About privileges:

+ source db :

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

+ target db

schema of template1 :

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)

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:

From: Thomas Poty
Date: 26 April 2018, 13:03:47
Subject: Re: Issue on public schéma with Pg_restore

From: Alexander Farber
Date: 26 April 2018, 17:00:14
Subject: Re: Same condition in the CTE and in the subsequent JOIN using it

Re: Issue on public schéma with Pg_restore - Mailing list pgsql-general

Previous

Next