Getting started
General configuration
On a new host, PostgreSQL server will be configured with a default cluster
main running on port 5432.
The cluster will only listen to connections from localhost.
The ansible management account and the root account will be able to login
to the PostgreSQL server using the postgres role. For example:
user@host:~$ psql -U postgres
The PostgreSQL version installed by the role will be a default version offered by the distribution. If you want PostgreSQL 9.4 on Debian Wheezy, or an upstream version of the server, you can enable the upstream APT repository from inventory, by adding:
postgresql_server__upstream: True
Check postgresql_server__preferred_version to learn more about selecting custom PostgreSQL versions.
After installation you can use debops.postgresql role to configure PostgreSQL roles and databases.
Remote access to the database
By default, PostgreSQL listens only for connections on localhost.
To enable remote access, you need to change the
postgresql_server__listen_addresses list to specify either IP
addresses of the interfaces you want your host to listen on,
or * for all interfaces.
As the firewall blocks all connections to the PostgreSQL server by default, you will also need to specify IP addresses or CIDR subnets which should be able to connect to the clusters. Example configuration of variables in inventory:
postgresql_server__listen_addresses: [ '*' ]
postgresql_server__allow: [ '192.0.2.0/24', '2001:db8::/32' ]
The default set of Host-Based Authentication rules permit connections from remote hosts that are in the same subnet as the server, only over SSL, and require the correct password to be provided to accept connections.
If you want to allow connections from other subnets than the server, you will need to add your own HBA entries to the PostgreSQL cluster configuration. Example for the default cluster:
postgresql_server__cluster_main:
name: 'main'
port: '5432'
hba:
- type: 'hostssl'
database: 'samerole'
user: 'all'
address: [ '192.0.2.0/24', '2001:db8::/32' ]
method: 'md5'
The debops.postgresql_server role is designed to use the PKI infrastructure
managed by the debops.pki role.
A pki realm can be picked with the postgresql_server__pki_realm
variable, then remote connections to the server will be automatically
encrypted over SSL, otherwise dropped by default.
Example inventory
To install and configure PostgreSQL server on a host, you need to add the host
to the [debops_service_postgresql_server] Ansible host group:
[debops_service_postgresql_server]
hostname
Example playbook
Here's an example playbook which uses the debops.postgresql_server role:
---
- name: Manage PostgreSQL server
collections: [ 'debops.debops', 'debops.roles01',
'debops.roles02', 'debops.roles03' ]
hosts: [ 'debops_service_postgresql_server' ]
become: True
environment: '{{ inventory__environment | d({})
| combine(inventory__group_environment | d({}))
| combine(inventory__host_environment | d({})) }}'
roles:
- role: keyring
tags: [ 'role::keyring', 'skip::keyring', 'role::postgresql_server' ]
keyring__dependent_apt_keys:
- '{{ postgresql_server__keyring__dependent_apt_keys }}'
- role: apt_preferences
tags: [ 'role::apt_preferences', 'skip::apt_preferences' ]
apt_preferences__dependent_list:
- '{{ postgresql_server__apt_preferences__dependent_list }}'
- role: etc_services
tags: [ 'role::etc_services', 'skip::etc_services' ]
etc_services__dependent_list:
- '{{ postgresql_server__etc_services__dependent_list }}'
- role: ferm
tags: [ 'role::ferm', 'skip::ferm' ]
ferm__dependent_rules:
- '{{ postgresql_server__ferm__dependent_rules }}'
- role: python
tags: [ 'role::python', 'skip::python', 'role::postgresql' ]
python__dependent_packages3:
- '{{ postgresql_server__python__dependent_packages3 }}'
python__dependent_packages2:
- '{{ postgresql_server__python__dependent_packages2 }}'
- role: locales
tags: [ 'role::locales', 'skip::locales' ]
locales__dependent_list:
- '{{ postgresql_server__locales__dependent_list }}'
- role: postgresql_server
tags: [ 'role::postgresql_server', 'skip::postgresql_server' ]