Materialized view should only check permissions of underlying query for refresh

[dain]

From a security perspective a materialized view is just a table, and should be treated as such. The only time we should be checking if the definer has permissions to run the view attached to the query is during refresh.

This was found in the discussion: #23387

[lozbrown]

@dain should refresh materialized view be a separate permission in the API/SPI, refreshing a view of a big table can be a significant load on the platform and we may want to prevent users doing that too frequently?

[lozbrown]

@dain Thinking about this more, whilst I agree for select an MV should be treated as a table, should the end user really need permission to the underlying table just to refresh?

Imagine this scenario

You create an MV over a sensitive_table_a for a report that non_priveliged_userb uses on a regular basis. If the MV aggregates it otherwise does not include the sensitive data by refreshing non_priveliged_userb is not gaining any additional access to data?

By this notion only select on the MV should be necessary for the refresh and only the creator needs access to the underlying data.

However refreshing an MV can be potentially expensive and may have been created (instead of a view) to prevent users from doing the expensive query underneath too frequently, hence a separate refresh permission would be better so that refreshes may only occur when the grace period runs out or someone with the permission refreshes

[dain]

should refresh materialized view be a separate permission in the API/SPI

It is. Here is the check.

should the end user really need permission to the underlying table just to refresh?

I think you misunderstood me. During refresh we check if the "definer" (a.k.a., the owner) of the materialized view still has permission to access the underlying data of the view. The refresh is executed using the permissions of the definer not the "invoker" of the command.

[codyzwief]

I have attempted to take a stab at this in #25872