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

--

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