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