Comparing first 3 numbers of a IPv4 address?

Hey Alexander,

2010/11/20 Alexander Farber <alexander.farber@gmail.com>

Hello,

I'm trying to program a PHP-script, where users
can rate the "goodness" of the other players:

create table pref_rep (
id varchar(32) references pref_users(id) check (id <>
author),
author varchar(32) references pref_users(id),
author_ip inet,
good boolean,
last_rated timestamp default current_timestamp
);

To (try to) prevent tampering I'd like to delete
entries for the same id coming
from the same IP in the course of last hour:

create or replace function pref_update_rep(_id varchar,
_author varchar, _author_ip inet,
_good boolean) returns void as $BODY$
begin

delete from pref_rep
where id = _id and
author_ip = _author_ip and
age(to_timestamp(last_rated)) < interval '1 hour';

update pref_rep set
author = _author,
author_ip = _author_ip,
good = _good,
last_rated = current_timestamp
where id = _id and author = _author;

if not found then
insert into pref_rep(id, author, author_ip, good)
values (_id, _author, _author_ip, _good);
end if;
end;
$BODY$ language plpgsql;

I have 2 questions please:

1) if I'd like to compare just the first 3 numbers of
the IP address instead of the 4, how can I do it?
(yes, I know about the A,B,C type of IPv4 networks...)

You may try something like this (this solution can be better):
SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
to get first 3 bytes of IP in array (ready to compare with another
array).

2) Do I need to add an index to my table
or are id and author indexed already?

Foreign keys columns does not indexed. You should create them
manually (if you need).

Thank you!
Alex

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

--
// Dmitriy.

I'm actually hoping to use inet (or cidr?) instead of strings...

On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.com> wrote:

Hey Alexander,

2010/11/20 Alexander Farber <alexander.farber@gmail.com>

Hello,

I'm trying to program a PHP-script, where users
can rate the "goodness" of the other players:

create table pref_rep (
id varchar(32) references pref_users(id) check (id <>
author),
author varchar(32) references pref_users(id),
author_ip inet,
good boolean,
last_rated timestamp default current_timestamp
);

To (try to) prevent tampering I'd like to delete
entries for the same id coming
from the same IP in the course of last hour:

create or replace function pref_update_rep(_id varchar,
_author varchar, _author_ip inet,
_good boolean) returns void as $BODY$
begin

delete from pref_rep
where id = _id and
author_ip = _author_ip and
age(to_timestamp(last_rated)) < interval '1 hour';

update pref_rep set
author = _author,
author_ip = _author_ip,
good = _good,
last_rated = current_timestamp
where id = _id and author = _author;

if not found then
insert into pref_rep(id, author, author_ip, good)
values (_id, _author, _author_ip, _good);
end if;
end;
$BODY$ language plpgsql;

I have 2 questions please:

1) if I'd like to compare just the first 3 numbers of
the IP address instead of the 4, how can I do it?
(yes, I know about the A,B,C type of IPv4 networks...)

You may try something like this (this solution can be better):
SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
to get first 3 bytes of IP in array (ready to compare with another
array).

2) Do I need to add an index to my table
or are id and author indexed already?

Foreign keys columns does not indexed. You should create them
manually (if you need).

Thank you!
Alex

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

--
// Dmitriy.

--
Sent from my mobile device

You can. host() just extract IP address from a value of inet
type as text, string_to_array() converts this text to text[], and
it makes it possible to compare with another text[]... I see no
problem here. It works just fine.
But probably there is another (better) solution...

2010/11/20 Alexander Farber <alexander.farber@gmail.com>

I'm actually hoping to use inet (or cidr?) instead of strings...

On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.com> wrote:

Hey Alexander,

2010/11/20 Alexander Farber <alexander.farber@gmail.com>

Hello,

I'm trying to program a PHP-script, where users
can rate the "goodness" of the other players:

create table pref_rep (
id varchar(32) references pref_users(id) check (id <>
author),
author varchar(32) references pref_users(id),
author_ip inet,
good boolean,
last_rated timestamp default current_timestamp
);

To (try to) prevent tampering I'd like to delete
entries for the same id coming
from the same IP in the course of last hour:

create or replace function pref_update_rep(_id varchar,
_author varchar, _author_ip inet,
_good boolean) returns void as $BODY$
begin

delete from pref_rep
where id = _id and
author_ip = _author_ip and
age(to_timestamp(last_rated)) < interval '1 hour';

update pref_rep set
author = _author,
author_ip = _author_ip,
good = _good,
last_rated = current_timestamp
where id = _id and author = _author;

if not found then
insert into pref_rep(id, author, author_ip, good)
values (_id, _author, _author_ip, _good);
end if;
end;
$BODY$ language plpgsql;

I have 2 questions please:

1) if I'd like to compare just the first 3 numbers of
the IP address instead of the 4, how can I do it?
(yes, I know about the A,B,C type of IPv4 networks...)

You may try something like this (this solution can be better):
SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
to get first 3 bytes of IP in array (ready to compare with another
array).

2) Do I need to add an index to my table
or are id and author indexed already?

Foreign keys columns does not indexed. You should create them
manually (if you need).

Thank you!
Alex

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

--
// Dmitriy.

--
Sent from my mobile device

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

--
// Dmitriy.

I think inet is a number internally, there is probably a more effective way...

On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.com> wrote:

You can. host() just extract IP address from a value of inet
type as text, string_to_array() converts this text to text[], and
it makes it possible to compare with another text[]... I see no
problem here. It works just fine.
But probably there is another (better) solution...

2010/11/20 Alexander Farber <alexander.farber@gmail.com>

I'm actually hoping to use inet (or cidr?) instead of strings...

On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.com> wrote:

Hey Alexander,

2010/11/20 Alexander Farber <alexander.farber@gmail.com>

Hello,

I'm trying to program a PHP-script, where users
can rate the "goodness" of the other players:

create table pref_rep (
id varchar(32) references pref_users(id) check (id <>
author),
author varchar(32) references pref_users(id),
author_ip inet,
good boolean,
last_rated timestamp default current_timestamp
);

To (try to) prevent tampering I'd like to delete
entries for the same id coming
from the same IP in the course of last hour:

create or replace function pref_update_rep(_id varchar,
_author varchar, _author_ip inet,
_good boolean) returns void as $BODY$
begin

delete from pref_rep
where id = _id and
author_ip = _author_ip and
age(to_timestamp(last_rated)) < interval '1 hour';

update pref_rep set
author = _author,
author_ip = _author_ip,
good = _good,
last_rated = current_timestamp
where id = _id and author = _author;

if not found then
insert into pref_rep(id, author, author_ip,
good)
values (_id, _author, _author_ip, _good);
end if;
end;
$BODY$ language plpgsql;

I have 2 questions please:

1) if I'd like to compare just the first 3 numbers of
the IP address instead of the 4, how can I do it?
(yes, I know about the A,B,C type of IPv4 networks...)

You may try something like this (this solution can be better):
SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
to get first 3 bytes of IP in array (ready to compare with another
array).

2) Do I need to add an index to my table
or are id and author indexed already?

Foreign keys columns does not indexed. You should create them
manually (if you need).

Thank you!
Alex

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

--
// Dmitriy.

--
Sent from my mobile device

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

--
// Dmitriy.

--
Sent from my mobile device

I will be glad to see the best solution, so if you find it please share. :-)

But you want to compare IP addresses by 3 first parts. I don't see any
function or operator at SQL level which allow to do it without converting
to text.
Probably, you can do it by manipulating a binary form (from libpq).
But is it worth it ? :-)

2010/11/20 Alexander Farber <alexander.farber@gmail.com>

I think inet is a number internally, there is probably a more effective
way...

On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.com> wrote:

You can. host() just extract IP address from a value of inet
type as text, string_to_array() converts this text to text[], and
it makes it possible to compare with another text[]... I see no
problem here. It works just fine.
But probably there is another (better) solution...

2010/11/20 Alexander Farber <alexander.farber@gmail.com>

I'm actually hoping to use inet (or cidr?) instead of strings...

On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.com> wrote:

Hey Alexander,

2010/11/20 Alexander Farber <alexander.farber@gmail.com>

Hello,

I'm trying to program a PHP-script, where users
can rate the "goodness" of the other players:

create table pref_rep (
id varchar(32) references pref_users(id) check (id <>
author),
author varchar(32) references pref_users(id),
author_ip inet,
good boolean,
last_rated timestamp default current_timestamp
);

To (try to) prevent tampering I'd like to delete
entries for the same id coming
from the same IP in the course of last hour:

create or replace function pref_update_rep(_id varchar,
_author varchar, _author_ip inet,
_good boolean) returns void as $BODY$
begin

delete from pref_rep
where id = _id and
author_ip = _author_ip and
age(to_timestamp(last_rated)) < interval '1 hour';

update pref_rep set
author = _author,
author_ip = _author_ip,
good = _good,
last_rated = current_timestamp
where id = _id and author = _author;

if not found then
insert into pref_rep(id, author, author_ip,
good)
values (_id, _author, _author_ip, _good);
end if;
end;
$BODY$ language plpgsql;

I have 2 questions please:

1) if I'd like to compare just the first 3 numbers of
the IP address instead of the 4, how can I do it?
(yes, I know about the A,B,C type of IPv4 networks...)

You may try something like this (this solution can be better):
SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
to get first 3 bytes of IP in array (ready to compare with another
array).

2) Do I need to add an index to my table
or are id and author indexed already?

Foreign keys columns does not indexed. You should create them
manually (if you need).

Thank you!
Alex

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

--
// Dmitriy.

--
Sent from my mobile device

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

--
// Dmitriy.

--
Sent from my mobile device

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

--
// Dmitriy.

Alexander Farber <alexander.farber@gmail.com> writes:

1) if I'd like to compare just the first 3 numbers of
the IP address instead of the 4, how can I do it?

regression=# select network(set_masklen('123.45.67.42'::inet, 24)) =
regression-# network(set_masklen('123.45.67.56'::inet, 24));
?column?
----------
t
(1 row)

regards, tom lane

Thank you. Or maybe also?

(author_ip & '255.255.255.0'::inet) = (_author_ip & '255.255.255.0'::inet)

Hey Tom,

Thanks for you solution!

2010/11/20 Tom Lane <tgl@sss.pgh.pa.us>

Alexander Farber <alexander.farber@gmail.com> writes:

1) if I'd like to compare just the first 3 numbers of
the IP address instead of the 4, how can I do it?

regression=# select network(set_masklen('123.45.67.42'::inet, 24)) =
regression-# network(set_masklen('123.45.67.56'::inet, 24));
?column?
----------
t
(1 row)

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

--
// Dmitriy.

On 2010-11-20, Alexander Farber <alexander.farber@gmail.com> wrote:

1) if I'd like to compare just the first 3 numbers of
the IP address instead of the 4, how can I do it?
(yes, I know about the A,B,C type of IPv4 networks...)

have you heard of CIDR (what about IPV6, which I'm going to ignore,
but you should consider unless this code is throw-away)

just use set_masklen and the >> operator.

select set_masklen('1.2.3.4'::inet,24) >> '1.2.3.244'::inet;

--
⚂⚃ 100% natural