错误：必须是材料化视图的所有者 PostgreSQL

Question

我遇到了以下错误:

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

当尝试刷新我的物化视图：

REFRESH MATERIALIZED VIEW CONCURRENTLY partners.mv_sessions;

mv_sessions的所有者是我的自定义master用户。我已经将所有权限分配给了子用户partner，并尝试刷新视图。

我不希望将partner作为mv_sessions的所有者。还有其他子用户需要能够刷新视图。

---

我尝试使用触发器函数：

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

出现相同的错误：

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

我该如何修复这个问题？

- Nikk

---

文档中指出：“要执行此命令，您必须是物化视图的所有者”。请参阅此答案以获取解决方法。 - Marth

@Marth 所以我需要创建三个相同的 MV，以便能够使用三个不同的用户访问它们？ - Nikk

不要混淆读取（访问）权限和刷新权限。您可以授予多个用户对同一物化视图的读取访问权限，但只有所有者才能触发刷新。或者您可以尝试我链接的答案中提供的解决方案。 - Marth

Answer 1

引起问题的函数：

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

根据这个答案中Marth的建议，使用SECURITY DEFINER修复问题，使得非所有者也能够刷新物化视图：

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;

- Nikk

---

永远不要创建没有SET search_path = ...的SECURITY DEFINER函数。虽然在这里是安全的。 - Laurenz Albe

@LaurenzAlbe，您能否详细说明一下search_path？ - Nikk

https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY - Laurenz Albe