Re: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres

"bemanuel(dot)pe(at)gmail(dot)com" <bemanuel(dot)pe(at)gmail(dot)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; or probably two.  I can simplify the test case:

CREATE ROLE user_dw;
SET ROLE user_dw;
CREATE TABLE foo_data AS SELECT i, md5(random()::text)
  FROM generate_series(1, 10) i;
CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data;
CREATE UNIQUE INDEX ON mv_foo (i);
RESET ROLE;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_foo;

It is running afoul of a security measure (the query to repopulate
data is run as the owner of the materialized view, to prevent
placing trojan horses for a superuser).  But it seems to be
creating the temporary table as the superuser, preventing even the
owner from running the REFRESH ... CONCURRENTLY.  The query that is
being displayed is internal; we should probably find a way to show
the statement that was run at the top level instead.

I'll look at fixing both.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company