Ansible Role postgresql

This is an Ansible role to install PostgreSQL on Linux systems, specifically for RHEL/CentOS, Debian, Ubuntu, and SUSE SLES distributions. The main tasks of this role include:

• Installing PostgreSQL
• Configuring PostgreSQL settings and authentication
• Creating users
• Creating databases

Installation

$ ansible-galaxy install mahdi22.postgresql

Role Variables

In variable/main.yml, you can set PostgreSQL configuration settings or use the default ones:

postgresql_config:
  - option: port
    value: 5432
  - option: log_destination
    value: syslog
  - option: log_directory
    value: /var/log/postgresql/

To set or change PostgreSQL authentication settings, you can modify the following:

postgresql_authentication:
  - {type: local, database: all, user: postgres, auth_method: peer}
  - {type: local, database: all, user: all, auth_method: md5}
  - {type: host, database: all, user: all, address: '127.0.0.1/32', auth_method: md5}
  - {type: host, database: all, user: all, address: '::1/128', auth_method: md5}
  - {type: local, database: replication, user: all, auth_method: peer}
  - {type: host, database: replication, user: all, address: '127.0.0.1/32', auth_method: md5}
  - {type: host, database: replication, user: all, address: '::1/128', auth_method: md5}

To create databases and users, you can set the following parameters; if you don't want to create them, set create_users: no and create_databases: no in default/main.yml.

Here’s how to create multiple databases and users:

postgresql_databases_users: []
#postgresql_databases_users:
#  - {database: test, user: user1, userpassword: user1pass, priv: ALL}  # This creates a database 'test' and user 'user1' with password 'user1pass' having ALL privileges
#  - {database: test1, user:'', userpassword: '', priv: ''}              # This creates only the database 'test1'
#  - {database: '', user: user2, userpassword: user2pass, priv: ''}     # This creates only the user 'user2' with password 'user2pass'

• To create only a database, set database: databasename, user:''.
• To create only a user, set database: '', user: username, userpassword: password.
• To create a database with user privileges, set database: databasename, user: username, userpassword: password, priv: privileges.

Basic Configuration

Notes

(1) If the hosts are behind a web proxy, set the following variables in the defaults/main.yml file:

use_proxy: False
proxy_env:
  http_proxy: http://proxy.local:8080/
  https_proxy: http://proxy.local:8080/

Example Playbook

- hosts: dbservers
  roles:
    - role: mahdi22.postgresql
      become: yes

Testing

This role has been tested on the following Linux distributions:

• RHEL/CentOS 8 PostgreSQL versions (9.5, 9.4, 10, 11, 12, 13)
• RHEL/CentOS 7 PostgreSQL versions (9.5, 9.4, 10, 11, 12, 13)
• Debian 10 PostgreSQL versions (9.5, 9.4, 10, 11, 12, 13)
• Debian 9 PostgreSQL versions (9.5, 9.4, 10, 11, 12, 13)
• Ubuntu 20.04 PostgreSQL versions (9.5, 9.4, 10, 11, 12, 13)
• Ubuntu 18.04 PostgreSQL versions (9.5, 9.4, 10, 11, 12, 13)
• Ubuntu 16.04 PostgreSQL versions (9.5, 9.4, 10, 11, 12, 13)
• Suse SLES 12 PostgreSQL versions (9.5, 9.4, 10, 11, 12)
• Suse SLES 15 PostgreSQL versions (11, 12, 13)