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
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tan...@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-requ...@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
