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