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"
