Re: BUG #18387: Erroneous permission checks and/or misleading error messages with refresh materialized view

On 11/03/2024 22:10, PG Bug reporting form wrote:
> 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)

So far, this is working correctly. REFRESH MATERIALIZED VIEW runs with
the permissions of the materialized view's owner. In this case, the
owner is 'test_role', which doesn't have select permission on the table.

> 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

That's interesting. REFRESH MATERIALIZED VIEW CONCURRENTLY uses
temporary tables internally, which fails if the user doesn't have
permissions to create temporary tables.

I guess we need to allow creating such internal temporary tables,
despite the missing permission. That'll need some careful analysis to
make sure we don't accidentally allow creating other temporary tables...

--
Heikki Linnakangas
Neon (https://neon.tech)