#105 archive database doesn't work correctly

BASE
closed-fixed
Nerveup
Database (41)
6
2006-01-23
2005-09-06
Anonymous
No

I am storing alerts in postgresql database.
Alerts database works fine, but archive database shows
no alerts.
I cosider myself right, that I made archivedb in the
same way as alertdb. .e.g \i
~/snort-2.3.3/schemas/create_postgresql, and then using
base created acid* and base* tables.

The problem is that, after (archiving (copy)) alerts to
archivedb, using archive=1 it shows me no alerts. Of
course, i see acid* and base* are booth empty, but in
event I got some random crap.

After looking in code I wander how can archivedb work.

File: base_action.inc.php:
$sql = "SELECT hostname, interface, filter, detail,
encoding FROM sensor ".
"WHERE sid=$sid";
$tmp_result = $db->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();

if ( $tmp_row )
{
$sql = "INSERT INTO sensor
(sid,hostname,interface,filter,detail,encoding) ".
"VALUES
($sid,'".$tmp_row[0]."','".$tmp_row[1]."','".$tmp_row[2]."','".
$tmp_row[3]."','".$tmp_row[4]."')";

so, the sql on my config is:
INSERT INTO sensor
(sid,hostname,interface,filter,detail,encoding) VALUES
(1,'sensor01','bridge0','net !192.168.0.0/16','1','0')

but this insert can't work, because last_cid isn't
defined so its null, but in database schema is defined
that last_cid can't be null:
snortarchive=# \d sensor;
Table "public.sensor"
Column | Type |
Modifiers
-----------+----------+---------------------------------------------------------
sid | integer | not null default
nextval('public.sensor_sid_seq'::text)
hostname | text |
interface | text |
filter | text |
detail | smallint |
encoding | smallint |
last_cid | bigint | not null

so postgresql say's:
ERROR: null value in column "last_cid" violates
not-null constraint

and if base can't select anything from table `sensor`,
it execute alert cache update. if i understand corectly.

aw, from snort/schemes i see that any db type has
last_cid - not null, weird. how can this work then?

Discussion

  • Kevin Johnson

    Kevin Johnson - 2005-09-07
    • labels: --> Database
    • milestone: --> BASE
    • priority: 5 --> 6
    • status: open --> open-works-for-me
     
  • Kevin Johnson

    Kevin Johnson - 2005-09-07

    Logged In: YES
    user_id=836228

    I can't reproduce this error. Could you please update the
    ticket to include more information about your setup?

    Thanks
    Kevin

     
  • Nobody/Anonymous

    Logged In: NO

    OpenBSD3.7 chrooted lighttpd with php compiled in postgres
    shared.

    I made archive db the same as alerts db.

     
  • Kevin Johnson

    Kevin Johnson - 2005-10-03
    • assigned_to: nobody --> secureideas
    • status: open-works-for-me --> closed-works-for-me
     
  • Nerveup

    Nerveup - 2006-01-23

    Logged In: YES
    user_id=1429350

    PostgreSQL archive database support was broken at all.
    Fixed into cvs... will be part of 1.2.4.

     
  • Nerveup

    Nerveup - 2006-01-23
    • assigned_to: secureideas --> nerveup
    • status: closed-works-for-me --> closed-fixed