path: root/modules/role/manifests/backup/postgresql.pp

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"],
      ]
    }
  }

}