From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Thomas Poty <thomas(dot)poty(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Issue on public schéma with Pg_restore |
Date: | 2018-04-26 14:28:14 |
Message-ID: | f159a8d0-b27d-6990-e782-4705ef83fba7@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Poty | 2018-04-26 14:52:17 | Re: Issue on public schéma with Pg_restore |
Previous Message | Alexander Farber | 2018-04-26 11:01:22 | Re: Same condition in the CTE and in the subsequent JOIN using it |