Assuming you have a user called "test", this will create a database that suffers from this problem.
create database mvtest;
\c mvtest
create table main_table (id serial not null, something varchar(20), primary key (id));
create table child_table (id serial not null, parent_id int not null, somedate date not null, someval int not null, primary key(id), foreign key(parent_id) references main_table(id));
insert into main_table(something) values('X-Men'),('Batman');
insert into child_table(parent_id, somedate, someval) values(2,'1989-06-23',10),(2,'1992-06-19',4),(1,'2000-07-14',13),(1,'2014-05-23',16);
CREATE MATERIALIZED VIEW movie_things AS
SELECT mt.*, jsonb_object(array_agg(ct.somedate)::text[], array_agg(ct.someval)::text[]) AS release_prizes FROM main_table mt
LEFT JOIN child_table ct ON (mt.id = ct.parent_id) GROUP BY mt.id;
CREATE UNIQUE INDEX IF NOT EXISTS movie_things_id_idx ON movie_things USING btree (id);
ALTER MATERIALIZED VIEW movie_things CLUSTER ON movie_things_id_idx, OWNER TO test;
\c mvtest
create table main_table (id serial not null, something varchar(20), primary key (id));
create table child_table (id serial not null, parent_id int not null, somedate date not null, someval int not null, primary key(id), foreign key(parent_id) references main_table(id));
insert into main_table(something) values('X-Men'),('Batman');
insert into child_table(parent_id, somedate, someval) values(2,'1989-06-23',10),(2,'1992-06-19',4),(1,'2000-07-14',13),(1,'2014-05-23',16);
CREATE MATERIALIZED VIEW movie_things AS
SELECT mt.*, jsonb_object(array_agg(ct.somedate)::text[], array_agg(ct.someval)::text[]) AS release_prizes FROM main_table mt
LEFT JOIN child_table ct ON (mt.id = ct.parent_id) GROUP BY mt.id;
CREATE UNIQUE INDEX IF NOT EXISTS movie_things_id_idx ON movie_things USING btree (id);
ALTER MATERIALIZED VIEW movie_things CLUSTER ON movie_things_id_idx, OWNER TO test;
On 21 July 2017 at 17:25, Jordan Gigov <coladict@xxxxxxxxx> wrote:
This is on version 9.5, 9.6 and 10beta2. I could probably make a
test-case over the weekend if I'm at home.
On 21 July 2017 at 17:03, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Jordan Gigov <coladict@xxxxxxxxx> writes:
>> When running pg_restore as the superuser it gives the following error
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 3253; 0 320628
>> MATERIALIZED VIEW DATA combined_query_data web_user
>> pg_restore: [archiver (db)] could not execute query: ERROR:
>> permission denied for relation first_table_in_from_list
>> Command was: REFRESH MATERIALIZED VIEW combined_query_data;
>
> What PG version is this? Can you provide a self-contained test case?
>
>> I see no reason why the superuser would get a "permission denied"
>> error.
>
> Matview queries are run as the owner of the matview, so this isn't
> as surprising as all that. But if the matview works in your normal
> usage, then pg_dump must be doing something wrong, perhaps emitting
> grants in the wrong order.
>
> regards, tom lane
