Re: Storing a chain - Mailing list pgsql-general

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