Re: pg_restore misuse or bug?

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;

On 21 July 2017 at 17:25, Jordan Gigov <coladict(at)gmail(dot)com> 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Jordan Gigov <coladict(at)gmail(dot)com> 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
>