Re: Issue on public schéma with Pg_restore

[Thomas Poty <thomas.poty@xxxxxxxxx>]

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@xxxxxxxxx>:

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@xxxxxxxxxxx>:

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