I have a situation where I admin user and a ETL pipeline user. The admin is a super user and runs all schema migrations and is the owner of all objects by default. I created a materialized view that I wanted to refresh using the pipeline user. So I needed to make the pipeline user an owner of the materialized view. I ran something like the following SQL:
CREATE ROLE materialized_view_owner WITH NOLOGIN;
ALTER MATERIALIZED VIEW scheme.pivot_view OWNER TO materialized_view_owner;
GRANT materialized_view_owner TO pipeline;
Refreshing fails and I get the following error: permission denied for table used_in_view. The user has the priveleges required to run the view query. It fails the same way even when using the admin which is a superuser (after granting the materialized_view_owner to the admin).
If I grant all the required permissions to run the underlying query for the view to materialized_view_owner, it works. So it is as if the permissions of the actual user/role is ignored and only the materialized_view_owner permissions are applied. My understanding is that the permissions should be additive.
What is going on?
Notes: The view uses crosstab (from tablefunc extension) This is in Postgres 13.9.
Permissions on the tables, functions and other objects used in a view are checked for the owner of the view, not the user who uses the view. See the documentation:
By default, access to the underlying base relations referenced in the view is determined by the permissions of the view owner. In some cases, this can be used to provide secure but restricted access to the underlying tables. However, not all views are secure against tampering; see Section 41.5 for details.