must be owner of materialized view

[dilly-sixmo]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I created a materialized view along with a function to refresh it and a few triggers. The update function returns an error that says "must be owner of materialized view department_monthly_fees".

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Create a materialized view using the SQL Editor from within the Supabase frontend
2. Create a function that updates the materialized view using the SQL Editor

CREATE OR REPLACE FUNCTION refresh_department_monthly_fees()
RETURNS TRIGGER AS $$
BEGIN
  REFRESH MATERIALIZED VIEW department_monthly_fees;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. Create a trigger that will call the refresh function

CREATE TRIGGER refresh_department_monthly_fees_departments
AFTER INSERT OR UPDATE OR DELETE
ON departments
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_department_monthly_fees();

4. Alter the table for the trigger, causing the refresh function fire
5. See error: "must be owner of materialized view department_monthly_fees" . I can see this as a response in my browser when I try to alter the department_monthly_fees

Expected behavior

The refresh function should fire, updating the materialized view

What I tried

1. I tried altering the view owner from the SQL editor:

alter materialized view department_monthly_fees owner to authenticated;

Result: Failed to run sql query: permission denied for schema public

2. I paused my project and restored it, which caused PostgreSQL to update to version 15. I then tried to recreate my materialized view with security invoker:

CREATE MATERIALIZED VIEW department_monthly_fees WITH (security_invoker) AS
...

Result: Failed to run sql query: unrecognized parameter "security_invoker"

System information

• PostgreSQL Version: 15 now (tried on 14, then updated and trying in 15 now)

[aimeeraymond]

I ran into the same issue and was able to solve by adding a "security definer" to the function, per this link https://stackoverflow.com/questions/67530999/error-must-be-owner-of-materialized-view-postgresql

[chreds]

Just ran into this today. The stackoverflow is dead do you have another link?

[antogg]

Just copy the link's text and paste it into a new browser tab/window.