Re: Error with pg_dump (of data), with --role - Mailing list pgsql-general

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"