Thread: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres
BUG #11208: Refresh Materialized View Concurrently bug using user Postgres
From
bemanuel.pe@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 11208 Logged by: Bruno Emanuel de Andrade Silva Email address: bemanuel.pe@gmail.com PostgreSQL version: 9.4beta2 Operating system: Linux Description: tjma_dw=> set role user_dw; tjma_dw=> CREATE TABLE foo_data AS SELECT i, md5(random()::text) FROM generate_series(1, 10) i; SELECT 10 tjma_dw=> CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data; SELECT 10 tjma_dw=> ALTER MATERIALIZED VIEW mv_foo OWNER TO user_dw; ALTER MATERIALIZED VIEW tjma_dw=> REFRESH MATERIALIZED VIEW mv_foo; REFRESH MATERIALIZED VIEW tjma_dw=> ALTER TABLE foo_data OWNER TO user_dw; ALTER TABLE tjma_dw=> REFRESH MATERIALIZED VIEW mv_foo; REFRESH MATERIALIZED VIEW tjma_dw=> \d+ mv_foo Materialized view "public.mv_foo" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+----------+--------------+------------- i | integer | | plain | | md5 | text | | extended | | View definition: SELECT foo_data.i, foo_data.md5 FROM foo_data; tjma_dw=> create unique index on mv_foo (i); CREATE INDEX tjma_dw=> \q --ATÃ AQUI OK /pgsql/pg94/bin/psql -Upostgres -p 5434 tjma_dw psql (9.4beta2) Type "help" for help. tjma_dw=# \d+ mv_foo ^C tjma_dw=# refresh materialized view CONCURRENTLY mv_foo; ERROR: permission denied for relation pg_temp_432971_2 CONTEXT: SQL statement "DELETE FROM public.mv_foo mv WHERE ctid OPERATOR(pg_catalog.=) ANY (SELECT diff.tid FROM pg_temp_10.pg_temp_432971_2 diff WHERE diff.tid IS NOT NULL AND diff.newdata IS NULL)" --WRONG THING tjma_dw=#
Re: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres
From
Kevin Grittner
Date:
"bemanuel.pe@gmail.com" <bemanuel.pe@gmail.com> wrote:=0A=0A> tjma_dw=3D> s= et role user_dw;=0A>=0A> tjma_dw=3D> CREATE TABLE foo_data AS SELECT i, md5= (random()::text) FROM=0A> generate_series(1, 10) i;=0A> SELECT 10=0A> tjma_= dw=3D> CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data;=0A> SELEC= T 10=0A> tjma_dw=3D> ALTER MATERIALIZED VIEW mv_foo OWNER TO user_dw;=0A> A= LTER MATERIALIZED VIEW=0A> tjma_dw=3D> REFRESH MATERIALIZED VIEW mv_foo;=0A= > REFRESH MATERIALIZED VIEW=0A> tjma_dw=3D> ALTER TABLE foo_data OWNER TO u= ser_dw;=0A> ALTER TABLE=0A> tjma_dw=3D> REFRESH MATERIALIZED VIEW mv_foo;= =0A> REFRESH MATERIALIZED VIEW=0A> tjma_dw=3D> create unique index on mv_fo= o (i);=0A> CREATE INDEX=0A=0A> /pgsql/pg94/bin/psql -Upostgres -p 5434 tjma= _dw=0A=0A> tjma_dw=3D# refresh materialized view CONCURRENTLY mv_foo;=0A> E= RROR:=A0 permission denied for relation pg_temp_432971_2=0A> CONTEXT:=A0 SQ= L statement "DELETE FROM public.mv_foo mv WHERE ctid=0A> OPERATOR(pg_catalo= g.=3D) ANY (SELECT diff.tid FROM pg_temp_10.pg_temp_432971_2=0A> diff WHERE= diff.tid IS NOT NULL AND diff.newdata IS NULL)"=0A=0AYeah, that's a bug; o= r probably two.=A0 I can simplify the test case:=0A=0ACREATE ROLE user_dw;= =0ASET ROLE user_dw;=0ACREATE TABLE foo_data AS SELECT i, md5(random()::tex= t)=0A=A0 FROM generate_series(1, 10) i;=0ACREATE MATERIALIZED VIEW mv_foo A= S SELECT * FROM foo_data;=0ACREATE UNIQUE INDEX ON mv_foo (i);=0ARESET ROLE= ;=0AREFRESH MATERIALIZED VIEW CONCURRENTLY mv_foo;=0A=0AIt is running afoul= of a security measure (the query to repopulate=0Adata is run as the owner = of the materialized view, to prevent=0Aplacing trojan horses for a superuse= r).=A0 But it seems to be=0Acreating the temporary table as the superuser, = preventing even the=0Aowner from running the REFRESH ... CONCURRENTLY.=A0 T= he query that is=0Abeing displayed is internal; we should probably find a w= ay to show=0Athe statement that was run at the top level instead.=0A=0AI'll= look at fixing both.=0A=0A--=0AKevin Grittner=0AEDB: http://www.enterprise= db.com=0AThe Enterprise PostgreSQL Company
Re: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres
From
Kevin Grittner
Date:
Kevin Grittner <kgrittn@ymail.com> wrote: > "bemanuel.pe@gmail.com" <bemanuel.pe@gmail.com> wrote: > >> tjma_dw=> set role user_dw; >> >> tjma_dw=> CREATE TABLE foo_data AS SELECT i, md5(random()::text) FROM >> generate_series(1, 10) i; >> SELECT 10 >> tjma_dw=> CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data; >> SELECT 10 >> tjma_dw=> ALTER MATERIALIZED VIEW mv_foo OWNER TO user_dw; >> ALTER MATERIALIZED VIEW >> tjma_dw=> REFRESH MATERIALIZED VIEW mv_foo; >> REFRESH MATERIALIZED VIEW >> tjma_dw=> ALTER TABLE foo_data OWNER TO user_dw; >> ALTER TABLE >> tjma_dw=> REFRESH MATERIALIZED VIEW mv_foo; >> REFRESH MATERIALIZED VIEW >> tjma_dw=> create unique index on mv_foo (i); >> CREATE INDEX > >> /pgsql/pg94/bin/psql -Upostgres -p 5434 tjma_dw > >> tjma_dw=# refresh materialized view CONCURRENTLY mv_foo; >> ERROR: permission denied for relation pg_temp_432971_2 >> CONTEXT: SQL statement "DELETE FROM public.mv_foo mv WHERE ctid >> OPERATOR(pg_catalog.=) ANY (SELECT diff.tid FROM > pg_temp_10.pg_temp_432971_2 >> diff WHERE diff.tid IS NOT NULL AND diff.newdata IS NULL)" > > Yeah, that's a bug Attached is my proposed fix. I will push it in a day or two if there are no objections. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Attachment
Re: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres
From
Kevin Grittner
Date:
Kevin Grittner <kgrittn@ymail.com> wrote: > Kevin Grittner <kgrittn@ymail.com> wrote: >> "bemanuel.pe@gmail.com" <bemanuel.pe@gmail.com> wrote: >> >>> tjma_dw=> set role user_dw; >>> >>> tjma_dw=> CREATE TABLE foo_data AS SELECT i, md5(random()::text) FROM >>> generate_series(1, 10) i; >>> SELECT 10 >>> tjma_dw=> CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data; >>> SELECT 10 >>> tjma_dw=> ALTER MATERIALIZED VIEW mv_foo OWNER TO user_dw; >>> ALTER MATERIALIZED VIEW >>> tjma_dw=> REFRESH MATERIALIZED VIEW mv_foo; >>> REFRESH MATERIALIZED VIEW >>> tjma_dw=> ALTER TABLE foo_data OWNER TO user_dw; >>> ALTER TABLE >>> tjma_dw=> REFRESH MATERIALIZED VIEW mv_foo; >>> REFRESH MATERIALIZED VIEW >>> tjma_dw=> create unique index on mv_foo (i); >>> CREATE INDEX >> >>> /pgsql/pg94/bin/psql -Upostgres -p 5434 tjma_dw >> >>> tjma_dw=# refresh materialized view CONCURRENTLY mv_foo; >>> ERROR: permission denied for relation pg_temp_432971_2 >>> CONTEXT: SQL statement "DELETE FROM public.mv_foo mv WHERE ctid >>> OPERATOR(pg_catalog.=) ANY (SELECT diff.tid FROM pg_temp_10.pg_temp_432971_2 >>> diff WHERE diff.tid IS NOT NULL AND diff.newdata IS NULL)" >> >> Yeah, that's a bug > > Attached is my proposed fix. I will push it in a day or two if there > are no objections. Done. I think we will have a third beta release; which should include this fix. The master branch needed to be adjusted from the initially posted patch because of changes there. That version is attached. Thanks for testing the beta and for the report! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company