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

Поиск

Список

Период

Сортировка

От	Ken Tanzer
Тема	Error with pg_dump (of data), with --role
Дата	19 февраля 2021 г. 04:18:18
Msg-id	CAD3a31XywdXfeLeSH0NTptRR4iQyiC7CsO_kkMBF6qTNhGC5zw@mail.gmail.com обсуждение исходный текст
Ответы	Re: Error with pg_dump (of data), with --role (Rob Sargent <robjsargent@gmail.com>)
Список	pgsql-general

Дерево обсуждения

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.

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.