Re: Issue on public schéma with Pg_restore

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(dot)klaver(at)aklaver(dot)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(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
>>
>
> 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(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)
>>
>>
> So how did you revoke the privileges for PUBLIC in the above?
>
>
>> _+ 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
>> **
>>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>