Possible to create canonicalized range type without being superuser?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Possible to create canonicalized range type without being superuser?

Paul Jungwirth
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



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Paul Jungwirth <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Tue, Jul 5, 2016 at 5:37 PM, Tom Lane <[hidden email]> wrote:
Paul Jungwirth <[hidden email]> 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?

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.

​Can the canonical function be definitionally optional but runtime required?  That is, have it only be an error to use a type lacking a canonical function?  If so I think a usable idiom is that for types that don't want to canonicalize (i.e., presently have a NULL assigned) they would make an explicit declaration by doing something like:

CREATE TYPE int4range AS RANGE (subtype = int4, canonical = int4_identity);

Now you have a window where the type is incompletely defined and when the missing canonical function is encountered the system balks.  At some future point a function can be associated via ALTER TYPE which makes the type completely defined.

CREATE TYPE inetrange AS RANGE (subtype = inet, canonical = NULL);
ALTER TYPE inetrange SET PROPERTY canonical = inet_canonicalizer;
 ​
David J.

Reply | Threaded
Open this post in threaded view
|

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

Paul Jungwirth
On Tue, Jul 5, 2016 at 3:28 PM, David G. Johnston
<[hidden email]> wrote:
> On Tue, Jul 5, 2016 at 5:37 PM, Tom Lane <[hidden email]> wrote:
>> Paul Jungwirth <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

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

Paul Jungwirth
On Tue, Jul 5, 2016 at 10:17 PM, Paul A Jungwirth
<[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Previous Thread Next Thread