How to grant Refresh permissions to the materialized view to user in POSTGRESQL?

Question

I am executing sql file on linux by running script.

I can see my queries are getting executed fine but I have following query to refresh view in my testData.sql file that is giving me the error

refresh MATERIALIZED VIEW view_test

Error

psql:/home/test/sql/testData.sql:111: ERROR:  must be owner of relation view_test

I have applied following permissions

grant select,update,delete,insert on view_test to "user123";

How to grant refresh permissions to the View in POSTGRESQL?

Answer 1

You should create a new role to own the view, then grant that role to any users who need permission to refresh the view. This ensures that you can easily assign the permission when you need to create additional user credentials.

CREATE ROLE refresh_materialized_views;
GRANT SELECT, INSERT, UPDATE, DELETE ON view_test TO refresh_materialized_views;
GRANT refresh_materialized_views TO user123;
GRANT refresh_materialized_views TO current_owner_of_view_test;
ALTER TABLE view_test OWNER TO refresh_materialized_views;

A tempting answer would be to alter the owner of the view to user123, but that's dangerous - you'd be breaking the current owner's access to their view.