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

On 2/18/21 6:18 PM, Ken Tanzer wrote:

Hi.  I'm trying to do a data dump with pg_dump using RLS and --set-role,
but am getting an error, and I'm not understanding why. With this
command, run as postgres:

pg_dump -p 5433 -O --role=rcafe_TACOMA --enable-row-security
--column-inserts -a -f ~/ag_tacoma_data.pg_dump ag_rcafe

I get
[Multiple notices about circular foreign keys, like this, which I don't
think are directly-relevant]
NOTICE: there are circular foreign-key constraints among these tables:
pg_dump:   tbl_client
pg_dump:   tbl_l_veteran_status
pg_dump:   tbl_staff
pg_dump: You might not be able to restore the dump without using
--disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to
avoid this problem.

But then crash out with:

pg_dump: [archiver (db)] query failed: ERROR:  function
has_segment_access(character varying, name) does not exist
LINE 3: SELECT has_segment_access(segment,current_user);
               ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:

SELECT has_segment_access(segment,current_user);

CONTEXT:  SQL function "has_segment_access" during inlining
pg_dump: [archiver (db)] query was: DECLARE _pg_dump_cursor CURSOR FOR
SELECT * FROM ONLY public.tbl_client

Which I don't get.  That function does exist, and is callable by both
postgres and the ag_TACOMA users.

ag_rcafe=# \df has_segment_access
                                         List of functions
 Schema |        Name        | Result data type |           Argument
data types           |  Type
--------+--------------------+------------------+-----------------------------------------+--------
 public | has_segment_access | boolean          | segment character
varying               | normal
 public | has_segment_access | boolean          | segment character
varying, db_user name | normal
 public | has_segment_access | boolean          | segments character
varying[]            | normal
(3 rows)

ag_rcafe=# SELECT current_user,has_segment_access('TACOMA',current_user);
 current_user | has_segment_access
--------------+--------------------
 postgres     | f
(1 row)

ag_rcafe=# SET ROLE "rcafe_TACOMA";
SET
ag_rcafe=> SELECT current_user,has_segment_access('TACOMA',current_user);
 current_user | has_segment_access
--------------+--------------------
 rcafe_TACOMA | t
(1 row)

So if the error means what it says, I don't get why.  It would make more
sense to me if there were a restore, with an issue about how to sequence
the creation of things.  But since it's a dump, shouldn't everything
just be there?

Any help appreciated.  More info & context below.

Thanks.

Ken

version:  9.6.20
This database is about 8 years old, and has been through one if not two
upgrades, which I mention to say who knows what weirdness or cruft (or
corruption?) might have crept in.

It's a multi-tenant DB using RLS so that each tenant can only see their
own data.  One of the tenants needs to have their data created in a
separate database.  My initial take on how to do this was to dump the
schema as postgres, and then dump the data as the particular user.
(ag_TACOMA).  But I haven't gotten very far with that. :)

There is only one schema, public.

I suspect it is because "set role" doesn't "set search_path"

On Thu, Feb 18, 2021 at 5:23 PM Rob Sargent <robjsargent@gmail.com> wrote:

There is only one schema, public.

I suspect it is because "set role" doesn't "set search_path"

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

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

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

On Feb 18, 2021, at 8:00 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:



On Thu, Feb 18, 2021 at 5:23 PM Rob Sargent <robjsargent@gmail.com> wrote:

There is only one schema, public.

I suspect it is because "set role" doesn't "set search_path"

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

--

Do you need to set role at all?
Can you put the function in “public”?

Ken Tanzer <ken.tanzer@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.

regards, tom lane

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

Ken Tanzer <ken.tanzer@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/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

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

Ken Tanzer <ken.tanzer@gmail.com> writes:

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

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.

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:

No, the way to do it is with a SET function property, like

create or replace function myfunc(...) returns ... language ...
as $$body here$$
SET search_path = whatever
... other function properties ...
;

That takes care of restoring the old value on the way out of the
function, so it's okay to use in an immutable function.

I think you can plaster this property onto an existing function
with ALTER FUNCTION, which should be less error-prone than
repeating the whole CREATE.

regards, tom lane

On Fri, Feb 19, 2021 at 3:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ken Tanzer <ken.tanzer@gmail.com> writes:

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

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.

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:

No, the way to do it is with a SET function property, like

create or replace function myfunc(...) returns ... language ...
as $$body here$$
SET search_path = whatever
... other function properties ...
;

That takes care of restoring the old value on the way out of the
function, so it's okay to use in an immutable function.

I think you can plaster this property onto an existing function
with ALTER FUNCTION, which should be less error-prone than
repeating the whole CREATE.

Great, that works, and ALTER FUNCTION definitely much better for me.

One thing about the search path though, regarding pg_temp. If I add a

SET search_path = public;

Do I need instead to specify "public, pg_temp" to prevent it from being
(silently) at the beginning?

This seems to be what the "Writing SECURITY DEFINER Functions Safely"
section suggests (
https://www.postgresql.org/docs/9.6/sql-createfunction.html). But pg_temp
isn't mentioned at all on the page about schemas (
https://www.postgresql.org/docs/9.6/ddl-schemas.html), so I'm a little
unclear. Also if there are other hidden schemas in the search path.

And along those lines, any chance of seeing something like "SHOW
search_path_complete" (or search_path_explicit) implemented? Seems like it
could be helpful!

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

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

Ken Tanzer <ken.tanzer@gmail.com> writes:

One thing about the search path though, regarding pg_temp. If I add a
SET search_path = public;
Do I need instead to specify "public, pg_temp" to prevent it from being
(silently) at the beginning?

Yeah, that would be slightly safer. If the public schema is
world-writable, though, you're in big trouble anyway ...

regards, tom lane

On Fri, Feb 19, 2021 at 4:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yeah, that would be slightly safer. If the public schema is
world-writable, though, you're in big trouble anyway ...

Sorry, you lost me with the last sentence. My scenario is that public
_isn't_ world-writable. But everyone can set their own temp objects. So
these would be found first unless pg_temp is explicitly specified at the
end of the search path.

And I guess that's a "No" on an option to show the complete search path. :)

Thanks again for your help!

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

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