Red Hat Summit Red Hat SummitSupportKonsoleEntwicklerDemo startenKontakt

Dieser Inhalt ist in der von Ihnen ausgewählten Sprache nicht verfügbar.

Chapter 21. 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.

You can configure PostgreSQL with TLS encryption using the postgresql RHEL system role to automate secure database setup with existing certificates and private keys.

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>
      Copy to Clipboard Toggle word wrap

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

      pwd: <password>
      Copy to Clipboard Toggle word wrap
    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: redhat.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: redhat.rhel_system_roles.firewall
      vars:
        firewall:
          - service: postgresql
            state: enabled
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

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)
    Copy to Clipboard Toggle word wrap

    If the output displays a TLS protocol version and cipher details, the connection works and TLS encryption is enabled.

You can configure PostgreSQL with TLS encryption using the postgresql RHEL system role to automate secure database setup with certificates issued from Identity Management (IdM) and managed by the certmonger service.

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>
      Copy to Clipboard Toggle word wrap

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

      pwd: <password>
      Copy to Clipboard Toggle word wrap
    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
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

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)
    Copy to Clipboard Toggle word wrap

    If the output displays a TLS protocol version and cipher details, the connection works and TLS encryption is enabled.

Nach oben
Red Hat logoGithubredditYoutubeTwitter

Lernen

Testen, kaufen und verkaufen

Communitys

Über Red Hat Dokumentation

Wir helfen Red Hat Benutzern, mit unseren Produkten und Diensten innovativ zu sein und ihre Ziele zu erreichen – mit Inhalten, denen sie vertrauen können. Entdecken Sie unsere neuesten Updates.

Mehr Inklusion in Open Source

Red Hat hat sich verpflichtet, problematische Sprache in unserem Code, unserer Dokumentation und unseren Web-Eigenschaften zu ersetzen. Weitere Einzelheiten finden Sie in Red Hat Blog.

Über Red Hat

Wir liefern gehärtete Lösungen, die es Unternehmen leichter machen, plattform- und umgebungsübergreifend zu arbeiten, vom zentralen Rechenzentrum bis zum Netzwerkrand.

Theme

© 2025 Red Hat