# Sending the Output of the Honeypot to an PostgreSQL Database

- [Sending the Output of the Honeypot to an PostgreSQL Database](#sending-the-output-of-the-honeypot-to-an-postgresql-database)
  - [Prerequisites](#prerequisites)
  - [Installation](#installation)
  - [PostgreSQL Database Creation](#postgresql-database-creation)
  - [Honeypot Configuration](#honeypot-configuration)
  - [Restart the honeypot](#restart-the-honeypot)

## Prerequisites

- Working honeypot installation
- PostgreSQL

## Installation

When writing to a PostgreSQL database, the honeypot uses the free databases
provided by MaxMind for the purposes of geoloacting the IP addresses. Start by
downloading the database update program for your particular kind of Windows from
[GitHub](https://github.com/maxmind/geoipupdate/releases) and put it in a
directory listed in the `PATH` variable of the environment.

Create an account at the [MaxMind web
site](https://support.maxmind.com/knowledge-base/articles/create-a-maxmind-account),
log in, go to "My Account" and then to "Manage license keys". Write down the
account ID, generate a license key, and copy it.

Go to the directory `data`, where the gelolocation databases will reside:

```powershell
PS C:\> cd \adbhoneypot-workdir\data
```

Create in this directory a file named `geoip.cfg` with the following contents:

```geoip.cfg
AccountID <ACCOUNT>
LicenseKey <KEY>
EditionIDs GeoLite2-City GeoLite2-ASN
DatabaseDirectory C:\adbhoneypot-workdir\data
LockFile C:\adbhoneypot-workdir\data\.geoipupdate.lock
```

Change the paths in the options `DatabaseDirectory` and `LockFile` if you
have opted to use paths different from the ones suggested by the
honeypot installation documentation. Make sure you replace `<ACCOUNT>`
and `<KEY>` with the account and license key obtained from MaxMind.

Download the latest version of the Maxmind geolocation databases:

```powershell
PS C:\adbhoneypot-workdir\data> geoipupdate -f geoip.cfg
```

To have the database updated automatically (it is updated on MaxMind's site
every second Tuesday of each month, so download it every second Wednesday),
run the script `geoipupdtask.ps1` in the working directory:

```powershell
PS C:\adbhoneypot-workdir\data> ..\geoipupdtask.ps1 
```

It expects that the program `geoipupdate.exe` resides in one of the directories
listed in the `PATH` variable of the environment, that the configuration file
for it is named `geoip.cfg` and resides in the current directory, and that the
updating task is to be run at 00:00. Also, it creates an updating task named
`GeoIPUpdate`, which resides in the task folder `\` and has the description
`GeoIP database updater`. You can change any of these parameters via
command-line options to the script:

```powershell
PS C:\adbhoneypot-workdir\data> ..\geoipupdtask.ps1 -TaskName "My GeoIP Database Updater" -TaskPath "\MyTasks" -TaskDescription "Updates the GeoIP database" -RunTime "03:00:00" -geoipupdate "C:\Program File\geoipupdate\geoipupdate.exe" -geoipconfig "C:\adbhoneypot-workdir\data\geoip.cfg"
```

If you already have the MaxMind geolocation databases installed and updated on
your machine in some other place, use their respective paths in the
`[output_postgres]` section of the file `honeypot.cfg`, as mentioned below.

Finally, return to the main working directory:

```powershell
PS C:\adbhoneypot-workdir\data> cd ..
```

## PostgreSQL Database Creation

In order to access a PostgreSQL database from the command line on Windows, we
need to install the command-line tools for this platform. Download the full
package (datbase server, phpMyAdmin, etc.)
[from here](https://sbp.enterprisedb.com/getfile.jsp?fileid=1259914), start the
installer wizard and click the `Next` button. Specify the installation directory
(e.g., `C:\Program Files\PostgreSQL`) and click the `Next` button. On the
"Select Components" screen, uncheck everything except "Command Line Toos" (you
can't uncheck them anyway) and click the `Next` button. Finally, on the next
screen, click the `Next` button again to start the installation process.

Once the installation is finished, open the Windows Settings, search for "env"
and select "Edit environment variables for your account". Select the `Path`
variable in the upper panel of the dialog that opens (or in the lower panel, if
you want to make the command-line PostgreSQL client visible to all users), and
click the `Edit` button. Then press the `New` button and add
`%ProgramFiles%\PostgreSQL\bin` to the `Path` environmane variable. Finally,
click `OK` to close all dialogs and exit the Windows Settings. Alternatively,
you can do it via PowerShell like this:

```powershell
[System.Environment]::SetEnvironmentVariable("Path", $env:Path + ";%ProgramFiles%\PostgreSQL\bin", [System.EnvironmentVariableTarget]::User)
```

If you want the tools to be available to all users on the machine, instead of
only to the current one, use `Machine` instead of `User` in the above command.
However, you'll have to do this from a PowerShell prompt with `Administrator`
privileges.

Log in as the user `postgres` (the PostgreSQL administrator) and create the
honeypot-related database users, the database, and grant proper privileges to it
to the users:

```psql
PS C:\adbhoneypot-workdir> psql
postgres=# create user adbhoneypot with password 'PASSWORD HERE';
postgres=# create database adbhoneypot;
postgres=# grant all privileges on database adbhoneypot to adbhoneypot;
```

(Make sure you specify a proper password that you want to use for the user
`adbhoneypot` instead of 'PASSWORD HERE'.)

If you're going to use a third-party tool for accessing the data from the
database (e.g., [Grafana](https://www.grafana.com) for visualizing the data),
it is advisable also to create a separate user that has read-only privileges
to the database and have the third-party tool access the database as that
user, so that in case the third-party tool contains some kind of vulnerability
and is breached (and the attacker obtains the database user password from it),
the attacker cannot modify the database:

```psql
postgres=# create user adbhoneypotReadOnly with password 'OTHER PASSWORD HERE';
postgres=# grant select on database adbhoneypot to adbhoneypot;
```

(Make sure you specify a proper password that you want to use for the user
`adbhoneypotReadOnly` instead of 'OTHER PASSWORD HERE'.)

Finally, exit `psql`:

```psql
postgres=# \q
```

The database is already created but it is completely empty. Now we have to
specify its schema (tables and indexes):

```powershell
PS C:\adbhoneypot-workdir> psql -f C:\adbhoneypot-workdir\docs\postgres\postgres.sql -W adbhoneypot adbhoneypot
```

You will be prompted for the password of the database user `adbhoneypot`.

If your database does not reside on your local machine but is on some remote
database server, use the options `-h host` and `-p port` of `psql` to specify
how to connect to it.

## Honeypot Configuration

Add the following entries to the file `C:\adbhoneypot-workdir\etc\honeypot.cfg`

```honeypot.cfg
[output_postgres]
enabled = true
debug = false
host = localhost
port = 5432
username = adbhoneypot
password = secret
database = adbhoneypot
# Whether to store geolocation data in the database
geoip = true
# Location of the databases used for geolocation
geoip_citydb = data/GeoLite2-City.mmdb
geoip_asndb = data/GeoLite2-ASN.mmdb
```

Make sure that you specify the correct information needed to connect to the
database (the options `host`, `port`, `username`, and `password`) and that the
options `geoip_citydb` and `geoip_asndb` point to the correct paths of the two
MaxMind geolocation databases. Also, if you prefer to keep the PostgreSQL
database under a different name, make sure that you specify its correct name
with the `database` option.

## Restart the honeypot

```powershell
PS C:\adbhoneypot-workdir> C:\adbhoneypot-env\scripts\activate.ps1
(adbhoneypot-env) PS C:\adbhoneypot-workdir> adbhoneypot restart
```
