How to create a function that will allow a user who is not a superuser or relation owner to refresh a materialized view.

Products

• Greenplum
• VMware Tanzu Greenplum
• Pivotal Data Suite Non Production Edition
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

Issue/Introduction

Customers may encounter situations where a user lacks superuser privileges or ownership of a relation but still needs to execute the REFRESH MATERIALIZED VIEW operation. The documentation on REFRESH MATERIALIZED VIEW states, "To run this command you must be the owner of the materialized view".

Environment

GPDB: 6.x and above

Resolution

The database administrator (superuser) or the materialized view owner has the option to create a function responsible for executing the refresh of a materialized view and can grant specific users the authority to refresh a particular materialized view using an alias, along with the required permissions.

Here is an example of the function that will allow a user to run the REFRESH MATERIALIZED VIEW. 

CREATE TABLE foobar AS SELECT i, i+1 AS j, i+2 AS k FROM generate_series(1,100)i;
CREATE MATERIALIZED VIEW foobar_mv AS SELECT j, k FROM foobar WHERE i < 20;
INSERT INTO foobar SELECT i, i+1, i+2 FROM generate_series(1,10)i;

CREATE ROLE foorole;
GRANT SELECT ON foobar_mv TO foorole;

SET ROLE foorole;
SELECT * FROM foobar_mv;
REFRESH MATERIALIZED VIEW public.foobar_mv; -- will get permission denied
RESET ROLE;

-- Create SECURITY DEFINER function to let others refresh the
-- materialized view.
CREATE OR REPLACE FUNCTION refresh_foobar_mv()
    RETURNS void SECURITY DEFINER
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW public.foobar_mv;
END;
$$
LANGUAGE plpgsql;

-- Make sure to remove and grant permissions accordingly
REVOKE ALL ON FUNCTION refresh_foobar_mv() FROM public;
GRANT EXECUTE ON FUNCTION refresh_foobar_mv() TO foorole;

SET ROLE foorole;
SELECT * FROM foobar_mv;
SELECT refresh_foobar_mv();
SELECT * FROM foobar_mv;
RESET ROLE;