must be owner of function when creating a function in Postgres

Question

I'm trying to debug an instance where I try to create a function with a special role "myrole" and the operation fails with

ERROR: must be owner of function refresh_view

Other functions can be created successfully — it's only this single function that is failing.

SET ROLE to "myrole";

CREATE OR REPLACE FUNCTION refresh_view(schema_name text, table_name text)
RETURNS void
SECURITY DEFINER
AS $$
DECLARE sql text;
BEGIN
sql := 'REFRESH MATERIALIZED VIEW ' || quote_ident(schema_name) || '.' || quote_ident(table_name) || ' with data';
EXECUTE sql;
RETURN;
END;
$$ LANGUAGE plpgsql;

because you try to replace it?..

Vao Tsun
– Vao Tsun

2018-01-10 10:42:31 +00:00
Commented Jan 10, 2018 at 10:42 — Vao Tsun
– Vao Tsun, Commented Jan 10, 2018 at 10:42

Laurenz Albe · Accepted Answer · 2018-01-10 10:43:39Z

14

The function already exists and is owned by a different role.

Only the owner or a superuser can DROP and ALTER objects, and the same applies for CREATE OR REPLACE FUNCTION if the function already exists.

answered Jan 10, 2018 at 10:43

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

salient Over a year ago

It had made it to template1 for some inexplicable reason. Probably PEBKAC as per ususal.

Collectives™ on Stack Overflow

must be owner of function when creating a function in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related