Allow `postgres` role to `create operator class` and `create operator family`

[olirice]

Feature request

A user is requesting the ability to create operator class and create operator family so they can bring a bit of a DSL-y experience to their project complete with custom operators that can leverage indexes.

Repro case

CREATE OR REPLACE FUNCTION integer_compare(a integer, b integer) RETURNS boolean AS $$
BEGIN
    RETURN a > b;
END;
$$ LANGUAGE plpgsql;

CREATE OPERATOR @@ (
    LEFTARG = integer,
    RIGHTARG = integer,
    PROCEDURE = integer_compare,
    RETURNS = boolean
);


CREATE OPERATOR FAMILY integer_family USING btree;


CREATE OPERATOR CLASS integer_ops FOR TYPE integer USING btree FAMILY integer_family AS
    OPERATOR 1 @@ (integer, integer);

Currently this will fail on

CREATE OPERATOR FAMILY integer_family USING btree;

with

ERROR:  42501: must be superuser to create an operator family

The existing postgres permissions system does not have a dedicated role for these capabilities.

This task is to evaluate

• if it is possible to enable creating operator families and operator classes using the postgres role
• If possible, how much work would it be

Given how niche the request is, unless it only requires an extremely straightforward and safe change we should not implement this feature.

[freshtonic]

The company I work for maintains a Postgres extension that requires the ability to create operator families and classes. Can anyone at Supabase confirm whether this is possible?

[olirice]

create operator is functional via native extensions. For example pgvector defines several operators for vector comparison

it is not currently supported as end-user activity

[ebellani]

👍 on this. I have a custom type that would really benefit from an operator class.