Message-ID: <36bb333d-13ec-4f8e-af33-48249ca2fe69@iki.fi>
Date: Tue, 12 Mar 2024 01:02:16 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18387: Erroneous permission checks and/or misleading error
 messages with refresh materialized view
Content-Language: en-US
To: maxim.boguk@gmail.com, pgsql-bugs@lists.postgresql.org
References: <18387-12041858d9efb6f6@postgresql.org>
From: Heikki Linnakangas <hlinnaka@iki.fi>
In-Reply-To: <18387-12041858d9efb6f6@postgresql.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/36bb333d-13ec-4f8e-af33-48249ca2fe69%40iki.fi>
Precedence: bulk

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)