Paul Jungwirth <pj@illuminatedcomputing.com> writes:
> The problem is this (tried on 9.3 and 9.5):
> db=> create type inetrange;
> ERROR: must be superuser to create a base type
> So I'm wondering whether there is any way around this circle without
> being a superuser?
Hm. It seems like it should be OK to allow ordinary users to create
shell types and then convert them to non-base types. However, even
if we did that, you would not get far as a non-superuser, because
you can't create SQL or PL functions dealing in shell types:
regression=# create type inetrange;
CREATE TYPE
regression=# create function f(inetrange) returns inetrange as
regression-# $$begin return $1; end$$ language plpgsql;
NOTICE: argument type inetrange is only a shell
NOTICE: return type inetrange is only a shell
ERROR: PL/pgSQL functions cannot return type inetrange
regression=# create function f(inetrange) returns inetrange as
regression-# 'select $1' language sql;
ERROR: SQL function cannot accept shell type inetrange
I'm a bit hesitant to relax that prohibition; it seems like it
could lead to security problems. But that means that range
canonical functions have to be written in C, which means you need
to be superuser anyway.
The only other obvious way to deal with this is to allow the canonical
function to be defined after the range type is created, and then added to
the type via an ALTER TYPE command. But then you have an interval where
the type is not restricted, in which you might store values that aren't
canonical.
I have some recollection that we discussed this when range types
were being invented, and didn't think of any nice solution.
regards, tom lane