Thread: Possible to create canonicalized range type without being superuser?

Hello,

I'm trying to create a custom inetrange type. This works:

     CREATE TYPE inetrange AS RANGE (
       subtype = inet,
       subtype_diff = inet_diff
     );

But since inet is discrete, not continuous, I'd like to define a
canonical function too:

     CREATE TYPE inetrange AS RANGE (
       subtype = inet,
       subtype_diff = inet_diff,
       canonical = inetrange_canonical
     );

But of course I can't define the function until I have the type, and I
can't define the type until I have the function.

Now the docs address this
(https://www.postgresql.org/docs/9.5/static/sql-createtype.html), and
recommend:

 > Creating a canonical function is a bit tricky, since it must be
defined before the range type can be declared. To do this, you must
first create a shell type, which is a placeholder type that has no
properties except a name and an owner. This is done by issuing the
command CREATE TYPE name, with no additional parameters. Then the
function can be declared using the shell type as argument and result,
and finally the range type can be declared using the same name. This
automatically replaces the shell type entry with a valid range type.

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?

Thanks!
Paul

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

On Tue, Jul 5, 2016 at 3:28 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Tue, Jul 5, 2016 at 5:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Paul Jungwirth <pj@illuminatedcomputing.com> writes:
>> > The problem is this (tried on 9.3 and 9.5):
>>
>> 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.
>
> Can the canonical function be definitionally optional but runtime required?

Thanks Tom and David for your help! I actually tried to find something
in ALTER TYPE that would do just that, but of course it's not there.
It seems like an appealing solution though.

With Tom's warning that a shell type would require writing the
function in C, I figured I might as well just package it all up as an
extension, so here it is:

https://github.com/pjungwir/inetrange

Of course it still requires superuser privileges, but as least the C
part is just `make && sudo make install`.

Thanks again!
Paul

On Tue, Jul 5, 2016 at 10:17 PM, Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
> db=> create type inetrange;

Here is a follow-up question for creating inet ranges. Is there any
way to prevent someone from doing this?:

    db=> select inetrange('1.2.3.4',
'2001:0db8:0000:0042:0000:8a2e:0370:7334', '[]');
                    inetrange
    -----------------------------------------
     [1.2.3.4,2001:db8:0:42:0:8a2e:370:7334]

Thanks!
Paul