PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?
You can do this with a function that runs in the security context of its owner.
Function that refreshes the view (create it with the user that owns the MV/table):
CREATE OR REPLACE FUNCTION refresh_mvw1()
RETURNS void
SECURITY DEFINER
AS $$
BEGIN
REFRESH MATERIALIZED VIEW mvw1 with data;
RETURN;
END;
$$ LANGUAGE plpgsql;
Grant execute on the function to any user that you want to be able to refresh the view:
-- Users have 'execute' permissions by default on functions!
revoke all on function refresh_mvw1() from public;
grant execute on function refresh_mvw1() to u1;
To refresh:
select refresh_mvw1();
From the Postgres docs:
SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it.
Version that supports a parameter:
CREATE OR REPLACE FUNCTION refresh_mv_xxx(table_name text)
RETURNS void
SECURITY DEFINER
AS $$
DECLARE sql text;
BEGIN
sql := 'REFRESH MATERIALIZED VIEW ' || table_name || ' with data';
EXECUTE sql;
RETURN;
END;
$$ LANGUAGE plpgsql;
... but I'm not sure if dynamic SQL will still execute as the definer.
Another option is to create a (non-superuser) group role to which both spu1 and u1 belong to/inherit from (e.g. "refreshers"), and assign that group role as the owner of the materialized view.