Automating SSL implementation in PostgreSQL

Here is quick Ansible playbook to enable SSL and creating self-signed certificate. By default SSL is not on, certificate and key file is controlled by parameter ssl_cert_file = ‘server.crt’ and ssl_key_file = ‘server.key’, therefore I am creating with same name, but you can change as per your choice. The step would be to create self-signed certificate and then set the ssl=on parameter as well as modify the pg_hba.conf rule as per your requirement, In my case I have enabled SSL for external user, replication communication is non-ssl based. For reference you can use below task to cater your requirement. I have not included as requirement could be very different. So you can combine both of them as per your requirement or you may have to write task to modify existing rule as per the requirement. After all this modification you need to restart database which is included in last task.

- name: Configuring pg_hba.conf
  blockinfile:
        insertbefore: EOF
        create: yes
        marker_begin: ANSIBLE PostgreSQL HBA START
        marker_end: ANSIBLE PostgreSQL HBA END
        backup: yes
        path: /u00/app/postgres/12/data/pg_hba.conf
        block: |
                hostssl    all             all             0.0.0.0/0          md5
                local   all             postgres                           ident
                host    replication     replication     {{ ansible_default_ipv4.address }}/32    trust
                host    replication     postgres        {{ ansible_default_ipv4.address }}/32    trust
  become: yes
  become_user: postgres
- name: PostgreSQL SSL
  become: true
  become_user: postgres
  tasks:
        - openssl_privatekey:
                path: "/u00/app/postgres/12/data/server.key"
                mode: 0400
                type: RSA
                size: 2048

        - openssl_csr:
                path: "/u00/app/postgres/12/data/server.req"
                privatekey_path: "/u00/app/postgres/12/data/server.key"
                country_name: "UK"
                state_or_province_name: "London"
                organization_name: "YOUR-company"
                organizational_unit_name: "ANYTHING-YOU-WANT-TO-IDENTIFY"
                mode: 0444

        - openssl_certificate:
                path: "/u00/app/postgres/12/data/server.crt"
                csr_path: "/u00/app/postgres/12/data/server.req"
                privatekey_path: "/u00/app/postgres/12/data/server.key"
                provider: selfsigned
                invalid_at: YYYYmmddHHMMSS
                invalid_at: 20200310235959
                mode: 0444

        - name: Setting PostgreSQL parameters
          postgresql_set:
                name: "{{ item.name }}"
                value: "{{ item.value }}"
          loop:
                - { name: 'ssl', value: on }
          become: yes
          become_user: postgres

        - name: Restarting database
          shell: 'source /u00/home/postgres/.bash_profile ; /opt/rh/rh-postgresql12/root/usr/bin/pg_ctl restart -D /u00/app/postgres/12/data'
          become: yes
          become_user: postgres