It may be simpler to just run the query from the materialized view
definition as the user that you want to refresh the mv.
On Tue, Nov 28, 2017 at 10:30 PM, Daevor The Devoted
wrote:
>
>
> On 28 Nov 2017 5:18 pm, "Tom Lane" wrote:
>
> Henrik Uggla writes:
> > The underlying tables are foreign tables. The user has been mapped to a
> foreign user with select permission. I have no problem selecting from the
> foreign tables or the materialized views.
>
> [ shrug... ] WFM; if I can select from the foreign table then I can make
> a materialized view that selects from it, and that refreshes without
> complaint. Admittedly, getting things set up to select from the foreign
> table is trickier than it sounds: your local user needs SELECT on the
> foreign table plus a mapping to some remote userid, and *on the remote
> server* that remote userid needs SELECT on whatever the foreign table
> is referencing. I'm guessing you messed up one of these components.
>
> regards, tom lane
>
> Hendrik, perhaps an easy way to check out Tom's suggestion is to create a
> very simple materialized view that selects just from one of the foreign
> tables, then attempt the REFRESH. If that works, then keep adding more
> tables from your original materialized view until you have found the
> problem.
> Basically, reduce the problem to the simplest case, and if that works,
> then keep adding to it until you hit the problem. You may still not know
> why the problem is happening, but you'll at least know where to focus any
> further investigation.
>
> Kind regards,
> Daevor, The Devoted
>
--
*Ben Primrose | Postgres DBA | TraceLink Inc.*400 Riverpark Dr.
Floor 2, Suite 200
North Reading, MA 01864
o: +1.978.396.6507
e: bprimrose@tracelink.com