Re: 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"