SupportoConsoleSviluppatoriInizia una provaContatti

Questo contenuto non è disponibile nella lingua selezionata.

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

  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: 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.

  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

Verification

  • Use the postgres super user to connect to a PostgreSQL server and execute the \conninfo meta command: 

    # 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)

    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 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

  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: 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.

  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

Verification

  • Use the postgres super user to connect to a PostgreSQL server and execute the \conninfo meta command: 

    # 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)

    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
Red Hat logoGithubRedditYoutubeTwitter

Formazione

Prova, acquista e vendi

Community

Informazioni sulla documentazione di Red Hat

Aiutiamo gli utenti Red Hat a innovarsi e raggiungere i propri obiettivi con i nostri prodotti e servizi grazie a contenuti di cui possono fidarsi.

Rendiamo l’open source più inclusivo

Red Hat si impegna a sostituire il linguaggio problematico nel codice, nella documentazione e nelle proprietà web. Per maggiori dettagli, visita ilBlog di Red Hat.

Informazioni su Red Hat

Forniamo soluzioni consolidate che rendono più semplice per le aziende lavorare su piattaforme e ambienti diversi, dal datacenter centrale all'edge della rete.

© 2024 Red Hat, Inc.