ERROR: must be owner of materialized view PostgreSQL

Question

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?

Swedenborgianism

The documentation does state "To execute this command you must be the owner of the materialized view". See this answer for a workaround. – Keratinize

@Keratinize So I’m supposed to create 3 MVs which are the same in order to be able to access them with the 3 separate users? – Swedenborgianism

No. Read (access) permissions are not the same as refresh permissions. You can grant multiple users read-access to the same materialized view, but only the owner(s) will be able to trigger a refresh. Or you can try the solution in the answer I linked. – Keratinize

Answer 1

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;

Swedenborgianism

Never create a SECURITY DEFINER function without SET search_path = .... It happens to be safe here, though. – Schonthal

@LaurenzAlbe Can you please elaborate on the search_path? – Swedenborgianism

postgresql.org/docs/current/… – Schonthal