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: source1, source2, ..., 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.

  • Welcome to DBA.SE.If I understand your question correctly, then you would prefer to receive information on how to debug your issue instead of a solution. Is that correct? This type of question is very open ended and might be closed as too broad. Now you might be already doing the right thing, but might be missing the actual issue. However, there is no way for the community to tell, because you aren't supplying any error messages. Could your share some of your findings? And could you let us know which user is performing which query? – hot2use Jan 28 at 6:40
  • @hot2use No, I would get any help I can get. If you can be spot on and squash that bug, I'd be very glad. I'd be surprised if I were the first to use this technique to consolidate data. I was hoping someone would have face a similar issue and solved it. But I am not sure that the elements I am able provide, so far, are good enough for that. Hence ideas on how to troubleshoot the issue could help narrow things down, so we can resolve the issue. – Cans Jan 28 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.