Обсуждение: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres

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=#

"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

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

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