Chapter 21. Installing and configuring PostgreSQL by using the postgresql RHEL system role

You can set the version of the PostgreSQL server to any released and supported version of PostgreSQL on RHEL 8 and RHEL 9 managed nodes. For example:

postgresql_version: "13"

Optionally, you can set a password for the postgres database superuser. By default, no password is set, and a database is accessible from the postgres system account through a UNIX socket. It is recommended to encrypt the password by using Ansible Vault. For example:

postgresql_password: !vault |
      	$ANSIBLE_VAULT;1.2;AES256;dev
      	....

The content of the postgresql_pg_hba_conf variable replaces the default upstream configuration in the /var/lib/pgsql/data/pg_hba.conf file. For example:

postgresql_pg_hba_conf:
  - type: local
    database: all
    user: all
    auth_method: peer
  - type: host
    database: all
    user: all
    address: '127.0.0.1/32'
    auth_method: ident
  - type: host
    database: all
    user: all
    address: '::1/128'
    auth_method: ident

The content of the postgresql_server_conf variable is added to the end of the /var/lib/pgsql/data/postgresql.conf file. As a result, the default settings are overwritten. For example:

postgresql_server_conf:
  ssl: on
  shared_buffers: 128MB
  huge_pages: try

To set up an SSL/TLS connection, set the postgresql_ssl_enable variable to true:

postgresql_ssl_enable: true

and use one of the following approaches to provide a server certificate and a private key:

If you want to use your own certificate and private key, use the postgresql_cert_name variable to specify the certificate name. You must keep both certificate and key files in the same directory and under the same name with the .crt and .key suffixes.

For example, if your certificate file is located in /etc/certs/server.crt and your private key in /etc/certs/server.key, set the postgresql_cert_name value to:

postgresql_cert_name: /etc/certs/server

The postgresql_certificates variable requires a list of dict in the same format as used by the redhat.rhel_system_roles.certificate role. Specify the postgresql_certificates variable if you want the certificate role to generate certificates for the PostgreSQL server configured by the PostgreSQL role. In the following example, a self-signed certificate postgresql_cert.crt is generated in the /etc/pki/tls/certs/ directory. By default, no certificates are automatically generated ([]).

postgresql_certificates:
  - name: postgresql_cert
    dns: ['localhost', 'www.example.com']
    ca: self-sign

To run an SQL script, define a path to your SQL file by using the postgresql_input_file variable:

postgresql_input_file: "/tmp/mypath/file.sql"

By default, the PostgreSQL system role enables server settings optimization based on system resources. To disable the tuning, set the postgresql_server_tuning variable to false:

postgresql_server_tuning: false