Ansible role: Postgresql

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

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
• 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.