ERROR: must be owner of materialized view PostgreSQL

user_2253323 0
Viewed 2521

I get the following error:

ERROR:  must be owner of materialized view mv_sessions
SQL state: 42501

When trying to refresh my materialized view:

REFRESH MATERIALIZED VIEW CONCURRENTLY partners.mv_sessions;

The owner of mv_sessions is my custom master user. And I've assigned all privileges to the sub user partner with which I'm trying to refresh the view.

I do not want to make partner the owner of mv_sessions. And also there are other sub users that will need to be able to refresh the view.

I tried with a trigger function:

BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY partners.mv_sessions;
    RETURN NULL;
END

Getting the same error:

psycopg2.errors.InsufficientPrivilege: must be owner of materialized view mv_sessions
CONTEXT:  SQL statement "REFRESH MATERIALIZED VIEW CONCURRENTLY partners.mv_sessions"
PL/pgSQL function partners.refresh_mv_sessions() line 3 at SQL statement

How do I fix this?

materialized-views view postgresql
1 Answers

Function causing the problem:

CREATE OR REPLACE FUNCTION partners.refresh_mv_sessions()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY partners.mv_sessions;
    RETURN NULL;
END $$;

A fix using SECURITY DEFINER according to this answer (as suggested by Marth) so that a non-owner can refresh a materialized view:

CREATE OR REPLACE FUNCTION partners.refresh_mv_sessions()
RETURNS TRIGGER
SECURITY DEFINER
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY partners.mv_sessions;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
user_22533230
Related