Re: pg_dump and search_path

On Tue, Jul 9, 2019 at 8:48 AM Igal @ Lucee.org <igal(at)lucee(dot)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(at)lucee(dot)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.