pg_dump without setting search_path
When I use pg_dump to export schema from a database, it adds the following line at the beginning:
SELECT pg_catalog.set_config('search_path', '', false);
Is it possible set an option where pg_dump will not add this line?
It is causing issues later when I try to execute other SQL commands, without the schema qualifier.
This is the pg_dump command I am using right now:
pg_dump -O -x -h <db-host> -p <db-port> -U <db-user> -d <db-name> --schema public --schema-only > public-schema.sql
gzh <gzhcoder@126.com> writes:
When I use pg_dump to export schema from a database, it adds the following line at the beginning:
SELECT pg_catalog.set_config('search_path', '', false);
Is it possible set an option where pg_dump will not add this line?
No. It's a security precaution.
It is causing issues later when I try to execute other SQL commands, without the schema qualifier.
That probably means you have some user-defined functions that are
not sufficiently careful about search_path. You want to either
schema-qualify every reference in the function body, or attach a
"SET search_path" clause to remove the function's context dependency.
(If you're using SQL-language functions in v14 or later, another
option is to use the SQL-standard syntax instead of writing the
body as a string literal. Then the object references in the function
are parsed at definition time.)
regards, tom lane
Hi Tom,
Thank you for your prompt response.
When I use pg_dump to export schema from an older version of PostgreSQL 8.2.3 , it adds the following line at the beginning:
SET search_path = public, pg_catalog;
Is it possible set an option where pg_dump will add this line in PostgreSQL 12.5?
At 2022-08-25 11:07:46, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Show quoted text
gzh <gzhcoder@126.com> writes:
When I use pg_dump to export schema from a database, it adds the following line at the beginning:
SELECT pg_catalog.set_config('search_path', '', false);
Is it possible set an option where pg_dump will not add this line?No. It's a security precaution.
It is causing issues later when I try to execute other SQL commands, without the schema qualifier.
That probably means you have some user-defined functions that are
not sufficiently careful about search_path. You want to either
schema-qualify every reference in the function body, or attach a
"SET search_path" clause to remove the function's context dependency.(If you're using SQL-language functions in v14 or later, another
option is to use the SQL-standard syntax instead of writing the
body as a string literal. Then the object references in the function
are parsed at definition time.)regards, tom lane
On Wednesday, August 24, 2022, gzh <gzhcoder@126.com> wrote:
When I use pg_dump to export schema from an older version of PostgreSQL
8.2.3 , it adds the following line at the beginning:SET search_path = public, pg_catalog;
Is it possible set an option where pg_dump will add this line in
PostgreSQL 12.5?
Your observation about the past isn’t going to change the answer
(especially if you choose to appeal to 8.2 behavior). It is a policy
choice, not a technical limitation.
David J.
On 8/24/22 20:39, gzh wrote:
Hi Tom,
Thank you for your prompt response.
When I use pg_dump to export schema from an older version of PostgreSQL
8.2.3 , it adds the following line at the beginning:SET search_path = public, pg_catalog;
Is it possible set an option where pg_dump will add this line in
PostgreSQL 12.5?
No for this reason:
https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
regards, tom lane
--
Adrian Klaver
adrian.klaver@aklaver.com
Dear all,
Thank you so much for your response to my request for information.
I appreciated you taking the time to provide some answers and for getting back to me so promptly.
It made a lot of sense and was exactly what I needed to know.
At 2022-08-25 12:19:03, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
Show quoted text
On 8/24/22 20:39, gzh wrote:
Hi Tom,
Thank you for your prompt response.
When I use pg_dump to export schema from an older version of PostgreSQL
8.2.3 , it adds the following line at the beginning:SET search_path = public, pg_catalog;
Is it possible set an option where pg_dump will add this line in
PostgreSQL 12.5?No for this reason:
https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
regards, tom lane
--
Adrian Klaver
adrian.klaver@aklaver.com