Postgresql setup for NGINX controller

Download and Install PostgreSQL 9.5

See offical download and install guide for:

For example, PostgreSQL 9.5 on Ubuntu 18.04:

# Create the file /etc/apt/sources.list.d/pgdg.list and add a line for the repository 
sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" >> /etc/apt/sources.list.d/pgdg.list
#  Import the repository signing key, and update the package lists 
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

# Install Specifc version supported by NGINX Controller
sudo apt-get install postgresql-9.5 -y

Create `naas` user on Postgresql

Note: You are not required to create the naas database for Controller v3.2+

Once you have install postgresql, run the following steps to create a user and database called naas and allow connections from NGINX Controller

Add a Linux/UNIX user called naas

sudo useradd -r naas
sudo passwd naas

Become superuser: sudo su -
Now connect to database server

# Run this:
sudo -u postgres psql
#OR
psql -U postgres

Add a user called naas

# Type the following command to create a user called naas with a password called naas
postgres=# CREATE USER naas WITH PASSWORD 'naas';

Grant create DB permission for user, naas

# Grant create DB permission for user, naas
postgres=# ALTER USER naas CREATEDB;

Quit postgres

# Type \q to quit:
postgres=# \q

From localhost, on the postgres server: Test access to DB as your user, naas

# change to naas user
su - naas
# connect to database, naas as naas
psql -d naas -U naas

psql (9.5.20)                                                                                                                  
Type "help" for help.
# Type \q to quit:
naas=> \q

# Exit naas user back to root
exit

Configure access to postgres server

Change postgres listen address

Open your postgresql.conf file in your editor:

# locate the postgresql.conf
sudo locate postgresql.conf
# or using find
find / -name postgresql.conf 
# Edit with vim
# Centos:
# vim /var/lib/pgsql/9.5/data/postgresql.conf
# Ubuntu:
vim /etc/postgresql/9.5/main/postgresql.conf 

Search for listen_addresses, and set it to '*' for all IPs or just specific the exact IPs to listen on in a comma delimited list

For example:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'         # what IP address(es) to listen on;

Save changes to the file.

Open postgres to external clients

First confirm Firewall (SE Linux / Iptables firewall rules) allow for external access

Check (SE Linux / Iptables firewall rules) allow for external access. Make sure iptables are not blocking communication, open port 5432:

For example, append rules to your iptables scripts or file /etc/sysconfig/iptables:

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d [ip_address]  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s [ip_address] --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Restart Postgres service

# Find the postgres service
sudo systemctl | grep postgresql

# Restart postgres service
sudo systemctl restart postgresql

# If the service did not start
sudo systemctl stop postgresql
sudo systemctl start postgresql

# Check service status
sudo systemctl status postgresql

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Wed 2020-03-25 10:14:18 MDT; 3s ago
  Process: 14897 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 14897 (code=exited, status=0/SUCCESS)

Mar 25 10:14:18 postgres systemd[1]: Starting PostgreSQL RDBMS...
Mar 25 10:14:18 postgres systemd[1]: Started PostgreSQL RDBMS.

We need to allow remote connections to actually reach your PostgreSQL server. Open pg_hba.conf to make configure these changes. To locate and edit the pg_hba.conf you can use the following commands:

# locate the pg_hba.conf
sudo locate pg_hba.conf
# or using find
find / -name postgresql.conf 

# Edit with vim
# Centos
vim /var/lib/pgsql/9.5/data/pg_hba.conf
# Ubuntu
vim /etc/postgresql/9.5/main/pg_hba.conf

Enforce access controls to Postgres Database using any of the following access methods below. Note: It appears after testing in Controller 3.2 Enforce MD5-encrypted password does not work and you must enable Trust networks access controls on your postgresql configuration or configure access using client certificates:

Trust specific IP (or network)

To allow connections from a specific address (i.e. NGINX Controller) from specifc IP Adddress or Network, add this line at the end of pg_hba.conf file.

It is recommended to specify the Controller IP address or the private network/mask instead of All IPs, 0.0.0.0/0
true - Trust all connections from these IP Networks
See other pg_hba.conf configuration options in the documentation:

# Just allow controller ip address
host all all 172.16.29.91/32 trust

# Allow Any client on ip address
host all all 172.16.29.0/24 trust

Save changes to the file.

~~#### B. Enforce MD5-encrypted password~~

1. To allow connections from a specific address (i.e. NGINX Controller), or absolutely any address with password authentication add this line at the end of pg_hba.conf file. ~~ ~~ * It is recommended to specify the Controller IP address or the private network/mask instead of All IPs, 0.0.0.0/0 * md5 - Require the client to supply an MD5-encrypted password for authentication ~~* See other pg_hba.conf configuration options in the documentation: ~~

~~For example:~~

~~# Just allow controller ip address~~
~~host all all 172.16.29.91/32 md5~~

~~# Allow Any client on ip address~~
~~host all all 172.16.29.0/24 md5~~

~~2. Save changes to the file.~~

Secure TCP/IP Connections with Using Client Certificates

Configure Server Side (postgres server): Generate self-signed server certificates

Find enter the configured postgres data_directory:

cat /etc/postgresql/9.5/main/postgresql.conf | grep data_directory

data_directory = '/var/lib/postgresql/9.5/main'         # use data in another directory

cd /var/lib/postgresql/9.5/main

Generate a private key (you must provide a passphrase).

# To add passphrase add "-des3" flag
openssl genrsa -out server.key 4096
Generating RSA private key, 4096 bit long modulus (2 primes)
.......................................................................................................++++
.........................................................................................................................................++++
e is 65537 (0x010001)

Set appropriate permission and owner on the private key file.

chmod 400 server.key
chown postgres.postgres server.key

Create a self-signed server certificate.

-subj is a shortcut to avoid prompting for the info.
-x509 produces a self signed certificate rather than a certificate request.
CN Must be FQDN of postgres server to pass sslmode=verify-full checks on controller

# CN Must be FQDN of postgres server to pass `sslmode=verify-full` checks on controller
openssl req -new -key server.key \
	-days 3650 \
	-out server.crt \
	-x509 \
	-subj '/C=US/ST=Colorado/L=Denver/O=nginxf5/CN=postgres.nginxf5.com/[email protected]'

Note: if you get an error Can't load ./.rnd into RNG, you can comment out the line with RANDFILE from the /etc/ssl/openssl.cnf config.

Since we are self-signing, we use the server certificate as the trusted root certificate.

cp server.crt root.crt

You’ll need to edit pg_hba.conf. For example:

vim /etc/postgresql/9.5/main/pg_hba.conf:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32         trust

# IPv4 remote connections for authenticated users
# hostssl nameofdatabase +group|user xxx.xxx.xxx.xxx./xx cert clientcert=1 # only client cert
# hostssl nameofdatabase +group|user xxx.xxx.xxx.xxx./xx md5 clientcert=1 # md5 and client cert
hostssl all         naas    172.16.29.91/32             md5 clientcert=1

Make sure postgresql.conf has the relevant to the SSL setup on the server:

vim /etc/postgresql/9.5/main/postgresql.conf:

ssl = on                                # (change requires restart)
ssl_cert_file = '/var/lib/postgresql/9.5/main/server.crt'               # (change requires restart)
ssl_key_file = '/var/lib/postgresql/9.5/main/server.key'                # (change requires restart)
ssl_ca_file = '/var/lib/postgresql/9.5/main/root.crt'                   # (change requires restart)
#ssl_crl_file = ''                      # (change requires restart)

After saving changes, you can quickly check the SSL settings in the file using grep:

cat /etc/postgresql/9.5/main/postgresql.conf | grep ssl

Restart postgres service

/etc/init.d/postgresql restart

If the server fails to (re)start, look in the postgresql startup log, /var/lib/pgsql/pgstartup.log (default for CentOS), or /var/log/postgresql/postgresql-9.5-main.log (default for Ubuntu), for the reason.

Check service status

sudo systemctl status postgresql

Install generated certs a root/CA Certificate on Ubuntu. ( For other Linux Distros, see How to add trusted root certificates)

a. Locate your generated certs (in the data_directory) and copy your generated CA to the ca-certificate folder. Note: The certificate filenames have to end in .crt, otherwise the update-ca-certificates script won’t pick up on them:

cd /var/lib/postgresql/9.5/main

sudo cp server.crt /usr/local/share/ca-certificates/

b. Then, update CA store. No file is needed to edit. Link to your CA is created automatically:

sudo update-ca-certificates


Updating certificates in /etc/ssl/certs...
1 added, 0 removed; done.
Running hooks in /etc/ca-certificates/update.d...
done.

Configure Client Side (postgres client)

We will create and place the Client side certificates with the following requirements:

On the client, we need three files on the user who will install NGINX Controller (i.e. armand), on the NGINX Controller host this will be /home/armand/.postgresql/ directory.
We also need to set the** CN of certificate to the postgres client the NGINX Controller runs as** (i.e. naas).
root.crt (trusted root certificate)
postgresql.crt (client certificate)
postgresql.key (private key)

Generate the the needed files on the server machine, and then copy them to the client. We’ll generate the needed files in the /data_directory/ directory:

# Enter the data_directory in my example:
cd /var/lib/postgresql/9.5/main

# To add passphrase add "-des3" flag
openssl genrsa -out postgresql.key 4096

On the postgres server, then create the certificate postgresql.crt for the client machine: It must be signed by our trusted root (which is using the private key file on the server machine). Also, the certificate common name (CN) must be set to the database user name we’ll connect as, i.e. naas

openssl req -new -key postgresql.key -out postgresql.csr -subj '/C=US/ST=Colorado/L=Denver/O=nginxf5.com/CN=naas'

openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out postgresql.crt -CAcreateserial

Note: if you get an error Can't load ./.rnd into RNG, you can comment out the line with RANDFILE from the /etc/ssl/openssl.cnf config.

We can quickly check with openssl and verify the client->root signing link:

openssl verify -CAfile root.crt -purpose sslclient postgresql.crt 
postgresql.crt: OK

From the client server, Controller Host: , create a ~/.postgresql/ directory, on the user who will run the Controller Installer i.e.armand

# change to armand user (the non-root user who will run the Controller Installer)
su armand
# Then make a directory for the postgresql client cert 
mkdir ~/.postgresql

On the postgres server, Copy the three files we created from the server /tmp/ directory to the client machine to the ~/.postgresql directory of the user who will run the Controller Installer .

cd /var/lib/postgresql/9.5/main

scp root.crt [email protected]:~/.postgresql
scp postgresql.crt [email protected]:~/.postgresql
scp postgresql.key [email protected]:~/.postgresql

Check connectivity from Client to postgresql server using specific client certificates. NGINX Controller connects to postgres using sslmode=verify-full, so we can test that with the first command below:

# As the User to run NGINX Controller Installer(root.crt, postgresql.crt and postgresql.key` are in ~/.postgresql directory)
psql "port=5432 host=postgres.nginxf5.com user=naas sslmode=verify-ca dbname=postgres sslmode=verify-full"

# From another user you can explictly point to the sslcert, sslkey and sslrootcert:
psql "port=5432 host=postgres.nginxf5.com user=naas sslcert=/home/armand/.postgresql/postgresql.crt sslkey=/home/armand/.postgresql/postgresql.key  sslrootcert=/home/armand/.postgresql/root.crt sslmode=verify-ca dbname=postgres sslmode=verify-full"

Troubleshooting

View postgresql logs

View the postgres logs in realtime by tailing the logs:

# Ubuntu
tail -f  /var/log/postgresql/postgresql-9.5-main.log

Test connectivity from a remote client (controller)

Install the postgressql client

# ubuntu / debian
sudo apt-get install -y postgresql-client

# Red Hat Enterprise Linux 7+ (installs client packages)
sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat10-10-2.noarch.rpm  
sudo yum install postgresql10  
# OR, Simply
dnf install postgresql

Run the following command on the Postgres client / NGINX Controller:

# On the NGINX Controller host, check external postgres connectivity
psql -h postgres.nginxf5.com -U naas -d postgres
# OR
psql -h postgres.nginxf5.com -U naas -d template1

Password for user naas:
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1), server 9.5.18)
Type "help" for help.

naas=> \q

Check connectivity from Client to postgresql server using specific client certificates. NGINX Controller connects to postgres using sslmode=verify-full, so we can test that with the first command below:

# As the User to run NGINX Controller Installer(root.crt, postgresql.crt and postgresql.key` are in ~/.postgresql directory)
psql "port=5432 host=postgres.nginxf5.com user=naas sslmode=verify-ca dbname=postgres sslmode=verify-full"

# From another user you can explictly point to the sslcert, sslkey and sslrootcert:
psql "port=5432 host=postgres.nginxf5.com user=naas sslcert=/home/armand/.postgresql/postgresql.crt sslkey=/home/armand/.postgresql/postgresql.key  sslrootcert=/home/armand/.postgresql/root.crt sslmode=verify-ca dbname=postgres sslmode=verify-full"

Reset the password if you have forgotten:

# Switch to superuser
sudo su -
# connect to postgres
$ psql -d template1 -U postgres

# Change password for user, naas
ALTER USER naas WITH PASSWORD 'new_password';

Grant all privileges on database

Allow full privileges for naas user on naas database

# Connect to database server
$ psql template1
#OR
$ psql -d template1 -U postgres

# GRANT ALL PRIVILEGES ON DATABASE naas to naas;
template1=# GRANT ALL PRIVILEGES ON DATABASE naas to naas;

View user postgres permissions

template1=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 naas      | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Delete NGINX Controller databases

On NGINX Controller v3.2+: After Deleting All NGINX Controller Databases, you are not required to re-create the naas Database

On the postgres server, delete (drop) the naas database

Delete databases with the Owner naas. This will likely be naas, common, data, system and vault

[root@postgres ~]# sudo -i -u postgres
-bash-4.2$ psql template1
psql (9.5.18)
Type "help" for help.

template1=#  DROP DATABASE naas;
DROP DATABASE
template1=# DROP DATABASE common;
DROP DATABASE
template1=# DROP DATABASE data;
DROP DATABASE
template1=# DROP DATABASE system;
DROP DATABASE
template1=# DROP DATABASE vault;
DROP DATABASECREATE DATABASE

template1=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)