Secure Database with SSL – A Step-by-Step Example
- MariaDB
- PostgreSQL
1. Generate SSL Certificates
Option A: Use Self‑Signed Certificates
# 1. Create Certificate Directory
mkdir -p mariadb_certs && cd mariadb_certs
# 2. Generate Certificate Authority (CA)
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem -subj "/CN=MySQL_CA"
# 3. Generate Server Certificate
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem -subj "/CN=localhost"
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# 4. Set Permissions
sudo chmod 644 *.pem
#Import the server certificate into a Java truststore:
keytool -importcert -file server-cert.pem -alias mariadbserver -keystore truststore.jks
Copy truststore.jks to your REST API’s config-files directory.
Option B: Use CA‑Signed Certificates
For production environments, it is recommended to use certificates issued by a trusted Certificate Authority (CA). Follow your organization's standard process for requesting and deploying these certificates.
2. Mount Certificate Files into the Container
Create MariaDB SSL Configuration
Create a custom configuration file named custom.cnf:
[mysqld]
ssl=ON
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
Update docker-compose.yml
Mount the certificate directory and configuration file into the container:
services:
securosys_sql:
volumes:
- ./mariadb_certs:/etc/mysql/certs:ro
- ./custom.cnf:/etc/mysql/conf.d/custom.cnf:ro
3. Verify SSL is Enabled
Start the container and inspect the MariaDB logs to confirm SSL is active.
Connect and Test SSL
docker exec -it securosys_sql /bin/bash
mysql -h localhost -u <db-username> -p --ssl-ca=/etc/mysql/certs/ca-cert.pem
Replace <db-username> with your actual database user.
4. Configure REST API to Use SSL
Edit the application-local.yml file and update the JDBC URL to include SSL settings:
spring:
datasource:
url: jdbc:mariadb://securosys_sql:3306/securosys?sslMode=verify-ca&trustStore=/etc/app/config/truststore.jks&trustStorePassword=change-it
...
Restart your REST API container and verify it starts without errors.
1. Enable SSL in the Container
The Bitnami PostgreSQL Docker image supports SSL out of the box. To enable it, set the appropriate environment variable in your docker-compose.yml file:
environment:
- ...
- POSTGRESQL_ENABLE_TLS=yes
2. Generate SSL Certificates
PostgreSQL requires a server certificate and private key for SSL. You can choose from two options:
a. Use Self‑Signed Certificates
# Generate a private key
openssl genrsa -out server.key 4096
# Generate a certificate signing request (CSR)
openssl req -new -key server.key -out server.csr -subj "/CN=securosys_psql"
# Self-sign the certificate (valid for 3650 days)
openssl x509 -req -in server.csr -signkey server.key -out server.crt -days 3650
# Set strict permissions on the private key
chmod 600 server.key
Place server.crt and server.key in a directory like ./config-files/certs/db.
b. Use CA‑Signed Certificates
In production environments, use certificates issued by a trusted Certificate Authority (CA). Follow your organization’s guidelines for obtaining and managing these certificates.
3. Mount the Certificate Files Into the Container
The Bitnami PostgreSQL container typically runs as a non-root user with UID 1001. Ensure the certificate files are owned by this user:
chown 1001:1001 ./certs/server.key
chown 1001:1001 ./certs/server.crt
Then update your docker-compose.yml:
environment:
- ...
- POSTGRESQL_ENABLE_TLS=yes
- POSTGRESQL_TLS_CERT_FILE=/opt/bitnami/postgresql/certs/server.crt
- POSTGRESQL_TLS_KEY_FILE=/opt/bitnami/postgresql/certs/server.key
volumes:
- ./config-files/certs/db:/opt/bitnami/postgresql/certs:ro
4. Client Connection Considerations
Ensure clients use SSL by including the sslmode parameter:
docker exec -it securosys_psql /bin/bash
psql "host=localhost user=replace-me_db-username dbname=securosys sslmode=require"
For stricter validation, use sslmode=verify-full with a trusted CA certificate.
5. Verify SSL is Working
Check the PostgreSQL logs after starting the container for SSL-related messages.
You can also connect and run:
SHOW ssl;
It should return on.
6. Configure REST API to Use SSL
Edit your application-local-psql.yml:
spring:
datasource:
url: jdbc:postgresql://localhost:5434/securosys?allowPublicKeyRetrieval=true&useSSL=true
...
Restart your REST API container and verify proper startup.