The following bug has been logged on the website:
Bug reference: 18387
Logged by: Maxim Boguk
Email address: maxim.boguk@gmail.com
PostgreSQL version: 16.2
Operating system: Linux
Description:
Hi,
There are weird behavior with permission checks for refresh materialized
view for superuser.
Reproducer script (run from postgres):
create role test_role;
create database test with owner postgres;
\c test
create table test as select 1 val;
create materialized view test_mv as select * from test;
REFRESH MATERIALIZED VIEW test_mv;
create unique index test_mv_pk on test_mv(val);
REFRESH MATERIALIZED VIEW CONCURRENTLY test_mv;
alter materialized view test_mv owner to test_role;
REFRESH MATERIALIZED VIEW CONCURRENTLY test_mv;
select * from test;
revoke temporary on database test from public;
\c test
REFRESH MATERIALIZED VIEW CONCURRENTLY test_mv;
REFRESH MATERIALIZED VIEW test_mv;
Reproducer log (starting from interesting part):
test=# REFRESH MATERIALIZED VIEW CONCURRENTLY test_mv;
REFRESH MATERIALIZED VIEW
test=# alter materialized view test_mv owner to test_role;
ALTER MATERIALIZED VIEW
test=# REFRESH MATERIALIZED VIEW CONCURRENTLY test_mv;
ERROR: permission denied for table test
--what??? N1
--check that im not hallucinating
test=# select * from test;
val
-----
1
(1 row)
test=# revoke temporary on database test from public;
REVOKE
test=# \c test
You are now connected to database "test" as user "postgres".
test=# REFRESH MATERIALIZED VIEW CONCURRENTLY test_mv;
ERROR: permission denied to create temporary tables in database "test"
--what??? N2
test=# REFRESH MATERIALIZED VIEW test_mv;
ERROR: permission denied for table test
--without CONCURRENTLY wrong behavior too