This article demonstrates how to configure Nexus Lifecycle to connect to a PostgreSQL database over SSL. For testing, we'll set up a PostgreSQL instance using Docker Compose with a basic self-signed SSL certificate. In real-world or production environments, database admins typically configure PostgreSQL with certificates signed by a trusted certificate authority (CA). Afterward, we'll modify the config.yml file in Nexus Lifecycle to ensure secure SSL communication with the PostgreSQL database.
The focus of this article is not on setting up PostgreSQL with SSL, but rather on explaining the necessary Nexus Lifecycle configuration changes to ensure it connects to the database over SSL.
Step-1). Creating Self Signed SSL certificates.
mkdir -p /Articles/Lifecycle_Postgres_SSL/IQ_187 /Articles/Lifecycle_Postgres_SSL/LAB
cd /Articles/Lifecycle_Postgres_SSL/LAB
mkdir ./ssl
openssl genpkey -algorithm RSA -out ./ssl/postgresdb.example.com.key
openssl req -new -newkey rsa:4096 -days 365 -nodes -x509 -subj "/C=US/ST=NSW/L=Sydney/O=Dis/CN=postgresdb.example.com" -key ./ssl/postgresdb.example.com.key -out ./ssl/postgresdb.example.com.cert
sudo chown 999:999 ./ssl/postgresdb.example.com.key
sudo chown 999:999 ./ssl/postgresdb.example.com.cert
chmod 0600 ./ssl/postgresdb.example.com.key
chmod 0644 ./ssl/postgresdb.example.com.cert
Step-2). (Optional ) Writing Database User and the Database creation script for the IQ Server and granting the user permissions. Optional step if we want to have IQ DB with some other name/vredentials.
cat > ./init-db.sql << 'EOF'
CREATE USER iq_db_user WITH PASSWORD 'iq_db_password';
CREATE DATABASE iqdb WITH OWNER iq_db_user ENCODING 'UTF8';
GRANT ALL ON DATABASE iqdb to iq_db_user;
EOF
Step-3). Ensuring that the Postgres DNB listens on port 5432 and SSL is ON and ssl_cert_file / ssl_key_file are correctly updated.
cat > ./postgresql.conf << EOF
listen_addresses = '*'
port = 5432
ssl = on
ssl_cert_file = '/etc/ssl/certs/postgresdb.example.com.cert'
ssl_key_file = '/etc/ssl/certs/postgresdb.example.com.key'
EOF
Step-4). Using the above DB script and postgres.conf to set up Postgres DB instance using docker compose.
cat > ./docker-compose.yml << 'EOF'
version: '3.8'
services:
postgres:
image: postgres:latest
container_name: postgres_ssl
environment:
POSTGRES_PASSWORD: sonatype
POSTGRES_USER: sonatype
POSTGRES_DB: iqdb
ports:
- "5432:5432"
volumes:
- ./ssl:/etc/ssl/certs
- ./init-db.sql:/docker-entrypoint-initdb.d/init-db.sql
- ./postgresql.conf:/etc/postgresql/postgresql.conf
command: postgres -c 'config_file=/etc/postgresql/postgresql.conf'
restart: always
EOF
docker compose down
docker compose up -d
Step-5). Verify if Postgres DB is listening on SSL port 5432 and check if it is serving the self signed certificate to the clients or not?
docker exec -it postgres_ssl ls /etc/ssl/certs
docker exec -it postgres_ssl psql -U sonatype -d iqdb -c "SHOW ssl;"
# Check if the SSL certificate can be downloaded from Postgres DB?
openssl s_client -connect postgresdb.example.com:5432 -showcerts < /dev/null | openssl x509 -outform PEM > postgres-server-cert.pem
Step-6). Update the config.yml file with the additional configs as "sslmode: require" and "ssl: true" as following and then restart the IQ Server.
database:
type: postgresql
hostname: postgresdb.example.com
port: 5432
name: iqdb
username: sonatype
password: sonatype
parameters:
sslmode: require
ssl: true
Step-7). Verify if the IQ Specific database tables are accessible or not ?
docker exec -it postgres_ssl psql -U sonatype -d iqdb -c "\dn+"
docker exec -it postgres_ssl psql -U sonatype -d iqdb -c "SELECT * FROM insight_brain_ods.application"