On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Ken Tanzer <ken.tanzer@xxxxxxxxx> 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
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
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
