Re: Storing a chain

Keith C. Perry wrote:
> Quoting Johan Fredrik Øhman <johanfo(at)ohman(dot)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