Re: Storing a chain - Mailing list pgsql-general

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