Allow format 0000-0000-0000 in postgresql MAC parser

Started by Herwin Weststrateover 11 years ago6 messages

herwin@quarantainenet.nl

over 11 years ago

1 attachment(s)

Hello,

Some devices send the MAC address in RADIUS requests in the format
0000-0000-0000. I've seen this with a 3com switch, but there may be
others. Currently, postgresql doesn't understand this format.

This patch adds an extra line to the macaddr parsing in postgres to
support this format as well. A unit test has been added.

Kind regards,

--
Herwin Weststrate
Quarantainenet BV
www.quarantainenet.nl

Attachments:

postgres-mac-parsing.difftext/x-patch; name=postgres-mac-parsing.diffDownload

diff --git a/src/backend/utils/adt/mac.c b/src/backend/utils/adt/mac.c
index aa9993f..509315a 100644
--- a/src/backend/utils/adt/mac.c
+++ b/src/backend/utils/adt/mac.c
@@ -57,6 +57,9 @@ macaddr_in(PG_FUNCTION_ARGS)
 		count = sscanf(str, "%2x%2x.%2x%2x.%2x%2x%1s",
 					   &a, &b, &c, &d, &e, &f, junk);
 	if (count != 6)
+		count = sscanf(str, "%2x%2x-%2x%2x-%2x%2x%1s",
+					   &a, &b, &c, &d, &e, &f, junk);
+	if (count != 6)
 		count = sscanf(str, "%2x%2x%2x%2x%2x%2x%1s",
 					   &a, &b, &c, &d, &e, &f, junk);
 	if (count != 6)
diff --git a/src/test/regress/expected/macaddr.out b/src/test/regress/expected/macaddr.out
index 91edc5a..90e9b34 100644
--- a/src/test/regress/expected/macaddr.out
+++ b/src/test/regress/expected/macaddr.out
@@ -7,14 +7,15 @@ INSERT INTO macaddr_data VALUES (2, '08-00-2b-01-02-03');
 INSERT INTO macaddr_data VALUES (3, '08002b:010203');
 INSERT INTO macaddr_data VALUES (4, '08002b-010203');
 INSERT INTO macaddr_data VALUES (5, '0800.2b01.0203');
-INSERT INTO macaddr_data VALUES (6, '08002b010203');
-INSERT INTO macaddr_data VALUES (7, '0800:2b01:0203'); -- invalid
+INSERT INTO macaddr_data VALUES (6, '0800-2b01-0203');
+INSERT INTO macaddr_data VALUES (7, '08002b010203');
+INSERT INTO macaddr_data VALUES (8, '0800:2b01:0203'); -- invalid
 ERROR:  invalid input syntax for type macaddr: "0800:2b01:0203"
-LINE 1: INSERT INTO macaddr_data VALUES (7, '0800:2b01:0203');
+LINE 1: INSERT INTO macaddr_data VALUES (8, '0800:2b01:0203');
                                             ^
-INSERT INTO macaddr_data VALUES (8, 'not even close'); -- invalid
+INSERT INTO macaddr_data VALUES (9, 'not even close'); -- invalid
 ERROR:  invalid input syntax for type macaddr: "not even close"
-LINE 1: INSERT INTO macaddr_data VALUES (8, 'not even close');
+LINE 1: INSERT INTO macaddr_data VALUES (9, 'not even close');
                                             ^
 INSERT INTO macaddr_data VALUES (10, '08:00:2b:01:02:04');
 INSERT INTO macaddr_data VALUES (11, '08:00:2b:01:02:02');
@@ -30,12 +31,13 @@ SELECT * FROM macaddr_data;
   4 | 08:00:2b:01:02:03
   5 | 08:00:2b:01:02:03
   6 | 08:00:2b:01:02:03
+  7 | 08:00:2b:01:02:03
  10 | 08:00:2b:01:02:04
  11 | 08:00:2b:01:02:02
  12 | 08:00:2a:01:02:03
  13 | 08:00:2c:01:02:03
  14 | 08:00:2a:01:02:04
-(11 rows)
+(12 rows)
 
 CREATE INDEX macaddr_data_btree ON macaddr_data USING btree (b);
 CREATE INDEX macaddr_data_hash ON macaddr_data USING hash (b);
@@ -52,9 +54,10 @@ SELECT a, b, trunc(b) FROM macaddr_data ORDER BY 2, 1;
   4 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
   5 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
   6 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
+  7 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
  10 | 08:00:2b:01:02:04 | 08:00:2b:00:00:00
  13 | 08:00:2c:01:02:03 | 08:00:2c:00:00:00
-(11 rows)
+(12 rows)
 
 SELECT b <  '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true
  ?column? 
@@ -113,12 +116,13 @@ SELECT ~b                       FROM macaddr_data;
  f7:ff:d4:fe:fd:fc
  f7:ff:d4:fe:fd:fc
  f7:ff:d4:fe:fd:fc
+ f7:ff:d4:fe:fd:fc
  f7:ff:d4:fe:fd:fb
  f7:ff:d4:fe:fd:fd
  f7:ff:d5:fe:fd:fc
  f7:ff:d3:fe:fd:fc
  f7:ff:d5:fe:fd:fb
-(11 rows)
+(12 rows)
 
 SELECT  b & '00:00:00:ff:ff:ff' FROM macaddr_data;
      ?column?      
@@ -129,12 +133,13 @@ SELECT  b & '00:00:00:ff:ff:ff' FROM macaddr_data;
  00:00:00:01:02:03
  00:00:00:01:02:03
  00:00:00:01:02:03
+ 00:00:00:01:02:03
  00:00:00:01:02:04
  00:00:00:01:02:02
  00:00:00:01:02:03
  00:00:00:01:02:03
  00:00:00:01:02:04
-(11 rows)
+(12 rows)
 
 SELECT  b | '01:02:03:04:05:06' FROM macaddr_data;
      ?column?      
@@ -145,11 +150,12 @@ SELECT  b | '01:02:03:04:05:06' FROM macaddr_data;
  09:02:2b:05:07:07
  09:02:2b:05:07:07
  09:02:2b:05:07:07
+ 09:02:2b:05:07:07
  09:02:2b:05:07:06
  09:02:2b:05:07:06
  09:02:2b:05:07:07
  09:02:2f:05:07:07
  09:02:2b:05:07:06
-(11 rows)
+(12 rows)
 
 DROP TABLE macaddr_data;
diff --git a/src/test/regress/sql/macaddr.sql b/src/test/regress/sql/macaddr.sql
index 1ccf501..7bad8f5 100644
--- a/src/test/regress/sql/macaddr.sql
+++ b/src/test/regress/sql/macaddr.sql
@@ -9,9 +9,10 @@ INSERT INTO macaddr_data VALUES (2, '08-00-2b-01-02-03');
 INSERT INTO macaddr_data VALUES (3, '08002b:010203');
 INSERT INTO macaddr_data VALUES (4, '08002b-010203');
 INSERT INTO macaddr_data VALUES (5, '0800.2b01.0203');
-INSERT INTO macaddr_data VALUES (6, '08002b010203');
-INSERT INTO macaddr_data VALUES (7, '0800:2b01:0203'); -- invalid
-INSERT INTO macaddr_data VALUES (8, 'not even close'); -- invalid
+INSERT INTO macaddr_data VALUES (6, '0800-2b01-0203');
+INSERT INTO macaddr_data VALUES (7, '08002b010203');
+INSERT INTO macaddr_data VALUES (8, '0800:2b01:0203'); -- invalid
+INSERT INTO macaddr_data VALUES (9, 'not even close'); -- invalid
 
 INSERT INTO macaddr_data VALUES (10, '08:00:2b:01:02:04');
 INSERT INTO macaddr_data VALUES (11, '08:00:2b:01:02:02');

Michael Paquier

michael.paquier@gmail.com

over 11 years ago

In reply to: Herwin Weststrate (#1)

Re: Allow format 0000-0000-0000 in postgresql MAC parser

On Mon, Sep 29, 2014 at 6:30 PM, Herwin Weststrate
<herwin@quarantainenet.nl> wrote:

Some devices send the MAC address in RADIUS requests in the format
0000-0000-0000. I've seen this with a 3com switch, but there may be
others. Currently, postgresql doesn't understand this format.

This patch adds an extra line to the macaddr parsing in postgres to
support this format as well. A unit test has been added.

You should register this patch in the next commit fest where it will
be fully reviewed and hopefully committed for 9.5:
https://commitfest.postgresql.org/action/commitfest_view?id=24

Looking at your patch, you should update the documentation as well,
the list of authorized outputs being clearly listed:
http://www.postgresql.org/docs/devel/static/datatype-net-types.html#DATATYPE-MACADDR
This consists in adding simply one line to doc/src/sgml/datatype.sgml.
Regards,
--
Michael

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

Herwin Weststrate

herwin@quarantainenet.nl

over 11 years ago

In reply to: Michael Paquier (#2)

1 attachment(s)

Re: Allow format 0000-0000-0000 in postgresql MAC parser

On 01-10-14 01:19, Michael Paquier wrote:

Looking at your patch, you should update the documentation as well,
the list of authorized outputs being clearly listed:
http://www.postgresql.org/docs/devel/static/datatype-net-types.html#DATATYPE-MACADDR
This consists in adding simply one line to doc/src/sgml/datatype.sgml.
Regards,

It has been registered now
(https://commitfest.postgresql.org/action/patch_view?id=1585). I've got
an updated version of the patch with the documentation fix.

--
Herwin Weststrate
Quarantainenet BV

Attachments:

postgres-mac-parsing.difftext/x-patch; name=postgres-mac-parsing.diffDownload

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 3e83dbb..0d277fa 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -3628,6 +3628,7 @@ SELECT person.name, holidays.num_weeks FROM person, holidays
       <member><literal>'08002b:010203'</></member>
       <member><literal>'08002b-010203'</></member>
       <member><literal>'0800.2b01.0203'</></member>
+      <member><literal>'0800-2b01-0203'</></member>
       <member><literal>'08002b010203'</></member>
      </simplelist>
 
@@ -3649,7 +3650,7 @@ SELECT person.name, holidays.num_weeks FROM person, holidays
     </para>
 
     <para>
-     The remaining four input formats are not part of any standard.
+     The remaining five input formats are not part of any standard.
     </para>
    </sect2>
 
diff --git a/src/backend/utils/adt/mac.c b/src/backend/utils/adt/mac.c
index aa9993f..509315a 100644
--- a/src/backend/utils/adt/mac.c
+++ b/src/backend/utils/adt/mac.c
@@ -57,6 +57,9 @@ macaddr_in(PG_FUNCTION_ARGS)
 		count = sscanf(str, "%2x%2x.%2x%2x.%2x%2x%1s",
 					   &a, &b, &c, &d, &e, &f, junk);
 	if (count != 6)
+		count = sscanf(str, "%2x%2x-%2x%2x-%2x%2x%1s",
+					   &a, &b, &c, &d, &e, &f, junk);
+	if (count != 6)
 		count = sscanf(str, "%2x%2x%2x%2x%2x%2x%1s",
 					   &a, &b, &c, &d, &e, &f, junk);
 	if (count != 6)
diff --git a/src/test/regress/expected/macaddr.out b/src/test/regress/expected/macaddr.out
index 91edc5a..90e9b34 100644
--- a/src/test/regress/expected/macaddr.out
+++ b/src/test/regress/expected/macaddr.out
@@ -7,14 +7,15 @@ INSERT INTO macaddr_data VALUES (2, '08-00-2b-01-02-03');
 INSERT INTO macaddr_data VALUES (3, '08002b:010203');
 INSERT INTO macaddr_data VALUES (4, '08002b-010203');
 INSERT INTO macaddr_data VALUES (5, '0800.2b01.0203');
-INSERT INTO macaddr_data VALUES (6, '08002b010203');
-INSERT INTO macaddr_data VALUES (7, '0800:2b01:0203'); -- invalid
+INSERT INTO macaddr_data VALUES (6, '0800-2b01-0203');
+INSERT INTO macaddr_data VALUES (7, '08002b010203');
+INSERT INTO macaddr_data VALUES (8, '0800:2b01:0203'); -- invalid
 ERROR:  invalid input syntax for type macaddr: "0800:2b01:0203"
-LINE 1: INSERT INTO macaddr_data VALUES (7, '0800:2b01:0203');
+LINE 1: INSERT INTO macaddr_data VALUES (8, '0800:2b01:0203');
                                             ^
-INSERT INTO macaddr_data VALUES (8, 'not even close'); -- invalid
+INSERT INTO macaddr_data VALUES (9, 'not even close'); -- invalid
 ERROR:  invalid input syntax for type macaddr: "not even close"
-LINE 1: INSERT INTO macaddr_data VALUES (8, 'not even close');
+LINE 1: INSERT INTO macaddr_data VALUES (9, 'not even close');
                                             ^
 INSERT INTO macaddr_data VALUES (10, '08:00:2b:01:02:04');
 INSERT INTO macaddr_data VALUES (11, '08:00:2b:01:02:02');
@@ -30,12 +31,13 @@ SELECT * FROM macaddr_data;
   4 | 08:00:2b:01:02:03
   5 | 08:00:2b:01:02:03
   6 | 08:00:2b:01:02:03
+  7 | 08:00:2b:01:02:03
  10 | 08:00:2b:01:02:04
  11 | 08:00:2b:01:02:02
  12 | 08:00:2a:01:02:03
  13 | 08:00:2c:01:02:03
  14 | 08:00:2a:01:02:04
-(11 rows)
+(12 rows)
 
 CREATE INDEX macaddr_data_btree ON macaddr_data USING btree (b);
 CREATE INDEX macaddr_data_hash ON macaddr_data USING hash (b);
@@ -52,9 +54,10 @@ SELECT a, b, trunc(b) FROM macaddr_data ORDER BY 2, 1;
   4 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
   5 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
   6 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
+  7 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
  10 | 08:00:2b:01:02:04 | 08:00:2b:00:00:00
  13 | 08:00:2c:01:02:03 | 08:00:2c:00:00:00
-(11 rows)
+(12 rows)
 
 SELECT b <  '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true
  ?column? 
@@ -113,12 +116,13 @@ SELECT ~b                       FROM macaddr_data;
  f7:ff:d4:fe:fd:fc
  f7:ff:d4:fe:fd:fc
  f7:ff:d4:fe:fd:fc
+ f7:ff:d4:fe:fd:fc
  f7:ff:d4:fe:fd:fb
  f7:ff:d4:fe:fd:fd
  f7:ff:d5:fe:fd:fc
  f7:ff:d3:fe:fd:fc
  f7:ff:d5:fe:fd:fb
-(11 rows)
+(12 rows)
 
 SELECT  b & '00:00:00:ff:ff:ff' FROM macaddr_data;
      ?column?      
@@ -129,12 +133,13 @@ SELECT  b & '00:00:00:ff:ff:ff' FROM macaddr_data;
  00:00:00:01:02:03
  00:00:00:01:02:03
  00:00:00:01:02:03
+ 00:00:00:01:02:03
  00:00:00:01:02:04
  00:00:00:01:02:02
  00:00:00:01:02:03
  00:00:00:01:02:03
  00:00:00:01:02:04
-(11 rows)
+(12 rows)
 
 SELECT  b | '01:02:03:04:05:06' FROM macaddr_data;
      ?column?      
@@ -145,11 +150,12 @@ SELECT  b | '01:02:03:04:05:06' FROM macaddr_data;
  09:02:2b:05:07:07
  09:02:2b:05:07:07
  09:02:2b:05:07:07
+ 09:02:2b:05:07:07
  09:02:2b:05:07:06
  09:02:2b:05:07:06
  09:02:2b:05:07:07
  09:02:2f:05:07:07
  09:02:2b:05:07:06
-(11 rows)
+(12 rows)
 
 DROP TABLE macaddr_data;
diff --git a/src/test/regress/sql/macaddr.sql b/src/test/regress/sql/macaddr.sql
index 1ccf501..7bad8f5 100644
--- a/src/test/regress/sql/macaddr.sql
+++ b/src/test/regress/sql/macaddr.sql
@@ -9,9 +9,10 @@ INSERT INTO macaddr_data VALUES (2, '08-00-2b-01-02-03');
 INSERT INTO macaddr_data VALUES (3, '08002b:010203');
 INSERT INTO macaddr_data VALUES (4, '08002b-010203');
 INSERT INTO macaddr_data VALUES (5, '0800.2b01.0203');
-INSERT INTO macaddr_data VALUES (6, '08002b010203');
-INSERT INTO macaddr_data VALUES (7, '0800:2b01:0203'); -- invalid
-INSERT INTO macaddr_data VALUES (8, 'not even close'); -- invalid
+INSERT INTO macaddr_data VALUES (6, '0800-2b01-0203');
+INSERT INTO macaddr_data VALUES (7, '08002b010203');
+INSERT INTO macaddr_data VALUES (8, '0800:2b01:0203'); -- invalid
+INSERT INTO macaddr_data VALUES (9, 'not even close'); -- invalid
 
 INSERT INTO macaddr_data VALUES (10, '08:00:2b:01:02:04');
 INSERT INTO macaddr_data VALUES (11, '08:00:2b:01:02:02');

Ali Akbar

the.apaan@gmail.com

about 11 years ago

In reply to: Herwin Weststrate (#3)

Re: Allow format 0000-0000-0000 in postgresql MAC parser

It has been registered now
(https://commitfest.postgresql.org/action/patch_view?id=1585). I've got
an updated version of the patch with the documentation fix.

Looks like the patch is all good. I'm marking as ready for commiter.

On a side note, i'm noticing from http://en.wikipedia.org/wiki/MAC_address,
that there is three numbering namespace for MAC: MAC-48, EUI-48 and EUI-64.
The last one is 64 bits long (8 bytes). Currently PostgreSQL's macaddr is
only 6 bytes long. Should we change it to 8 bytes (not in this patch, of
course)?

Regards,
--
Ali Akbar

Peter Eisentraut

peter_e@gmx.net

about 11 years ago

In reply to: Herwin Weststrate (#3)

Re: Allow format 0000-0000-0000 in postgresql MAC parser

On 10/1/14 8:34 AM, Herwin Weststrate wrote:

It has been registered now
(https://commitfest.postgresql.org/action/patch_view?id=1585). I've got
an updated version of the patch with the documentation fix.

committed

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

Peter Eisentraut

peter_e@gmx.net

about 11 years ago

In reply to: Ali Akbar (#4)

Re: Allow format 0000-0000-0000 in postgresql MAC parser

On 10/17/14 6:37 PM, Ali Akbar wrote:

On a side note, i'm noticing from
http://en.wikipedia.org/wiki/MAC_address, that there is three numbering
namespace for MAC: MAC-48, EUI-48 and EUI-64. The last one is 64 bits
long (8 bytes). Currently PostgreSQL's macaddr is only 6 bytes long.
Should we change it to 8 bytes (not in this patch, of course)?

It looks like nothing current is actually using EUI-64, so probably not,
unless someone comes with an actual use case.

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