Re: Error with pg_dump (of data), with --role

On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ken Tanzer <ken(dot)tanzer(at)gmail(dot)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(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.