modules/role/manifests/backup/postgresql.pp

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