ansible-role-postgresql/README.md

# Ansible role: Postgresql

[![Version](https://img.shields.io/badge/latest_version-1.0.0-green.svg)](https://code.waks.be/nishiki/ansible-role-postgresql/releases)
[![License](https://img.shields.io/badge/license-Apache--2.0-blue.svg)](https://code.waks.be/nishiki/ansible-role-postgresql/src/branch/main/LICENSE)
[![Build](https://code.waks.be/nishiki/ansible-role-postgresql/actions/workflows/molecule.yml/badge.svg?branch=main)](https://code.waks.be/nishiki/ansible-role-postgresql/actions?workflow=molecule.yml)

Install and configure Postgresql

## Requirements

- Ansible >= 2.10
- Debian
  - Bullseye
  - Bookworm

## Role variables

| Name                               | Type  | Required | Default | Comment                                             |
| ---------------------------------- | ----- | -------- | ------- | --------------------------------------------------- |
| postgresql_version                 | str   | no       | 15      | the major version to use                            |
| postgresql_use_official_repository | bool  | no       | false   | set true if you want official postgresql repository |
| postgresql_primary                 | bool  | no       | false   | set true to create user and database on this server |
| postgresql_config                  | dict  | no       |         | add option config file                              |
| postgresql_databases               | array | no       |         | the database to manage                              |
| postgresql_users                   | dict  | no       |         | the users to manage                                 |
| postgresql_privileges              | array | no       |         | set the privileges for roles                        |
| postgresql_hba                     | array | no       |         | the hba authorizations                              |
| postgresql_role_memberships        | dict  | no       |         | add membership in a role group                      |

### postgresql_config

See all options on [postgresql documentation](https://www.postgresql.org/docs/current/runtime-config.html)

Example:

```
  listen_addresses: '*'
  port: 5432
  ssl: true
  unix_socket_directories: /var/run/postgresql
```

### postgresql_databases

| Name       | Type | Required | Default   | Comment                           |
| ---------- | ---- | -------- | --------- | --------------------------------- |
| name       | str  | yes      |           | the database name                 |
| owner      | str  | yes      |           | the owner's database name         |
| encoding   | str  | no       | UTF-8     | encoding to use                   |
| lc_collate | str  | no       | C.UTF-8   | lc_collate to use                 |
| lc_ctype   | str  | no       | C.UTF-8   | lc_ctype to use                   |
| template   | str  | no       | template0 | template to use                   |
| state      | str  | no       | present   | if absent the database is deleted |

Example:

```
  - name: test
    owner: user
    encoding: UTF-8
    state: present
```

### postgresql_users

| Name       | Type  | Required | Default | Comment                                                       |
| ---------- | ----- | -------- | ------- | ------------------------------------------------------------- |
| key        | str   | yes      |         | the user name                                                 |
| password   | str   | yes      |         | the password to logon                                         |
| attributes | array | no       |         | list with the user's attributes (REPLICATION, SUPERUSER, ...) |
| state      | str   | no       | present | if absent the user is deleted                                 |

Example:

```
  user1:
    password: secret
    attributes:
      - REPLICATION
    state: present
```

### postgresql_privileges

| Name       | Type  | Required | Default           | Comment                                                     |
| ---------- | ----- | -------- | ----------------- | ----------------------------------------------------------- |
| roles      | str   | yes      |                   | the roles with this privileges                              |
| database   | str   | yes      |                   | the database where to apply the privileges                  |
| type       | str   | no       | table             | the type of privileges (table, function, schema, ...)       |
| objets     | array | yes      | ["ALL_IN_SCHEMA"] | list with the objets (ex: table name)                       |
| privileges | array | yes      |                   | list with the privileges (SELECT, UPDATE, INSERT, ALL, ...) |
| state      | str   | no       | present           | if absent the privileges is deleted                         |

Example:

```
  - role: user1
    database: test
    type: table
    objets:
      - mytable
    privileges:
      - SELECT
      - INSERT
    state: present
```

### postgresql_hba

| Name      | Type  | Required | Default | Comment                                     |
| --------- | ----- | -------- | ------- | ------------------------------------------- |
| type      | str   | yes      |         | connection type (host, hostssl, local)      |
| databases | array | yes      |         | array with the database names or all string |
| users     | array | yes      |         | array with the user names or all string     |
| subnet    | str   | no       |         | allow subnet, not require if type is local  |
| method    | str   | yes      |         | auth method (trust, md5, ...)               |

Example:

```
  - type: hostssl
    databases:
      - myappli
    users: all
    subnet: samenet
    method: md5
```

### postgresql_role_memberships

| Name   | Type  | Required | Default | Comment                                  |
| ------ | ----- | -------- | ------- | ---------------------------------------- |
| key    | str   | yes      |         | the role name                            |
| groups | array | yes      |         | the groups (roles) where to add the role |
| state  | str   | no       | present | if absent the memberships are deleted    |

Example:

```
  postgres:
    groups:
      - myuser
    state: present
```

## How to use

```
- hosts: server
  roles:
    - postgresql
  vars:
    postgresql_use_official_repository: true
    postgresql_config:
      listen_addresses: '*'
    postgresql_databases:
      - name: test
    postgresql_users
      user1:
        password: secret
    postgresql_privilges:
      - role: user1
        database: test
        type: table
        privileges:
          - SELECT
      - role: user1
        database: test
        type: table
        objets:
          - table1
        privileges:
          - INSERT
```

## Development

### Test with molecule and docker

- install [docker](https://docs.docker.com/engine/installation/)
- install `python3` and `python3-pip`
- install molecule and dependencies `pip3 install molecule molecule-docker docker ansible-lint testinfra yamllint`
- run `molecule test`

## License

```
Copyright (c) 2021 Adrien Waksberg

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
```