Encrypt PostgreSQL Database with SSL

This topic describes how to secure your PostgreSQL database by implementing SSL encryption, ensuring enhanced data protection and secure communication.

Create a Server Certificate

Log in to the App Server
- Open PuTTY and connect to the app server using the IP address.
- Log in with the username: ubuntu and password: hubbledefault.
Switch to Administrator Mode

Run the command: sudo su
Check if OpenSSL is installed
- Verify OpenSSL installation:
  
  openssl version -a
- If OpenSSL is not installed, install it using:
  
  sudo apt-get install openssl
Navigate to the directory

Change to the directory where the SSL certificates will be stored:

cd /mnt/data/containers/hubble_repository
Generate the SSL Private Key and Set Correct Permissions
- Create a new RSA private key, encrypted with DES3
  
  openssl genrsa -des3 -out server.key 2048
- Remove the passphrase from the key:
  
  openssl rsa -in server.key -out server.key
- Set the correct permissions:
  
  chmod 400 server.key
  
  chown 70:70 server.key
Generate the Self-Signed Server Certificate
- Create a new SSL certificate valid for 3650 days (10 years), using the private key
  
  openssl req -new -x509 -key server.key -days 3650 -out server.crt
- Enter the information as prompted:
```
Country Name: CA
```
```
State: British Columbia
```
```
Locality Name: Comox
```
```
Organization: http://TheBrain.ca
```
```
Organizational Unit Name: thebrain.ca
```
```
Common Name: thebrain.ca
```
```
Email: info@thebrain.ca
```
- If you encounter an error related to /root/.rnd, try:
  
  openssl rand -out /root/.rnd -hex 256
Set Permissions for the Certificate and Copy the Certificate
- Restrict permissions so only the owner can read and write the certificate
  
  chmod a+rw server.crt
- Copy the certificate:
  
  cp server.crt root.crt

Create a Client-Side Certificate (Optional)

Generate a private key (with passphrase):

openssl genrsa -des3 -out postgresql.key 2048
Remove the passphrase from the private key (optional but often required by PostgreSQL):

openssl rsa -in postgresql.key -out postgresql.key
Create a Certificate Signing Request (CSR):

openssl req -new -key postgresql.key -out postgresql.csr

Alternatively, you can provide the subject information directly:

openssl req -new -key postgresql.key -out postgresql.csr \

-subj "/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=postgresql-client/emailAddress=info@thebrain.ca"
Sign the CSR with your Certificate Authority (CA):

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

-out postgresql.crt -CAcreateserial -days 365
Secure the key and certificate files:

chmod 600 postgresql.key postgresql.crt
Verify the contents of the client certificate:

openssl x509 -in postgresql.crt -text -noout

Configure SSL Certificate Mapping in Docker for hubble_repository

Follow these steps to securely map SSL certificates into your PostgreSQL container using Docker Compose.

Navigate to the Docker Compose Directory

Open a terminal and change to the directory containing your docker-compose.yml file:

cd /etc/hubble
Open the Docker Compose File

Use a text editor to open the docker-compose.yml file. For example, using nano:

sudo nano docker-compose.yml

Update the Service Configuration

Locate the hubble_repository service in the file. Add the following under the appropriate sections:

Command Section

Enable SSL and specify certificate file paths:

command:

-c ssl=on

-c ssl_cert_file=/var/lib/postgresql/server.crt

-c ssl_key_file=/var/lib/postgresql/server.key

-c ssl_ca_file=/var/lib/postgresql/root.crt

Environment Section

Define environment variables for PostgreSQL SSL:

environment:

PGSSLCERT: /var/lib/postgresql/server.crt

PGSSLKEY: /var/lib/postgresql/server.key

PGSSLROOTCERT: /var/lib/postgresql/root.crt

Volumes Section

Map the certificate files from the host to the container:

volumes:

- ${DATA_DIRECTORY}hubble_repository/root.crt:/var/lib/postgresql/root.crt

- ${DATA_DIRECTORY}hubble_repository/server.crt:/var/lib/postgresql/server.crt

- ${DATA_DIRECTORY}hubble_repository/server.key:/var/lib/postgresql/server.key

Save and Exit

If using nano, press Ctrl + O to save and Ctrl + X to exit the editor.
Validate the Configuration

Run the following command to verify that the syntax is correct:

docker-compose config
Restart the Container

Apply the changes by restarting the container:

docker-compose restart

Or use one of the following alternatives:
```
docker-compose up -d
```
```
# or
```
```
docker-compose up -d --build
```

Set File Permissions for SSL Certificates in Docker

Follow these steps to configure secure file permissions for server.crt, server.key, and root.crt inside the PostgreSQL container.

Access the Running Container

Use the following command to open a shell session inside the container:
```
docker exec -it hubble_hubble_repository_1 bash
```
Navigate to the Certificate Directory
- Change to the directory where the certificates are mounted:
  
  cd /var/lib/postgresql
- Verify the current directory:
  
  pwd
- List all files with detailed permissions:
  
  ls -la
Set File Permissions

Apply the appropriate permissions and ownership:
```
chmod a+rw server.crt
```
```
chmod 400 server.key
```
```
chown 70:70 server.key
```
```
chmod a+rw root.crt
```
Note:
- chmod a+rw grants read and write permissions to all users.
- chmod 400 restricts access to read-only for the owner.
- chown 70:70 sets the user and group ownership to UID and GID 70 (commonly used by PostgreSQL).
Verify Permissions

Check the updated permissions for each file:
```
ls -l server.crt
```
```
ls -l server.key
```
```
ls -l root.crt
```

Configure SSL in PostgreSQL

To enable SSL encryption for PostgreSQL connections, update the postgresql.conf configuration file with the appropriate SSL settings.

Open the Configuration File

Use a text editor to open the postgresql.conf file:
```
sudo nano /mnt/data/containers/hubble_repository/data/postgresql.conf
```
Modify or Add SSL Settings

Add or update the following parameters in the file:
```
ssl = on
```
```
ssl_cert_file = '/var/lib/postgresql/server.crt'
```
```
ssl_key_file = '/var/lib/postgresql/server.key'
```
```
ssl_ca_file = '/var/lib/postgresql/root.crt'
```
Description of Settings:
- ssl = on: Enables SSL encryption for client connections.
- ssl_cert_file: Specifies the path to the server’s SSL certificate file.
- ssl_key_file: Specifies the path to the server’s private key file used for SSL.
- ssl_ca_file: Specifies the path to the Certificate Authority (CA) file used to verify client certificates.
Save and Exit

If using nano, press Ctrl + O to save the file, then Ctrl + X to exit the editor.
Restart PostgreSQL

Changes to postgresql.conf require a server restart to take effect. Restart the PostgreSQL container using:

docker-compose restart

Configure Client Authentication in PostgreSQL

To control how clients connect to the PostgreSQL server, update the pg_hba.conf file with the appropriate authentication rules.

Open the Authentication Configuration File

Use a text editor to open the pg_hba.conf file:
```
sudo nano /mnt/data/containers/hubble_repository/data/pg_hba.conf
```
(Optional) View the Current Configuration

To review the existing configuration before making changes:
```
sudo cat /mnt/data/containers/hubble_repository/data/pg_hba.conf
```

Add or Modify Authentication Rules

Update the file with the following entries:

# "local" is for Unix domain socket connections only

local   all             all                                     trust

# IPv4 local connections:

host    all             all             127.0.0.1/32            trust

# IPv6 local connections:

host    all             all             ::1/128                 trust

# SSL connections for 'hubble' user from specific IP

hostssl hubble_repository hubble        0.0.0.0/0               md5

# Allow all users to connect to all databases from any host (non-SSL)

host    all             all             0.0.0.0/0               trust

# Allow SSL connections from any host to all databases for any user

hostssl all             all             all                    md5

Tip: To enforce SSL-only connections, comment out or remove the following line:

#host all all all md5

Save and Exit

If using nano, press Ctrl + O to save the file, then Ctrl + X to exit the editor.
Restart PostgreSQL

Changes to pg_hba.conf require a server restart. Use the following command to reboot the container:
```
docker-compose restart
```

Restart PostgreSQL and Resolve Port Conflicts

Restart PostgreSQL

After updating PostgreSQL configuration files, restart the service to apply the changes:

sudo systemctl restart postgresql

Or, on systems using the older init system:

sudo service postgresql restart
Check for Port Conflicts

If PostgreSQL fails to start, it might be due to another process using the default port 5432.

To identify the process using the port:

sudo lsof -i tcp:5432
Terminate the Conflicting Process

If a conflicting process is found, note its PID (Process ID) from the output and terminate it:

sudo kill -9 <PID>

Replace <PID> with the actual number shown in the lsof output.

Restart the Server and Export the PostgreSQL Client Certificate

Restart the Server

Navigate to the Hubble directory and run the startup script:

cd /etc/hubble

./start.sh
Check for Errors

If the server fails to start:
- List running containers:
  
  docker ps
- View logs for the container:
  
  docker logs <container_id>
- Fix permission issues if indicated in the logs by adjusting file permissions.
Export the Client Certificate
- Go to the certificate directory:
  
  cd /mnt/data/containers/hubble_repository
- Export the certificate to a .pfx file:
  
  openssl pkcs12 -export -out postgresql.pfx -inkey postgresql.key -in postgresql.crt -certfile root.crt
- Move the .pfx file to a temporary directory:
  
  cp postgresql.pfx /tmp
  
  cd /tmp
- Set appropriate permissions:
  
  chmod 755 postgresql.pfx
  
  ls -la postgresql.pfx
Transfer the Certificate to Your Local Machine
- Use WinSCP to connect to the app server using its IP and credentials (ubuntu / hubbledefault).
- Navigate to the /tmp directory.
- Drag and drop the postgresql.pfx file to your local system.
Import the Certificate on the Web Server
- Copy the .pfx file from your local machine to the web server.
- Open the Microsoft Management Console (MMC):
  - Press Windows + R, type mmc, and hit Enter.
  - Go to File > Add/Remove Snap-in.
  - Select Certificates > Computer Account > Local Computer.
- Import the certificate into:

Restart the necessary services:

iisreset

net stop insightybservice

net start insightybservice

Note: If the certificates were created in the /mnt/data/containers/hubble_repository/data directory, restarting the server may not be required.

Configure the Web Server

Connect to the Web Server

Use Remote Desktop Connection to access the web server.
Update the Connection String

Navigate to the configuration file: C:\Insight\YellowBoxWeb\Config\YellowBox.config

Update the DbConnectionString to include SSL settings:

<add key="DbConnectionString" value="Server=10.13.0.102;Port=5432;database=hubble_repository;user id=hubble;password=password;SSL Mode=Require;TrustServerCertificate=True;Pooling=true;MinPoolSize=50;MaxPoolSize=550" />

Replace 10.13.0.102 with the actual App Server IP if different.
Restart Services

After saving the config file, restart the following:
```
iisreset
```
```
net stop insightybservice
```
```
net start insightybservice
```

Retrieve the PostgreSQL Password from Vault

Get the Vault Token:

cat /mnt/data/containers/hubble_vault/root_token

Access the Vault Container:

sudo su

docker ps

docker exec -it hubble_vault sh

Set the Vault Token and Retrieve the Password:

export VAULT_TOKEN='<token_from_step_1>'

vault kv get secret/postgres

# or

vault kv get secret/scality

Test the PostgreSQL Connection with SSL

Access the Repository Container:

cd /mnt/data/containers/hubble_repository

docker exec -it hubble_hubble_repository_1 bash

Connect Using psql with SSL:

psql "host=10.13.0.102 port=5432 dbname=hubble_repository user=hubble password=password sslmode=require"

sslmode=require ensures SSL is enforced.

Verify SSL Settings in psql:

SHOW ssl;

SHOW ssl_cert_file;

SHOW ssl_key_file;

SHOW ssl_ca_file;

Use Alternate Method if `psql` Fails

Check the Container Name:
```
docker ps
```

Connect as the postgres User:

docker exec -itu postgres hubble_hubble_repository_1 psql

Run SSL Verification Commands Again:
```
SHOW ssl;
```

Configure Repository Selection File for Hubble

Open Hubble Administrator: Launch Hubble Administrator on the Web Server.
Create Repository Database Connection: Connect to the hubble_repository database.
Configure SSL Settings

In the Advanced Connection Properties:
- Set SSL Mode to Required.
- Set Trust Server Certificate to True.
Log in to the Repository: After creating the connection, log in to the repository.
Save the Repository Selection File

Save the file in the following directories:
- C:\inetpub\wwwroot\Hubble → (Hubble Web)
- C:\inetpub\wwwroot\Hubble\bin → (Hubble Web)
- C:\Program Files\InsightSoftware.com\Hubble → (Desktop)
Restart Services:
- Restart IIS.
- Restart insightybservice.
Launch Hubble Web Application: Open the Hubble Web Application in a browser.
Analyze Traffic Using Wireshark
- Use the filter:
  
  tcp.port == 5432
- All traffic and SQL queries will be encrypted.

Encrypt PostgreSQL Database with SSL

Create a Server Certificate

Create a Client-Side Certificate (Optional)

Configure SSL Certificate Mapping in Docker for hubble_repository

Set File Permissions for SSL Certificates in Docker

Configure SSL in PostgreSQL

Configure Client Authentication in PostgreSQL

Restart PostgreSQL and Resolve Port Conflicts

Restart the Server and Export the PostgreSQL Client Certificate

Configure the Web Server

Retrieve the PostgreSQL Password from Vault

Test the PostgreSQL Connection with SSL

Use Alternate Method if `psql` Fails

Configure Repository Selection File for Hubble

Recommended Reading

Was this article helpful?

Encrypt PostgreSQL Database with SSL

Create a Server Certificate

Create a Client-Side Certificate (Optional)

Configure SSL Certificate Mapping in Docker for hubble_repository

Set File Permissions for SSL Certificates in Docker

Configure SSL in PostgreSQL

Configure Client Authentication in PostgreSQL

Restart PostgreSQL and Resolve Port Conflicts

Restart the Server and Export the PostgreSQL Client Certificate

Configure the Web Server

Retrieve the PostgreSQL Password from Vault

Test the PostgreSQL Connection with SSL

Use Alternate Method if psql Fails

Configure Repository Selection File for Hubble

Recommended Reading

Was this article helpful?

Use Alternate Method if `psql` Fails