Thread: Want to disable fully qualified table names on pg_dump in pg_dump(PostgreSQL) 9.6.8
Want to disable fully qualified table names on pg_dump in pg_dump(PostgreSQL) 9.6.8
From
Foolish Ewe
Date:
Hello All:
A number of our team members and I use pg_dump to export schema in an Ubuntu 16.04 environment, I happen to have a postgress 9.6.4 server
that runs in a docker container, and in some cases I see the following select statement and fully qualified table names in the
CREATE TABLE and ALTER TABLE statements:
SELECT pg_catalog.set_config('search_path', '', false);
CREATE TABLE database_name.table_name
and likewise for ALTER TABLE.
But other users (who haven't updated their systems recently) do not see the SELECT statement and we see unqualified table names, e.g.:
CREATE TABLE table_name
These changes in format impact our workflows, we would prefer to have unqualified table names.
So far I've made some docker containers (to minimize interactions or config errors at the host level), and in the containers the
following pg_dump client versions all have this behavior.
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.6.4
-- Dumped by pg_dump version 9.6.8
-- Started on 2018-03-15 21:49:58 UTC
and
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.6.4
-- Dumped by pg_dump version 10.3 (Ubuntu 10.3-1.pgdg16.04+1)
-- Started on 2018-03-15 22:20:47 UTC
The pg_dump command we run is:
pg_dump --verbose -d postgresql://our_server_user:xxxx@localhost:5433/server --column-inserts -t table1 -t table2 ... -t tableN --schema-only
Does anyone have an idea of what might cause this? Is there something we can do on the client side to avoid getting qualified table names?
With best regards:
Bill
Re: Want to disable fully qualified table names on pg_dump in pg_dump(PostgreSQL) 9.6.8
From
Adrian Klaver
Date:
On 03/15/2018 04:00 PM, Foolish Ewe wrote:
> Hello All:
>
> A number of our team members and I use pg_dump to export schema in an
> Ubuntu 16.04 environment, I happen to have a postgress 9.6.4 server
> that runs in a docker container, and in some cases I see the following
> select statement and fully qualified table names in the
> CREATE TABLE and ALTER TABLE statements:
>
> SELECT pg_catalog.set_config('search_path', '', false);
>
> CREATE TABLE database_name.table_name
I am pretty sure you are actually seeing:
CREATE TABLE schema_name.table_name
>
> and likewise for ALTER TABLE.
>
> But other users (who haven't updated their systems recently) do not see
> the SELECT statement and we see unqualified table names, e.g.:
>
>
> CREATE TABLE table_name
>
>
> These changes in format impact our workflows, we would prefer to have
> unqualified table names.
>
>
> Does anyone have an idea of what might cause this? Is there something
> we can do on the client side to avoid getting qualified table names?
The reason:
https://www.postgresql.org/about/news/1834/
"The purpose of this release is to address CVE-2018-1058, which
describes how a user can create like-named objects in different schemas
that can change the behavior of other users' queries and cause
unexpected or malicious behavior, also known as a "trojan-horse" attack. "
More information
https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
There is no flag to unset this.
>
> With best regards:
>
> Bill
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Re: Want to disable fully qualified table names on pg_dump in pg_dump(PostgreSQL) 9.6.8
From
Foolish Ewe
Date:
Thank you Adrian, this is informative.
With best regards:
Bill
________________________________________
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, March 15, 2018 11:09 PM
To: Foolish Ewe; pgsql-general@lists.postgresql.org
Subject: Re: Want to disable fully qualified table names on pg_dump in pg_dump (PostgreSQL) 9.6.8
On 03/15/2018 04:00 PM, Foolish Ewe wrote:
> Hello All:
>
> A number of our team members and I use pg_dump to export schema in an
> Ubuntu 16.04 environment, I happen to have a postgress 9.6.4 server
> that runs in a docker container, and in some cases I see the following
> select statement and fully qualified table names in the
> CREATE TABLE and ALTER TABLE statements:
>
> SELECT pg_catalog.set_config('search_path', '', false);
>
> CREATE TABLE database_name.table_name
I am pretty sure you are actually seeing:
CREATE TABLE schema_name.table_name
>
> and likewise for ALTER TABLE.
>
> But other users (who haven't updated their systems recently) do not see
> the SELECT statement and we see unqualified table names, e.g.:
>
>
> CREATE TABLE table_name
>
>
> These changes in format impact our workflows, we would prefer to have
> unqualified table names.
>
>
> Does anyone have an idea of what might cause this? Is there something
> we can do on the client side to avoid getting qualified table names?
The reason:
https://www.postgresql.org/about/news/1834/
"The purpose of this release is to address CVE-2018-1058, which
describes how a user can create like-named objects in different schemas
that can change the behavior of other users' queries and cause
unexpected or malicious behavior, also known as a "trojan-horse" attack. "
More information
https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
There is no flag to unset this.
>
> With best regards:
>
> Bill
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Re: Want to disable fully qualified table names on pg_dump in pg_dump(PostgreSQL) 9.6.8
From
Adrian Klaver
Date:
On 03/16/2018 01:55 PM, Foolish Ewe wrote: > Thank you Adrian, this is informative. > > With best regards: On a hunch: 1) Installed SQL Workbench/J (https://www.sql-workbench.eu/index.html) 2) Set up Postgres JDBC driver(https://jdbc.postgresql.org/download.html) 3) Opened DbExplorer: https://www.sql-workbench.eu/manual/dbexplorer.html 4) Selected all the objects in a schema and used Create DDL Script to generate a file with the objects. The objects where not created with a schema qualifier. > > Bill -- Adrian Klaver adrian.klaver@aklaver.com