Обсуждение: 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
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.
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
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).
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
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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
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