pg_dump and search_path

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_dump and search_path

Igal Sapir

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

Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Igal Sapir
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




Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Laurenz Albe
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



Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Ron-2
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.


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Adrian Klaver-4
In reply to this post by Igal Sapir
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Adrian Klaver-4
In reply to this post by Ron-2
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Igal Sapir
In reply to this post by Adrian Klaver-4
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





Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

David G Johnston
On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Igal Sapir

David,

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

On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

David G Johnston
On Tue, Jul 9, 2019 at 8:48 AM Igal @ Lucee.org <[hidden email]> 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 <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Adrian Klaver-4
In reply to this post by Igal Sapir
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Igal Sapir
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


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Ryan Lambert
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


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Ryan Lambert
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
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and search_path

Adrian Klaver-4
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
[hidden email]