From: | Thomas Poty <thomas(dot)poty(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(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 07:03:47 |
Message-ID: | CAN_ctnhp6yfQJa1ej0=GVrJbOr-kUi2OkTP7Xvwt4JuQcXW=aA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
*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
*+ 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(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)
*+ 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
2018-04-25 20:24 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
> 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(dot)klaver(at)aklaver(dot)com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Poty | 2018-04-26 08:30:23 | Re: Issue on public schéma with Pg_restore |
Previous Message | Michael Paquier | 2018-04-26 02:18:22 | Re: Pulling initial physical replication pg_basebackup from a downstream server |