Converting timestamps and IP addresses

[Erwin Van de Velde]

Hi,

I'm building a central logging system for security applications as my master
thesis, but I've run into some troubles:

Different applications make database logs using different formats:
- Timestamps as timestamps or as numeric values
- IP addresses in dotted notation (aaa.bbb.ccc.ddd) or as numeric values

I'd like to convert these to the same type, so that I can compare them. Two
questions thereby:
- Is there an easy way with built-in functions? (I didn't found them)
- Can I use triggers and C-functions to reach my goal?

For the second question: in that case, all tables in my database would have
the same dataformat for timestamps and IP's, and conversion would happen at
insertion. However, there could be a data type mismatch between what stays in
the query (a numeric value for instance) and the column type in the database
(string type: dotted notation for IP): So, when are the types checked? Before
or after a trigger on INSERT? (Of course when using a TRIGGER BEFORE INSERT
:-)) I'm only wondering when type checks are executed...

If anyone has built such functions already, I'd gladly accept, and you can win
a line in my thank word ;-)

Greetings,
Erwin Van de Velde
Student of University of Antwerp,
Belgium
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Jean-Michel POURE]

Le Mercredi 11 Février 2004 12:12, Erwin Van de Velde a écrit :

If anyone has built such functions already, I'd gladly accept, and you can
win a line in my thank word ;-)

Dear Erwin,

I built a small centralised database built for Ulogd and ran into the same
questions. You can either use implicit or explicit CASTs:

SELECT '192.168.0.3'::inet AS myexplicitcast
will convert a string into an inet

Sometimes, when you do not have contol over the logging deamon sending queries
(which is the case for example of Ulogd), you may be obliged to use implicit
CASTs (a solution described in PostgreSQL bits):

CREATE OR REPLACE FUNCTION ulog_timecast(int4)
RETURNS timestamp AS
'select "timestamp"($1::abstime);'
LANGUAGE 'sql' VOLATILE;

CREATE CAST (int4 AS timestamp)
WITH FUNCTION ulog_timecast(int4)
AS IMPLICIT;

In the end, you may need to add fields to your table and compute the logs when
they are received, using triggers and procedures (better STABLE ones). But
this can slow down logging. You may also prefer to run cron jobs (my volume
is too small for such optimisations). Also, do not forget using partial
indexes.

Using PostgreSQL on a double-athlon server, I can log up to 1000 messages
every second, but I did not try to stress the server too long (it should not
be a problem with partial indexing).

If you are interested in my code, just drop me an email and I will send you
the dump (just a few functions and triggers).

Next week, I plan to use PLbash to be able to send IPTABLES scripts to my
firewall interactively. This could make PostgreSQL one of the only database
able to counter attack on the fly during data acquisition.

Also, in order to write fast server-side applications, do not hesitate to try
pgAdmin III from http://www.pgadmin.org. This will give you direct access to
the list of CASTs. pgAdmin III has a large number of very handy features to
write server-side applications.

Cheers,
Jean-Michel
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match