Postgresql reader / writer plugin for Bro
C++ Bro CMake Shell Makefile
Switch branches/tags
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
cmake
scripts
src
tests
.gitignore
CMakeLists.txt
COPYING
Makefile
README
README.md
VERSION
bro-pkg.meta
configure
configure.plugin

README.md

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 bro-pkg, or manually via the command-line.

To install the plugin using bro-pkg, use

# bro-pkg 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 bro -N to verify correct installation:

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

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 bro_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 bro_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:

Bro type PostgreSQL type
Boolboolean
intbigint
countbigint
portbigint
addrinet
subnetinet
timedouble precision
intervaldouble precision
doubledouble precision
enumtext
stringtext/bytea
functext/bytea
set[type]type[]
vector[type]type[]

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