# Use SSL with an external PostgreSQL database > Encrypt the connection between Cloud Management Platform (CMP) and an external PostgreSQL database --- Site [llms.txt](/llms.txt) --- This procedure applies to a distributed deployment in which the database runs outside the appliance. Every web server and job engine node needs outbound access to the database on TCP 5432. See [Network connectivity](/docs/cmp/system-architecture/network-connectivity/). ## PostgreSQL database configuration Perform the following on the PostgreSQL server. 1. Add the following to the PostgreSQL data directory: `/var/lib/pgsql/14/data` - Server SSL certificate: `server.crt` - Private key: `server.key` 1. Update the SSL configuration to enable SSL: 1. Edit `/var/lib/pgsql/14/data/postgresql.conf`: 1. Uncomment `ssl` and set the value to `on` 1. If the filename for the server SSL certificate is not `server.crt`, or the private key is not `server.key`, do the following: 1. Uncomment `ssl_cert_file` and set the value to the server SSL certificate filename 1. Uncomment `ssl_key_file` and set the value to the private key filename **Example `/var/lib/pgsql/14/data/postgresql.conf`** ```text # - SSL - ssl = on #ssl_ca_file = '' #ssl_cert_file = 'server.crt' #ssl_crl_file = '' #ssl_crl_dir = '' #ssl_key_file = 'server.key' #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers #ssl_prefer_server_ciphers = on #ssl_ecdh_curve = 'prime256v1' #ssl_min_protocol_version = 'TLSv1.2' #ssl_max_protocol_version = '' #ssl_dh_params_file = '' #ssl_passphrase_command = '' #ssl_passphrase_command_supports_reload = off ``` 1. Update the allowed hosts to use SSL by adding `hostssl` entries for your IP addresses or IP ranges: 1. Edit `/var/lib/pgsql/14/data/pg_hba.conf`: **Example `/var/lib/pgsql/14/data/pg_hba.conf`** ```text # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256 hostssl all all 192.168.56.0/24 scram-sha-256 ``` 1. Restart the database ```text systemctl restart postgresql-14 ``` 1. Verify SSL is configured properly by connecting to the database using the `psql` command below: ```text #Replace value for password in the psql command below psql "dbname=cloudbolt host=db.cloudbolt.vm user=cb_dba password=yourdbpassword sslmode=require" ``` **Example expected return after running the `psql` command above** ```text # Example of successful psql command return psql (14.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. cloudbolt=> ``` For more detailed information, refer to the PostgreSQL documentation: [Secure TCP/IP Connections with SSL](https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-SETUP). ## CMP database configuration 1. Add the following files to the CMP database directory: `/var/opt/cloudbolt/proserv/db` - `server.crt` - `server.key` - `root.crt` 1. Change ownership of all three files to `cloudbolt:cloudbolt` with the following commands: ```text chown cloudbolt:cloudbolt /var/opt/cloudbolt/proserv/db/server.crt chown cloudbolt:cloudbolt /var/opt/cloudbolt/proserv/db/server.key chown cloudbolt:cloudbolt /var/opt/cloudbolt/proserv/db/root.crt ``` 1. On all web nodes and all job engine nodes, update your database settings: 1. Edit `/var/opt/cloudbolt/proserv/customer_settings.py` to add the `OPTIONS` for SSL: **Example `/var/opt/cloudbolt/proserv/customer_settings.py` with `OPTIONS`** ```text #Replace value for PASSWORD in the block below DATABASES = { 'default': { "ENGINE": "django.db.backends.postgresql", "NAME": "cloudbolt", "USER": "cb_dba", "PASSWORD": "yourpassword", "HOST": "db.cloudbolt.vm", "PORT": "5432", 'OPTIONS': { 'sslmode': "require", 'sslcert': "/var/opt/cloudbolt/proserv/db/server.crt", 'sslkey': "/var/opt/cloudbolt/proserv/db/server.key", 'sslrootcert': "/var/opt/cloudbolt/proserv/db/root.crt", }, } } ``` 1. In a web browser, log in to the CMP UI and go to a couple of different pages. 1. On the database server, connect to the database using `psql` and run the following query. ```text #Replace value for password in the psql command below psql "dbname=cloudbolt host=db.cloudbolt.vm user=cb_dba password=yourdbpassword sslmode=require" psql (14.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. cloudbolt=> SELECT * FROM pg_stat_ssl; ``` - The **ssl** column should have the value `t` for all pids. ```text #Example Expected Result pid | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn ------+-----+---------+-----------------------------+------+-----------+---------------+----------- 2963 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | | | 2952 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | | | 2994 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | | | 2995 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | | | 2993 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | | | 2996 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | | | 2997 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | | | (7 rows) ``` ## Related - [Set up high availability](/docs/cmp/get-started/install/high-availability/): Point every node at the shared database. - [Network connectivity](/docs/cmp/system-architecture/network-connectivity/): Open TCP 5432 from each node to the database. - [Appliance security](/docs/cmp/system-architecture/appliance-security/): Other transport encryption and hardening measures.