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.kla...@aklaver.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/serverco >> nfig_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.kla...@aklaver.com >