Manually creating a Postgres database

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  

Finally, 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"

Start service and ensure is running

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

Finally, 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"

Start service and ensure is running

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.