Re: pg_dump and search_path

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

> 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