21.2. Configuring PostgreSQL with a TLS certificate issued from IdM by using the postgresql RHEL system role

Procedure

1. Store your sensitive variables in an encrypted file:

   1. Create the vault:

      $ ansible-vault create ~/vault.yml
      New Vault password: <vault_password>
      Confirm New Vault password: <vault_password>

   2. After the ansible-vault create command opens an editor, enter the sensitive data in the <key>: <value> format:

      pwd: <password>

   3. Save the changes, and close the editor. Ansible encrypts the data in the vault.

2. Create a playbook file, for example, ~/playbook.yml, with the following content:

   ---
   - name: Installing and configuring PostgreSQL
     hosts: managed-node-01.example.com
     vars_files:
       - ~/vault.yml
     tasks:
       - name: PostgreSQL with certificates issued by IdM
         ansible.builtin.include_role:
           name: redhat.rhel_system_roles.postgresql
         vars:
           postgresql_version: "16"
           postgresql_password: "{{ pwd }}"
           postgresql_ssl_enable: true
           postgresql_certificates:
             - name: postgresql_cert
               dns: "{{ inventory_hostname }}"
               ca: ipa
               principal: "postgresql/{{ inventory_hostname }}@EXAMPLE.COM"
           postgresql_server_conf:
             listen_addresses: "'*'"
             password_encryption: scram-sha-256
           postgresql_pg_hba_conf:
             - type: local
               database: all
               user: all
               auth_method: scram-sha-256
             - type: hostssl
               database: all
               user: all
               address: '127.0.0.1/32'
               auth_method: scram-sha-256
             - type: hostssl
               database: all
               user: all
               address: '::1/128'
               auth_method: scram-sha-256
             - type: hostssl
               database: all
               user: all
               address: '192.0.2.0/24'
               auth_method: scram-sha-256
   
   
       - name: Open the PostgresQL port in firewalld
         ansible.builtin.include_role:
           name: redhat.rhel_system_roles.firewall
         vars:
           firewall:
             - service: postgresql
               state: enabled

   The settings specified in the example playbook include the following:

   postgresql_version: <version>

   Sets the version of PostgreSQL to install. The version you can set depends on the PostgreSQL versions that are available in Red Hat Enterprise Linux running on the managed node.

   You cannot upgrade or downgrade PostgreSQL by changing the postgresql_version variable and running the playbook again.

   postgresql_password: <password>

   Sets the password of the postgres database superuser.

   You cannot change the password by changing the postgresql_password variable and running the playbook again.

   postgresql_certificates: <certificate_role_settings>

   A list of YAML dictionaries with settings for the certificate role.

   postgresql_server_conf: <list_of_settings>

   Defines postgresql.conf settings you want the role to set. The role adds these settings to the /etc/postgresql/system-roles.conf file and includes this file at the end of /var/lib/pgsql/data/postgresql.conf. Consequently, settings from the postgresql_server_conf variable override settings in /var/lib/pgsql/data/postgresql.conf.

   Re-running the playbook with different settings in postgresql_server_conf overwrites the /etc/postgresql/system-roles.conf file with the new settings.

   postgresql_pg_hba_conf: <list_of_authentication_entries>

   Configures client authentication entries in the /var/lib/pgsql/data/pg_hba.conf file. For details, see the PostgreSQL documentation.

   The example allows the following connections to PostgreSQL:

   • Unencrypted connections by using local UNIX domain sockets.
   • TLS-encrypted connections to the IPv4 and IPv6 localhost addresses.
   • TLS-encrypted connections from the 192.0.2.0/24 subnet. Note that access from remote addresses is only possible if you also configure the listen_addresses setting in the postgresql_server_conf variable appropriately.

   Re-running the playbook with different settings in postgresql_pg_hba_conf overwrites the /var/lib/pgsql/data/pg_hba.conf file with the new settings.

   For details about all variables used in the playbook, see the /usr/share/ansible/roles/rhel-system-roles.postgresql/README.md file on the control node.

3. Validate the playbook syntax:

   $ ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml

   Note that this command only validates the syntax and does not protect against a wrong but valid configuration.

4. Run the playbook:

   $ ansible-playbook --ask-vault-pass ~/playbook.yml