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@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".