pg_dump and search_path

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
/messages/by-id/MWHPR14MB160079A6D9DC64A2F60E9004C0D00@MWHPR14MB1600.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

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
/messages/by-id/MWHPR14MB160079A6D9DC64A2F60E9004C0D00@MWHPR14MB1600.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

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
/messages/by-id/MWHPR14MB160079A6D9DC64A2F60E9004C0D00@MWHPR14MB1600.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.

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/&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

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

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

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.

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
<mailto: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

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.

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

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

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

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

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

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

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