Using PostgreSQL with Snort and ACID

Problem

You want to use Snort to log into a PostgreSQL database.

Solution

Setting up Snort to log to a PostgreSQL database is similar to how you'd set up MySQL. However, there are several different steps that have to be accomplished to get Snort to log there. For simplicity, we will set up Snort to use PostgreSQL as the database for an ACID web frontend, and we'll compile from PostgreSQL source to tweak our database.

Discussion

The following steps enable a PostgreSQL database and Snort support.

Download the database source from http://www.postgresql.org. Then, place the source file postgresql-7.4.5.tar.gz in a temp directory such as /tmp to compile:

root# pwd 

/tmp 

root# ls 

postgresql-7.4.5.tar.gz 

root# tar xvfz postgresql-7.4.5.tar.gz 

root# cd postgresql-7.4.5

root# ./configure  

DIR:--prefix=/usr/local/postgres> ; make; make install

Create a user account to run as:

# Linux systems 

useradd postgres 

# BSD systems 

echo "postgres;;;;;;;;;no" | adduser -w - -f "no"

Next, install the database using the optional --with-openssl configure option, if you are considering encrypting your Snort-to-database connections:

root# ./configure [--with-openssl] 

root# make; make install 

# Running make install places the database into the default directory 

# of "/usr/local/pgsql" with subdirectories.

Make a data directory in which to hold the databases:

root# mkdir /usr/local/pgsql/data

root# chown -R postgres /usr/local/pgsql/data

Start the new PostgreSQL database:

root# su - postgres 

postgres$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data 

postgres$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data &

To have the PostgreSQL database start automatically when you boot the system, try the following example script. Thanks go to the postgresql.org archives for this example:

##!/bin/sh

 

## Start postgres at boot time script

# from postgresql.org site

#

########

 

# INSTALLATION Prefix

prefix=/usr/local/pgsql

 

# Data directory

PGDATA="/usr/local/pgsql/data"

 

# Who to run as

PGUSER=postgres

 

# Where to keep a log file

PGLOG="$PGDATA/serverlog"

 

# Path for the script to use

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/bin:/usr/sbin

 

# What to use to start the postmaster

DAEMON="$prefix/bin/pg_ctl"

 

test -x "$DAEMON" || exit 0

 

case $1 in

 start)

 su $PGUSER -c "$DAEMON start -D $PGDATA -s $PGLOG"

 echo -n ' postgresql'

 ;;

 stop)

 su $PGUSER -c "$DAEMON stop -D $PGDATA -s -m fast"

 ;;

 restart)

 su $PGUSER -c "$DAEMON restart -D $PGDATA -s -m fast"

 ;;

 status)

 su $PGUSER -c "$DAEMON status -D $PGDATA"

 ;;

 *)

 # PRINT HELP

 echo "Usage: `basename $0` (start|stop|restart|status)" 1>&2

 exit 1

 ;;

esac

Next, since the new PostgreSQL instance has no databases or users, you must create them.

PostgreSQL has a much different look and feel to it if you are coming from a MySQL background. If you are uncomfortable running some of these commands from a console, try using a GUI application such as phpPgAdmin (available at http://phppgadmin.sourceforge.net) for ease of use and management. However, for the brave, read on to find out how to create a PostgreSQL database, user account, and grants for a user.

Create the database for Snort to log to:

root# /usr/local/pgsql/bin/createdb -U postgres acidpg 

# IF YOU ARE RUNNNG AS user 'postgres' you don't need the 

# "-U postgres"

Next, create the two user accounts: sensor(s) (snortpguser) and web interface(s) (webpguser):

root#/usr/local/pgsql/bin/createuser -U postgres -A -D 

-P  snortpguser 

root#/usr/local/pgsql/bin/createuser -U postgres -A -D -P 

 webpguser

Next, import the correct database schema (structure) for Snort to use:

root# cat /tmp/snort-2.2.x/contrib/create_postgresql |

/usr/local/pgsql/bin/psql -U postgres -d acidpg 

root# gunzip /tmp/snort-2.2.x/contrib/snortdb-extra.gz 

root# cat /tmp/snort-2.2.x/contrib/snortdb-extra |

/usr/local/pgsql/bin/psql -U postgres -d acidpg

Grant the two user accounts permissions on the database:

root#/usr/local/pgsql/bin/psql -U postgres -D acidpg 

acidpg=# GRANT ALL PRIVILEGES ON DATABASE acidpg TO webpguser; 

acidpg=# GRANT ALL PRIVILEGES ON DATABASE acidpg TO snortpguser;

You can have more than two accounts if you want every sensor to have a unique account to log in with.

If you want to restrict what the sensor accounts have access to, try pasting the following example into the PostgreSQL prompt in place of the ALL PRIVILEGES line:

 GRANT UPDATE,SELECT,INSERT ON sensor to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON sensor_sid_seq to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON data to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON detail to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON encoding to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON event to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON flags to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON icmphdr to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON iphdr to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON opt to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON protocols to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON reference to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON reference_ref_id_seq to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON reference_system to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON reference_system_ref_system_id_seq to 

 snortpguser;

 GRANT UPDATE,SELECT,INSERT ON schema to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON services to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON sig_class to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON sig_class_sig_class_id_seq to 

 snortpguser;

 GRANT UPDATE,SELECT,INSERT ON sig_reference to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON signature to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON signature_sig_id_seq to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON tcphdr to snortpguser;

 GRANT UPDATE,SELECT,INSERT ON udphdr to snortpguser;

If you are already trying to connect to the database with one or both of the accounts and get errors such as:

ERROR: Relation `table_name' does not exist

then for some reason, one of the Snort database tables didn't apply all the correct privileges to that table. You can try to correct this by either adjusting the single table manually with one of the previous commands or pasting in the 24-line GRANT statement in the previous code.

ACID needs to add several additional tables to the PostgreSQL database to function properly. Unfortunately, the PostgreSQL language has changed since the original ACID code was written, so there is a small name change that must be implemented.

As of PostgreSQL 7.4x, the function DATETIME is now called TIMESTAMP. This means you need to change the files found in the ACID directory that have to do with PostgreSQL (.sql files) and the ACID .html and .php files. This actually only involves editing the following three files, replacing each occurrence of DATETIME with TIMESTAMP:

• Create_acid_tbls_psql.sql
• Create_acid_tbls_pgsql_extra.sql
• Acid_db_setup.php

Also, before you begin trying to set up PostgreSQL ACID, remember to check that your Apache server is compiled with PostgreSQL support. Apache should be configured using a command line similar to the following:

# ./configure --with-pgsql --with-your-other-options

Next, edit the file acid_conf.php with your database account:

$alert_dbname = "acidpg";

$alert_host = "localhost";

$alert_port = "5432";

$alert_user = "webpguser";

$alert_password = "webpass";

Next, set up the ACID schema through the ACID main page acid_main.php in a browser. This will redirect you to the acid_db_setup.php page to create the extra tables that ACID needs. If you get errors, check privileges in the database and try again.

If you got no errors with the database or ACID, then set up Snort to log to the database by editing your snort.conf file such as:

output database: log, postgresql, user=snortpguser, 

password=snortpass, dbname=acidpg host=localhost

If you are going to use more than one sensor, it's a good idea to use the sensor_name parameter, as in this example:

output database: log, postgresql, user=snortpguser, 

password=snortpass, dbname=acidpg host=localhost 

sensor_name=oreilly_test

Lastly, download and compile Snort from source again. First, extract the Snort source code:

root# ls 

snort-2.2.x.tar.gz

root# tar xvfz snort-2.2.x.tar.gz 

root# cd snort-2.2.x

Compile Snort to use your PostgreSQL database:

root# ./configure --with-postgresql=/usr/local/pgsql (other options) 

root# make 

# WARNING! 

# YOU MAY HAVE TO PLACE THE FILE "libpq.so.3" in your /lib directory

# in order for snort to work.

The last part of the build is testing. Run Snort with the -T option to make sure everything is working (see Recipe 3.12). If no errors occur, restart Snort. You're now running Snort logging to a PostgreSQL database with an ACID frontend.

See Also

PostgreSQL home (http://www.postgresql.org)

Quick, local-only Snort/PostgreSQL setup (http://kellys.net/snort)

Recipe 2 14 Logging in PCAP Format (TCPDump)