Обсуждение: [GENERAL] pg_restore misuse or bug?
So we have this database dump created using "pg_dump -Fc ourdb >
file.dump" that has a Materialized View that gets refreshed under
certain conditions by our Java web-app.
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;
If we run it as "pg_restore -d ourdb file.dump" it is treated as a
warning, and the restore is successful, except for the data in the MV.
We have to run the command separately in order to fill the data.
If we run it as "pg_restore -d ourdb -1 file.dump", then it becomes a
fatal error and rolls back the transaction.
I see no reason why the superuser would get a "permission denied"
error. Is this a bug or am I doing something wrong.
Jordan Gigov <coladict@gmail.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
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@sss.pgh.pa.us> wrote: > Jordan Gigov <coladict@gmail.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
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@gmail.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@sss.pgh.pa.us> wrote:
> Jordan Gigov <coladict@gmail.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