Environment

NetGovern Secure

Synopsis

How to configure security and prevent access on the logging PostgreSQL database on NetGovern Secure.

Solution

Depending on the configuration of NetGovern Secure in your environment will determine how to correctly secure PostgreSQL. In single node environments the PostgreSQL database doesn't need to be accessible externally. With multiple nodes the PostgreSQL database needs to be read and written to by all the nodes.



Configuration
Everything this goes over will be configuration done through SSH and vi. It is important to be familiar with this command and SSH before attempting to secure PostgreSQL. Note that after editing the configuration files no changes will take effect unless you restart the PostgreSQL server. To restart the PostgreSQL server make the NetGovern services are stopped on all of the nodes in the cluster, then issue a 'service postgresql restart' command on the database node.


Single Node
Configure/Check how NetGovern Secure connects to PostgreSQL
1. vi /root/.odbc.ini
The main thing to check here it to make sure that the 'Servername=' is set to either localhost or a loopback IP Address. This will assure that NetGoven Secure is only connecting locally.
Example:
[mplus]
Description=NeGovern Secure ODBC
Driver=/usr/local/lib/psqlodbc.so
Database=mplus
Servername=127.0.0.1
Username=postgres
Password=M3ss4g1ng
Port=5432
Protocol=6.4
ReadOnly=No

Configure the listening port of PostgreSQL
2. vi /var/lib/pgsql/data/postgresql.conf
This is a fairly large file so you are going to want to go down quite a ways. What you are looking for is this section:
listen_addresses = 'localhost'
If it is set to anything other than localhost it is listening on other interfaces.

Configure Trusted Connections
3. vi /var/lib/pgsql/data/pg_hba.conf
Here you are going to want to change the IPv4 Connection to trust only the local connection.
# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident
# IPv4 local connections:
host all all 127.0.0.1/8 trust
# IPv6 local connections:
host all all ::1/128 ident
Done! Make sure to restart the PostgreSQL Service



Multi-Node

Configure/Check how Netmail Secure connects to PostgreSQL
1. vi /root/.odbc.ini
The main thing to check here it to make sure that the 'Servername=' is set to either localhost, for the database node, or the database node's IP Address. This will assure that NetGovern Secure is connecting to the correct node. This is the only step you will have to do for the nodes that will not have the database on them.
Example Connecting Node:
[mplus]
Description=NetGovern Secure ODBC
Driver=/usr/local/lib/psqlodbc.so
Database=mplus
Servername=192.168.0.100
Username=postgres
Password=M3ss4g1ng
Port=5432
Protocol=6.4
ReadOnly=No

Configure the listening port of PostgreSQL
2. vi /var/lib/pgsql/data/postgresql.conf
This is a fairly large file so you are going to want to go down quite a ways. You will only need to do this on the database node. You are looking for is this section:
listen_addresses = '*'
If it is set to an localhost it is not listening on other interfaces so the other nodes will not be able to connect to it.

Configure Trusted Connections
3. vi /var/lib/pgsql/data/pg_hba.conf
Here you are going to want to change the IPv4 Connection to trust the local connections from each of the ip addresses.
# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident
# IPv4 local connections:
host all all 127.0.0.1/8 trust
host all all 192.168.0.101/8 trust
host all all 192.168.0.105/8 trust
# IPv6 local connections:
host all all ::1/128 ident

Done! Make sure that you restart the postgresql service!

Notes

Former KB Article: 1413
http://kb.messagingarchitects.com/article.aspx?article=1413&p=1