On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Ken Tanzer <ken.tan...@gmail.com> writes: > > I'm not sure what you mean or are suggesting by that. Is there something > > I'm supposed to do to set the search path? Is that a known bug in > > pg_dump? Something else? As mentioned, there is only one schema.... > > There was a security change to pg_dump a few years ago to make it > put "set search_path = pg_catalog" into the dump script. This > basically means that any user-defined function in indexes, check > constraints, etc is on its own to be sure that it schema-qualifies > non-system names, or has a "SET search_path" clause to do that > for it. While that's annoying, it's also good practice. Functions > that could be invoked in these contexts really ought not assume > what search path they are called with. > > I do not think any of the other details you mentioned, such as > use of --role, have any impact on this. > > Thank you Tom for that explanation. To follow on, I tried adding:
SET search_path = public; to the functions, but that prevents my function from working at all: pg_dump: [archiver (db)] query failed: ERROR: SET is not allowed in a non-volatile function CONTEXT: SQL function "has_segment_access" during startup pg_dump: [archiver (db)] query was: FETCH 100 FROM _pg_dump_cursor I can get it to work by schema-qualifying every reference within the functions involved. So is the upshot of this that functions used for RLS need to either have every reference schema-qualified, or else be marked volatile? (At least in order to also work with pg_dump?) Or am I still misunderstanding or missing something? Cheers, Ken -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
