1

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).

Improve this question

1 Answer 1

Reset to default
0

You can create a function with security definer owned by the MVIEW owner that does the refresh and then grant execute privilege on the function (or procedure) to your app user.

Improve this answer
2
  • that would be neat. unfortunately after all migrations we reassign owned to dummy role (common for the whole cluster with no privileges at all) because we're using dynamicaly generated credentials (each migration connects with different role). maybe reowning after each migration is bad practice and the solution would be migrating through role in group and dropping owner to group instead of the dummy role. (and giving group ddl privileges)
    – krszp
    Commented Feb 12, 2019 at 17:43
  • Then create that function as the dummy role (or assign it)
    – user1822
    Commented Feb 12, 2019 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.