Using the Database

The database used by the Datapository anomaly detection testbed is the open source PostgreSQL database. While most of the functionality of the testbed is done through a Ruby interface to the database, hiding many of the database details from the user, knowing how to use PostgreSQL is extremely helpful for generating your own scripts and synthetic attacks. It is strongly suggested that you read and consult the PostgreSQL documentation for help writing your own queries to the database, increasing the functionality of the testbed.

As mentioned, the majority of the testbed framework is build around Ruby and is described in our Ruby testbed usage guide. However in this section of the wiki we will focus on getting you familiar with using the actual database.

Logging in to the Database

To login to the database and run queries from a command line, the psql command is used from a Datapository shell. When using the psql command you will also specify the database name which contains the network traffic and associated metadata for your project. The database should be setup according to the main page Database Information section. To login to the database dp, the following command would be issued which will give you the psql prompt: 

$ psql dp
Welcome to psql 8.1.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

dp=>

Using the Database

Once you are logged in to the database, it can then be used to examine tables, constraints, rules, etc. Using the database directly most helpful when designing and testing new queries for the framework and synthetic attacks. When the queries or attacks are stable, they can then be pushed in to ruby scripts for further usage with the rest of the framework.

You can examine tables (which should match our documentation): 

dp=> \d metrics
    Table "public.metrics"
 Column |  Type   | Modifiers 
--------+---------+-----------
 metric | integer | not null
 name   | text    | 
Indexes:
    "metrics_pk" PRIMARY KEY, btree (metric)

Or peak in to their current data: 

dp=> select * from metrics;
 metric |    name    
--------+------------
      0 | addr_src
      1 | addr_dst
      2 | ports_src
      3 | ports_dst
      4 | degree_in
      5 | degree_out
      6 | fsd
      7 | volume
(8 rows)

Finding Help With PostgreSQL

Finding help with PostgreSQL is easy and can be done in many different forms: