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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

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

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


--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.
lup
Reply | Threaded
Open this post in threaded view
|

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

lup


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"



Reply | Threaded
Open this post in threaded view
|

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

Ken Tanzer


On Thu, Feb 18, 2021 at 5:23 PM Rob Sargent <[hidden email]> 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
(253) 245-3801

learn more about AGENCY or
follow the discussion.
lup
Reply | Threaded
Open this post in threaded view
|

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

lup


On Feb 18, 2021, at 8:00 PM, Ken Tanzer <[hidden email]> wrote:




On Thu, Feb 18, 2021 at 5:23 PM Rob Sargent <[hidden email]> 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”?

Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Ken Tanzer
Ken Tanzer <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Ken Tanzer
On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <[hidden email]> wrote:
Ken Tanzer <[hidden email]> 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
(253) 245-3801

learn more about AGENCY or
follow the discussion.
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Ken Tanzer <[hidden email]> writes:
> On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Ken Tanzer


On Fri, Feb 19, 2021 at 3:22 PM Tom Lane <[hidden email]> wrote:
Ken Tanzer <[hidden email]> writes:
> On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <[hidden email]> 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
(253) 245-3801

learn more about AGENCY or
follow the discussion.
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Ken Tanzer <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Ken Tanzer
On Fri, Feb 19, 2021 at 4:21 PM Tom Lane <[hidden email]> 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
(253) 245-3801

learn more about AGENCY or
follow the discussion.