Re: Issue on public schéma with Pg_restore

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/serverco
nfig_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(dot)poty(at)gmail(dot)com>:

> Hi,
>
> *About version :*
>
> This is the same on both server
>
> * + source server :*
>
>
> [[local]] thomasproot(at)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(at)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(at)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(at)template1=# \dn+
> List of schemas
> ┌──────┬───────┬───────────────────┬─────────────┐
> │ Name │ Owner │ Access privileges │ Description
> │
> ├──────┼───────┼───────────────────┼─────────────┤
> └──────┴───────┴───────────────────┴─────────────┘
> (0 rows)
>
>
> *after restore:*
>
>
> [[local]] thomasproot(at)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>>
>
>