CODEWITHSUNDEEP
postgresqlviewmaterialized-views
Nikk
Nikk

Reputation: 7891

ERROR: must be owner of materialized view PostgreSQL

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?

Upvotes: 8

Views: 10841

Answers (1)

Nikk
Nikk

Reputation: 7891

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;

Upvotes: 8

Related Questions