Hi All,

I wanted to store ip addresses in table. I wanted to support the following 3
types of ip addresses.

*1. Wildcard format :* 1.2.3.*
*
*
*2. CIDR format :* 1.2.3/24 OR 1.2.3.4/255.255.255.0
*
*
*3. Start-End IP format :* 1.2.3.0-1.2.3.255


I had a look at CIDR datatype and inet datatype but it seems they support
only partial of the above requirements. Any suggestions on what datatype is
useful for this purpose?

Thanks

Search Discussions

  • Sim Zacks at Feb 23, 2011 at 12:32 pm
    a regular varchar or text field.

    On 02/23/2011 02:09 PM, Gaini Rajeshwar wrote:

    Hi All,

    I wanted to store ip addresses in table. I wanted to support the
    following 3 types of ip addresses.
    *1. Wildcard format :* 1.2.3.*
    *
    *|
    *2. CIDR format :* 1.2.3/24 OR 1.2.3.4/255.255.255.0
    <http://1.2.3.4/255.255.255.0>
    *
    *|
    *3. Start-End IP format :* 1.2.3.0-1.2.3.255
    I had a look at CIDR datatype and inet datatype but it seems they
    support only partial of the above requirements. Any suggestions on
    what datatype is useful for this purpose?

    Thanks
  • Stephane Bortzmeyer at Feb 23, 2011 at 12:42 pm
    On Wed, Feb 23, 2011 at 02:30:18PM +0200,
    Sim Zacks <sim@compulab.co.il> wrote
    a message of 97 lines which said:
    a regular varchar or text field.
    Very bad idea since they don't support canonicalization (2001:db8::1
    == 2001:db8:0:0:0:0:0:1) or masking (set_masklen(address, 20)).
  • Stephane Bortzmeyer at Feb 23, 2011 at 12:44 pm
    On Wed, Feb 23, 2011 at 05:39:26PM +0530,
    Gaini Rajeshwar <raja.rajeshwar2006@gmail.com> wrote
    a message of 52 lines which said:
    I wanted to store ip addresses in table. I wanted to support the following 3
    types of ip addresses.

    *1. Wildcard format :* 1.2.3.*
    *
    *
    *2. CIDR format :* 1.2.3/24 OR 1.2.3.4/255.255.255.0
    What is the difference between 1.2.3.* and 1.2.3.0/24? For me, it is
    exactly the same. If you just want the ability to INPUT 1.2.3.*, just
    write a small conversion routine in your favorite programming
    language.
    *3. Start-End IP format :* 1.2.3.0-1.2.3.255
    You don't even need to program the conversion, it is already done:

    % netmask 1.2.3.0:1.2.3.255
    1.2.3.0/24
  • John R Pierce at Feb 23, 2011 at 6:49 pm

    On 02/23/11 4:44 AM, Stephane Bortzmeyer wrote:
    *3. Start-End IP format :* 1.2.3.0-1.2.3.255
    You don't even need to program the conversion, it is already done:

    % netmask 1.2.3.0:1.2.3.255
    1.2.3.0/24
    yes, but what about 10.1.2.57-10.1.2.123 ? presumably valid in his
    range system, and certainly NOT a valid CIDR range.
  • Michael Glaesemann at Feb 23, 2011 at 7:40 pm

    On Feb 23, 2011, at 13:49, John R Pierce wrote:
    On 02/23/11 4:44 AM, Stephane Bortzmeyer wrote:
    *3. Start-End IP format :* 1.2.3.0-1.2.3.255
    You don't even need to program the conversion, it is already done:

    % netmask 1.2.3.0:1.2.3.255
    1.2.3.0/24
    yes, but what about 10.1.2.57-10.1.2.123 ? presumably valid in his range system, and certainly NOT a valid CIDR range.
    If it hasn't been mentioned already, take a look at ip4r. <http://pgfoundry.org/projects/ip4r/>

    Michael Glaesemann
    grzm seespotcode net
  • Gaini Rajeshwar at Feb 24, 2011 at 9:25 am
    On Thu, Feb 24, 2011 at 1:10 AM, Michael Glaesemann wrote:
    On Feb 23, 2011, at 13:49, John R Pierce wrote:
    On 02/23/11 4:44 AM, Stephane Bortzmeyer wrote:
    *3. Start-End IP format :* 1.2.3.0-1.2.3.255
    You don't even need to program the conversion, it is already done:

    % netmask 1.2.3.0:1.2.3.255
    1.2.3.0/24
    yes, but what about 10.1.2.57-10.1.2.123 ? presumably valid in his
    range system, and certainly NOT a valid CIDR range.

    If it hasn't been mentioned already, take a look at ip4r. <
    http://pgfoundry.org/projects/ip4r/>
    I will look into it. May i know how to use this data type? It is not
    available with postgreSQL 8.4 right?
    I guess i have to download and integrate into postgreSQL, any suggestions on
    how to do?
    Michael Glaesemann
    grzm seespotcode net




    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general
  • Tom Lane at Feb 23, 2011 at 9:33 pm

    John R Pierce writes:
    On 02/23/11 4:44 AM, Stephane Bortzmeyer wrote:
    *3. Start-End IP format :* 1.2.3.0-1.2.3.255
    You don't even need to program the conversion, it is already done:

    % netmask 1.2.3.0:1.2.3.255
    1.2.3.0/24
    yes, but what about 10.1.2.57-10.1.2.123 ? presumably valid in his
    range system, and certainly NOT a valid CIDR range.
    The question is does he actually have a use-case for address ranges that
    don't correspond to legal CIDR ranges, but do nonetheless have an
    identifiable lower boundary, upper boundary, and no holes? And if so,
    what is it? The whole thing looked to me like somebody inventing
    requirements with little or no study of what they really needed.

    regards, tom lane
  • John R Pierce at Feb 23, 2011 at 10:01 pm

    On 02/23/11 1:33 PM, Tom Lane wrote:
    The question is does he actually have a use-case for address ranges that
    don't correspond to legal CIDR ranges, but do nonetheless have an
    identifiable lower boundary, upper boundary, and no holes? And if so,
    what is it? The whole thing looked to me like somebody inventing
    requirements with little or no study of what they really needed.
    indeed. i had to poke around the ban tables of MyBB the other day (a
    php bbs that works reasonably well* on postgresql), and they were
    storing IP bans as strings like 1.2.*.* or 1.2.3.4-1.2.4.5 or whatever.
    UGH.




    * it works mostly on PG but has some bugs around the edges of the admin
    stuff. the worst ones I've found fixes for and submitted back to the
    mybb team, but mostly I'm too lazy and just work around them
  • Gaini Rajeshwar at Feb 24, 2011 at 9:23 am

    On Thu, Feb 24, 2011 at 3:03 AM, Tom Lane wrote:

    John R Pierce <pierce@hogranch.com> writes:
    On 02/23/11 4:44 AM, Stephane Bortzmeyer wrote:
    *3. Start-End IP format :* 1.2.3.0-1.2.3.255
    You don't even need to program the conversion, it is already done:

    % netmask 1.2.3.0:1.2.3.255
    1.2.3.0/24
    yes, but what about 10.1.2.57-10.1.2.123 ? presumably valid in his
    range system, and certainly NOT a valid CIDR range.
    The question is does he actually have a use-case for address ranges that
    don't correspond to legal CIDR ranges, but do nonetheless have an
    identifiable lower boundary, upper boundary, and no holes? And if so,
    what is it? The whole thing looked to me like somebody inventing
    requirements with little or no study of what they really needed.
    I have customers who wanted to access application from different
    locations without using login credentials every time. So they wanted to
    register their ip addresses and have automated authentication for them. As
    i don't know how their ip addresses definitely going to be, i am assuming
    that they might have a ip address rage that is not a valid CIDR.

    regards, tom lane

    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedFeb 23, '11 at 12:09p
activeFeb 24, '11 at 9:25a
posts10
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase