Use SSL with an external PostgreSQL database

Encrypt the connection between Cloud Management Platform (CMP) and an external PostgreSQL database

  4 minute read  

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.

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

  2. 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
    2. 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
      2. Uncomment ssl_key_file and set the value to the private key filename

    Example /var/lib/pgsql/14/data/postgresql.conf

    # - 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

  3. 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

      # 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

  4. Restart the database

    systemctl restart postgresql-14

  5. Verify SSL is configured properly by connecting to the database using the psql command below:

    #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

    # 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.

CMP database configuration

  1. Add the following files to the CMP database directory: /var/opt/cloudbolt/proserv/db

    • server.crt
    • server.key
    • root.crt

  2. Change ownership of all three files to cloudbolt:cloudbolt with the following commands:

    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

  3. 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

      #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",
        },
    }
}

  4. In a web browser, log in to the CMP UI and go to a couple of different pages.

  5. On the database server, connect to the database using psql and run the following query.

    #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.

      #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)