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)