From c53ac3f84852a42aa8b7341ee7fe0a629d2e3579 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Isma=C3=ABl=20Bouya?= Date: Wed, 27 Jun 2018 20:45:15 +0200 Subject: Refactor postgresql configuration --- modules/profile/files/postgresql/pam_postgresql | 3 + modules/profile/manifests/pam_ldap.pp | 13 +++ modules/profile/manifests/postgresql/pam_ldap.pp | 28 +++++ .../profile/manifests/postgresql/replication.pp | 60 +++++++++++ modules/profile/manifests/postgresql/ssl.pp | 73 +++++++++++++ modules/profile/manifests/postgresql_master.pp | 119 ++------------------- .../postgresql/pam_ldap_postgresql.conf.erb | 6 ++ modules/role/manifests/backup/postgresql.pp | 2 - 8 files changed, 191 insertions(+), 113 deletions(-) create mode 100644 modules/profile/files/postgresql/pam_postgresql create mode 100644 modules/profile/manifests/pam_ldap.pp create mode 100644 modules/profile/manifests/postgresql/pam_ldap.pp create mode 100644 modules/profile/manifests/postgresql/replication.pp create mode 100644 modules/profile/manifests/postgresql/ssl.pp create mode 100644 modules/profile/templates/postgresql/pam_ldap_postgresql.conf.erb diff --git a/modules/profile/files/postgresql/pam_postgresql b/modules/profile/files/postgresql/pam_postgresql new file mode 100644 index 0000000..70a90ae --- /dev/null +++ b/modules/profile/files/postgresql/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/profile/manifests/pam_ldap.pp b/modules/profile/manifests/pam_ldap.pp new file mode 100644 index 0000000..956a7cd --- /dev/null +++ b/modules/profile/manifests/pam_ldap.pp @@ -0,0 +1,13 @@ +class profile::pam_ldap ( +) { + ensure_packages(["pam_ldap"]) + + file { "/etc/pam_ldap.d": + ensure => directory, + mode => "0755", + owner => "root", + group => "root", + require => Package["pam_ldap"], + } +} + diff --git a/modules/profile/manifests/postgresql/pam_ldap.pp b/modules/profile/manifests/postgresql/pam_ldap.pp new file mode 100644 index 0000000..f068245 --- /dev/null +++ b/modules/profile/manifests/postgresql/pam_ldap.pp @@ -0,0 +1,28 @@ +class profile::postgresql::pam_ldap ( + String $pg_user = "postgres" +) { + include "profile::pam_ldap" + + $password_seed = lookup("base_installation::puppet_pass_seed") + $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/postgresql.conf": + ensure => "present", + mode => "0400", + owner => $pg_user, + group => "root", + content => template("profile/postgresql/pam_ldap_postgresql.conf.erb"), + require => File["/etc/pam_ldap.d"], + } -> + file { "/etc/pam.d/postgresql": + ensure => "present", + mode => "0644", + owner => "root", + group => "root", + source => "puppet:///modules/profile/postgresql/pam_postgresql" + } +} diff --git a/modules/profile/manifests/postgresql/replication.pp b/modules/profile/manifests/postgresql/replication.pp new file mode 100644 index 0000000..33b147f --- /dev/null +++ b/modules/profile/manifests/postgresql/replication.pp @@ -0,0 +1,60 @@ +define profile::postgresql::replication ( + Boolean $handle_role = false, + Boolean $add_self_role = false, + Boolean $handle_slot = false, +) { + include "profile::postgresql::pam_ldap" + + $host_cn = $title + $host_infos = find_host($facts["ldapvar"]["other"], $host_cn) + + if empty($host_infos) { + fail("Unable to find host for replication") + } + + ensure_resource("postgresql::server::config_entry", "wal_level", { + value => "logical", + }) + + $host_infos["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 replication to user $host_cn from $ipaddress/$mask": + type => 'hostssl', + database => 'replication', + user => $host_cn, + address => "$ipaddress/$mask", + auth_method => 'pam', + order => "06-01", + } + } + + if $handle_role { + postgresql::server::role { $host_cn: + replication => true, + } + + if $add_self_role { + $ldap_cn = lookup("base_installation::ldap_cn") + + # Needed to be replicated to the backup and be able to recover later + ensure_resource("postgresql::server::role", $ldap_cn, { + replication => true, + }) + } + } + + if $handle_slot { + postgresql_replication_slot { regsubst($host_cn, '-', "_", "G"): + ensure => present + } + } +} diff --git a/modules/profile/manifests/postgresql/ssl.pp b/modules/profile/manifests/postgresql/ssl.pp new file mode 100644 index 0000000..e4da8af --- /dev/null +++ b/modules/profile/manifests/postgresql/ssl.pp @@ -0,0 +1,73 @@ +define profile::postgresql::ssl ( + Optional[String] $cert = undef, + Optional[String] $key = undef, + Optional[String] $certname = undef, + Optional[Boolean] $copy_keys = true, + Optional[String] $pg_user = $profile::postgresql::pg_user, + Optional[String] $pg_group = $profile::postgresql::pg_user +) { + $pg_dir = $title + $datadir = "$pg_dir/data" + + file { "$datadir/certs": + ensure => directory, + mode => "0700", + owner => $pg_user, + group => $pg_group, + require => File[$pg_dir], + } + + if empty($cert) or empty($key) { + if empty($certname) { + fail("A certificate name is necessary to generate ssl certificate") + } + + ssl::self_signed_certificate { $certname: + common_name => $certname, + country => "FR", + days => "3650", + organization => "Immae", + owner => $pg_user, + group => $pg_group, + directory => "$datadir/certs", + } + + $ssl_key = "$datadir/certs/$backup_host_cn.key" + $ssl_cert = "$datadir/certs/$backup_host_cn.crt" + } elsif $copy_keys { + $ssl_key = "$datadir/certs/privkey.pem" + $ssl_cert = "$datadir/certs/cert.pem" + + file { $ssl_cert: + source => "file://$cert", + mode => "0600", + links => "follow", + owner => $pg_user, + group => $pg_group, + require => File["$datadir/certs"], + } + file { $ssl_key: + source => "file://$key", + mode => "0600", + links => "follow", + owner => $pg_user, + group => $pg_group, + require => File["$datadir/certs"], + } + } else { + $ssl_key = $key + $ssl_cert = $cert + } + + postgresql::server::config_entry { "ssl": + value => "on", + } + + postgresql::server::config_entry { "ssl_cert_file": + value => $ssl_cert, + } + + postgresql::server::config_entry { "ssl_key_file": + value => $ssl_key, + } +} diff --git a/modules/profile/manifests/postgresql_master.pp b/modules/profile/manifests/postgresql_master.pp index 067345a..e28c1b0 100644 --- a/modules/profile/manifests/postgresql_master.pp +++ b/modules/profile/manifests/postgresql_master.pp @@ -2,120 +2,17 @@ define profile::postgresql_master ( $letsencrypt_host = undef, $backup_hosts = [], ) { - $password_seed = lookup("base_installation::puppet_pass_seed") - - ensure_resource("file", "/var/lib/postgres/data/certs", { - ensure => directory, - mode => "0700", - owner => $::profile::postgresql::pg_user, - group => $::profile::postgresql::pg_user, - require => File["/var/lib/postgres"], - }) - - ensure_resource("file", "/var/lib/postgres/data/certs/cert.pem", { - source => "file:///etc/letsencrypt/live/$letsencrypt_host/cert.pem", - mode => "0600", - links => "follow", - owner => $::profile::postgresql::pg_user, - group => $::profile::postgresql::pg_user, - require => [Letsencrypt::Certonly[$letsencrypt_host], File["/var/lib/postgres/data/certs"]] - }) - - ensure_resource("file", "/var/lib/postgres/data/certs/privkey.pem", { - source => "file:///etc/letsencrypt/live/$letsencrypt_host/privkey.pem", - mode => "0600", - links => "follow", - owner => $::profile::postgresql::pg_user, - group => $::profile::postgresql::pg_user, - require => [Letsencrypt::Certonly[$letsencrypt_host], File["/var/lib/postgres/data/certs"]] - }) - - ensure_resource("postgresql::server::config_entry", "wal_level", { - value => "logical", - }) - - ensure_resource("postgresql::server::config_entry", "ssl", { - value => "on", + profile::postgresql::ssl { "/var/lib/postgres": + cert => "/etc/letsencrypt/live/$letsencrypt_host/cert.pem", + key => "/etc/letsencrypt/live/$letsencrypt_host/privkey.pem", require => Letsencrypt::Certonly[$letsencrypt_host], - }) - - ensure_resource("postgresql::server::config_entry", "ssl_cert_file", { - value => "/var/lib/postgres/data/certs/cert.pem", - require => Letsencrypt::Certonly[$letsencrypt_host], - }) - - ensure_resource("postgresql::server::config_entry", "ssl_key_file", { - value => "/var/lib/postgres/data/certs/privkey.pem", - require => Letsencrypt::Certonly[$letsencrypt_host], - }) + } $backup_hosts.each |$backup_host| { - ensure_packages(["pam_ldap"]) - - $host = find_host($facts["ldapvar"]["other"], $backup_host) - 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 to replication user from backup for replication from $ipaddress/$mask": - type => 'hostssl', - database => 'replication', - user => $backup_host, - 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 - } + profile::postgresql::replication { $backup_host: + handle_role => true, + handle_slot => true, + add_self_role => true, } } - - $ldap_server = lookup("base_installation::ldap_server") - $ldap_base = lookup("base_installation::ldap_base") - $ldap_dn = lookup("base_installation::ldap_dn") - $ldap_cn = lookup("base_installation::ldap_cn") - $ldap_password = generate_password(24, $password_seed, "ldap") - $ldap_attribute = "cn" - - # This is to be replicated to the backup - postgresql::server::role { $ldap_cn: - replication => true, - } - - 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 => $::profile::postgresql::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" - } - } diff --git a/modules/profile/templates/postgresql/pam_ldap_postgresql.conf.erb b/modules/profile/templates/postgresql/pam_ldap_postgresql.conf.erb new file mode 100644 index 0000000..f3d9674 --- /dev/null +++ b/modules/profile/templates/postgresql/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/manifests/backup/postgresql.pp b/modules/role/manifests/backup/postgresql.pp index ee62a00..8c7542b 100644 --- a/modules/role/manifests/backup/postgresql.pp +++ b/modules/role/manifests/backup/postgresql.pp @@ -1,6 +1,4 @@ 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") -- cgit v1.2.3 From d2f031ece106ed2dc37283b194edfa94718a6306 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Isma=C3=ABl=20Bouya?= Date: Wed, 27 Jun 2018 21:52:02 +0200 Subject: Refactor backup postgresql --- modules/profile/files/postgresql/pam_pgbouncer | 3 + .../profile/files/postgresql/pgbouncer_head.ini | 15 + .../profile/files/postgresql_master/pam_postgresql | 3 - .../profile/manifests/postgresql/backup_dump.pp | 53 +++ .../manifests/postgresql/backup_pgbouncer.pp | 92 +++++ .../manifests/postgresql/backup_replication.pp | 135 ++++++++ .../manifests/postgresql/base_pg_hba_rules.pp | 76 +++++ .../manifests/postgresql/pam_ldap_pgbouncer.pp | 33 ++ .../profile/manifests/postgresql/replication.pp | 36 +- modules/profile/manifests/postgresql/ssl.pp | 47 +-- modules/profile/manifests/postgresql_master.pp | 10 +- .../postgresql/pam_ldap_pgbouncer.conf.erb | 7 + .../postgresql/postgresql_backup@.service.erb | 34 ++ .../postgresql_master/pam_ldap_postgresql.conf.erb | 6 - modules/role/files/backup/pam_pgbouncer | 3 - modules/role/manifests/backup.pp | 8 - modules/role/manifests/backup/postgresql.pp | 374 +-------------------- .../templates/backup/pam_ldap_pgbouncer.conf.erb | 7 - modules/role/templates/backup/pgbouncer.ini.erb | 15 - .../backup/postgresql_backup@.service.erb | 34 -- 20 files changed, 514 insertions(+), 477 deletions(-) create mode 100644 modules/profile/files/postgresql/pam_pgbouncer create mode 100644 modules/profile/files/postgresql/pgbouncer_head.ini delete mode 100644 modules/profile/files/postgresql_master/pam_postgresql create mode 100644 modules/profile/manifests/postgresql/backup_dump.pp create mode 100644 modules/profile/manifests/postgresql/backup_pgbouncer.pp create mode 100644 modules/profile/manifests/postgresql/backup_replication.pp create mode 100644 modules/profile/manifests/postgresql/base_pg_hba_rules.pp create mode 100644 modules/profile/manifests/postgresql/pam_ldap_pgbouncer.pp create mode 100644 modules/profile/templates/postgresql/pam_ldap_pgbouncer.conf.erb create mode 100644 modules/profile/templates/postgresql/postgresql_backup@.service.erb delete mode 100644 modules/profile/templates/postgresql_master/pam_ldap_postgresql.conf.erb delete mode 100644 modules/role/files/backup/pam_pgbouncer delete mode 100644 modules/role/templates/backup/pam_ldap_pgbouncer.conf.erb delete mode 100644 modules/role/templates/backup/pgbouncer.ini.erb delete mode 100644 modules/role/templates/backup/postgresql_backup@.service.erb diff --git a/modules/profile/files/postgresql/pam_pgbouncer b/modules/profile/files/postgresql/pam_pgbouncer new file mode 100644 index 0000000..13f0d3d --- /dev/null +++ b/modules/profile/files/postgresql/pam_pgbouncer @@ -0,0 +1,3 @@ +auth required pam_ldap.so config=/etc/pam_ldap.d/pgbouncer.conf +account required pam_ldap.so config=/etc/pam_ldap.d/pgbouncer.conf + diff --git a/modules/profile/files/postgresql/pgbouncer_head.ini b/modules/profile/files/postgresql/pgbouncer_head.ini new file mode 100644 index 0000000..3ba8728 --- /dev/null +++ b/modules/profile/files/postgresql/pgbouncer_head.ini @@ -0,0 +1,15 @@ +[pgbouncer] + +listen_addr = 0.0.0.0 +listen_port = 5432 + +unix_socket_dir = /run/postgresql +unix_socket_mode = 0777 + +auth_type = pam + +admin_users = postgres +max_client_conn = 100 +default_pool_size = 20 + +[databases] diff --git a/modules/profile/files/postgresql_master/pam_postgresql b/modules/profile/files/postgresql_master/pam_postgresql deleted file mode 100644 index 70a90ae..0000000 --- a/modules/profile/files/postgresql_master/pam_postgresql +++ /dev/null @@ -1,3 +0,0 @@ -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/profile/manifests/postgresql/backup_dump.pp b/modules/profile/manifests/postgresql/backup_dump.pp new file mode 100644 index 0000000..10e349a --- /dev/null +++ b/modules/profile/manifests/postgresql/backup_dump.pp @@ -0,0 +1,53 @@ +define profile::postgresql::backup_dump ( + String $pg_user = "postgres", + String $pg_group = "postgres", +) { + $base_path = $title + $pg_path = "$base_path/postgresql" + $pg_backup_path = "$base_path/postgresql_backup" + $pg_host = split($base_path, "/")[-1] + + ensure_packages(["python", "python-pip"]) + ensure_resource("package", "pylog2rotate", { + source => "git+https://github.com/avian2/pylog2rotate", + ensure => present, + provider => "pip3", + require => Package["python-pip"], + }) + + file { $pg_backup_path: + ensure => directory, + owner => $pg_user, + group => $pg_group, + mode => "0700", + require => File[$base_path], + } + + 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", + }, + ] + } +} diff --git a/modules/profile/manifests/postgresql/backup_pgbouncer.pp b/modules/profile/manifests/postgresql/backup_pgbouncer.pp new file mode 100644 index 0000000..45b8ed5 --- /dev/null +++ b/modules/profile/manifests/postgresql/backup_pgbouncer.pp @@ -0,0 +1,92 @@ +define profile::postgresql::backup_pgbouncer ( + String $base_path, + Hash $pg_infos, + String $pg_user = "postgres", + String $pg_group = "postgres", +) { + include "profile::postgresql::pam_ldap_pgbouncer" + ensure_packages(["pgbouncer"]) + + $host_cn = $title + + $host = find_host($facts["ldapvar"]["other"], $host_cn) + if empty($host) { + fail("No host found for pgbouncer") + } elsif has_key($host["vars"], "host") { + $pg_backup_host = $host["vars"]["host"][0] + } else { + $pg_backup_host = $host["vars"]["real_hostname"][0] + } + + $pg_path = "$base_path/$pg_backup_host/postgresql" + + if has_key($host["vars"], "postgresql_backup_port") { + $pg_port = " port=${host[vars][postgresql_backup_port][0]}" + } else { + $pg_port = "" + } + + # Config + ensure_resource("concat", "/etc/pgbouncer/pgbouncer.ini", { + mode => "0644", + owner => "root", + group => "root", + ensure_newline => true, + notify => Service["pgbouncer"], + before => Service["pgbouncer"], + }) + + ensure_resource("concat::fragment", "pgbouncer_head", { + target => "/etc/pgbouncer/pgbouncer.ini", + order => 01, + source => "puppet:///modules/profile/postgresql/pgbouncer_head.ini", + }) + + concat::fragment { "pgbouncer_$pg_backup_host": + target => "/etc/pgbouncer/pgbouncer.ini", + order => 02, + content => "${pg_infos[pgbouncer_dbname]} = host=$pg_path$pg_port user=${pg_infos[dbuser]} dbname=${pg_infos[dbname]}", + } + + # pg_hba for accessed cluster + 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", + } + + # service + ensure_resource("file", "/etc/systemd/system/pgbouncer.service.d", { + ensure => "directory", + mode => "0644", + owner => "root", + group => "root", + }) + + ensure_resource("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"], + before => Service["pgbouncer"], + }) + + ensure_resource("service", "pgbouncer", { + ensure => "running", + enable => true, + require => [ + Package["pgbouncer"], + File["/etc/systemd/system/pgbouncer.service.d/override.conf"], + Concat["/etc/pgbouncer/pgbouncer.ini"] + ], + }) + + +} diff --git a/modules/profile/manifests/postgresql/backup_replication.pp b/modules/profile/manifests/postgresql/backup_replication.pp new file mode 100644 index 0000000..a4edb8f --- /dev/null +++ b/modules/profile/manifests/postgresql/backup_replication.pp @@ -0,0 +1,135 @@ +define profile::postgresql::backup_replication ( + String $base_path, + Hash $pg_infos, + String $pg_user = "postgres", + String $pg_group = "postgres", +) { + $host_cn = $title + + $host = find_host($facts["ldapvar"]["other"], $host_cn) + if empty($host) { + $pg_backup_host = $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 = "$base_path/$pg_backup_host/postgresql" + + # Replication folder + ensure_resource("file", "$base_path/$pg_backup_host", { + ensure => directory, + }) + + file { $pg_path: + ensure => directory, + owner => $pg_user, + group => $pg_group, + mode => "0700", + require => File["$base_path/$pg_backup_host"], + } + + # pg_hba.conf + profile::postgresql::base_pg_hba_rules { $pg_backup_host: + pg_path => $pg_path + } + + # postgresql.conf file and ssl + concat { "$pg_path/postgresql.conf": + owner => $pg_user, + group => $pg_group, + mode => '0640', + warn => true, + } + + if !empty($host) and has_key($host["vars"], "postgresql_backup_port") { + $pg_listen_port = $host["vars"]["postgresql_backup_port"][0] + + profile::postgresql::ssl { $pg_path: + certname => $host_cn, + handle_concat_config => true, + before => Service["postgresql_backup@$pg_backup_host"] + } + + concat::fragment { "$pg_path/postgresql.conf listen": + target => "$pg_path/postgresql.conf", + content => "listen_addresses = '*'\nport = $pg_listen_port\n", + } + + profile::postgresql::replication { $host_cn: + target => "$pg_path/pg_hba.conf", + } + } else { + concat::fragment { "$pg_path/postgresql.conf listen": + target => "$pg_path/postgresql.conf", + content => "listen_addresses = ''\n", + } + } + + concat::fragment { "$pg_path/postgresql.conf paths": + target => "$pg_path/postgresql.conf", + content => "unix_socket_directories = '$pg_path'\ndata_directory = '$pg_path'\nwal_level = logical\n", + } + + $password_seed = lookup("base_installation::puppet_pass_seed") + $pg_host = $pg_backup_host + $pg_port = $pg_infos["dbport"] + $ldap_cn = lookup("base_installation::ldap_cn") + $ldap_password = generate_password(24, $password_seed, "ldap") + $pg_slot = regsubst($ldap_cn, '-', "_", "G") + + # recovery.conf file + $primary_conninfo = "host=$pg_host port=$pg_port user=$ldap_cn password=$ldap_password sslmode=require" + $primary_slot_name = $pg_slot + $standby_mode = "on" + + file { "$pg_path/recovery.conf": + owner => $pg_user, + group => $pg_group, + mode => '0640', + content => template('postgresql/recovery.conf.erb'), + } + + # Initial replication + 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 -p $pg_port -U $ldap_cn -D $pg_path -S $pg_slot", + before => [ + Concat["$pg_path/pg_hba.conf"], + File["$pg_path/recovery.conf"], + Concat["$pg_path/postgresql.conf"], + ] + } + + # Service + ensure_resource("file", "/etc/systemd/system/postgresql_backup@.service", { + mode => "0644", + owner => "root", + group => "root", + content => template("profile/postgresql/postgresql_backup@.service.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"], + File["$pg_path/recovery.conf"], + Concat["$pg_path/postgresql.conf"], + ], + subscribe => [ + Concat["$pg_path/pg_hba.conf"], + File["$pg_path/recovery.conf"], + Concat["$pg_path/postgresql.conf"], + ] + } + + # Dumps + profile::postgresql::backup_dump { "$base_path/$pg_backup_host": } + +} diff --git a/modules/profile/manifests/postgresql/base_pg_hba_rules.pp b/modules/profile/manifests/postgresql/base_pg_hba_rules.pp new file mode 100644 index 0000000..13ab4ff --- /dev/null +++ b/modules/profile/manifests/postgresql/base_pg_hba_rules.pp @@ -0,0 +1,76 @@ +define profile::postgresql::base_pg_hba_rules ( + Optional[String] $pg_path = undef, + String $pg_user = "postgres", + String $pg_group = "postgres", +) { + unless empty($pg_path) { + concat { "$pg_path/pg_hba.conf": + owner => $pg_user, + group => $pg_group, + mode => '0640', + warn => true, + require => File[$pg_path], + } + + Postgresql::Server::Pg_hba_rule { + target => "$pg_path/pg_hba.conf", + postgresql_version => "10", + } + } + + postgresql::server::pg_hba_rule { "$title - 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", + } + postgresql::server::pg_hba_rule { "$title - 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", + } + postgresql::server::pg_hba_rule { "$title - 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 { "$title - 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", + } + postgresql::server::pg_hba_rule { "$title - local access": + description => 'Allow local access with password', + type => 'local', + database => 'all', + user => 'all', + auth_method => 'md5', + order => "10-01", + } + + postgresql::server::pg_hba_rule { "$title - local access with same name": + description => 'Allow local access with same name', + type => 'local', + database => 'all', + user => 'all', + auth_method => 'ident', + order => "10-02", + } + +} diff --git a/modules/profile/manifests/postgresql/pam_ldap_pgbouncer.pp b/modules/profile/manifests/postgresql/pam_ldap_pgbouncer.pp new file mode 100644 index 0000000..67714f2 --- /dev/null +++ b/modules/profile/manifests/postgresql/pam_ldap_pgbouncer.pp @@ -0,0 +1,33 @@ +class profile::postgresql::pam_ldap_pgbouncer ( + String $pg_user = "postgres" +) { + include "profile::pam_ldap" + + $password_seed = lookup("base_installation::puppet_pass_seed") + $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 = "uid" + $ldap_filter = lookup("role::backup::postgresql::pgbouncer_access_filter", { "default_value" => undef }) + + if empty($ldap_filter) { + fail("need ldap filter for pgbouncer") + } + + file { "/etc/pam_ldap.d/pgbouncer.conf": + ensure => "present", + mode => "0600", + owner => $pg_user, + group => "root", + content => template("profile/postgresql/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/profile/postgresql/pam_pgbouncer" + } +} diff --git a/modules/profile/manifests/postgresql/replication.pp b/modules/profile/manifests/postgresql/replication.pp index 33b147f..2fcb71c 100644 --- a/modules/profile/manifests/postgresql/replication.pp +++ b/modules/profile/manifests/postgresql/replication.pp @@ -1,7 +1,9 @@ define profile::postgresql::replication ( - Boolean $handle_role = false, - Boolean $add_self_role = false, - Boolean $handle_slot = false, + Boolean $handle_role = false, + Boolean $handle_config = false, + Boolean $add_self_role = false, + Boolean $handle_slot = false, + Optional[String] $target = undef, ) { include "profile::postgresql::pam_ldap" @@ -12,9 +14,11 @@ define profile::postgresql::replication ( fail("Unable to find host for replication") } - ensure_resource("postgresql::server::config_entry", "wal_level", { - value => "logical", - }) + if empty($target) { + $pg_version = undef + } else { + $pg_version = "10" + } $host_infos["ipHostNumber"].each |$ip| { $infos = split($ip, "/") @@ -28,15 +32,23 @@ define profile::postgresql::replication ( } postgresql::server::pg_hba_rule { "allow TCP access for replication to user $host_cn from $ipaddress/$mask": - type => 'hostssl', - database => 'replication', - user => $host_cn, - address => "$ipaddress/$mask", - auth_method => 'pam', - order => "06-01", + type => 'hostssl', + database => 'replication', + user => $host_cn, + address => "$ipaddress/$mask", + auth_method => 'pam', + order => "06-01", + target => $target, + postgresql_version => $pg_version, } } + if $handle_config { + ensure_resource("postgresql::server::config_entry", "wal_level", { + value => "logical", + }) + } + if $handle_role { postgresql::server::role { $host_cn: replication => true, diff --git a/modules/profile/manifests/postgresql/ssl.pp b/modules/profile/manifests/postgresql/ssl.pp index e4da8af..dc56c0b 100644 --- a/modules/profile/manifests/postgresql/ssl.pp +++ b/modules/profile/manifests/postgresql/ssl.pp @@ -1,20 +1,21 @@ define profile::postgresql::ssl ( - Optional[String] $cert = undef, - Optional[String] $key = undef, - Optional[String] $certname = undef, - Optional[Boolean] $copy_keys = true, - Optional[String] $pg_user = $profile::postgresql::pg_user, - Optional[String] $pg_group = $profile::postgresql::pg_user + Optional[String] $cert = undef, + Optional[String] $key = undef, + Optional[String] $certname = undef, + Optional[Boolean] $copy_keys = true, + Optional[Boolean] $handle_config_entry = false, + Optional[Boolean] $handle_concat_config = false, + Optional[String] $pg_user = "postgres", + Optional[String] $pg_group = "postgres", ) { - $pg_dir = $title - $datadir = "$pg_dir/data" + $datadir = $title file { "$datadir/certs": ensure => directory, mode => "0700", owner => $pg_user, group => $pg_group, - require => File[$pg_dir], + require => File[$datadir], } if empty($cert) or empty($key) { @@ -32,8 +33,8 @@ define profile::postgresql::ssl ( directory => "$datadir/certs", } - $ssl_key = "$datadir/certs/$backup_host_cn.key" - $ssl_cert = "$datadir/certs/$backup_host_cn.crt" + $ssl_key = "$datadir/certs/$certname.key" + $ssl_cert = "$datadir/certs/$certname.crt" } elsif $copy_keys { $ssl_key = "$datadir/certs/privkey.pem" $ssl_cert = "$datadir/certs/cert.pem" @@ -59,15 +60,23 @@ define profile::postgresql::ssl ( $ssl_cert = $cert } - postgresql::server::config_entry { "ssl": - value => "on", - } + if $handle_config_entry { + postgresql::server::config_entry { "ssl": + value => "on", + } - postgresql::server::config_entry { "ssl_cert_file": - value => $ssl_cert, - } + postgresql::server::config_entry { "ssl_cert_file": + value => $ssl_cert, + } - postgresql::server::config_entry { "ssl_key_file": - value => $ssl_key, + postgresql::server::config_entry { "ssl_key_file": + value => $ssl_key, + } + } elsif $handle_concat_config { + concat::fragment { "$datadir/postgresql.conf ssl config": + target => "$datadir/postgresql.conf", + content => "ssl = on\nssl_key_file = '$ssl_key'\nssl_cert_file = '$ssl_cert'\n" + } } + } diff --git a/modules/profile/manifests/postgresql_master.pp b/modules/profile/manifests/postgresql_master.pp index e28c1b0..3de4f22 100644 --- a/modules/profile/manifests/postgresql_master.pp +++ b/modules/profile/manifests/postgresql_master.pp @@ -2,14 +2,16 @@ define profile::postgresql_master ( $letsencrypt_host = undef, $backup_hosts = [], ) { - profile::postgresql::ssl { "/var/lib/postgres": - cert => "/etc/letsencrypt/live/$letsencrypt_host/cert.pem", - key => "/etc/letsencrypt/live/$letsencrypt_host/privkey.pem", - require => Letsencrypt::Certonly[$letsencrypt_host], + profile::postgresql::ssl { "/var/lib/postgres/data": + cert => "/etc/letsencrypt/live/$letsencrypt_host/cert.pem", + key => "/etc/letsencrypt/live/$letsencrypt_host/privkey.pem", + require => Letsencrypt::Certonly[$letsencrypt_host], + handle_config_entry => true, } $backup_hosts.each |$backup_host| { profile::postgresql::replication { $backup_host: + handle_config => true, handle_role => true, handle_slot => true, add_self_role => true, diff --git a/modules/profile/templates/postgresql/pam_ldap_pgbouncer.conf.erb b/modules/profile/templates/postgresql/pam_ldap_pgbouncer.conf.erb new file mode 100644 index 0000000..12fa9bb --- /dev/null +++ b/modules/profile/templates/postgresql/pam_ldap_pgbouncer.conf.erb @@ -0,0 +1,7 @@ +host <%= @ldap_server %> + +base <%= @ldap_base %> +binddn <%= @ldap_dn %> +bindpw <%= @ldap_password %> +pam_login_attribute <%= @ldap_attribute %> +pam_filter <%= @ldap_filter %> diff --git a/modules/profile/templates/postgresql/postgresql_backup@.service.erb b/modules/profile/templates/postgresql/postgresql_backup@.service.erb new file mode 100644 index 0000000..74f5a98 --- /dev/null +++ b/modules/profile/templates/postgresql/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=<%= @base_path %>/%i/postgresql + +SyslogIdentifier=postgres +PIDFile=<%= @base_path %>/%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 diff --git a/modules/profile/templates/postgresql_master/pam_ldap_postgresql.conf.erb b/modules/profile/templates/postgresql_master/pam_ldap_postgresql.conf.erb deleted file mode 100644 index f3d9674..0000000 --- a/modules/profile/templates/postgresql_master/pam_ldap_postgresql.conf.erb +++ /dev/null @@ -1,6 +0,0 @@ -host <%= @ldap_server %> - -base <%= @ldap_base %> -binddn <%= @ldap_dn %> -bindpw <%= @ldap_password %> -pam_login_attribute <%= @ldap_attribute %> diff --git a/modules/role/files/backup/pam_pgbouncer b/modules/role/files/backup/pam_pgbouncer deleted file mode 100644 index 13f0d3d..0000000 --- a/modules/role/files/backup/pam_pgbouncer +++ /dev/null @@ -1,3 +0,0 @@ -auth required pam_ldap.so config=/etc/pam_ldap.d/pgbouncer.conf -account required pam_ldap.so config=/etc/pam_ldap.d/pgbouncer.conf - diff --git a/modules/role/manifests/backup.pp b/modules/role/manifests/backup.pp index 51b689d..b35c542 100644 --- a/modules/role/manifests/backup.pp +++ b/modules/role/manifests/backup.pp @@ -17,14 +17,6 @@ class role::backup ( include "role::backup::postgresql" - ensure_packages(["python", "python-pip"]) - package { "pylog2rotate": - source => "git+https://github.com/avian2/pylog2rotate", - ensure => present, - provider => "pip3", - require => Package["python-pip"], - } - ensure_packages(["rsync"]) ssh_keygen { $user: diff --git a/modules/role/manifests/backup/postgresql.pp b/modules/role/manifests/backup/postgresql.pp index 8c7542b..8a65dec 100644 --- a/modules/role/manifests/backup/postgresql.pp +++ b/modules/role/manifests/backup/postgresql.pp @@ -1,379 +1,21 @@ class role::backup::postgresql inherits role::backup { - $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"]) + ensure_packages(["postgresql"]) $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 + profile::postgresql::backup_replication { $backup_host_cn: + base_path => $mountpoint, + pg_infos => $pg_infos, } - - 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 $pg_infos["pgbouncer"] { + profile::postgresql::backup_pgbouncer { $backup_host_cn: + base_path => $mountpoint, + pg_infos => $pg_infos, } } - 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"], - ] - } } } diff --git a/modules/role/templates/backup/pam_ldap_pgbouncer.conf.erb b/modules/role/templates/backup/pam_ldap_pgbouncer.conf.erb deleted file mode 100644 index 384a418..0000000 --- a/modules/role/templates/backup/pam_ldap_pgbouncer.conf.erb +++ /dev/null @@ -1,7 +0,0 @@ -host <%= @ldap_server %> - -base <%= @ldap_base %> -binddn <%= @ldap_dn %> -bindpw <%= @ldap_password %> -pam_login_attribute <%= @pgbouncer_ldap_attribute %> -pam_filter <%= @ldap_filter %> diff --git a/modules/role/templates/backup/pgbouncer.ini.erb b/modules/role/templates/backup/pgbouncer.ini.erb deleted file mode 100644 index 3ba8728..0000000 --- a/modules/role/templates/backup/pgbouncer.ini.erb +++ /dev/null @@ -1,15 +0,0 @@ -[pgbouncer] - -listen_addr = 0.0.0.0 -listen_port = 5432 - -unix_socket_dir = /run/postgresql -unix_socket_mode = 0777 - -auth_type = pam - -admin_users = postgres -max_client_conn = 100 -default_pool_size = 20 - -[databases] diff --git a/modules/role/templates/backup/postgresql_backup@.service.erb b/modules/role/templates/backup/postgresql_backup@.service.erb deleted file mode 100644 index 245a1cb..0000000 --- a/modules/role/templates/backup/postgresql_backup@.service.erb +++ /dev/null @@ -1,34 +0,0 @@ -[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 -- cgit v1.2.3 From 436cae5ebfd889b1bf625b757939a57842b564da Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Isma=C3=ABl=20Bouya?= Date: Thu, 28 Jun 2018 02:38:10 +0200 Subject: Rename file --- modules/profile/manifests/postgresql/master.pp | 20 ++++++++++++++++++++ modules/profile/manifests/postgresql_master.pp | 20 -------------------- modules/role/manifests/etherpad.pp | 2 +- 3 files changed, 21 insertions(+), 21 deletions(-) create mode 100644 modules/profile/manifests/postgresql/master.pp delete mode 100644 modules/profile/manifests/postgresql_master.pp diff --git a/modules/profile/manifests/postgresql/master.pp b/modules/profile/manifests/postgresql/master.pp new file mode 100644 index 0000000..969905f --- /dev/null +++ b/modules/profile/manifests/postgresql/master.pp @@ -0,0 +1,20 @@ +define profile::postgresql::master ( + $letsencrypt_host = undef, + $backup_hosts = [], +) { + profile::postgresql::ssl { "/var/lib/postgres/data": + cert => "/etc/letsencrypt/live/$letsencrypt_host/cert.pem", + key => "/etc/letsencrypt/live/$letsencrypt_host/privkey.pem", + require => Letsencrypt::Certonly[$letsencrypt_host], + handle_config_entry => true, + } + + $backup_hosts.each |$backup_host| { + profile::postgresql::replication { $backup_host: + handle_config => true, + handle_role => true, + handle_slot => true, + add_self_role => true, + } + } +} diff --git a/modules/profile/manifests/postgresql_master.pp b/modules/profile/manifests/postgresql_master.pp deleted file mode 100644 index 3de4f22..0000000 --- a/modules/profile/manifests/postgresql_master.pp +++ /dev/null @@ -1,20 +0,0 @@ -define profile::postgresql_master ( - $letsencrypt_host = undef, - $backup_hosts = [], -) { - profile::postgresql::ssl { "/var/lib/postgres/data": - cert => "/etc/letsencrypt/live/$letsencrypt_host/cert.pem", - key => "/etc/letsencrypt/live/$letsencrypt_host/privkey.pem", - require => Letsencrypt::Certonly[$letsencrypt_host], - handle_config_entry => true, - } - - $backup_hosts.each |$backup_host| { - profile::postgresql::replication { $backup_host: - handle_config => true, - handle_role => true, - handle_slot => true, - add_self_role => true, - } - } -} diff --git a/modules/role/manifests/etherpad.pp b/modules/role/manifests/etherpad.pp index 109da96..28b9eb6 100644 --- a/modules/role/manifests/etherpad.pp +++ b/modules/role/manifests/etherpad.pp @@ -87,7 +87,7 @@ class role::etherpad ( subscribe => Aur::Package["etherpad-lite"], } - profile::postgresql_master { "postgresql master for etherpad": + profile::postgresql::master { "postgresql master for etherpad": letsencrypt_host => $web_host, backup_hosts => ["backup-1"], } -- cgit v1.2.3 From 3925777d9715d271c0643faef9f520e7816dba89 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Isma=C3=ABl=20Bouya?= Date: Thu, 28 Jun 2018 10:32:57 +0200 Subject: Refactor cryptoportfolio postgresql --- modules/profile/manifests/postgresql.pp | 64 +------ .../manifests/postgresql/base_pg_hba_rules.pp | 2 - .../role/manifests/cryptoportfolio/postgresql.pp | 194 +++------------------ 3 files changed, 26 insertions(+), 234 deletions(-) diff --git a/modules/profile/manifests/postgresql.pp b/modules/profile/manifests/postgresql.pp index 2cd1bcc..97ce572 100644 --- a/modules/profile/manifests/postgresql.pp +++ b/modules/profile/manifests/postgresql.pp @@ -1,4 +1,7 @@ -class profile::postgresql { +class profile::postgresql ( + Optional[String] $pg_user = "postgres", + Optional[String] $pg_group = "postgres", +) { $password_seed = lookup("base_installation::puppet_pass_seed") class { '::postgresql::globals': @@ -7,16 +10,13 @@ class profile::postgresql { pg_hba_conf_defaults => false, } - # FIXME: get it from the postgresql module? - $pg_user = "postgres" - class { '::postgresql::client': } # FIXME: postgresql module is buggy and doesn't create dir? file { "/var/lib/postgres": ensure => directory, owner => $pg_user, - group => $pg_user, + group => $pg_group, before => File["/var/lib/postgres/data"], require => Package["postgresql-server"], } @@ -26,59 +26,7 @@ class profile::postgresql { listen_addresses => "*", } - postgresql::server::pg_hba_rule { '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", - } - postgresql::server::pg_hba_rule { '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", - } - postgresql::server::pg_hba_rule { '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", - } - postgresql::server::pg_hba_rule { '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", - } - - postgresql::server::pg_hba_rule { 'local access': - description => 'Allow local access with password', - type => 'local', - database => 'all', - user => 'all', - auth_method => 'md5', - order => "10-01", - } - - postgresql::server::pg_hba_rule { 'local access with same name': - description => 'Allow local access with same name', - type => 'local', - database => 'all', - user => 'all', - auth_method => 'ident', - order => "10-02", - } + profile::postgresql::base_pg_hba_rules { "default": } } diff --git a/modules/profile/manifests/postgresql/base_pg_hba_rules.pp b/modules/profile/manifests/postgresql/base_pg_hba_rules.pp index 13ab4ff..07c4bb6 100644 --- a/modules/profile/manifests/postgresql/base_pg_hba_rules.pp +++ b/modules/profile/manifests/postgresql/base_pg_hba_rules.pp @@ -43,8 +43,6 @@ define profile::postgresql::base_pg_hba_rules ( address => "::1/128", auth_method => 'md5', order => "00-03", - target => "$pg_path/pg_hba.conf", - postgresql_version => "10", } postgresql::server::pg_hba_rule { "$title - deny access to postgresql user": description => 'Deny remote access to postgres user', diff --git a/modules/role/manifests/cryptoportfolio/postgresql.pp b/modules/role/manifests/cryptoportfolio/postgresql.pp index 776b30f..addad67 100644 --- a/modules/role/manifests/cryptoportfolio/postgresql.pp +++ b/modules/role/manifests/cryptoportfolio/postgresql.pp @@ -2,194 +2,40 @@ class role::cryptoportfolio::postgresql inherits role::cryptoportfolio { $password_seed = lookup("base_installation::puppet_pass_seed") $pg_password = generate_password(24, $password_seed, "postgres_cryptoportfolio") - $pg_replication_password = generate_password(24, $password_seed, "postgres_cryptoportfolio_replication") - file { "/var/lib/postgres/data/certs": - ensure => directory, - mode => "0700", - owner => $::profile::postgresql::pg_user, - group => $::profile::postgresql::pg_user, - require => File["/var/lib/postgres"], - } - - file { "/var/lib/postgres/data/certs/cert.pem": - source => "file:///etc/letsencrypt/live/$web_host/cert.pem", - mode => "0600", - links => "follow", - owner => $::profile::postgresql::pg_user, - group => $::profile::postgresql::pg_user, - require => [Letsencrypt::Certonly[$web_host], File["/var/lib/postgres/data/certs"]] - } - - file { "/var/lib/postgres/data/certs/privkey.pem": - source => "file:///etc/letsencrypt/live/$web_host/privkey.pem", - mode => "0600", - links => "follow", - owner => $::profile::postgresql::pg_user, - group => $::profile::postgresql::pg_user, - require => [Letsencrypt::Certonly[$web_host], File["/var/lib/postgres/data/certs"]] - } - - postgresql_replication_slot { $pg_user_replication: - ensure => present - } - - postgresql::server::config_entry { "wal_level": - value => "logical", - } - - postgresql::server::config_entry { "ssl": - value => "on", - require => Letsencrypt::Certonly[$web_host], - } - - postgresql::server::config_entry { "ssl_cert_file": - value => "/var/lib/postgres/data/certs/cert.pem", - require => Letsencrypt::Certonly[$web_host], - } - - postgresql::server::config_entry { "ssl_key_file": - value => "/var/lib/postgres/data/certs/privkey.pem", - require => Letsencrypt::Certonly[$web_host], + profile::postgresql::master { "postgresql master for cryptoportfolio": + letsencrypt_host => $web_host, + backup_hosts => ["backup-1"], } postgresql::server::db { $pg_db: user => $pg_user, password => postgresql_password($pg_user, $pg_password), } - -> - postgresql_psql { "CREATE PUBLICATION ${pg_db}_publication FOR ALL TABLES": - db => $pg_db, - unless => "SELECT 1 FROM pg_catalog.pg_publication WHERE pubname = '${pg_db}_publication'", - } - -> - postgresql::server::role { $pg_user_replication: - db => $pg_db, - replication => true, - password_hash => postgresql_password($pg_user_replication, $pg_replication_password), - } - -> - postgresql::server::database_grant { $pg_user_replication: - db => $pg_db, - privilege => "CONNECT", - role => $pg_user_replication, - } - -> - postgresql::server::grant { "all tables in schema:public:$pg_user_replication": - db => $pg_db, - role => $pg_user_replication, - privilege => "SELECT", - object_type => "ALL TABLES IN SCHEMA", - object_name => "public", - } - -> - postgresql::server::grant { "all sequences in schema:public:$pg_user_replication": - db => $pg_db, - role => $pg_user_replication, - privilege => "SELECT", - object_type => "ALL SEQUENCES IN SCHEMA", - object_name => "public", - } postgresql::server::pg_hba_rule { 'allow localhost TCP access to cryptoportfolio user': - type => 'host', + type => 'local', database => $pg_db, user => $pg_user, - address => '127.0.0.1/32', - auth_method => 'md5', + auth_method => 'ident', order => "05-01", } - postgresql::server::pg_hba_rule { 'allow localhost ip6 TCP access to cryptoportfolio user': - type => 'host', - database => $pg_db, - user => $pg_user, - address => '::1/128', - auth_method => 'md5', - order => "05-01", - } - - postgresql::server::pg_hba_rule { 'allow TCP access to replication user from immae.eu for replication': - type => 'hostssl', - database => 'replication', - user => $pg_user_replication, - address => 'immae.eu', - auth_method => 'md5', - order => "05-01", - } - - postgresql::server::pg_hba_rule { 'allow TCP access to replication user from immae.eu': - type => 'hostssl', - database => $pg_db, - user => $pg_user_replication, - address => 'immae.eu', - auth_method => 'md5', - 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 => "0600", - owner => $::profile::postgresql::pg_user, - 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" - } + # cleanup + postgresql_psql { "DROP PUBLICATION ${pg_db}_publication": + db => $pg_db, + onlyif => "SELECT 1 FROM pg_catalog.pg_publication WHERE pubname = '${pg_db}_publication'", + } -> + postgresql_replication_slot { $pg_user_replication: + ensure => absent + } -> + postgresql_psql { "DROP OWNED BY $pg_user_replication": + db => $pg_db, + onlyif => "SELECT 1 FROM pg_user WHERE usename='$pg_user_replication'" + } -> + postgresql::server::role { $pg_user_replication: + ensure => absent, } + # /cleanup } -- cgit v1.2.3