Chapter 23. Installing and configuring a PostgreSQL database server by using RHEL system roles
Chapter 23. Installing and configuring a PostgreSQL database server by using RHEL system roles You can use the postgresql RHEL system role to automate the installation and management of the PostgreSQL database server. By default, this role also optimizes PostgreSQL by automatically configuring performance-related settings in the PostgreSQL service configuration files. 23.1. Configuring PostgreSQL with an existing TLS certificate by using the postgresql RHEL system role If your application requires a PostgreSQL database server, you can configure this service with TLS encryption to enable secure communication between the application and the database. By using the postgresql RHEL system role, you can automate this process and remotely install and configure PostgreSQL with TLS encryption. In the playbook, you can use an existing private key and a TLS certificate that was issued by a certificate authority (CA). Note The postgresql role cannot open ports in the firewalld service. To allow remote access to the PostgreSQL server, add a task that uses the firewall RHEL system role to your playbook. Prerequisites You have prepared the control node and the managed nodes . You are logged in to the control node as a user who can run playbooks on the managed nodes. The account you use to connect to the managed nodes has sudo permissions on them. Both the private key of the managed node and the certificate are stored on the control node in the following files: Private key: ~/ <FQDN_of_the_managed_node> .key Certificate: ~/ <FQDN_of_the_managed_node> .crt Procedure Store your sensitive variables in an encrypted file: Create the vault: After the ansible-vault create command opens an editor, enter the sensitive data in the <key> : <value> format: pwd: <password> Save the changes, and close the editor. Ansible encrypts the data in the vault. 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: Create directory for TLS certificate and key ansible.builtin.file: path: /etc/postgresql/ state: directory mode: 755 - name: Copy CA certificate ansible.builtin.copy: src: "~/{{ inventory_hostname }}.crt" dest: "/etc/postgresql/server.crt" - name: Copy private key ansible.builtin.copy: src: "~/{{ inventory_hostname }}.key" dest: "/etc/postgresql/server.key" mode: 0600 - name: PostgreSQL with an existing private key and certificate ansible.builtin.include_role: name: rhel-system-roles.postgresql vars: postgresql_version: "16" postgresql_password: "{{ pwd }}" postgresql_ssl_enable: true postgresql_cert_name: "/etc/postgresql/server" 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: 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_cert_name: <private_key_and_certificate_file> Defines the path and base name of both the certificate and private key on the managed node without .crt and key suffixes. During the PostgreSQL configuration, the role creates symbolic links in the /var/lib/pgsql/data/ directory that refer to these files. The certificate and private key must exist locally on the managed node. You can use tasks with the ansible.builtin.copy module to transfer the files from the control node to the managed node, as shown in the playbook. postgresql_server_conf: <list_of_settings> Defines postgresql.conf settings the role should 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 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. Validate the playbook syntax: Note that this command only validates the syntax and does not protect against a wrong but valid configuration. Run the playbook: Verification Use the postgres super user to connect to a PostgreSQL server and execute the \conninfo meta command: If the output displays a TLS protocol version and cipher details, the connection works and TLS encryption is enabled. Additional resources /usr/share/ansible/roles/rhel-system-roles.postgresql/README.md file /usr/share/doc/rhel-system-roles/postgresql/ directory Ansible vault 23.2. Configuring PostgreSQL with a TLS certificate issued from IdM by using the postgresql RHEL system role If your application requires a PostgreSQL database server, you can configure the PostgreSQL service with TLS encryption to enable secure communication between the application and the database. If the PostgreSQL host is a member of a Red Hat Enterprise Linux Identity Management (IdM) domain, the certmonger service can manage the certificate request and future renewals. By using the postgresql RHEL system role, you can automate this process. You can remotely install and configure PostgreSQL with TLS encryption, and the postgresql role uses the certificate RHEL system role to configure certmonger and request a certificate from IdM. Note The postgresql role cannot open ports in the firewalld service. To allow remote access to the PostgreSQL server, add a task to your playbook that uses the firewall RHEL system role. Prerequisites You have prepared the control node and the managed nodes . You are logged in to the control node as a user who can run playbooks on the managed nodes. The account you use to connect to the managed nodes has sudo permissions on them. You enrolled the managed node in an IdM domain. Procedure Store your sensitive variables in an encrypted file: Create the vault: After the ansible-vault create command opens an editor, enter the sensitive data in the <key> : <value> format: pwd: <password> Save the changes, and close the editor. Ansible encrypts the data in the vault. 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: 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: 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 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. Validate the playbook syntax: Note that this command only validates the syntax and does not protect against a wrong but valid configuration. Run the playbook: Verification Use the postgres super user to connect to a PostgreSQL server and execute the \conninfo meta command: If the output displays a TLS protocol version and cipher details, the connection works and TLS encryption is enabled. Additional resources /usr/share/ansible/roles/rhel-system-roles.postgresql/README.md file /usr/share/doc/rhel-system-roles/postgresql/ directory Ansible vault
ansible-vault create vault.yml New Vault password: <vault_password> Confirm New Vault password: <vault_password> pwd: <password> --- - name: Installing and configuring PostgreSQL hosts: managed-node-01.example.com vars_files: - vault.yml tasks: - name: Create directory for TLS certificate and key ansible.builtin.file: path: /etc/postgresql/ state: directory mode: 755 - name: Copy CA certificate ansible.builtin.copy: src: "~/{{ inventory_hostname }}.crt" dest: "/etc/postgresql/server.crt" - name: Copy private key ansible.builtin.copy: src: "~/{{ inventory_hostname }}.key" dest: "/etc/postgresql/server.key" mode: 0600 - name: PostgreSQL with an existing private key and certificate ansible.builtin.include_role: name: rhel-system-roles.postgresql vars: postgresql_version: "16" postgresql_password: "{{ pwd }}" postgresql_ssl_enable: true postgresql_cert_name: "/etc/postgresql/server" 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: rhel-system-roles.firewall vars: firewall: - service: postgresql state: enabled ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml ansible-playbook --ask-vault-pass ~/playbook.yml psql "postgresql://postgres@managed-node-01.example.com:5432" -c '\conninfo' Password for user postgres: You are connected to database "postgres" as user "postgres" on host "192.0.2.1" at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) ansible-vault create vault.yml New Vault password: <vault_password> Confirm New Vault password: <vault_password> pwd: <password> --- - 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: 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: rhel-system-roles.firewall vars: firewall: - service: postgresql state: enabled ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml ansible-playbook --ask-vault-pass ~/playbook.yml psql "postgresql://postgres@managed-node-01.example.com:5432" -c '\conninfo' Password for user postgres: You are connected to database "postgres" as user "postgres" on host "192.0.2.1" at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)