Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump and search_path

pg_dump and search_path

From
"Igal @ Lucee.org"
Date:

I have a custom search_path:

# show search_path;
           search_path
----------------------------------
 "staging, transient, pg_catalog"
(1 row)

I ran `pg_dump --schema-only` and the only reference in the output to search_path is:

  SELECT pg_catalog.set_config('search_path', '', false);

Then one of my functions which does not reference the full name of a table with its schema fails with "relation [rel-name] does not exist".

Is that a bug?  I have seen some old posts about this issue but am not sure if there is a ticket or why it still is an issue.

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org

Re: pg_dump and search_path

From
"Igal @ Lucee.org"
Date:
On 7/8/2019 11:48 PM, Igal @ Lucee.org wrote:
>
> I have a custom search_path:
>
> # show search_path;
>            search_path
> ----------------------------------
>  "staging, transient, pg_catalog"
> (1 row)
>
> I ran `pg_dump --schema-only` and the only reference in the output to 
> search_path is:
>
>   SELECT pg_catalog.set_config('search_path', '', false);
>
> Then one of my functions which does not reference the full name of a 
> table with its schema fails with "relation [rel-name] does not exist".
>
> Is that a bug?  I have seen some old posts about this issue but am not 
> sure if there is a ticket or why it still is an issue.
>
Looks like this might be by design.  I will follow the links at 

https://www.postgresql.org/message-id/flat/MWHPR14MB160079A6D9DC64A2F60E9004C0D00%40MWHPR14MB1600.namprd14.prod.outlook.com

and ask more questions if I have them.

I might need to add the schema name to the table in my function.

Igal

Re: pg_dump and search_path

From
Laurenz Albe
Date:
On Mon, 2019-07-08 at 23:54 -0700, Igal @ Lucee.org wrote:
> > I have a custom search_path:
> > 
> > # show search_path;
> >            search_path
> > ----------------------------------
> >  "staging, transient, pg_catalog"
> > (1 row)
> > 
> > I ran `pg_dump --schema-only` and the only reference in the output to 
> > search_path is:
> > 
> >   SELECT pg_catalog.set_config('search_path', '', false);
> > 
> > Then one of my functions which does not reference the full name of a 
> > table with its schema fails with "relation [rel-name] does not exist".
> > 
> > Is that a bug?  I have seen some old posts about this issue but am not 
> > sure if there is a ticket or why it still is an issue.
> > 
> Looks like this might be by design.  I will follow the links at 
>
https://www.postgresql.org/message-id/flat/MWHPR14MB160079A6D9DC64A2F60E9004C0D00%40MWHPR14MB1600.namprd14.prod.outlook.com

> and ask more questions if I have them.
> 
> I might need to add the schema name to the table in my function.

Right.

Allowing object lookup along the search_path during pg_restore opens
doors to abuse, because it can make a superuser inadvertedly execute
code crafted by an attacker.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: pg_dump and search_path

From
Ron
Date:
On 7/9/19 2:22 AM, Laurenz Albe wrote:
> On Mon, 2019-07-08 at 23:54 -0700, Igal @ Lucee.org wrote:
>>> I have a custom search_path:
>>>
>>> # show search_path;
>>>             search_path
>>> ----------------------------------
>>>   "staging, transient, pg_catalog"
>>> (1 row)
>>>
>>> I ran `pg_dump --schema-only` and the only reference in the output to
>>> search_path is:
>>>
>>>    SELECT pg_catalog.set_config('search_path', '', false);
>>>
>>> Then one of my functions which does not reference the full name of a
>>> table with its schema fails with "relation [rel-name] does not exist".
>>>
>>> Is that a bug?  I have seen some old posts about this issue but am not
>>> sure if there is a ticket or why it still is an issue.
>>>
>> Looks like this might be by design.  I will follow the links at
>>
https://www.postgresql.org/message-id/flat/MWHPR14MB160079A6D9DC64A2F60E9004C0D00%40MWHPR14MB1600.namprd14.prod.outlook.com
>> and ask more questions if I have them.
>>
>> I might need to add the schema name to the table in my function.
> Right.
>
> Allowing object lookup along the search_path during pg_restore opens
> doors to abuse, because it can make a superuser inadvertedly execute
> code crafted by an attacker.

Then should search_path be set at the end of pg_restore?

-- 
Angular momentum makes the world go 'round.

Re: pg_dump and search_path

From
Adrian Klaver
Date:
On 7/8/19 11:48 PM, Igal @ Lucee.org wrote:
> I have a custom search_path:
> 
> # show search_path;
>             search_path
> ----------------------------------
>   "staging, transient, pg_catalog"
> (1 row)
> 
> I ran `pg_dump --schema-only` and the only reference in the output to 
> search_path is:
> 
>    SELECT pg_catalog.set_config('search_path', '', false);
> 
> Then one of my functions which does not reference the full name of a 
> table with its schema fails with "relation [rel-name] does not exist".

Where is this failing?

Do you have the search_path set in the config for the server you are 
dumping to?

> 
> Is that a bug?  I have seen some old posts about this issue but am not 
> sure if there is a ticket or why it still is an issue.
> 
> Thanks,
> 
> Igal Sapir
> Lucee Core Developer
> Lucee.org <http://lucee.org/>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump and search_path

From
Adrian Klaver
Date:
On 7/9/19 6:28 AM, Ron wrote:

> 
> Then should search_path be set at the end of pg_restore?
> 

1) That would be to late for anything happening in the restore.

2) The:

SELECT pg_catalog.set_config('search_path', '', false);

only applies to the restore session. After the restore the search_path 
will return to whatever has been configured.



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump and search_path

From
"Igal @ Lucee.org"
Date:
On 7/9/2019 7:02 AM, Adrian Klaver wrote:
> On 7/8/19 11:48 PM, Igal @ Lucee.org wrote:
>> I have a custom search_path:
>>
>> # show search_path;
>>             search_path
>> ----------------------------------
>>   "staging, transient, pg_catalog"
>> (1 row)
>>
>> I ran `pg_dump --schema-only` and the only reference in the output to 
>> search_path is:
>>
>>    SELECT pg_catalog.set_config('search_path', '', false);
>>
>> Then one of my functions which does not reference the full name of a 
>> table with its schema fails with "relation [rel-name] does not exist".
>
> Where is this failing?
>
> Do you have the search_path set in the config for the server you are 
> dumping to?

It is failing during the Restore operation.  I can provide more 
information if I'll understand what you mean exactly by "Where".

search_path is not set int he config, but rather with ALTER DATABASE SET 
search_path TO ... but I have executed that prior to the RESTORE on the 
target database.  Would it make a difference if I set it in the config?

Thanks,

Igal

Re: pg_dump and search_path

From
"David G. Johnston"
Date:
On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org <igal@lucee.org> wrote:
search_path is not set int he config, but rather with ALTER DATABASE SET
search_path TO ... but I have executed that prior to the RESTORE on the
target database.  Would it make a difference if I set it in the config?

What is your restore command then?  Because if you are dropping and recreating the same named database the ALTER DATABASE SET command is going to be lost with the drop since it is associated to an OID and not just the name.  By placing the search_path into postgres.conf you avoid that issue altogether.

But, yes, objects saved to the database should usually have schema qualifications (which gets a bit messy with custom operators).  search_path reliance should probably be reserved to interactive use or at worse client supplied queries.

David J.

Re: pg_dump and search_path

From
"Igal @ Lucee.org"
Date:

David,

On 7/9/2019 7:49 AM, David G. Johnston wrote:

On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org <igal@lucee.org> wrote:
search_path is not set int he config, but rather with ALTER DATABASE SET
search_path TO ... but I have executed that prior to the RESTORE on the
target database.  Would it make a difference if I set it in the config?

What is your restore command then?  Because if you are dropping and recreating the same named database the ALTER DATABASE SET command is going to be lost with the drop since it is associated to an OID and not just the name.  By placing the search_path into postgres.conf you avoid that issue altogether.

The restore command is:

pg_restore.exe --verbose --single-transaction -h <ip> -p <port> -d <dbname> -U postgres <path-to-pgdump-file>

But how will I avoid the issue if the command  `SELECT pg_catalog.set_config('search_path', '', false);` is part of the pgdump file?  Wouldn't that override the config file setting during the restore process?

But, yes, objects saved to the database should usually have schema qualifications (which gets a bit messy with custom operators).  search_path reliance should probably be reserved to interactive use or at worse client supplied queries.

In my case I use a separate Postgres cluster for each database and the roles, absent of any successful hacking, are all limited to trusted users, so the risk mentioned in the CVE is non-existent and it would be great if there was an option to turn off that "feature".

Thanks,

Igal

Re: pg_dump and search_path

From
"David G. Johnston"
Date:
On Tue, Jul 9, 2019 at 8:48 AM Igal @ Lucee.org <igal@lucee.org> wrote:

David,

On 7/9/2019 7:49 AM, David G. Johnston wrote:

On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org <igal@lucee.org> wrote:
search_path is not set int he config, but rather with ALTER DATABASE SET
search_path TO ... but I have executed that prior to the RESTORE on the
target database.  Would it make a difference if I set it in the config?

What is your restore command then?  Because if you are dropping and recreating the same named database the ALTER DATABASE SET command is going to be lost with the drop since it is associated to an OID and not just the name.  By placing the search_path into postgres.conf you avoid that issue altogether.

The restore command is:

pg_restore.exe --verbose --single-transaction -h <ip> -p <port> -d <dbname> -U postgres <path-to-pgdump-file>

But how will I avoid the issue if the command  `SELECT pg_catalog.set_config('search_path', '', false);` is part of the pgdump file?  Wouldn't that override the config file setting during the restore process?

Yes, you are correct.  I should have waited to respond to that point until I was more woke.

But, yes, objects saved to the database should usually have schema qualifications (which gets a bit messy with custom operators).  search_path reliance should probably be reserved to interactive use or at worse client supplied queries.

In my case I use a separate Postgres cluster for each database and the roles, absent of any successful hacking, are all limited to trusted users, so the risk mentioned in the CVE is non-existent and it would be great if there was an option to turn off that "feature".

This has been discussed a number of times shortly after the fix was released.  I'm of generally the same mind but no one of both mind and capability has come forth and proposed a patch.  IIRC there wasn't a conclusive "we don't want this" so an implementation to discuss would at least not be a foregone waste of time.

David J.

Re: pg_dump and search_path

From
Adrian Klaver
Date:
On 7/9/19 7:41 AM, Igal @ Lucee.org wrote:
> On 7/9/2019 7:02 AM, Adrian Klaver wrote:
>> On 7/8/19 11:48 PM, Igal @ Lucee.org wrote:
>>> I have a custom search_path:
>>>
>>> # show search_path;
>>>             search_path
>>> ----------------------------------
>>>   "staging, transient, pg_catalog"
>>> (1 row)
>>>
>>> I ran `pg_dump --schema-only` and the only reference in the output to 
>>> search_path is:
>>>
>>>    SELECT pg_catalog.set_config('search_path', '', false);
>>>
>>> Then one of my functions which does not reference the full name of a 
>>> table with its schema fails with "relation [rel-name] does not exist".
>>
>> Where is this failing?
>>
>> Do you have the search_path set in the config for the server you are 
>> dumping to?
> 
> It is failing during the Restore operation.  I can provide more 
> information if I'll understand what you mean exactly by "Where".

Yes, because I cannot replicate with just a function:

CREATE OR REPLACE FUNCTION public.search_path_test(integer)
  RETURNS integer
  LANGUAGE plpgsql
AS $function$
BEGIN
     perform * from test_tbl;
     RETURN 1;
END;
$function$

test_(postgres)# \d test_tbl
            Table "test_schema.test_tbl"
  Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
  id     | integer |

pg_dump -d test -U postgres -x -p 5412 -Fc -f dump_search_path.out
pg_restore --single-transaction -d test -c -U postgres -p 5412 
dump_search_path.out

SELECT pg_catalog.set_config('search_path', '', false);

postgres-2019-07-09 10:37:32.488 PDT-604LOG:  statement: CREATE FUNCTION 
public.search_path_test(integer) RETURNS integer
             LANGUAGE plpgsql
             AS $$
         BEGIN
             perform * from test_tbl;
             RETURN 1;
         END;
         $$;



postgres-2019-07-09 10:37:32.489 PDT-604LOG:  statement: ALTER FUNCTION 
public.search_path_test(integer) OWNER TO aklaver;


My guess is the function is being used somewhere.

> 
> search_path is not set int he config, but rather with ALTER DATABASE SET 
> search_path TO ... but I have executed that prior to the RESTORE on the 
> target database.  Would it make a difference if I set it in the config?
> 
> Thanks,
> 
> Igal
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump and search_path

From
"Igal @ Lucee.org"
Date:
On 7/9/2019 10:45 AM, Adrian Klaver wrote:
> On 7/9/19 7:41 AM, Igal @ Lucee.org wrote:
>> On 7/9/2019 7:02 AM, Adrian Klaver wrote:
>>> On 7/8/19 11:48 PM, Igal @ Lucee.org wrote:
>>>> I have a custom search_path:
>>>>
>>>> # show search_path;
>>>>             search_path
>>>> ----------------------------------
>>>>   "staging, transient, pg_catalog"
>>>> (1 row)
>>>>
>>>> I ran `pg_dump --schema-only` and the only reference in the output 
>>>> to search_path is:
>>>>
>>>>    SELECT pg_catalog.set_config('search_path', '', false);
>>>>
>>>> Then one of my functions which does not reference the full name of 
>>>> a table with its schema fails with "relation [rel-name] does not 
>>>> exist".
>>>
>>> Where is this failing?
>>>
>>> Do you have the search_path set in the config for the server you are 
>>> dumping to?
>>
>> It is failing during the Restore operation.  I can provide more 
>> information if I'll understand what you mean exactly by "Where".
>
> Yes, because I cannot replicate with just a function:
>
> CREATE OR REPLACE FUNCTION public.search_path_test(integer)
>  RETURNS integer
>  LANGUAGE plpgsql
> AS $function$
> BEGIN
>     perform * from test_tbl;
>     RETURN 1;
> END;
> $function$
>
> test_(postgres)# \d test_tbl
>            Table "test_schema.test_tbl"
>  Column |  Type   | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
>  id     | integer |
>
> pg_dump -d test -U postgres -x -p 5412 -Fc -f dump_search_path.out
> pg_restore --single-transaction -d test -c -U postgres -p 5412 
> dump_search_path.out
>
> SELECT pg_catalog.set_config('search_path', '', false);
>
> postgres-2019-07-09 10:37:32.488 PDT-604LOG:  statement: CREATE 
> FUNCTION public.search_path_test(integer) RETURNS integer
>             LANGUAGE plpgsql
>             AS $$
>         BEGIN
>             perform * from test_tbl;
>             RETURN 1;
>         END;
>         $$;
>
>
>
> postgres-2019-07-09 10:37:32.489 PDT-604LOG:  statement: ALTER 
> FUNCTION public.search_path_test(integer) OWNER TO aklaver;
>
>
> My guess is the function is being used somewhere.

I see.  Yes, the function is used by an INDEX.  So somewhere down the 
line in the pgdump file I have:

   CREATE INDEX ix_items_tags ON staging.items USING gin 
(staging.some_func_returning_array(col1));

Igal

Re: pg_dump and search_path

From
Adrian Klaver
Date:
On 7/9/19 4:24 PM, Igal @ Lucee.org wrote:
> On 7/9/2019 10:45 AM, Adrian Klaver wrote:
>> On 7/9/19 7:41 AM, Igal @ Lucee.org wrote:
>>> On 7/9/2019 7:02 AM, Adrian Klaver wrote:
>>>> On 7/8/19 11:48 PM, Igal @ Lucee.org wrote:
>>>>> I have a custom search_path:
>>>>>
>>>>> # show search_path;
>>>>>             search_path
>>>>> ----------------------------------
>>>>>   "staging, transient, pg_catalog"
>>>>> (1 row)
>>>>>
>>>>> I ran `pg_dump --schema-only` and the only reference in the output 
>>>>> to search_path is:
>>>>>
>>>>>    SELECT pg_catalog.set_config('search_path', '', false);
>>>>>
>>>>> Then one of my functions which does not reference the full name of 
>>>>> a table with its schema fails with "relation [rel-name] does not 
>>>>> exist".
>>>>
>>>> Where is this failing?
>>>>
>>>> Do you have the search_path set in the config for the server you are 
>>>> dumping to?
>>>
>>> It is failing during the Restore operation.  I can provide more 
>>> information if I'll understand what you mean exactly by "Where".
>>
>> Yes, because I cannot replicate with just a function:
>>
>> CREATE OR REPLACE FUNCTION public.search_path_test(integer)
>>  RETURNS integer
>>  LANGUAGE plpgsql
>> AS $function$
>> BEGIN
>>     perform * from test_tbl;
>>     RETURN 1;
>> END;
>> $function$
>>
>> test_(postgres)# \d test_tbl
>>            Table "test_schema.test_tbl"
>>  Column |  Type   | Collation | Nullable | Default
>> --------+---------+-----------+----------+---------
>>  id     | integer |
>>
>> pg_dump -d test -U postgres -x -p 5412 -Fc -f dump_search_path.out
>> pg_restore --single-transaction -d test -c -U postgres -p 5412 
>> dump_search_path.out
>>
>> SELECT pg_catalog.set_config('search_path', '', false);
>>
>> postgres-2019-07-09 10:37:32.488 PDT-604LOG:  statement: CREATE 
>> FUNCTION public.search_path_test(integer) RETURNS integer
>>             LANGUAGE plpgsql
>>             AS $$
>>         BEGIN
>>             perform * from test_tbl;
>>             RETURN 1;
>>         END;
>>         $$;
>>
>>
>>
>> postgres-2019-07-09 10:37:32.489 PDT-604LOG:  statement: ALTER 
>> FUNCTION public.search_path_test(integer) OWNER TO aklaver;
>>
>>
>> My guess is the function is being used somewhere.
> 
> I see.  Yes, the function is used by an INDEX.  So somewhere down the 
> line in the pgdump file I have:
> 
>    CREATE INDEX ix_items_tags ON staging.items USING gin 
> (staging.some_func_returning_array(col1));

Well you are part of the way there, the function is schema qualified:)

I will leave it others more knowledgeable on the subject as to whether a
function as a GIN expression is a good idea or not.

> 
> Igal
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump and search_path

From
Ryan Lambert
Date:
I had a similar problem and was able to being the command with the search_path to work around it.  I did this on Linux and it looks like you are on Windows but I maybe you can do something similar that will work?

PGOPTIONS='-c search_path=staging, transient, pg_catalog'


Ryan Lambert
RustProof Labs


Re: pg_dump and search_path

From
Adrian Klaver
Date:
On 7/10/19 1:19 PM, Ryan Lambert wrote:
> I had a similar problem and was able to being the command with the 
> search_path to work around it.  I did this on Linux and it looks like 
> you are on Windows but I maybe you can do something similar that will work?
> 
> PGOPTIONS='-c search_path=staging, transient, pg_catalog'

Not sure how that worked:

export PGOPTIONS="-c  search_path=public"

psql -d test -U postgres

psql (11.4)
Type "help" for help.

test_(postgres)# show search_path;
  search_path
-------------
  public

test_(postgres)# SELECT pg_catalog.set_config('search_path', '', false);
  set_config
------------

(1 row)

test_(postgres)# show search_path;
  search_path
-------------

(1 row)



> 
> 
> *Ryan Lambert*
> RustProof Labs
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump and search_path

From
Ryan Lambert
Date:
My exact situation was a deployment via sqitch,  It appears that uses psql under the hood based on the error message I get.

Running just "sqitch deploy" I  get an error due to a non-fully qualified name and a missing search path (my mistakes).  The error I get:

 + 004 .. psql:deploy/004.sql:72: ERROR:  relation "vobservations" does not exist
LINE 11:                FROM vobservations
                             ^
not ok
"psql" unexpectedly returned exit value 3

Reverting all changes

Running the following works for me in this case and allows it to find the view in the proper schema.

PGOPTIONS='-c search_path=piws,public' sqitch deploy

Ryan

Re: pg_dump and search_path

From
Adrian Klaver
Date:
On 7/10/19 4:31 PM, Ryan Lambert wrote:
> My exact situation was a deployment via sqitch,  It appears that uses 
> psql under the hood based on the error message I get.

Yes it does:
https://sqitch.org/docs/manual/sqitch/
"Native scripting

Changes are implemented as scripts native to your selected database 
engine. Writing a PostgreSQL application? Write SQL scripts for psql. 
Writing an Oracle-backed app? Write SQL scripts for SQL*Plus."

> 
> Running just "sqitch deploy" I  get an error due to a non-fully 
> qualified name and a missing search path (my mistakes).  The error I get:
> 
>       + 004 .. psql:deploy/004.sql:72: ERROR:  relation "vobservations"
>     does not exist
>     LINE 11:                FROM vobservations
>                                   ^
>     not ok
>     "psql" unexpectedly returned exit value 3
> 
>     Reverting all changes
> 
> 
> Running the following works for me in this case and allows it to find 
> the view in the proper schema.
> 
> PGOPTIONS='-c search_path=piws,public' sqitch deploy

Would it not be easier to just set the search_path in postgresql.conf?

Or if you want it just for Sqitch, modify the pg templates to include 
the search_path?

Unfortunately in the OP's case the restore is going to overwrite the env 
setting.

> 
> Ryan


-- 
Adrian Klaver
adrian.klaver@aklaver.com