201 lines
7.5 KiB
Markdown
201 lines
7.5 KiB
Markdown
# Ansible role: Postgresql
|
|
|
|
[](https://code.waks.be/nishiki/ansible-role-postgresql/releases)
|
|
[](https://code.waks.be/nishiki/ansible-role-postgresql/src/branch/main/LICENSE)
|
|
[](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.
|
|
```
|