modules/role/manifests/backup/postgresql.pp

   1 class role::backup::postgresql inherits role::backup {
   2   $password_seed = lookup("base_installation::puppet_pass_seed")
   3
   4   $user = lookup("role::backup::user")
   5   $group = lookup("role::backup::group")
   6   $pg_user = "postgres"
   7   $pg_group = "postgres"
   8
   9   $ldap_cn = lookup("base_installation::ldap_cn")
  10   $ldap_password = generate_password(24, $password_seed, "ldap")
  11   $ldap_server = lookup("base_installation::ldap_server")
  12   $ldap_base   = lookup("base_installation::ldap_base")
  13   $ldap_dn     = lookup("base_installation::ldap_dn")
  14   $pgbouncer_ldap_attribute = "uid"
  15
  16   $pg_slot = regsubst($ldap_cn, '-', "_", "G")
  17
  18   ensure_packages(["postgresql", "pgbouncer", "pam_ldap"])
  19
  20   $pg_backup_hosts = lookup("role::backup::postgresql::backup_hosts", { "default_value" => {} })
  21   $ldap_filter = lookup("role::backup::postgresql::pgbouncer_access_filter", { "default_value" => undef })
  22
  23   unless empty($pg_backup_hosts) {
  24     file { "/etc/systemd/system/postgresql_backup@.service":
  25       mode    => "0644",
  26       owner   => "root",
  27       group   => "root",
  28       content => template("role/backup/postgresql_backup@.service.erb"),
  29     }
  30
  31     unless empty($ldap_filter) {
  32       concat { "/etc/pgbouncer/pgbouncer.ini":
  33         mode           => "0644",
  34         owner          => "root",
  35         group          => "root",
  36         ensure_newline => true,
  37         notify         => Service["pgbouncer"],
  38       }
  39
  40       concat::fragment { "pgbouncer_head":
  41         target  => "/etc/pgbouncer/pgbouncer.ini",
  42         order   => "01",
  43         content => template("role/backup/pgbouncer.ini.erb"),
  44       }
  45
  46       file { "/etc/systemd/system/pgbouncer.service.d":
  47         ensure => "directory",
  48         mode   => "0644",
  49         owner  => "root",
  50         group  => "root",
  51       }
  52
  53       file { "/etc/systemd/system/pgbouncer.service.d/override.conf":
  54         ensure  => "present",
  55         mode    => "0644",
  56         owner   => "root",
  57         group   => "root",
  58         content => "[Service]\nUser=\nUser=$pg_user\n",
  59         notify  => Service["pgbouncer"],
  60       }
  61
  62       service { "pgbouncer":
  63         ensure  => "running",
  64         enable  => true,
  65         require => [
  66           Package["pgbouncer"],
  67           File["/etc/systemd/system/pgbouncer.service.d/override.conf"],
  68           Concat["/etc/pgbouncer/pgbouncer.ini"]
  69         ],
  70       }
  71
  72       file { "/etc/pam_ldap.d/pgbouncer.conf":
  73         ensure  => "present",
  74         mode    => "0600",
  75         owner   => $pg_user,
  76         group   => "root",
  77         content => template("role/backup/pam_ldap_pgbouncer.conf.erb"),
  78         require => File["/etc/pam_ldap.d"],
  79       } ->
  80       file { "/etc/pam.d/pgbouncer":
  81         ensure => "present",
  82         mode   => "0644",
  83         owner  => "root",
  84         group  => "root",
  85         source => "puppet:///modules/role/backup/pam_pgbouncer"
  86       }
  87     }
  88   }
  89
  90   $ldap_attribute = "cn"
  91
  92   file { "/etc/pam_ldap.d":
  93     ensure => directory,
  94     mode   => "0755",
  95     owner  => "root",
  96     group  => "root",
  97   } ->
  98   file { "/etc/pam_ldap.d/postgresql.conf":
  99     ensure  => "present",
 100     mode    => "0600",
 101     owner   => $pg_user,
 102     group   => "root",
 103     content => template("profile/postgresql_master/pam_ldap_postgresql.conf.erb"),
 104   } ->
 105   file { "/etc/pam.d/postgresql":
 106     ensure => "present",
 107     mode   => "0644",
 108     owner  => "root",
 109     group  => "root",
 110     source => "puppet:///modules/profile/postgresql_master/pam_postgresql"
 111   }
 112
 113   $pg_backup_hosts.each |$backup_host_cn, $pg_infos| {
 114     $host = find_host($facts["ldapvar"]["other"], $backup_host_cn)
 115     if empty($host) {
 116       $pg_backup_host = $backup_host_cn
 117     } elsif has_key($host["vars"], "host") {
 118       $pg_backup_host = $host["vars"]["host"][0]
 119     } else {
 120       $pg_backup_host = $host["vars"]["real_hostname"][0]
 121     }
 122
 123     $pg_path = "$mountpoint/$pg_backup_host/postgresql"
 124     $pg_backup_path = "$mountpoint/$pg_backup_host/postgresql_backup"
 125     $pg_host = "$pg_backup_host"
 126     $pg_port = $pg_infos["dbport"]
 127
 128     if has_key($host["vars"], "postgresql_backup_port") {
 129       $pg_listen_port = $host["vars"]["postgresql_backup_port"][0]
 130       file { "$pg_path/certs":
 131         ensure => directory,
 132         mode   => "0700",
 133         owner  => $pg_user,
 134         group  => $pg_group,
 135       } ->
 136       ssl::self_signed_certificate { $backup_host_cn:
 137         common_name  => $backup_host_cn,
 138         country      => "FR",
 139         days         => "3650",
 140         organization => "Immae",
 141         owner        => $pg_user,
 142         group        => $pg_group,
 143         directory    => "$pg_path/certs",
 144         before       => File["$pg_path/postgresql.conf"],
 145       }
 146       $ssl_key  = "$pg_path/certs/$backup_host_cn.key"
 147       $ssl_cert = "$pg_path/certs/$backup_host_cn.crt"
 148     } else {
 149       $pg_listen_port = undef
 150       $ssl_key = undef
 151       $ssl_cert = undef
 152     }
 153
 154
 155     unless empty($host) {
 156       $host["ipHostNumber"].each |$ip| {
 157         $infos = split($ip, "/")
 158         $ipaddress = $infos[0]
 159         if (length($infos) == 1 and $ipaddress =~ /:/) {
 160           $mask = "128"
 161         } elsif (length($infos) == 1) {
 162           $mask = "32"
 163         } else {
 164           $mask = $infos[1]
 165         }
 166
 167         postgresql::server::pg_hba_rule { "allow TCP access for initial replication from $ipaddress/$mask":
 168           type        => 'hostssl',
 169           database    => 'replication',
 170           user        => $backup_host_cn,
 171           address     => "$ipaddress/$mask",
 172           auth_method => 'pam',
 173           order       => "06-01",
 174           target      => "$pg_path/pg_hba.conf",
 175           postgresql_version => "10",
 176         }
 177       }
 178     }
 179
 180     if !empty($ldap_filter) and ($pg_infos["pgbouncer"]) {
 181       if empty($pg_listen_port) {
 182         $pg_listen_port_key = ""
 183       } else {
 184         $pg_listen_port_key = "port=$pg_listen_port"
 185       }
 186
 187       concat::fragment { "pgbouncer_$pg_backup_host":
 188         target  => "/etc/pgbouncer/pgbouncer.ini",
 189         order   => 02,
 190         content => "${pg_infos[pgbouncer_dbname]} = host=$mountpoint/$pg_backup_host/postgresql $pg_listen_port_key user=${pg_infos[dbuser]} dbname=${pg_infos[dbname]}",
 191       }
 192
 193       postgresql::server::pg_hba_rule { "$pg_backup_host - local access as ${pg_infos[dbuser]} user":
 194         description => "Allow local access to ${pg_infos[dbuser]} user",
 195         type        => 'local',
 196         database    => $pg_infos["dbname"],
 197         user        => $pg_infos["dbuser"],
 198         auth_method => 'trust',
 199         order       => "01-00",
 200         target      => "$pg_path/pg_hba.conf",
 201         postgresql_version => "10",
 202       }
 203     }
 204
 205     file { "$mountpoint/$pg_backup_host":
 206       ensure => directory,
 207       owner  => $user,
 208       group  => $group,
 209     }
 210
 211     file { $pg_path:
 212       ensure  => directory,
 213       owner   => $pg_user,
 214       group   => $pg_group,
 215       mode    => "0700",
 216       require => File["$mountpoint/$pg_backup_host"],
 217     }
 218
 219     file { $pg_backup_path:
 220       ensure  => directory,
 221       owner   => $pg_user,
 222       group   => $pg_group,
 223       mode    => "0700",
 224       require => File["$mountpoint/$pg_backup_host"],
 225     }
 226
 227     cron::job::multiple { "backup_psql_$pg_host":
 228       ensure  => "present",
 229       require => [File[$pg_backup_path], File[$pg_path]],
 230       jobs    => [
 231         {
 232           command     => "/usr/bin/pg_dumpall -h $pg_path -f $pg_backup_path/\$(date -Iseconds).sql",
 233           user        => $pg_user,
 234           hour        => "22,4,10,16",
 235           minute      => 0,
 236           description => "Backup the database",
 237         },
 238         {
 239           command     => "/usr/bin/rm -f $(ls -1 $pg_backup_path/*.sql | grep -v 'T22:' | sort -r | sed -e '1,12d')",
 240           user        => $pg_user,
 241           hour        => 3,
 242           minute      => 0,
 243           description => "Cleanup the database backups",
 244         },
 245         {
 246           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')",
 247           user        => $pg_user,
 248           hour        => 3,
 249           minute      => 1,
 250           description => "Cleanup the database backups exponentially",
 251         },
 252       ]
 253     }
 254
 255     exec { "pg_basebackup $pg_path":
 256       cwd         => $pg_path,
 257       user        => $pg_user,
 258       creates     => "$pg_path/PG_VERSION",
 259       environment => ["PGPASSWORD=$ldap_password"],
 260       command     => "/usr/bin/pg_basebackup -w -h $pg_host -U $ldap_cn -D $pg_path -S $pg_slot",
 261       before      => [
 262         Concat["$pg_path/pg_hba.conf"],
 263         Concat["$pg_path/recovery.conf"],
 264         File["$pg_path/postgresql.conf"],
 265       ]
 266     }
 267
 268     concat { "$pg_path/pg_hba.conf":
 269       owner   => $pg_user,
 270       group   => $pg_group,
 271       mode    => '0640',
 272       warn    => true,
 273     }
 274     postgresql::server::pg_hba_rule { "$pg_backup_host - local access as postgres user":
 275       description => 'Allow local access to postgres user',
 276       type        => 'local',
 277       database    => 'all',
 278       user        => $pg_user,
 279       auth_method => 'ident',
 280       order       => "00-01",
 281       target      => "$pg_path/pg_hba.conf",
 282       postgresql_version => "10",
 283     }
 284     postgresql::server::pg_hba_rule { "$pg_backup_host - localhost access as postgres user":
 285       description => 'Allow localhost access to postgres user',
 286       type        => 'host',
 287       database    => 'all',
 288       user        => $pg_user,
 289       address     => "127.0.0.1/32",
 290       auth_method => 'md5',
 291       order       => "00-02",
 292       target      => "$pg_path/pg_hba.conf",
 293       postgresql_version => "10",
 294     }
 295     postgresql::server::pg_hba_rule { "$pg_backup_host - localhost ip6 access as postgres user":
 296       description => 'Allow localhost access to postgres user',
 297       type        => 'host',
 298       database    => 'all',
 299       user        => $pg_user,
 300       address     => "::1/128",
 301       auth_method => 'md5',
 302       order       => "00-03",
 303       target      => "$pg_path/pg_hba.conf",
 304       postgresql_version => "10",
 305     }
 306     postgresql::server::pg_hba_rule { "$pg_backup_host - deny access to postgresql user":
 307       description => 'Deny remote access to postgres user',
 308       type        => 'host',
 309       database    => 'all',
 310       user        => $pg_user,
 311       address     => "0.0.0.0/0",
 312       auth_method => 'reject',
 313       order       => "00-04",
 314       target      => "$pg_path/pg_hba.conf",
 315       postgresql_version => "10",
 316     }
 317
 318     postgresql::server::pg_hba_rule { "$pg_backup_host - local access":
 319       description => 'Allow local access with password',
 320       type        => 'local',
 321       database    => 'all',
 322       user        => 'all',
 323       auth_method => 'md5',
 324       order       => "10-01",
 325       target      => "$pg_path/pg_hba.conf",
 326       postgresql_version => "10",
 327     }
 328
 329     postgresql::server::pg_hba_rule { "$pg_backup_host - local access with same name":
 330       description => 'Allow local access with same name',
 331       type        => 'local',
 332       database    => 'all',
 333       user        => 'all',
 334       auth_method => 'ident',
 335       order       => "10-02",
 336       target      => "$pg_path/pg_hba.conf",
 337       postgresql_version => "10",
 338     }
 339
 340     $primary_conninfo  = "host=$pg_host port=$pg_port user=$ldap_cn password=$ldap_password sslmode=require"
 341     $primary_slot_name = regsubst($ldap_cn, '-', "_", "G")
 342     $standby_mode      = "on"
 343
 344     concat { "$pg_path/recovery.conf":
 345       owner  => $pg_user,
 346       group  => $pg_group,
 347       mode   => '0640',
 348       warn   => true,
 349     }
 350     concat::fragment { "$pg_path/recovery.conf":
 351       target  => "$pg_path/recovery.conf",
 352       content => template('postgresql/recovery.conf.erb'),
 353     }
 354
 355     file { "$pg_path/postgresql.conf":
 356       owner   => $pg_user,
 357       group   => $pg_group,
 358       mode    => '0640',
 359       content => template("role/backup/postgresql.conf.erb"),
 360     }
 361
 362     service { "postgresql_backup@$pg_backup_host":
 363       enable  => true,
 364       ensure  => "running",
 365       require => [
 366         File["/etc/systemd/system/postgresql_backup@.service"],
 367         Concat["$pg_path/pg_hba.conf"],
 368         Concat["$pg_path/recovery.conf"],
 369         File["$pg_path/postgresql.conf"],
 370       ],
 371       subscribe => [
 372         Concat["$pg_path/pg_hba.conf"],
 373         Concat["$pg_path/recovery.conf"],
 374         File["$pg_path/postgresql.conf"],
 375       ]
 376     }
 377   }
 378
 379 }