Ansible Role: PostgreSQL

This role installs and sets up the PostgreSQL server on Debian/Ubuntu servers.

Requirements

No special requirements. However, this role needs root access. To run it, use become: true in your playbook or call the role like this:

- hosts: database
  become: true
  roles:
    - role: mivek.postgresql

Role Variables

Here are the available variables with their default values (see defaults/main.yml):

• postgresql_restarted_state: "restarted"
  Defines how the service behaves when configuration changes occur. Use restarted or reloaded.

• postgresql_user: postgres
  The user under which PostgreSQL runs.

• postgresql_group: postgres
  The group under which PostgreSQL runs.

• postgresql_unix_socket_directories:

  • /var/run/postgresql
    Directories where PostgreSQL's socket will be created (can have more than one).

• postgresql_service_state: started
  Controls whether the PostgreSQL service is running.

• postgresql_service_enabled: true
  Determines if PostgreSQL starts at boot.

• postgresql_auth_method: scram-sha-256
  Authentication method. Can be scram-sha-256 or md5.

• postgresql_global_config_options:

  • option: unix_socket_directories
    value: '{{ postgresql_unix_socket_directories | join(",") }}'
  • option: log_directory
    value: 'log'
  • option: password_encryption
    value: "{{ postgresql_auth_method }}"
    Global options for postgresql.conf. For older PostgreSQL versions (before 9.3), set the option to unix_socket_directory.

• postgresql_hba_entries:

  • { type: local, database: all, user: postgres, auth_method: peer }
  • { type: local, database: all, user: all, auth_method: peer }
  • { 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: host, database: all, user: all, addresses: ['10.0.0.1/32', '10.0.0.2/32'], auth_method: md5 }
    Sets up authentication entries in pg_hba.conf. Required options include type, database, user, auth_method, etc.

• postgresql_locales:

  • 'en_US.UTF-8'
    (Debian/Ubuntu only) Used for the locales of PostgreSQL databases.

• postgresql_databases:

  • name: exampledb # required; others are optional
    lc_collate:
    lc_ctype:
    encoding:
    template:
    login_host:
    login_password:
    login_user:
    login_unix_socket:
    port:
    owner:
    state:
    A list of databases to ensure exist. Only name is required.

• postgresql_users:

  • name: jdoe # required; others are optional
    password:
    encrypted:
    priv:
    role_attr_flags:
    db:
    login_host:
    login_password:
    login_user:
    login_unix_socket:
    port:
    state:
    A list of users to ensure exist. Only name is needed.

• postgresql_privs:

  • database: "{{ item.database }}"
    login_host: "{{ item.login_host | default('localhost') }}"
    login_password: "{{ item.login_password | default(omit) }}"
    login_user: "{{ item.login_user | default(postgresql_user) }}"
    login_unix_socket: "{{ item.login_unix_socket | default(postgresql_unix_socket_directories[0]) }}"
    objs: "{{ item.objs | default(omit) }}"
    privs: "{{ item.privs | default(omit) }}"
    roles: "{{ item.roles }}"
    schema: "{{ item.schema | default(omit) }}"
    type: "{{ item.type | default(omit) }}"
    state: "{{ item.state | default('present') }}"
    A list of privileges to ensure exist. Only database and roles are required.

• postgresql_pgpass_users:

  • hostname: localhost
    port: 5432
    database: db1
    name: jdoe
    A list of users to add to the pgpass. Passwords are optional.

• postgresql_users_no_log: true
  Whether to hide user data when managing users.

• postgresql_privs_no_log: true
  Whether to hide privilege data when managing privileges.

• postgresql_version: [OS-specific]

• postgresql_data_dir: [OS-specific]

• postgresql_bin_path: [OS-specific]

• postgresql_config_path: [OS-specific]

• postgresql_daemon: [OS-specific]

• postgresql_packages: [OS-specific]
  These are OS-specific variables defined in this role's vars directory and should not be changed unless necessary.

Dependencies

None.

Example Playbook

- hosts: database
  become: true
  roles:
    - mivek.postgresql

Inside vars/main.yml:

postgresql_databases:
  - name: example_db
postgresql_users:
  - name: example_user
    password: supersecure

License

MIT / BSD

Author Information

This role was created in 2016 by Jeff Geerling, author of Ansible for DevOps.