Submit-Postgres

Dumps the Captured files to a postgresql database, so you can track how many hits a file gets.

create the poweruser ‘mwcollect’

createuser --createdb --login --createrole --pwprompt mwcollect

adjust his privs in pg_hba.conf

local   all         all                               trust # this is picky if the box is not dedicated
host    all         all         127.0.0.1/32          md5
host    mwcollect   all         0.0.0.0/0             md5

create the database, and load the pgsql interpreter for the database

createdb -U mwcollect mwcollect
createlang plpgsql mwcollect

connect the database

psql -U mwcollect mwcollect

create the roles

CREATE ROLE role_sensor WITH NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT NOLOGIN CONNECTION LIMIT 10;
CREATE ROLE role_analyzer WITH NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT NOLOGIN CONNECTION LIMIT 10;
CREATE ROLE role_www WITH NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT NOLOGIN CONNECTION LIMIT 10;

create the tables from the schema

psql -U mwcollect < schema.sql

create a user

CREATE USER foobar;
ALTER USER foobar PASSWORD 'fxfglsdfa';
INSERT INTO accounts (username) VALUES ('foobar');

grant the user foobar access as a sensor

GRANT role_sensor TO foobar;

my little link collection:

• postgres installation
  • http://gentoo-wiki.com/HOWTO_Configure_Postgresql

• roles
  • http://www.postgresql.org/docs/8.1/static/user-manag.html
  • http://www.postgresql.org/docs/8.1/static/sql-createrole.html

• trigger
  • http://www.postgresql.org/docs/8.1/interactive/triggers.html
  • http://www.postgresql.org/docs/8.1/interactive/sql-createtrigger.html
  • http://www.postgresql.org/docs/8.1/interactive/trigger-example.html

• rules
  • http://www.postgresql.org/docs/8.1/interactive/rules.html
  • http://www.postgresql.org/docs/8.1/static/rules-update.html
  • http://www.postgresql.org/docs/8.1/interactive/rules-views.html

• grant
  • http://www.postgresql.org/docs/8.1/static/sql-grant.html

• postgres exceptions in 8.x
  • http://www.postgresql.org/docs/8.1/static/plpgsql-porting.html
  • http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html EXCEPTIONS
  • http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html#ERRCODES-TABLE

• Nepenthes PostgreSQL Flavor HowTO
  • http://sqrt.ath.cx/~rui/doc/NepenthesPostgreSQLFlavor.HowTO.txt