Logging to, and input from, PostgreSQL Databases

Introduction and Warning

This plugin allows logging to as well as reading from PostgreSQL databases. While the basic functionality seems to work, it has not seen much real-life testing and no functionality guarantees are made. This plugin should be considered experimental.

Installation

After installing PostgreSQL, you can install the Bro PostgreSQL module either using zkg, or manually via the command-line.

To install the plugin using zkg, use

# zkg install 0xxon/bro-postgresql

To install manually from the cloned repository, use::

# ./configure && make && make install

If PostgreSQL is installed in a non-standard location, add `--with-postgresql=<postgresql-base-directory to the configure` command. Use zeek -N to verify correct installation:

# zeek -N Johanna::PostgreSQL
Johanna::PostgreSQL - PostgreSQL log writer and input reader (dynamic, version 0.2.0)

Logging Data into PostgreSQL databases

The easiest way to add PostgreSQL logging is by adding a logging filter to an already existing logging stream. This first example also sends the conn.log to PostgreSQL:

event zeek_init()
  {
  local filter: Log::Filter = [$name="postgres", $path="conn", $writer=Log::WRITER_POSTGRESQL, $config=table(["dbname"]="testdb")];
  Log::add_filter(Conn::LOG, filter);
  }

This will write to a database named testdb into the table named conn. Note that the table will be automatically be created by the PostgreSQL plugin, if it does not yet exist. If a table with the specified name already exists, it is used; the existing columns have to be compatible with the column names and types that the Bro plugin expects.

Data can be read from PostgreSQL using a script similar to:

redef exit_only_after_terminate = T;

type InfoType: record {
  ts: time;
  uid: string;
  duration: interval;
};

event line(description: Input::EventDescription, tpe: Input::Event, r: InfoType)
  {
  print r;
  }

event zeek_init()
  {
  Input::add_event([$source="select ts, uid, duration from conn;", $name="postgres", $fields=InfoType, $ev=line, $want_record=T,
    $reader=Input::READER_POSTGRESQL, $config=table(["dbname"]="testdb")]);
  }

event Input::end_of_data(name: string, source:string)
  {
  print "End of data";
  terminate();
  }

By default, the plugin connects to PostgreSQL as the user running Bro, without supplying any additional username or password.

Type mapping

The writer automatically maps the Bro types to the following PostgreSQL data types:

For string and func, bytea is used if the $config option "bytea_instead_of_text" is set.

Configuration options: PostgreSQL Writer

The PostgreSQL writer supports the following configuration options that can be passed in $config:

• hostname: hostname to connect to

• port: port to connect to

• dbname: name of database to connect to

• conninfo: connection string using parameter key words as defined in https://www.postgresql.org/docs/9.3/static/libpq-connect.html. Can be used to pass usernames, passwords, etc. hostname, port, and dbname are ignored if conninfo is specified.

  Example: host=127.0.0.1 user=johanna

• sql_addition: SQL string that is appended to the insert statement generated by the plugin. This can be used to specify a conflict clause like: "ON CONFLICT DO NOTHING"

• continue_on_errors: ignore insert errors and do not kill the database connection.

• bytea_instead_of_text: write strings/funcs to as bytea instead of text.

Configuration options: PostgreSQL Reader

The PostgreSQL reader supports the following configuration options that can be passed in $config:

• hostname: hostname to connect to

• port: port to connect to

• dbname: name of database to connect to

• conninfo: connection string using parameter key words as defined in https://www.postgresql.org/docs/9.3/static/libpq-connect.html. Can be used to pass usernames, passwords, etc. hostname, port, and dbname are ignored if conninfo is specified.

  Example: host=127.0.0.1 user=johanna