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