I am consolidating data from several postgresql databases, all with the same schema, into a new one. To do that I mapped the tables I am interested in the new DB as foreign tables with
postgres_fdw. Each source DB is mapped in its own schema:
sourceN. Then to analyze the data as a whole, I built views on top of the foreign tables. The view definition basically is this:
CREATE OR REPLACE consolidated_view AS SELECT "source1" AS source, * FROM source1.table UNION ALL SELECT "source2" AS source, * FROM source2.table UNION ALL ... SELECT "sourceN" AS source, * FROM sourceN.table ;
I did that, and it worked like a charm for many tables. But one is resisting me. I keep getting this error:
ERROR: permission denied for relation product_alias CONTEXT: Remote SQL command: SELECT * FROM public.table
But when I try to do a
SELECT * FROM sourceX.table, for each source in 1..N, i get the results I want.
Don’t know if I am completely off track here, but what I am not very clear on, and could explain the issue, is the identity under which the remote tables will be accessed throught the view, compared to when I do a
SELECT on an individual foreign table. We have two different users involved: one “admin” who owns the view, one “user” as who will execute the query on the view. Both have as
USER MAPPING defined. I tried to increase the log verbosity, have all sorts of message pop in the console as the query is executed, adding those settings:
client_min_messages debug5 log_min_error_statement debug5 log_min_messages debug5
But did not find much useful information that way. Any help to the least to improve the way I am trying debug this issue would be much appreciated.