From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: maxim.boguk@gmail.com
Subject: BUG #18387: Erroneous permission checks and/or misleading error messages with refresh materialized view
Date: Mon, 11 Mar 2024 20:10:08 +0000
Message-ID: <18387-12041858d9efb6f6@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, maxim.boguk@gmail.com
Subject: Re: BUG #18387: Erroneous permission checks and/or misleading error messages with refresh materialized view
In-Reply-To: <18387-12041858d9efb6f6@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox