Thread: Issue on public schéma with Pg_restore
Hello,
Here is the context :
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.
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.
I cannot explain this
Thank you.
Thomas?
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.klaver@aklaver.com
Hi,
About version :
This is the same on both 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
[[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
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
[[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)
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)
│ │ │ 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.klaver@aklaver.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.klaver@aklaver.com
I have run this command (same of previous one without -d and with -f argument :
/bin/pg_restore --username=backup --host=VM38 --port=5432 --no-password --disable-triggers --verbose --clean --create --if-exists -f /tmp/thomasp.log /mnt/backupPostgreSQL/serverco
Here is partial content of the file /tmp/thomasp.log
I don't see any "create schema public"... :
BUT I see create database ... TEMPLATE=template0
CREATE DATABASE "serverconfig" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_GB.UTF-8' LC_CTYPE = 'en_GB.UTF-8';
ALTER DATABASE serverconfig OWNER TO serverconfig_owner;
\connect serverconfig
Here is partial content of the file /tmp/thomasp.log
I don't see any "create schema public"... :
BUT I see create database ... TEMPLATE=template0
CREATE DATABASE "serverconfig" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_GB.UTF-8' LC_CTYPE = 'en_GB.UTF-8';
ALTER DATABASE serverconfig OWNER TO serverconfig_owner;
\connect serverconfig
dropping it after a create database or a pg_restore with --create
- use a postscript to revoke all privileges from public According to me, pg_dump/pg_restore could add new features :
- An other solution could be a new argument in order to specify a template (like --template) only relevent with --create of pg_restore- An other solution could be :
with pg_dump : include create statement for all schema
with --create of pg_restore, have this behavior : create the database, drop all in the new db , and create all schema included in the dump.
- An other solution could be : a new argument in order to specify an other template than template0 (like --template) only relevent with --create of pg_restore.
Is it possible to discuss about these potentiel features? with whom? Is there a specific canal?
i would like to thank Adrian.
Regards Thomas
2018-04-26 9:03 GMT+02:00 Thomas Poty <thomas.poty@gmail.com>:
schema of template1 :+ source db :Hi,About version :This is the same on both server+ source server :+ target 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
[[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+ 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.7About privileges:
[[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)+ target db
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?RegardsThomas2018-04-25 20:24 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.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.klaver@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/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@aklaver.com
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]
Thanks for the 'no-op' .
Thomas
2018-04-26 16:28 GMT+02:00 Adrian Klaver <adrian.klaver@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/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/serverco nfig_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/serverco nfig_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@aklaver.com
On 04/26/2018 07:52 AM, Thomas Poty wrote: > 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; Hmm: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5955d934194c3888f30318209ade71b53d29777f Not sure if this patch is in 9.6.8 or not. The interesting part is: "We had some pretty ad-hoc handling of the public schema ..." "Previously, while it was normally not mentioned, --clean mode would drop and recreate it, again causing headaches for non-superuser usage." You might want to try without the --clean on the restore. > > Thanks for the 'no-op' . > > Thomas > > -- Adrian Klaver adrian.klaver@aklaver.com