Manually creating a PostgreSQL database – Capsule8

In this article, we will cover how you can manually create and configure PostgreSQL on your Linux environment:

RedHat/Centos
Debian/Ubuntu

Requirements

For Capsule8 Console we recommend these system requirements

Please note that the Console requires a minimum of PostgreSQL 9.6.

1. Step-by-Step Installation

For installation of PostgreSQL on RedHat/Centos environment, please perform the following steps below:

RedHat/Centos

Run the following to add the latest PostgreSQL Repository and install PostgreSQL:

$ sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Install PostgreSQL

$ sudo yum install -y postgresql96-server    #This will install PostgreSQL version 9.6

Once PostgreSQL is installed, initialize the database

$ sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb

Edit the configuration to allow the Capsule8 Sensor to connect using a password.

$ sudo find /etc /var -name pg_hba.conf | sudo xargs sed -i "s/ident/md5/g"

If remote connectivity is needed by the Console, ensure that PostgreSQL service is listening for remote connections by also changing the pg_hba.conf file accordingly.

listen_addresses = 'localhost'

listen_addresses = '*'

...or the appropriate IP address.

Enable Service

$ sudo systemctl enable postgresql-9.6

Start Service

$ sudo systemctl start postgresql-9.6

Check Service

$ sudo systemctl status postgresql-9.6

Debian/Ubuntu

Run the following to add the latest PostgreSQL Repository and install PostgreSQL:

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Run the command below to Import the repository signing key:

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Run the command below to update the package list:

$ sudo apt-get update

For Installing Postgres 9.6, run the command below:

$ sudo apt-get -y install postgresql-contrib-9.6   #This will install postgres version 9.6

Edit the configuration to allow the Capsule8 Console to connect using a password.

$ sudo find /etc /var -name pg_hba.conf | sudo xargs sed -i "s/ident/md5/g"

If remote connectivity is needed by the Console, ensure that Postgres is listening for remote connections by also changing the pg_hba.conf file accordingly.

listen_addresses = 'localhost'

listen_addresses = '*'

...or the appropriate IP address.

Enable Service

$ sudo systemctl enable postgresql

Start Service

$ sudo systemctl start postgresql

Check Service

$ sudo systemctl status postgresql

2. Create a database for the Capsule8 Console

 $ sudo su postgres

$ psql

If successful, you should see the PostgreSQL prompt:

postgres=#

From the PostgreSQL prompt, create a database, username, and password, then grant all access to this new user. For example:

CREATE DATABASE console;
CREATE USER console WITH PASSWORD 'password';
GRANT ALL ON DATABASE console TO console;

If successful, you should see a confirmation that these were created:

CREATE DATABASE
CREATE ROLE
GRANT

To exit the Prompt:

$ \q

3. Connect the Capsule8 Console to the PostgreSQL Database

With the username, password, and database name from the previous step, follow instructions in our configuration guide to connect the Capsule8 Console to the database.

4. (Optional) Verify connectivity

Successfully logging in to the console with a Capsule8 username and password is sufficient to confirm the Console can communicate with the database. Users who want extra confirmations may look for this line in the Console logs:

Finished migrations

Or may run this Postgres query against the configured database (console in this example):

  sudo -u postgres psql console -c 'select count(*) from settings';
  count
  -------
  1
  (1 row)

If you are unable to query the PostgreSQL database, verify that the Console has the needed network connectivity to the PostgreSQL database, for example:

nc -z <PostgreSQL IP> 5432

Please note, Capsule8 does not guarantee stability of the Console database schema across releases. We recommend against querying or manipulating the database directly.

Any such use is at your own risk.