I came across a pickle when dealing with question from one of our dev concerning postgresql and materialized views:
can I refresh materialized view with my application user?
well according to docs (https://www.postgresql.org/docs/11/sql-creatematerializedview.html), it is simple enough, BUT you must be owner of said view.
We have isolated acls for roles we run migrations under (ddl) and roles that are "application" and thus can operate with limited privileges. So I can't create the view during migration, cause application need to update it once a while and I can't create it in application runtime, because application role doesn't have create privilege.
I think someone must have came across similar issue, but search engines are not helping at all. last hope is making application user a little bit more privileged (with create on schema granted).
Best Answer
You can create a function with
security definerowned by the MVIEW owner that does the refresh and then grant execute privilege on the function (or procedure) to your app user.