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.
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
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)
Ken
--
![]()
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.
