Thread: Storing a chain

Storing a chain

From
Johan Fredrik Øhman
Date:
Hi,
I'm seeking some opinion on the following problem

For those familiar with "iptables/netfilter", I am actually storing firewall
rules in the database. The order of these rules is critical, so there has to
be some kind of system to how they are ordered. As I see it there are at
least 2 approaches.

1) Use a Sequence number in the table. This way it is possible to use ORDER
BY sequencenumber to retrive the correct list.  This works, but you have to
do some massive updating when you what to insert a rule between i.e "12" and
"13".


CREATE TABLE FW_CHAIN (
         FW_CHAIN_ID          INT4                 not null,
         PARENT_ID            INT4                 null,
          ....  fields ..............
         constraint PK_FW_CHAIN primary key (FW_CHAIN_ID)
);

2) Use a Parent_Id, as described intended in the table above.  I feel that
this is a little prettier, even if it might be somewhat less efficient.
Unfortunately, I'm really not sure how to perform the queries.  As I see it
some kind of recursive select statement is needed. Cursors ?  Stored
Procedure?

If somebody has the time, I'd really appreciate some links to relevant
examples, documentation or advice on how to solve this.

--
Johan Fredrik Øhman



Re: Storing a chain

From
Johan Fredrik Øhman
Date:
I wouldn't think using a sequence would make much sense. You don't want
automatically generated unique values, you want to impose an order.
If you want easy insertion at the expense of larger indexes you are
probably better off using a text or numeric (using digits to the right
of the decimal point) key.

[JFO]
My use of the word sequence was ambiguous.  What I ment was a "numeric" key.


Why do you want your rules in a database? I would think that a plain
text file would be a better approach unless you either want to be able
to ask questions about your firewall rules or have some automated
system for changing the firewall rules based on contidions (e.g. time of
day).

 [JFO] Exactly, I need it very flexible.  The tables are a small part of a
large administration database, which tell a lot of machines exactly what
firewall configuration they should have.


Re: Storing a chain

From
"Tim Clarke"
Date:
Just quickly here (it's a while since I've needed to do this!), consider a linked list of records of your own creation.
Eachrecord would have (besides your core data) pointers as necessary, one to the previous record (i.e. before "me") and
oneto the next record (i.e. after "me"). The first record in the chain would therefore have a null previous record and
thelast would have no following record. Inserting a record in the tree then becomes a matter of updating just two other
records,inserting the new record between them. The pointers are probably just a sequence number, since it only has to
beunique. 

HTH

Tim Clarke

-----Original Message-----
From: Johan Fredrik Øhman [mailto:johanfo@ohman.no]
Sent: 19 November 2003 15:09
To: pgsql-general@postgresql.org
Subject: [GENERAL] Storing a chain


Hi,
I'm seeking some opinion on the following problem

For those familiar with "iptables/netfilter", I am actually storing firewall
rules in the database. The order of these rules is critical, so there has to
be some kind of system to how they are ordered. As I see it there are at
least 2 approaches.

1) Use a Sequence number in the table. This way it is possible to use ORDER
BY sequencenumber to retrive the correct list.  This works, but you have to
do some massive updating when you what to insert a rule between i.e "12" and
"13".


CREATE TABLE FW_CHAIN (
         FW_CHAIN_ID          INT4                 not null,
         PARENT_ID            INT4                 null,
          ....  fields ..............
         constraint PK_FW_CHAIN primary key (FW_CHAIN_ID)
);

2) Use a Parent_Id, as described intended in the table above.  I feel that
this is a little prettier, even if it might be somewhat less efficient.
Unfortunately, I'm really not sure how to perform the queries.  As I see it
some kind of recursive select statement is needed. Cursors ?  Stored
Procedure?

If somebody has the time, I'd really appreciate some links to relevant
examples, documentation or advice on how to solve this.

--
Johan Fredrik Øhman



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

Re: Storing a chain

From
Bruno Wolff III
Date:
On Wed, Nov 19, 2003 at 16:08:47 +0100,
  Johan Fredrik Øhman <johanfo@ohman.no> wrote:
>
> 1) Use a Sequence number in the table. This way it is possible to use ORDER
> BY sequencenumber to retrive the correct list.  This works, but you have to
> do some massive updating when you what to insert a rule between i.e "12" and
> "13".

I wouldn't think using a sequence would make much sense. You don't want
automatically generated unique values, you want to impose an order.
If you want easy insertion at the expense of larger indexes you are
probably better off using a text or numeric (using digits to the right
of the decimal point) key.

Why do you want your rules in a database? I would think that a plain
text file would be a better approach unless you either want to be able
to ask questions about your firewall rules or have some automated
system for changing the firewall rules based on contidions (e.g. time of day).

Re: Storing a chain

From
"Tim Clarke"
Date:
The sequence isn't the order! The order is the "previous record", "next record" fields in each record...

Tim Clarke

-----Original Message-----
From: Johan Fredrik Øhman [mailto:johanfo@ohman.no]
Sent: 19 November 2003 16:50
To: 'Bruno Wolff III'
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Storing a chain


I wouldn't think using a sequence would make much sense. You don't want
automatically generated unique values, you want to impose an order.
If you want easy insertion at the expense of larger indexes you are
probably better off using a text or numeric (using digits to the right
of the decimal point) key.

[JFO]
My use of the word sequence was ambiguous.  What I ment was a "numeric" key.


Why do you want your rules in a database? I would think that a plain
text file would be a better approach unless you either want to be able
to ask questions about your firewall rules or have some automated
system for changing the firewall rules based on contidions (e.g. time of
day).

 [JFO] Exactly, I need it very flexible.  The tables are a small part of a
large administration database, which tell a lot of machines exactly what
firewall configuration they should have.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Storing a chain

From
Herbert Liechti
Date:
Am Mittwoch, 19. November 2003 16.08 schrieben Sie:
> Hi,
> I'm seeking some opinion on the following problem

May be this article is interesting for you:

http://www.dbmsmag.com/9603d06.html

Best regards Herbie

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti                                  http://www.thinx.ch
The content management company.         Visit http://www.contentx.ch
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Re: Storing a chain

From
"Keith C. Perry"
Date:
Quoting Johan Fredrik Øhman <johanfo@ohman.no>:

> Hi,
> I'm seeking some opinion on the following problem
>
> For those familiar with "iptables/netfilter", I am actually storing firewall
> rules in the database. The order of these rules is critical, so there has to
> be some kind of system to how they are ordered. As I see it there are at
> least 2 approaches.
>
> 1) Use a Sequence number in the table. This way it is possible to use ORDER
> BY sequencenumber to retrive the correct list.  This works, but you have to
> do some massive updating when you what to insert a rule between i.e "12" and
> "13".
>
>
> CREATE TABLE FW_CHAIN (
>          FW_CHAIN_ID          INT4                 not null,
>          PARENT_ID            INT4                 null,
>           ....  fields ..............
>          constraint PK_FW_CHAIN primary key (FW_CHAIN_ID)
> );
>
> 2) Use a Parent_Id, as described intended in the table above.  I feel that
> this is a little prettier, even if it might be somewhat less efficient.
> Unfortunately, I'm really not sure how to perform the queries.  As I see it
> some kind of recursive select statement is needed. Cursors ?  Stored
> Procedure?
>
> If somebody has the time, I'd really appreciate some links to relevant
> examples, documentation or advice on how to solve this.
>
> --
> Johan Fredrik Øhman

Very interesting idea.  I build Linux firewalls with netfilter as well and I
never considered storing the rules in a database since as routers I keep them
pretty closed.

However, if I were to do this, I would use a non-integer field that would
contain the rule number (i.e. the place in the chain).  When you insert the rule
you could simply take the mid-point between the two rules.  So for example to
insert between rule 10 and 11 you would use 10.5.  That would work for some time
but would become unreadable if you have a lot of updates happening (i.e. your
intrustion detection system automatically writes rules).  To handle that, you
could cron a job to renumber the rules with whole numbers so again:

current rule order:
10.0
11.0
12.0

after 1st update
10.0
10.5
11.0
12.0

after second update
10.0
10.5
11.0
11.5
12.0

after 3rd update
10.0
10.25
10.5
11.0
11.5
12.0

after a renumbering
10
11
12
13
14
15


You'll want to vacuum after that renumbering as well.

--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

Re: Storing a chain

From
Joe Conway
Date:
Keith C. Perry wrote:
 > Quoting Johan Fredrik Øhman <johanfo@ohman.no>:
>>For those familiar with "iptables/netfilter", I am actually storing firewall
>>rules in the database. The order of these rules is critical, so there has to
>>be some kind of system to how they are ordered. As I see it there are at
>>least 2 approaches.
>>
>>CREATE TABLE FW_CHAIN (
>>         FW_CHAIN_ID          INT4                 not null,
>>         PARENT_ID            INT4                 null,
>>          ....  fields ..............
>>         constraint PK_FW_CHAIN primary key (FW_CHAIN_ID)
>>);
>>
>>2) Use a Parent_Id, as described intended in the table above.  I feel that
>>this is a little prettier, even if it might be somewhat less efficient.
>>Unfortunately, I'm really not sure how to perform the queries.  As I see it
>>some kind of recursive select statement is needed. Cursors ?  Stored
>>Procedure?

See contrib/tablefunc for a function called connectby(). It can do the
recursive union for you.

> However, if I were to do this, I would use a non-integer field that would
> contain the rule number (i.e. the place in the chain).  When you insert the rule
> you could simply take the mid-point between the two rules.  So for example to
> insert between rule 10 and 11 you would use 10.5.  That would work for some time
> but would become unreadable if you have a lot of updates happening (i.e. your
> intrustion detection system automatically writes rules).  To handle that, you
> could cron a job to renumber the rules with whole numbers so again:
>

If you are using Postgres 7.4, you could also use an array:

create table fw_chain (chain_id int, chain int[]);
insert into fw_chain values (1, ARRAY[1,2,3,4,5,6]);
regression=# select * from fw_chain where chain_id = 1;
  chain_id |     chain
----------+---------------
         1 | {1,2,3,4,5,6}
(1 row)

update fw_chain set chain = chain[1:2] || 42 || chain[3:6]
        where chain_id = 1;

regression=# select * from fw_chain where chain_id = 1;
  chain_id |      chain
----------+------------------
         1 | {1,2,42,3,4,5,6}
(1 row)

Then write a function to loop through the rules:

CREATE OR REPLACE FUNCTION iterate_rules(int) RETURNS SETOF text AS '
   DECLARE
     v_chain_id alias for $1;
     v_chain int[];
     v_ret text;
   BEGIN
     SELECT INTO v_chain chain FROM fw_chain WHERE chain_id = v_chain_id;
    for i in array_lower(v_chain,1) .. array_upper(v_chain,1) loop
        -- do something with v_chain[i]
             v_ret := ''Fired rule #'' || v_chain[i]::text;
             return next v_ret;
    end loop;
     return;
   END;
' LANGUAGE plpgsql;

regression=# select * from iterate_rules(1);
  iterate_rules
----------------
  Fired rule #1
  Fired rule #2
  Fired rule #42
  Fired rule #3
  Fired rule #4
  Fired rule #5
  Fired rule #6
(7 rows)

HTH,

Joe