Add cryptoportfolio postgresql backup

7 files changed, 276 insertions, 0 deletions

diff --git a/modules/role/files/cryptoportfolio/pam_postgresql b/modules/role/files/cryptoportfolio/pam_postgresql
new file mode 100644
index 0000000..70a90ae
--- /dev/null
+++ b/modules/role/files/cryptoportfolio/pam_postgresql
@@ -0,0 +1,3 @@
+auth            required        pam_ldap.so config=/etc/pam_ldap.d/postgresql.conf
+account         required        pam_ldap.so config=/etc/pam_ldap.d/postgresql.conf
+
diff --git a/modules/role/manifests/backup.pp b/modules/role/manifests/backup.pp
index ab485b0..37e6138 100644
--- a/modules/role/manifests/backup.pp
+++ b/modules/role/manifests/backup.pp
@@ -14,6 +14,7 @@ class role::backup (
   include "profile::xmr_stak"
   include "profile::known_hosts"
   include "profile::boinc"
+  include "role::cryptoportfolio::postgresql_backup"
 
   ensure_packages(["rsync"])
 
diff --git a/modules/role/manifests/cryptoportfolio/postgresql.pp b/modules/role/manifests/cryptoportfolio/postgresql.pp
index 5db5e25..d951874 100644
--- a/modules/role/manifests/cryptoportfolio/postgresql.pp
+++ b/modules/role/manifests/cryptoportfolio/postgresql.pp
@@ -126,4 +126,70 @@ class role::cryptoportfolio::postgresql inherits role::cryptoportfolio {
     order       => "05-02",
   }
 
+  $backup_host = "backup-1"
+
+  unless empty($backup_host) {
+    ensure_packages(["pam_ldap"])
+
+    $facts["ldapvar"]["other"].each |$host| {
+      if ($host["cn"][0] == $backup_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 to replication user from backup for replication from $ipaddress/$mask":
+            type        => 'hostssl',
+            database    => 'replication',
+            user        => 'all',
+            address     => "$ipaddress/$mask",
+            auth_method => 'pam',
+            order       => "06-01",
+          }
+        }
+
+        postgresql::server::role { $backup_host:
+          replication => true,
+        }
+
+        postgresql_replication_slot { regsubst($backup_host, '-', "_", "G"):
+          ensure => present
+        }
+      }
+    }
+
+    $ldap_server = lookup("base_installation::ldap_server")
+    $ldap_base   = lookup("base_installation::ldap_base")
+    $ldap_dn     = lookup("base_installation::ldap_dn")
+    $ldap_password = generate_password(24, $password_seed, "ldap")
+    $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    => "0644",
+      owner   => "root",
+      group   => "root",
+      content => template("role/cryptoportfolio/pam_ldap_postgresql.conf.erb"),
+    } ->
+    file { "/etc/pam.d/postgresql":
+      ensure => "present",
+      mode   => "0644",
+      owner  => "root",
+      group  => "root",
+      source => "puppet:///modules/role/cryptoportfolio/pam_postgresql"
+    }
+  }
+
 }
diff --git a/modules/role/manifests/cryptoportfolio/postgresql_backup.pp b/modules/role/manifests/cryptoportfolio/postgresql_backup.pp
new file mode 100644
index 0000000..c6ca0fa
--- /dev/null
+++ b/modules/role/manifests/cryptoportfolio/postgresql_backup.pp
@@ -0,0 +1,161 @@
+class role::cryptoportfolio::postgresql_backup 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")
+  $pg_slot = regsubst($ldap_cn, '-', "_", "G")
+
+  ensure_packages(["postgresql"])
+
+  $pg_backup_hosts = ["cryptoportfolio-dev.immae.eu"]
+
+  $pg_backup_hosts.each |$pg_backup_host| {
+    $pg_path = "$mountpoint/$pg_backup_host/postgresql"
+    $pg_host = "$pg_backup_host"
+    $pg_port = "5432"
+
+    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"],
+    }
+
+    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",
+    }
+
+    concat { "$pg_path/recovery.conf":
+      owner  => $pg_user,
+      group  => $pg_group,
+      mode   => '0640',
+      warn   => true,
+    }
+    postgresql::server::recovery { "$pg_backup_host recovery":
+      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",
+      target            => "$pg_path/recovery.conf",
+    }
+
+    file { "$pg_path/postgresql.conf":
+      owner   => $pg_user,
+      group   => $pg_group,
+      mode    => '0640',
+      content => template("role/cryptoportfolio/postgresql_backup.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"],
+      ]
+    }
+  }
+
+  file { "/etc/systemd/system/postgresql_backup@.service":
+    mode    => "0644",
+    owner   => "root",
+    group   => "root",
+    content => template("role/cryptoportfolio/postgresql_backup@.service.erb"),
+  }
+}
diff --git a/modules/role/templates/cryptoportfolio/pam_ldap_postgresql.conf.erb b/modules/role/templates/cryptoportfolio/pam_ldap_postgresql.conf.erb
new file mode 100644
index 0000000..f3d9674
--- /dev/null
+++ b/modules/role/templates/cryptoportfolio/pam_ldap_postgresql.conf.erb
@@ -0,0 +1,6 @@
+host <%= @ldap_server %>
+
+base <%= @ldap_base %>
+binddn <%= @ldap_dn %>
+bindpw <%= @ldap_password %>
+pam_login_attribute <%= @ldap_attribute %>
diff --git a/modules/role/templates/cryptoportfolio/postgresql_backup.conf.erb b/modules/role/templates/cryptoportfolio/postgresql_backup.conf.erb
new file mode 100644
index 0000000..860089b
--- /dev/null
+++ b/modules/role/templates/cryptoportfolio/postgresql_backup.conf.erb
@@ -0,0 +1,5 @@
+listen_addresses= ''
+unix_socket_directories = '<%= @pg_path %>'
+data_directory = '<%= @pg_path %>'
+wal_level = logical
+
diff --git a/modules/role/templates/cryptoportfolio/postgresql_backup@.service.erb b/modules/role/templates/cryptoportfolio/postgresql_backup@.service.erb
new file mode 100644
index 0000000..245a1cb
--- /dev/null
+++ b/modules/role/templates/cryptoportfolio/postgresql_backup@.service.erb
@@ -0,0 +1,34 @@
+[Unit]
+Description=PostgreSQL database server
+After=network.target
+
+[Service]
+Type=forking
+TimeoutSec=120
+User=postgres
+Group=postgres
+
+Environment=PGROOT=<%= @mountpoint %>/%i/postgresql
+
+SyslogIdentifier=postgres
+PIDFile=<%= @mountpoint %>/%i/postgresql/postmaster.pid
+RuntimeDirectory=postgresql
+RuntimeDirectoryMode=755
+
+ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}
+ExecStart= /usr/bin/pg_ctl -s -D ${PGROOT} start -w -t 120
+ExecReload=/usr/bin/pg_ctl -s -D ${PGROOT} reload
+ExecStop=  /usr/bin/pg_ctl -s -D ${PGROOT} stop -m fast
+
+# Due to PostgreSQL's use of shared memory, OOM killer is often overzealous in
+# killing Postgres, so adjust it downward
+OOMScoreAdjust=-200
+
+# Additional security-related features
+PrivateTmp=true
+ProtectHome=true
+ProtectSystem=full
+NoNewPrivileges=true
+
+[Install]
+WantedBy=multi-user.target