class role::backup::postgresql inherits role::backup {
# This manifest is supposed to be part of the backup server
$password_seed = lookup("base_installation::puppet_pass_seed")
$user = lookup("role::backup::user")
$group = lookup("role::backup::group")
$pg_user = "postgres"
$pg_group = "postgres"
$ldap_cn = lookup("base_installation::ldap_cn")
$ldap_password = generate_password(24, $password_seed, "ldap")
$ldap_server = lookup("base_installation::ldap_server")
$ldap_base = lookup("base_installation::ldap_base")
$ldap_dn = lookup("base_installation::ldap_dn")
$pgbouncer_ldap_attribute = "uid"
$pg_slot = regsubst($ldap_cn, '-', "_", "G")
ensure_packages(["postgresql", "pgbouncer", "pam_ldap"])
$pg_backup_hosts = lookup("role::backup::postgresql::backup_hosts", { "default_value" => {} })
$ldap_filter = lookup("role::backup::postgresql::pgbouncer_access_filter", { "default_value" => undef })
unless empty($pg_backup_hosts) {
file { "/etc/systemd/system/postgresql_backup@.service":
mode => "0644",
owner => "root",
group => "root",
content => template("role/backup/postgresql_backup@.service.erb"),
}
unless empty($ldap_filter) {
concat { "/etc/pgbouncer/pgbouncer.ini":
mode => "0644",
owner => "root",
group => "root",
ensure_newline => true,
notify => Service["pgbouncer"],
}
concat::fragment { "pgbouncer_head":
target => "/etc/pgbouncer/pgbouncer.ini",
order => "01",
content => template("role/backup/pgbouncer.ini.erb"),
}
file { "/etc/systemd/system/pgbouncer.service.d":
ensure => "directory",
mode => "0644",
owner => "root",
group => "root",
}
file { "/etc/systemd/system/pgbouncer.service.d/override.conf":
ensure => "present",
mode => "0644",
owner => "root",
group => "root",
content => "[Service]\nUser=\nUser=$pg_user\n",
notify => Service["pgbouncer"],
}
service { "pgbouncer":
ensure => "running",
enable => true,
require => [
Package["pgbouncer"],
File["/etc/systemd/system/pgbouncer.service.d/override.conf"],
Concat["/etc/pgbouncer/pgbouncer.ini"]
],
}
file { "/etc/pam_ldap.d/pgbouncer.conf":
ensure => "present",
mode => "0600",
owner => $pg_user,
group => "root",
content => template("role/backup/pam_ldap_pgbouncer.conf.erb"),
require => File["/etc/pam_ldap.d"],
} ->
file { "/etc/pam.d/pgbouncer":
ensure => "present",
mode => "0644",
owner => "root",
group => "root",
source => "puppet:///modules/role/backup/pam_pgbouncer"
}
}
}
$ldap_attribute = "cn"
file { "/etc/pam_ldap.d":
ensure => directory,
mode => "0755",
owner => "root",
group => "root",
} ->
file { "/etc/pam_ldap.d/postgresql.conf":
ensure => "present",
mode => "0600",
owner => $pg_user,
group => "root",
content => template("profile/postgresql_master/pam_ldap_postgresql.conf.erb"),
} ->
file { "/etc/pam.d/postgresql":
ensure => "present",
mode => "0644",
owner => "root",
group => "root",
source => "puppet:///modules/profile/postgresql_master/pam_postgresql"
}
$pg_backup_hosts.each |$backup_host_cn, $pg_infos| {
$host = find_host($facts["ldapvar"]["other"], $backup_host_cn)
if empty($host) {
$pg_backup_host = $backup_host_cn
} elsif has_key($host["vars"], "host") {
$pg_backup_host = $host["vars"]["host"][0]
} else {
$pg_backup_host = $host["vars"]["real_hostname"][0]
}
$pg_path = "$mountpoint/$pg_backup_host/postgresql"
$pg_backup_path = "$mountpoint/$pg_backup_host/postgresql_backup"
$pg_host = "$pg_backup_host"
$pg_port = $pg_infos["dbport"]
if has_key($host["vars"], "postgresql_backup_port") {
$pg_listen_port = $host["vars"]["postgresql_backup_port"][0]
file { "$pg_path/certs":
ensure => directory,
mode => "0700",
owner => $pg_user,
group => $pg_group,
} ->
ssl::self_signed_certificate { $backup_host_cn:
common_name => $backup_host_cn,
country => "FR",
days => "3650",
organization => "Immae",
owner => $pg_user,
group => $pg_group,
directory => "$pg_path/certs",
before => File["$pg_path/postgresql.conf"],
}
$ssl_key = "$pg_path/certs/$backup_host_cn.key"
$ssl_cert = "$pg_path/certs/$backup_host_cn.crt"
} else {
$pg_listen_port = undef
$ssl_key = undef
$ssl_cert = undef
}
unless empty($host) {
$host["ipHostNumber"].each |$ip| {
$infos = split($ip, "/")
$ipaddress = $infos[0]
if (length($infos) == 1 and $ipaddress =~ /:/) {
$mask = "128"
} elsif (length($infos) == 1) {
$mask = "32"
} else {
$mask = $infos[1]
}
postgresql::server::pg_hba_rule { "allow TCP access for initial replication from $ipaddress/$mask":
type => 'hostssl',
database => 'replication',
user => $backup_host_cn,
address => "$ipaddress/$mask",
auth_method => 'pam',
order => "06-01",
target => "$pg_path/pg_hba.conf",
postgresql_version => "10",
}
}
}
if !empty($ldap_filter) and ($pg_infos["pgbouncer"]) {
if empty($pg_listen_port) {
$pg_listen_port_key = ""
} else {
$pg_listen_port_key = "port=$pg_listen_port"
}
concat::fragment { "pgbouncer_$pg_backup_host":
target => "/etc/pgbouncer/pgbouncer.ini",
order => 02,
content => "${pg_infos[pgbouncer_dbname]} = host=$mountpoint/$pg_backup_host/postgresql $pg_listen_port_key user=${pg_infos[dbuser]} dbname=${pg_infos[dbname]}",
}
postgresql::server::pg_hba_rule { "$pg_backup_host - local access as ${pg_infos[dbuser]} user":
description => "Allow local access to ${pg_infos[dbuser]} user",
type => 'local',
database => $pg_infos["dbname"],
user => $pg_infos["dbuser"],
auth_method => 'trust',
order => "01-00",
target => "$pg_path/pg_hba.conf",
postgresql_version => "10",
}
}
file { "$mountpoint/$pg_backup_host":
ensure => directory,
owner => $user,
group => $group,
}
file { $pg_path:
ensure => directory,
owner => $pg_user,
group => $pg_group,
mode => "0700",
require => File["$mountpoint/$pg_backup_host"],
}
file { $pg_backup_path:
ensure => directory,
owner => $pg_user,
group => $pg_group,
mode => "0700",
require => File["$mountpoint/$pg_backup_host"],
}
cron::job::multiple { "backup_psql_$pg_host":
ensure => "present",
require => [File[$pg_backup_path], File[$pg_path]],
jobs => [
{
command => "/usr/bin/pg_dumpall -h $pg_path -f $pg_backup_path/\$(date -Iseconds).sql",
user => $pg_user,
hour => "22,4,10,16",
minute => 0,
description => "Backup the database",
},
{
command => "/usr/bin/rm -f $(ls -1 $pg_backup_path/*.sql | grep -v 'T22:' | sort -r | sed -e '1,12d')",
user => $pg_user,
hour => 3,
minute => 0,
description => "Cleanup the database backups",
},
{
command => "cd $pg_backup_path ; /usr/bin/rm -f $(ls -1 *T22*.sql | log2rotate --skip 7 --fuzz 7 --delete --format='%Y-%m-%dT%H:%M:%S+02:00.sql')",
user => $pg_user,
hour => 3,
minute => 1,
description => "Cleanup the database backups exponentially",
},
]
}
exec { "pg_basebackup $pg_path":
cwd => $pg_path,
user => $pg_user,
creates => "$pg_path/PG_VERSION",
environment => ["PGPASSWORD=$ldap_password"],
command => "/usr/bin/pg_basebackup -w -h $pg_host -U $ldap_cn -D $pg_path -S $pg_slot",
before => [
Concat["$pg_path/pg_hba.conf"],
Concat["$pg_path/recovery.conf"],
File["$pg_path/postgresql.conf"],
]
}
concat { "$pg_path/pg_hba.conf":
owner => $pg_user,
group => $pg_group,
mode => '0640',
warn => true,
}
postgresql::server::pg_hba_rule { "$pg_backup_host - local access as postgres user":
description => 'Allow local access to postgres user',
type => 'local',
database => 'all',
user => $pg_user,
auth_method => 'ident',
order => "00-01",
target => "$pg_path/pg_hba.conf",
postgresql_version => "10",
}
postgresql::server::pg_hba_rule { "$pg_backup_host - localhost access as postgres user":
description => 'Allow localhost access to postgres user',
type => 'host',
database => 'all',
user => $pg_user,
address => "127.0.0.1/32",
auth_method => 'md5',
order => "00-02",
target => "$pg_path/pg_hba.conf",
postgresql_version => "10",
}
postgresql::server::pg_hba_rule { "$pg_backup_host - localhost ip6 access as postgres user":
description => 'Allow localhost access to postgres user',
type => 'host',
database => 'all',
user => $pg_user,
address => "::1/128",
auth_method => 'md5',
order => "00-03",
target => "$pg_path/pg_hba.conf",
postgresql_version => "10",
}
postgresql::server::pg_hba_rule { "$pg_backup_host - deny access to postgresql user":
description => 'Deny remote access to postgres user',
type => 'host',
database => 'all',
user => $pg_user,
address => "0.0.0.0/0",
auth_method => 'reject',
order => "00-04",
target => "$pg_path/pg_hba.conf",
postgresql_version => "10",
}
postgresql::server::pg_hba_rule { "$pg_backup_host - local access":
description => 'Allow local access with password',
type => 'local',
database => 'all',
user => 'all',
auth_method => 'md5',
order => "10-01",
target => "$pg_path/pg_hba.conf",
postgresql_version => "10",
}
postgresql::server::pg_hba_rule { "$pg_backup_host - local access with same name":
description => 'Allow local access with same name',
type => 'local',
database => 'all',
user => 'all',
auth_method => 'ident',
order => "10-02",
target => "$pg_path/pg_hba.conf",
postgresql_version => "10",
}
$primary_conninfo = "host=$pg_host port=$pg_port user=$ldap_cn password=$ldap_password sslmode=require"
$primary_slot_name = regsubst($ldap_cn, '-', "_", "G")
$standby_mode = "on"
concat { "$pg_path/recovery.conf":
owner => $pg_user,
group => $pg_group,
mode => '0640',
warn => true,
}
concat::fragment { "$pg_path/recovery.conf":
target => "$pg_path/recovery.conf",
content => template('postgresql/recovery.conf.erb'),
}
file { "$pg_path/postgresql.conf":
owner => $pg_user,
group => $pg_group,
mode => '0640',
content => template("role/backup/postgresql.conf.erb"),
}
service { "postgresql_backup@$pg_backup_host":
enable => true,
ensure => "running",
require => [
File["/etc/systemd/system/postgresql_backup@.service"],
Concat["$pg_path/pg_hba.conf"],
Concat["$pg_path/recovery.conf"],
File["$pg_path/postgresql.conf"],
],
subscribe => [
Concat["$pg_path/pg_hba.conf"],
Concat["$pg_path/recovery.conf"],
File["$pg_path/postgresql.conf"],
]
}
}
}