Search Postgresql Archives

Re: Error with pg_dump (of data), with --role

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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"






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux