Issue on public schéma with Pg_restore

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

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

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.poty@gmail.com>:

On 04/26/2018 12:03 AM, Thomas Poty wrote:

Hi,

Comments in line below.

*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

The latest 9.6 version is 9.6.8 and it has changes to deal with this:

https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path

Not sure if it would cover your issues, but worth the ugrade anyway.

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

FYI, --disable-triggers in this context is a no-op.

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

So how did you revoke the privileges for PUBLIC in the above?

_+ 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
**

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

About the minor version, we will plan the upgrade soon .

So how did you revoke the privileges for PUBLIC in the above? [db source]
=> After creation of the database, I executed:
revoke all on schema public from public;

Thanks for the 'no-op' .

Thomas

2018-04-26 16:28 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 04/26/2018 07:52 AM, Thomas Poty wrote:

Hi,

About the minor version, we will plan the upgrade soon .

So how did you revoke the privileges for PUBLIC in the above? [db source]
=> After creation of the database, I executed:
    revoke all on schema public from public;

Hmm:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5955d934194c3888f30318209ade71b53d29777f

Not sure if this patch is in 9.6.8 or not. The interesting part is:

"We had some pretty ad-hoc handling of the public schema ..."

"Previously, while it was normally not mentioned, --clean mode would
drop and recreate it, again causing headaches for non-superuser usage."

You might want to try without the --clean on the restore.

Thanks for the 'no-op' .

Thomas

--
Adrian Klaver
adrian.klaver@aklaver.com