Refreshing materialized views

Henrik Uggla <Henrik.Uggla@kristianstad.se> writes:

I created some materialized views and set a group as owner. My problem is that I can't refresh the views. I get "permission denied" even when using the postgres super user. If I change the owner to an ordinary user I still can't refresh the view as the owner or postgres. Only if I change owner to postgres am I able to refresh the view, but only as the postgres user. I've mainly tried with Pgadmin4 but also a few times with psql, and the database is installed as a docker container. Please help.

Apparently, the view owner lacks select permission on some underlying
table. Pay attention to what is being denied access to.

regards, tom lane

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.

This is the error in the log (not very useful):
2017-11-28 13:58:03.207 UTC [347] STATEMENT: REFRESH MATERIALIZED VIEW kust_havsplan.fornlamning_linje WITH DATA;
2017-11-28 13:58:47.412 UTC [296] ERROR: permission denied for relation fornlamning_linje

Regards
Henrik
________________________________________
Från: Tom Lane <tgl@sss.pgh.pa.us>
Skickat: den 28 november 2017 15:25:47
Till: Henrik Uggla
Kopia: pgsql-general@lists.postgresql.org
Ämne: Re: Refreshing materialized views

Henrik Uggla <Henrik.Uggla@kristianstad.se> writes:

I created some materialized views and set a group as owner. My problem is that I can't refresh the views. I get "permission denied" even when using the postgres super user. If I change the owner to an ordinary user I still can't refresh the view as the owner or postgres. Only if I change owner to postgres am I able to refresh the view, but only as the postgres user. I've mainly tried with Pgadmin4 but also a few times with psql, and the database is installed as a docker container. Please help.

Apparently, the view owner lacks select permission on some underlying
table. Pay attention to what is being denied access to.

regards, tom lane

Henrik Uggla <Henrik.Uggla@kristianstad.se> 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

On 28 Nov 2017 5:18 pm, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Henrik Uggla <Henrik.Uggla@kristianstad.se> 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

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 <dollien@gmail.com>
wrote:

On 28 Nov 2017 5:18 pm, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Henrik Uggla <Henrik.Uggla@kristianstad.se> 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

I finally managed to sort out all needed permissions and mappings.
Thanks for all replies!

cheers
Henrik
________________________________________
Från: Ben Primrose <bprimrose@tracelink.com>
Skickat: den 29 november 2017 12:42:56
Till: Daevor The Devoted
Kopia: hendrik.uggla@kristianstad.se; pgsql-general@lists.postgresql.org
Ämne: Re: SV: Refreshing materialized views

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 <dollien@gmail.com<mailto:dollien@gmail.com>> wrote:

On 28 Nov 2017 5:18 pm, "Tom Lane" <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>> wrote:
Henrik Uggla <Henrik.Uggla@kristianstad.se<mailto:Henrik.Uggla@kristianstad.se>> 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<mailto:bprimrose@tracelink.com>