How to configure External PostgreSQL Database Server to use SSL with Red Hat Satellite 6?

Solution Verified - Updated

Environment

  • Red Hat Satellite 6

Issue

  • Is there any way to configure the External PostgreSQL database for Red Hat Satellite 6 over SSL?
  • How to configure the External PostgreSQL database Server to use SSL to Connect Red Hat Satellite 6 securely?

Resolution

  • First Setup an external PostgreSQL server with dependencies and configure the user/role of database PostgreSQL to connect with Red Hat Satellite 6, Refer to our Installation documentation.

  • Once the setup of the External Database is ready, create Certificates on a Database server to use SSL.

    • Create a private key to sign the Certificate Signing Request (CSR) on the external PostgreSQL server.
     # mkdir /root/postgres
 # openssl genrsa -out /root/postgres/`hostname -f`_cert_key.pem 4096
    • Generate the Certificate Signing Request (CSR) on the external PostgreSQL server.
          # openssl req -new -key `hostname -f`_cert_key.pem -out `hostname -f`_cert_csr.pem

      Country Name (2 letter code) [XX]:
      State or Province Name (full name) []:
      Locality Name (eg, city) [Default City]:
      Organization Name (eg, company) [Default Company Ltd]:
      Organizational Unit Name (eg, section) []:
      Common Name (eg, your name or your server's hostname) []:
      Email Address []:

      Please enter the following 'extra' attributes
      to be sent with your certificate request
      A challenge password []:
      An optional company name []:

      NOTE: Above information will be according to your environment.

  • Send the certificate signing request (CSR) to the Certificate Authority. When submitting the request, specify the lifespan of the certificate.
    NOTE: The method for sending the certificate request varies, so consult the Certificate Authority for the preferred method. In response to the request, you can expect to receive a Certificate Authority bundle and a signed certificate, in separate files.

  • Keep all the required SSL certificates inside /var/opt/rh/rh-postgresql12/lib/pgsql/data/ and change the Permission and Ownership certificates as below.

    • Ensure that PostgreSQL has access to the files and set the private key file permissions to disallow access to the world or group.
        # cp /root/postgres/*pem  /var/opt/rh/rh-postgresql12/lib/pgsql/data/
    # chown postgres:postgres  /var/opt/rh/rh-postgresql12/lib/pgsql/data/*.pem
    # chmod 600  /var/opt/rh/rh-postgresql12/lib/pgsql/data/dbhost.example.com_cert_key.pem

    # ls -l 
    -rw-r--r--. 1 postgres postgres  6120 Apr 28 16:58  CA-Bundle.pem
    -rw-------. 1 postgres postgres  3247 Apr 28 16:58  dbhost.example.com_cert_key.pem
    -rw-r--r--. 1 postgres postgres  1846 Apr 28 16:58  dbhost.example.com_cert.pem

  • Edit /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf configuration file and add the certificates details with SSL flag On. 

     ssl = on
 ssl_ca_file = '/var/opt/rh/rh-postgresql12/lib/pgsql/data/CA-Bundle.pem'
 ssl_cert_file = '/var/opt/rh/rh-postgresql12/lib/pgsql/data/dbhost.example.com_cert.pem'
 ssl_key_file = '/var/opt/rh/rh-postgresql12/lib/pgsql/data/dbhost.example.com_cert_key.pem'

  • Modify pg_hba.conf file, change all records for non-local connections from host to hostssl for require clients to connect over TLS.

        # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # ...
    hostssl all             all             all                     md5

  • Restart the PostgreSQL service to reflect the changes. 

     # systemctl restart rh-postgresql12-postgresql.service

From Red Hat Satellite side

  • To enable the Secure Sockets Layer (SSL) protocol for this external database, add the following options with satellite-installer

    • Copy CA certificates from the External Database server to the Satellite server, the CA root certificate will be used to verify that the client can trust the certificate presented by the server.
      # scp root@dbhost.example.com:/root/postgres/CA-Bundle.pem  /root/

     # cp /root/CA-Bundle.pem /etc/pki/ca-trust/source/anchors/
 # update-ca-trust

    • Run satellite-installer with the below parameters to add Certificates details in satellite configuration. 

       # satellite-installer  --scenario satellite \
 --foreman-db-sslmode verify-full \
 --foreman-db-root-cert  /root/CA-Bundle.pem \
 --katello-candlepin-db-ssl true \
 --katello-candlepin-db-ssl-verify true \
 --foreman-proxy-content-pulpcore-postgresql-ssl true \
 --foreman-proxy-content-pulpcore-postgresql-ssl-root-ca /root/CA-Bundle.pem

Diagnostic Steps

  • To verify the SSL connection from the Red Hat Satellite server to the External Postgresql database server use below command.

        # psql -h dbhost.example.com -p 5432 -U pulp -d pulpcore 
    Password for user pulp: 
    psql (12.9)
    SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
    Type "help" for help.

    pulpcore=> 

    # echo 0| openssl s_client -connect dbhost.example.com:5432 -starttls postgres -CAfile /root/CA-Bundle.pem 
    CONNECTED(00000003)
SBR
Product(s)
Components
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.