Re: pg_dump and search_path - Mailing list pgsql-general

From David G. Johnston
Subject Re: pg_dump and search_path
Date
Msg-id CAKFQuwZ9xGQF=QTPUKUNoco9fjmM9WruCaDh5AmDc-RwQop0Cw@mail.gmail.com
Whole thread Raw
In response to Re: pg_dump and search_path  ("Igal @ Lucee.org" <igal@lucee.org>)
List pgsql-general
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.

pgsql-general by date:

Previous
From: "Igal @ Lucee.org"
Date:
Subject: Re: pg_dump and search_path
Next
From: John Lumby
Date:
Subject: Re: REINDEX : new parameter to preserve current average leaf densityas new implicit FILLFACTOR