aboutsummaryrefslogtreecommitdiff
path: root/modules
diff options
context:
space:
mode:
authorIsmaël Bouya <ismael.bouya@normalesup.org>2018-06-27 21:52:02 +0200
committerIsmaël Bouya <ismael.bouya@normalesup.org>2018-06-28 02:34:04 +0200
commitd2f031ece106ed2dc37283b194edfa94718a6306 (patch)
tree42d4146e0f4b58ab724c4690c1ea431b3e733b7c /modules
parentc53ac3f84852a42aa8b7341ee7fe0a629d2e3579 (diff)
downloadPuppet-d2f031ece106ed2dc37283b194edfa94718a6306.tar.gz
Puppet-d2f031ece106ed2dc37283b194edfa94718a6306.tar.zst
Puppet-d2f031ece106ed2dc37283b194edfa94718a6306.zip
Refactor backup postgresql
Diffstat (limited to 'modules')
-rw-r--r--modules/profile/files/postgresql/pam_pgbouncer (renamed from modules/role/files/backup/pam_pgbouncer)0
-rw-r--r--modules/profile/files/postgresql/pgbouncer_head.ini (renamed from modules/role/templates/backup/pgbouncer.ini.erb)0
-rw-r--r--modules/profile/files/postgresql_master/pam_postgresql3
-rw-r--r--modules/profile/manifests/postgresql/backup_dump.pp53
-rw-r--r--modules/profile/manifests/postgresql/backup_pgbouncer.pp92
-rw-r--r--modules/profile/manifests/postgresql/backup_replication.pp135
-rw-r--r--modules/profile/manifests/postgresql/base_pg_hba_rules.pp76
-rw-r--r--modules/profile/manifests/postgresql/pam_ldap_pgbouncer.pp33
-rw-r--r--modules/profile/manifests/postgresql/replication.pp36
-rw-r--r--modules/profile/manifests/postgresql/ssl.pp47
-rw-r--r--modules/profile/manifests/postgresql_master.pp10
-rw-r--r--modules/profile/templates/postgresql/pam_ldap_pgbouncer.conf.erb (renamed from modules/profile/templates/postgresql_master/pam_ldap_postgresql.conf.erb)1
-rw-r--r--modules/profile/templates/postgresql/postgresql_backup@.service.erb (renamed from modules/role/templates/backup/postgresql_backup@.service.erb)4
-rw-r--r--modules/role/manifests/backup.pp8
-rw-r--r--modules/role/manifests/backup/postgresql.pp374
-rw-r--r--modules/role/templates/backup/pam_ldap_pgbouncer.conf.erb7
16 files changed, 458 insertions, 421 deletions
diff --git a/modules/role/files/backup/pam_pgbouncer b/modules/profile/files/postgresql/pam_pgbouncer
index 13f0d3d..13f0d3d 100644
--- a/modules/role/files/backup/pam_pgbouncer
+++ b/modules/profile/files/postgresql/pam_pgbouncer
diff --git a/modules/role/templates/backup/pgbouncer.ini.erb b/modules/profile/files/postgresql/pgbouncer_head.ini
index 3ba8728..3ba8728 100644
--- a/modules/role/templates/backup/pgbouncer.ini.erb
+++ b/modules/profile/files/postgresql/pgbouncer_head.ini
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 @@
1auth required pam_ldap.so config=/etc/pam_ldap.d/postgresql.conf
2account required pam_ldap.so config=/etc/pam_ldap.d/postgresql.conf
3
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 @@
1define profile::postgresql::backup_dump (
2 String $pg_user = "postgres",
3 String $pg_group = "postgres",
4) {
5 $base_path = $title
6 $pg_path = "$base_path/postgresql"
7 $pg_backup_path = "$base_path/postgresql_backup"
8 $pg_host = split($base_path, "/")[-1]
9
10 ensure_packages(["python", "python-pip"])
11 ensure_resource("package", "pylog2rotate", {
12 source => "git+https://github.com/avian2/pylog2rotate",
13 ensure => present,
14 provider => "pip3",
15 require => Package["python-pip"],
16 })
17
18 file { $pg_backup_path:
19 ensure => directory,
20 owner => $pg_user,
21 group => $pg_group,
22 mode => "0700",
23 require => File[$base_path],
24 }
25
26 cron::job::multiple { "backup_psql_$pg_host":
27 ensure => "present",
28 require => [File[$pg_backup_path], File[$pg_path]],
29 jobs => [
30 {
31 command => "/usr/bin/pg_dumpall -h $pg_path -f $pg_backup_path/\$(date -Iseconds).sql",
32 user => $pg_user,
33 hour => "22,4,10,16",
34 minute => 0,
35 description => "Backup the database",
36 },
37 {
38 command => "/usr/bin/rm -f $(ls -1 $pg_backup_path/*.sql | grep -v 'T22:' | sort -r | sed -e '1,12d')",
39 user => $pg_user,
40 hour => 3,
41 minute => 0,
42 description => "Cleanup the database backups",
43 },
44 {
45 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')",
46 user => $pg_user,
47 hour => 3,
48 minute => 1,
49 description => "Cleanup the database backups exponentially",
50 },
51 ]
52 }
53}
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 @@
1define profile::postgresql::backup_pgbouncer (
2 String $base_path,
3 Hash $pg_infos,
4 String $pg_user = "postgres",
5 String $pg_group = "postgres",
6) {
7 include "profile::postgresql::pam_ldap_pgbouncer"
8 ensure_packages(["pgbouncer"])
9
10 $host_cn = $title
11
12 $host = find_host($facts["ldapvar"]["other"], $host_cn)
13 if empty($host) {
14 fail("No host found for pgbouncer")
15 } elsif has_key($host["vars"], "host") {
16 $pg_backup_host = $host["vars"]["host"][0]
17 } else {
18 $pg_backup_host = $host["vars"]["real_hostname"][0]
19 }
20
21 $pg_path = "$base_path/$pg_backup_host/postgresql"
22
23 if has_key($host["vars"], "postgresql_backup_port") {
24 $pg_port = " port=${host[vars][postgresql_backup_port][0]}"
25 } else {
26 $pg_port = ""
27 }
28
29 # Config
30 ensure_resource("concat", "/etc/pgbouncer/pgbouncer.ini", {
31 mode => "0644",
32 owner => "root",
33 group => "root",
34 ensure_newline => true,
35 notify => Service["pgbouncer"],
36 before => Service["pgbouncer"],
37 })
38
39 ensure_resource("concat::fragment", "pgbouncer_head", {
40 target => "/etc/pgbouncer/pgbouncer.ini",
41 order => 01,
42 source => "puppet:///modules/profile/postgresql/pgbouncer_head.ini",
43 })
44
45 concat::fragment { "pgbouncer_$pg_backup_host":
46 target => "/etc/pgbouncer/pgbouncer.ini",
47 order => 02,
48 content => "${pg_infos[pgbouncer_dbname]} = host=$pg_path$pg_port user=${pg_infos[dbuser]} dbname=${pg_infos[dbname]}",
49 }
50
51 # pg_hba for accessed cluster
52 postgresql::server::pg_hba_rule { "$pg_backup_host - local access as ${pg_infos[dbuser]} user":
53 description => "Allow local access to ${pg_infos[dbuser]} user",
54 type => 'local',
55 database => $pg_infos["dbname"],
56 user => $pg_infos["dbuser"],
57 auth_method => 'trust',
58 order => "01-00",
59 target => "$pg_path/pg_hba.conf",
60 postgresql_version => "10",
61 }
62
63 # service
64 ensure_resource("file", "/etc/systemd/system/pgbouncer.service.d", {
65 ensure => "directory",
66 mode => "0644",
67 owner => "root",
68 group => "root",
69 })
70
71 ensure_resource("file", "/etc/systemd/system/pgbouncer.service.d/override.conf", {
72 ensure => "present",
73 mode => "0644",
74 owner => "root",
75 group => "root",
76 content => "[Service]\nUser=\nUser=$pg_user\n",
77 notify => Service["pgbouncer"],
78 before => Service["pgbouncer"],
79 })
80
81 ensure_resource("service", "pgbouncer", {
82 ensure => "running",
83 enable => true,
84 require => [
85 Package["pgbouncer"],
86 File["/etc/systemd/system/pgbouncer.service.d/override.conf"],
87 Concat["/etc/pgbouncer/pgbouncer.ini"]
88 ],
89 })
90
91
92}
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 @@
1define profile::postgresql::backup_replication (
2 String $base_path,
3 Hash $pg_infos,
4 String $pg_user = "postgres",
5 String $pg_group = "postgres",
6) {
7 $host_cn = $title
8
9 $host = find_host($facts["ldapvar"]["other"], $host_cn)
10 if empty($host) {
11 $pg_backup_host = $host_cn
12 } elsif has_key($host["vars"], "host") {
13 $pg_backup_host = $host["vars"]["host"][0]
14 } else {
15 $pg_backup_host = $host["vars"]["real_hostname"][0]
16 }
17
18 $pg_path = "$base_path/$pg_backup_host/postgresql"
19
20 # Replication folder
21 ensure_resource("file", "$base_path/$pg_backup_host", {
22 ensure => directory,
23 })
24
25 file { $pg_path:
26 ensure => directory,
27 owner => $pg_user,
28 group => $pg_group,
29 mode => "0700",
30 require => File["$base_path/$pg_backup_host"],
31 }
32
33 # pg_hba.conf
34 profile::postgresql::base_pg_hba_rules { $pg_backup_host:
35 pg_path => $pg_path
36 }
37
38 # postgresql.conf file and ssl
39 concat { "$pg_path/postgresql.conf":
40 owner => $pg_user,
41 group => $pg_group,
42 mode => '0640',
43 warn => true,
44 }
45
46 if !empty($host) and has_key($host["vars"], "postgresql_backup_port") {
47 $pg_listen_port = $host["vars"]["postgresql_backup_port"][0]
48
49 profile::postgresql::ssl { $pg_path:
50 certname => $host_cn,
51 handle_concat_config => true,
52 before => Service["postgresql_backup@$pg_backup_host"]
53 }
54
55 concat::fragment { "$pg_path/postgresql.conf listen":
56 target => "$pg_path/postgresql.conf",
57 content => "listen_addresses = '*'\nport = $pg_listen_port\n",
58 }
59
60 profile::postgresql::replication { $host_cn:
61 target => "$pg_path/pg_hba.conf",
62 }
63 } else {
64 concat::fragment { "$pg_path/postgresql.conf listen":
65 target => "$pg_path/postgresql.conf",
66 content => "listen_addresses = ''\n",
67 }
68 }
69
70 concat::fragment { "$pg_path/postgresql.conf paths":
71 target => "$pg_path/postgresql.conf",
72 content => "unix_socket_directories = '$pg_path'\ndata_directory = '$pg_path'\nwal_level = logical\n",
73 }
74
75 $password_seed = lookup("base_installation::puppet_pass_seed")
76 $pg_host = $pg_backup_host
77 $pg_port = $pg_infos["dbport"]
78 $ldap_cn = lookup("base_installation::ldap_cn")
79 $ldap_password = generate_password(24, $password_seed, "ldap")
80 $pg_slot = regsubst($ldap_cn, '-', "_", "G")
81
82 # recovery.conf file
83 $primary_conninfo = "host=$pg_host port=$pg_port user=$ldap_cn password=$ldap_password sslmode=require"
84 $primary_slot_name = $pg_slot
85 $standby_mode = "on"
86
87 file { "$pg_path/recovery.conf":
88 owner => $pg_user,
89 group => $pg_group,
90 mode => '0640',
91 content => template('postgresql/recovery.conf.erb'),
92 }
93
94 # Initial replication
95 exec { "pg_basebackup $pg_path":
96 cwd => $pg_path,
97 user => $pg_user,
98 creates => "$pg_path/PG_VERSION",
99 environment => ["PGPASSWORD=$ldap_password"],
100 command => "/usr/bin/pg_basebackup -w -h $pg_host -p $pg_port -U $ldap_cn -D $pg_path -S $pg_slot",
101 before => [
102 Concat["$pg_path/pg_hba.conf"],
103 File["$pg_path/recovery.conf"],
104 Concat["$pg_path/postgresql.conf"],
105 ]
106 }
107
108 # Service
109 ensure_resource("file", "/etc/systemd/system/postgresql_backup@.service", {
110 mode => "0644",
111 owner => "root",
112 group => "root",
113 content => template("profile/postgresql/postgresql_backup@.service.erb"),
114 })
115
116 service { "postgresql_backup@$pg_backup_host":
117 enable => true,
118 ensure => "running",
119 require => [
120 File["/etc/systemd/system/postgresql_backup@.service"],
121 Concat["$pg_path/pg_hba.conf"],
122 File["$pg_path/recovery.conf"],
123 Concat["$pg_path/postgresql.conf"],
124 ],
125 subscribe => [
126 Concat["$pg_path/pg_hba.conf"],
127 File["$pg_path/recovery.conf"],
128 Concat["$pg_path/postgresql.conf"],
129 ]
130 }
131
132 # Dumps
133 profile::postgresql::backup_dump { "$base_path/$pg_backup_host": }
134
135}
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 @@
1define profile::postgresql::base_pg_hba_rules (
2 Optional[String] $pg_path = undef,
3 String $pg_user = "postgres",
4 String $pg_group = "postgres",
5) {
6 unless empty($pg_path) {
7 concat { "$pg_path/pg_hba.conf":
8 owner => $pg_user,
9 group => $pg_group,
10 mode => '0640',
11 warn => true,
12 require => File[$pg_path],
13 }
14
15 Postgresql::Server::Pg_hba_rule {
16 target => "$pg_path/pg_hba.conf",
17 postgresql_version => "10",
18 }
19 }
20
21 postgresql::server::pg_hba_rule { "$title - local access as postgres user":
22 description => 'Allow local access to postgres user',
23 type => 'local',
24 database => 'all',
25 user => $pg_user,
26 auth_method => 'ident',
27 order => "00-01",
28 }
29 postgresql::server::pg_hba_rule { "$title - localhost access as postgres user":
30 description => 'Allow localhost access to postgres user',
31 type => 'host',
32 database => 'all',
33 user => $pg_user,
34 address => "127.0.0.1/32",
35 auth_method => 'md5',
36 order => "00-02",
37 }
38 postgresql::server::pg_hba_rule { "$title - localhost ip6 access as postgres user":
39 description => 'Allow localhost access to postgres user',
40 type => 'host',
41 database => 'all',
42 user => $pg_user,
43 address => "::1/128",
44 auth_method => 'md5',
45 order => "00-03",
46 target => "$pg_path/pg_hba.conf",
47 postgresql_version => "10",
48 }
49 postgresql::server::pg_hba_rule { "$title - deny access to postgresql user":
50 description => 'Deny remote access to postgres user',
51 type => 'host',
52 database => 'all',
53 user => $pg_user,
54 address => "0.0.0.0/0",
55 auth_method => 'reject',
56 order => "00-04",
57 }
58 postgresql::server::pg_hba_rule { "$title - local access":
59 description => 'Allow local access with password',
60 type => 'local',
61 database => 'all',
62 user => 'all',
63 auth_method => 'md5',
64 order => "10-01",
65 }
66
67 postgresql::server::pg_hba_rule { "$title - local access with same name":
68 description => 'Allow local access with same name',
69 type => 'local',
70 database => 'all',
71 user => 'all',
72 auth_method => 'ident',
73 order => "10-02",
74 }
75
76}
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 @@
1class profile::postgresql::pam_ldap_pgbouncer (
2 String $pg_user = "postgres"
3) {
4 include "profile::pam_ldap"
5
6 $password_seed = lookup("base_installation::puppet_pass_seed")
7 $ldap_server = lookup("base_installation::ldap_server")
8 $ldap_base = lookup("base_installation::ldap_base")
9 $ldap_dn = lookup("base_installation::ldap_dn")
10 $ldap_password = generate_password(24, $password_seed, "ldap")
11 $ldap_attribute = "uid"
12 $ldap_filter = lookup("role::backup::postgresql::pgbouncer_access_filter", { "default_value" => undef })
13
14 if empty($ldap_filter) {
15 fail("need ldap filter for pgbouncer")
16 }
17
18 file { "/etc/pam_ldap.d/pgbouncer.conf":
19 ensure => "present",
20 mode => "0600",
21 owner => $pg_user,
22 group => "root",
23 content => template("profile/postgresql/pam_ldap_pgbouncer.conf.erb"),
24 require => File["/etc/pam_ldap.d"],
25 } ->
26 file { "/etc/pam.d/pgbouncer":
27 ensure => "present",
28 mode => "0644",
29 owner => "root",
30 group => "root",
31 source => "puppet:///modules/profile/postgresql/pam_pgbouncer"
32 }
33}
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 @@
1define profile::postgresql::replication ( 1define profile::postgresql::replication (
2 Boolean $handle_role = false, 2 Boolean $handle_role = false,
3 Boolean $add_self_role = false, 3 Boolean $handle_config = false,
4 Boolean $handle_slot = false, 4 Boolean $add_self_role = false,
5 Boolean $handle_slot = false,
6 Optional[String] $target = undef,
5) { 7) {
6 include "profile::postgresql::pam_ldap" 8 include "profile::postgresql::pam_ldap"
7 9
@@ -12,9 +14,11 @@ define profile::postgresql::replication (
12 fail("Unable to find host for replication") 14 fail("Unable to find host for replication")
13 } 15 }
14 16
15 ensure_resource("postgresql::server::config_entry", "wal_level", { 17 if empty($target) {
16 value => "logical", 18 $pg_version = undef
17 }) 19 } else {
20 $pg_version = "10"
21 }
18 22
19 $host_infos["ipHostNumber"].each |$ip| { 23 $host_infos["ipHostNumber"].each |$ip| {
20 $infos = split($ip, "/") 24 $infos = split($ip, "/")
@@ -28,15 +32,23 @@ define profile::postgresql::replication (
28 } 32 }
29 33
30 postgresql::server::pg_hba_rule { "allow TCP access for replication to user $host_cn from $ipaddress/$mask": 34 postgresql::server::pg_hba_rule { "allow TCP access for replication to user $host_cn from $ipaddress/$mask":
31 type => 'hostssl', 35 type => 'hostssl',
32 database => 'replication', 36 database => 'replication',
33 user => $host_cn, 37 user => $host_cn,
34 address => "$ipaddress/$mask", 38 address => "$ipaddress/$mask",
35 auth_method => 'pam', 39 auth_method => 'pam',
36 order => "06-01", 40 order => "06-01",
41 target => $target,
42 postgresql_version => $pg_version,
37 } 43 }
38 } 44 }
39 45
46 if $handle_config {
47 ensure_resource("postgresql::server::config_entry", "wal_level", {
48 value => "logical",
49 })
50 }
51
40 if $handle_role { 52 if $handle_role {
41 postgresql::server::role { $host_cn: 53 postgresql::server::role { $host_cn:
42 replication => true, 54 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 @@
1define profile::postgresql::ssl ( 1define profile::postgresql::ssl (
2 Optional[String] $cert = undef, 2 Optional[String] $cert = undef,
3 Optional[String] $key = undef, 3 Optional[String] $key = undef,
4 Optional[String] $certname = undef, 4 Optional[String] $certname = undef,
5 Optional[Boolean] $copy_keys = true, 5 Optional[Boolean] $copy_keys = true,
6 Optional[String] $pg_user = $profile::postgresql::pg_user, 6 Optional[Boolean] $handle_config_entry = false,
7 Optional[String] $pg_group = $profile::postgresql::pg_user 7 Optional[Boolean] $handle_concat_config = false,
8 Optional[String] $pg_user = "postgres",
9 Optional[String] $pg_group = "postgres",
8) { 10) {
9 $pg_dir = $title 11 $datadir = $title
10 $datadir = "$pg_dir/data"
11 12
12 file { "$datadir/certs": 13 file { "$datadir/certs":
13 ensure => directory, 14 ensure => directory,
14 mode => "0700", 15 mode => "0700",
15 owner => $pg_user, 16 owner => $pg_user,
16 group => $pg_group, 17 group => $pg_group,
17 require => File[$pg_dir], 18 require => File[$datadir],
18 } 19 }
19 20
20 if empty($cert) or empty($key) { 21 if empty($cert) or empty($key) {
@@ -32,8 +33,8 @@ define profile::postgresql::ssl (
32 directory => "$datadir/certs", 33 directory => "$datadir/certs",
33 } 34 }
34 35
35 $ssl_key = "$datadir/certs/$backup_host_cn.key" 36 $ssl_key = "$datadir/certs/$certname.key"
36 $ssl_cert = "$datadir/certs/$backup_host_cn.crt" 37 $ssl_cert = "$datadir/certs/$certname.crt"
37 } elsif $copy_keys { 38 } elsif $copy_keys {
38 $ssl_key = "$datadir/certs/privkey.pem" 39 $ssl_key = "$datadir/certs/privkey.pem"
39 $ssl_cert = "$datadir/certs/cert.pem" 40 $ssl_cert = "$datadir/certs/cert.pem"
@@ -59,15 +60,23 @@ define profile::postgresql::ssl (
59 $ssl_cert = $cert 60 $ssl_cert = $cert
60 } 61 }
61 62
62 postgresql::server::config_entry { "ssl": 63 if $handle_config_entry {
63 value => "on", 64 postgresql::server::config_entry { "ssl":
64 } 65 value => "on",
66 }
65 67
66 postgresql::server::config_entry { "ssl_cert_file": 68 postgresql::server::config_entry { "ssl_cert_file":
67 value => $ssl_cert, 69 value => $ssl_cert,
68 } 70 }
69 71
70 postgresql::server::config_entry { "ssl_key_file": 72 postgresql::server::config_entry { "ssl_key_file":
71 value => $ssl_key, 73 value => $ssl_key,
74 }
75 } elsif $handle_concat_config {
76 concat::fragment { "$datadir/postgresql.conf ssl config":
77 target => "$datadir/postgresql.conf",
78 content => "ssl = on\nssl_key_file = '$ssl_key'\nssl_cert_file = '$ssl_cert'\n"
79 }
72 } 80 }
81
73} 82}
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 (
2 $letsencrypt_host = undef, 2 $letsencrypt_host = undef,
3 $backup_hosts = [], 3 $backup_hosts = [],
4) { 4) {
5 profile::postgresql::ssl { "/var/lib/postgres": 5 profile::postgresql::ssl { "/var/lib/postgres/data":
6 cert => "/etc/letsencrypt/live/$letsencrypt_host/cert.pem", 6 cert => "/etc/letsencrypt/live/$letsencrypt_host/cert.pem",
7 key => "/etc/letsencrypt/live/$letsencrypt_host/privkey.pem", 7 key => "/etc/letsencrypt/live/$letsencrypt_host/privkey.pem",
8 require => Letsencrypt::Certonly[$letsencrypt_host], 8 require => Letsencrypt::Certonly[$letsencrypt_host],
9 handle_config_entry => true,
9 } 10 }
10 11
11 $backup_hosts.each |$backup_host| { 12 $backup_hosts.each |$backup_host| {
12 profile::postgresql::replication { $backup_host: 13 profile::postgresql::replication { $backup_host:
14 handle_config => true,
13 handle_role => true, 15 handle_role => true,
14 handle_slot => true, 16 handle_slot => true,
15 add_self_role => true, 17 add_self_role => true,
diff --git a/modules/profile/templates/postgresql_master/pam_ldap_postgresql.conf.erb b/modules/profile/templates/postgresql/pam_ldap_pgbouncer.conf.erb
index f3d9674..12fa9bb 100644
--- a/modules/profile/templates/postgresql_master/pam_ldap_postgresql.conf.erb
+++ b/modules/profile/templates/postgresql/pam_ldap_pgbouncer.conf.erb
@@ -4,3 +4,4 @@ base <%= @ldap_base %>
4binddn <%= @ldap_dn %> 4binddn <%= @ldap_dn %>
5bindpw <%= @ldap_password %> 5bindpw <%= @ldap_password %>
6pam_login_attribute <%= @ldap_attribute %> 6pam_login_attribute <%= @ldap_attribute %>
7pam_filter <%= @ldap_filter %>
diff --git a/modules/role/templates/backup/postgresql_backup@.service.erb b/modules/profile/templates/postgresql/postgresql_backup@.service.erb
index 245a1cb..74f5a98 100644
--- a/modules/role/templates/backup/postgresql_backup@.service.erb
+++ b/modules/profile/templates/postgresql/postgresql_backup@.service.erb
@@ -8,10 +8,10 @@ TimeoutSec=120
8User=postgres 8User=postgres
9Group=postgres 9Group=postgres
10 10
11Environment=PGROOT=<%= @mountpoint %>/%i/postgresql 11Environment=PGROOT=<%= @base_path %>/%i/postgresql
12 12
13SyslogIdentifier=postgres 13SyslogIdentifier=postgres
14PIDFile=<%= @mountpoint %>/%i/postgresql/postmaster.pid 14PIDFile=<%= @base_path %>/%i/postgresql/postmaster.pid
15RuntimeDirectory=postgresql 15RuntimeDirectory=postgresql
16RuntimeDirectoryMode=755 16RuntimeDirectoryMode=755
17 17
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 (
17 17
18 include "role::backup::postgresql" 18 include "role::backup::postgresql"
19 19
20 ensure_packages(["python", "python-pip"])
21 package { "pylog2rotate":
22 source => "git+https://github.com/avian2/pylog2rotate",
23 ensure => present,
24 provider => "pip3",
25 require => Package["python-pip"],
26 }
27
28 ensure_packages(["rsync"]) 20 ensure_packages(["rsync"])
29 21
30 ssh_keygen { $user: 22 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 @@
1class role::backup::postgresql inherits role::backup { 1class role::backup::postgresql inherits role::backup {
2 $password_seed = lookup("base_installation::puppet_pass_seed") 2 ensure_packages(["postgresql"])
3
4 $user = lookup("role::backup::user")
5 $group = lookup("role::backup::group")
6 $pg_user = "postgres"
7 $pg_group = "postgres"
8
9 $ldap_cn = lookup("base_installation::ldap_cn")
10 $ldap_password = generate_password(24, $password_seed, "ldap")
11 $ldap_server = lookup("base_installation::ldap_server")
12 $ldap_base = lookup("base_installation::ldap_base")
13 $ldap_dn = lookup("base_installation::ldap_dn")
14 $pgbouncer_ldap_attribute = "uid"
15
16 $pg_slot = regsubst($ldap_cn, '-', "_", "G")
17
18 ensure_packages(["postgresql", "pgbouncer", "pam_ldap"])
19 3
20 $pg_backup_hosts = lookup("role::backup::postgresql::backup_hosts", { "default_value" => {} }) 4 $pg_backup_hosts = lookup("role::backup::postgresql::backup_hosts", { "default_value" => {} })
21 $ldap_filter = lookup("role::backup::postgresql::pgbouncer_access_filter", { "default_value" => undef })
22
23 unless empty($pg_backup_hosts) {
24 file { "/etc/systemd/system/postgresql_backup@.service":
25 mode => "0644",
26 owner => "root",
27 group => "root",
28 content => template("role/backup/postgresql_backup@.service.erb"),
29 }
30
31 unless empty($ldap_filter) {
32 concat { "/etc/pgbouncer/pgbouncer.ini":
33 mode => "0644",
34 owner => "root",
35 group => "root",
36 ensure_newline => true,
37 notify => Service["pgbouncer"],
38 }
39
40 concat::fragment { "pgbouncer_head":
41 target => "/etc/pgbouncer/pgbouncer.ini",
42 order => "01",
43 content => template("role/backup/pgbouncer.ini.erb"),
44 }
45
46 file { "/etc/systemd/system/pgbouncer.service.d":
47 ensure => "directory",
48 mode => "0644",
49 owner => "root",
50 group => "root",
51 }
52
53 file { "/etc/systemd/system/pgbouncer.service.d/override.conf":
54 ensure => "present",
55 mode => "0644",
56 owner => "root",
57 group => "root",
58 content => "[Service]\nUser=\nUser=$pg_user\n",
59 notify => Service["pgbouncer"],
60 }
61
62 service { "pgbouncer":
63 ensure => "running",
64 enable => true,
65 require => [
66 Package["pgbouncer"],
67 File["/etc/systemd/system/pgbouncer.service.d/override.conf"],
68 Concat["/etc/pgbouncer/pgbouncer.ini"]
69 ],
70 }
71
72 file { "/etc/pam_ldap.d/pgbouncer.conf":
73 ensure => "present",
74 mode => "0600",
75 owner => $pg_user,
76 group => "root",
77 content => template("role/backup/pam_ldap_pgbouncer.conf.erb"),
78 require => File["/etc/pam_ldap.d"],
79 } ->
80 file { "/etc/pam.d/pgbouncer":
81 ensure => "present",
82 mode => "0644",
83 owner => "root",
84 group => "root",
85 source => "puppet:///modules/role/backup/pam_pgbouncer"
86 }
87 }
88 }
89
90 $ldap_attribute = "cn"
91
92 file { "/etc/pam_ldap.d":
93 ensure => directory,
94 mode => "0755",
95 owner => "root",
96 group => "root",
97 } ->
98 file { "/etc/pam_ldap.d/postgresql.conf":
99 ensure => "present",
100 mode => "0600",
101 owner => $pg_user,
102 group => "root",
103 content => template("profile/postgresql_master/pam_ldap_postgresql.conf.erb"),
104 } ->
105 file { "/etc/pam.d/postgresql":
106 ensure => "present",
107 mode => "0644",
108 owner => "root",
109 group => "root",
110 source => "puppet:///modules/profile/postgresql_master/pam_postgresql"
111 }
112 5
113 $pg_backup_hosts.each |$backup_host_cn, $pg_infos| { 6 $pg_backup_hosts.each |$backup_host_cn, $pg_infos| {
114 $host = find_host($facts["ldapvar"]["other"], $backup_host_cn) 7 profile::postgresql::backup_replication { $backup_host_cn:
115 if empty($host) { 8 base_path => $mountpoint,
116 $pg_backup_host = $backup_host_cn 9 pg_infos => $pg_infos,
117 } elsif has_key($host["vars"], "host") {
118 $pg_backup_host = $host["vars"]["host"][0]
119 } else {
120 $pg_backup_host = $host["vars"]["real_hostname"][0]
121 }
122
123 $pg_path = "$mountpoint/$pg_backup_host/postgresql"
124 $pg_backup_path = "$mountpoint/$pg_backup_host/postgresql_backup"
125 $pg_host = "$pg_backup_host"
126 $pg_port = $pg_infos["dbport"]
127
128 if has_key($host["vars"], "postgresql_backup_port") {
129 $pg_listen_port = $host["vars"]["postgresql_backup_port"][0]
130 file { "$pg_path/certs":
131 ensure => directory,
132 mode => "0700",
133 owner => $pg_user,
134 group => $pg_group,
135 } ->
136 ssl::self_signed_certificate { $backup_host_cn:
137 common_name => $backup_host_cn,
138 country => "FR",
139 days => "3650",
140 organization => "Immae",
141 owner => $pg_user,
142 group => $pg_group,
143 directory => "$pg_path/certs",
144 before => File["$pg_path/postgresql.conf"],
145 }
146 $ssl_key = "$pg_path/certs/$backup_host_cn.key"
147 $ssl_cert = "$pg_path/certs/$backup_host_cn.crt"
148 } else {
149 $pg_listen_port = undef
150 $ssl_key = undef
151 $ssl_cert = undef
152 } 10 }
153 11
154 12 if $pg_infos["pgbouncer"] {
155 unless empty($host) { 13 profile::postgresql::backup_pgbouncer { $backup_host_cn:
156 $host["ipHostNumber"].each |$ip| { 14 base_path => $mountpoint,
157 $infos = split($ip, "/") 15 pg_infos => $pg_infos,
158 $ipaddress = $infos[0]
159 if (length($infos) == 1 and $ipaddress =~ /:/) {
160 $mask = "128"
161 } elsif (length($infos) == 1) {
162 $mask = "32"
163 } else {
164 $mask = $infos[1]
165 }
166
167 postgresql::server::pg_hba_rule { "allow TCP access for initial replication from $ipaddress/$mask":
168 type => 'hostssl',
169 database => 'replication',
170 user => $backup_host_cn,
171 address => "$ipaddress/$mask",
172 auth_method => 'pam',
173 order => "06-01",
174 target => "$pg_path/pg_hba.conf",
175 postgresql_version => "10",
176 }
177 } 16 }
178 } 17 }
179 18
180 if !empty($ldap_filter) and ($pg_infos["pgbouncer"]) {
181 if empty($pg_listen_port) {
182 $pg_listen_port_key = ""
183 } else {
184 $pg_listen_port_key = "port=$pg_listen_port"
185 }
186
187 concat::fragment { "pgbouncer_$pg_backup_host":
188 target => "/etc/pgbouncer/pgbouncer.ini",
189 order => 02,
190 content => "${pg_infos[pgbouncer_dbname]} = host=$mountpoint/$pg_backup_host/postgresql $pg_listen_port_key user=${pg_infos[dbuser]} dbname=${pg_infos[dbname]}",
191 }
192
193 postgresql::server::pg_hba_rule { "$pg_backup_host - local access as ${pg_infos[dbuser]} user":
194 description => "Allow local access to ${pg_infos[dbuser]} user",
195 type => 'local',
196 database => $pg_infos["dbname"],
197 user => $pg_infos["dbuser"],
198 auth_method => 'trust',
199 order => "01-00",
200 target => "$pg_path/pg_hba.conf",
201 postgresql_version => "10",
202 }
203 }
204
205 file { "$mountpoint/$pg_backup_host":
206 ensure => directory,
207 owner => $user,
208 group => $group,
209 }
210
211 file { $pg_path:
212 ensure => directory,
213 owner => $pg_user,
214 group => $pg_group,
215 mode => "0700",
216 require => File["$mountpoint/$pg_backup_host"],
217 }
218
219 file { $pg_backup_path:
220 ensure => directory,
221 owner => $pg_user,
222 group => $pg_group,
223 mode => "0700",
224 require => File["$mountpoint/$pg_backup_host"],
225 }
226
227 cron::job::multiple { "backup_psql_$pg_host":
228 ensure => "present",
229 require => [File[$pg_backup_path], File[$pg_path]],
230 jobs => [
231 {
232 command => "/usr/bin/pg_dumpall -h $pg_path -f $pg_backup_path/\$(date -Iseconds).sql",
233 user => $pg_user,
234 hour => "22,4,10,16",
235 minute => 0,
236 description => "Backup the database",
237 },
238 {
239 command => "/usr/bin/rm -f $(ls -1 $pg_backup_path/*.sql | grep -v 'T22:' | sort -r | sed -e '1,12d')",
240 user => $pg_user,
241 hour => 3,
242 minute => 0,
243 description => "Cleanup the database backups",
244 },
245 {
246 command => "cd $pg_backup_path ; /usr/bin/rm -f $(ls -1 *T22*.sql | log2rotate --skip 7 --fuzz 7 --delete --format='%Y-%m-%dT%H:%M:%S+02:00.sql')",
247 user => $pg_user,
248 hour => 3,
249 minute => 1,
250 description => "Cleanup the database backups exponentially",
251 },
252 ]
253 }
254
255 exec { "pg_basebackup $pg_path":
256 cwd => $pg_path,
257 user => $pg_user,
258 creates => "$pg_path/PG_VERSION",
259 environment => ["PGPASSWORD=$ldap_password"],
260 command => "/usr/bin/pg_basebackup -w -h $pg_host -U $ldap_cn -D $pg_path -S $pg_slot",
261 before => [
262 Concat["$pg_path/pg_hba.conf"],
263 Concat["$pg_path/recovery.conf"],
264 File["$pg_path/postgresql.conf"],
265 ]
266 }
267
268 concat { "$pg_path/pg_hba.conf":
269 owner => $pg_user,
270 group => $pg_group,
271 mode => '0640',
272 warn => true,
273 }
274 postgresql::server::pg_hba_rule { "$pg_backup_host - local access as postgres user":
275 description => 'Allow local access to postgres user',
276 type => 'local',
277 database => 'all',
278 user => $pg_user,
279 auth_method => 'ident',
280 order => "00-01",
281 target => "$pg_path/pg_hba.conf",
282 postgresql_version => "10",
283 }
284 postgresql::server::pg_hba_rule { "$pg_backup_host - localhost access as postgres user":
285 description => 'Allow localhost access to postgres user',
286 type => 'host',
287 database => 'all',
288 user => $pg_user,
289 address => "127.0.0.1/32",
290 auth_method => 'md5',
291 order => "00-02",
292 target => "$pg_path/pg_hba.conf",
293 postgresql_version => "10",
294 }
295 postgresql::server::pg_hba_rule { "$pg_backup_host - localhost ip6 access as postgres user":
296 description => 'Allow localhost access to postgres user',
297 type => 'host',
298 database => 'all',
299 user => $pg_user,
300 address => "::1/128",
301 auth_method => 'md5',
302 order => "00-03",
303 target => "$pg_path/pg_hba.conf",
304 postgresql_version => "10",
305 }
306 postgresql::server::pg_hba_rule { "$pg_backup_host - deny access to postgresql user":
307 description => 'Deny remote access to postgres user',
308 type => 'host',
309 database => 'all',
310 user => $pg_user,
311 address => "0.0.0.0/0",
312 auth_method => 'reject',
313 order => "00-04",
314 target => "$pg_path/pg_hba.conf",
315 postgresql_version => "10",
316 }
317
318 postgresql::server::pg_hba_rule { "$pg_backup_host - local access":
319 description => 'Allow local access with password',
320 type => 'local',
321 database => 'all',
322 user => 'all',
323 auth_method => 'md5',
324 order => "10-01",
325 target => "$pg_path/pg_hba.conf",
326 postgresql_version => "10",
327 }
328
329 postgresql::server::pg_hba_rule { "$pg_backup_host - local access with same name":
330 description => 'Allow local access with same name',
331 type => 'local',
332 database => 'all',
333 user => 'all',
334 auth_method => 'ident',
335 order => "10-02",
336 target => "$pg_path/pg_hba.conf",
337 postgresql_version => "10",
338 }
339
340 $primary_conninfo = "host=$pg_host port=$pg_port user=$ldap_cn password=$ldap_password sslmode=require"
341 $primary_slot_name = regsubst($ldap_cn, '-', "_", "G")
342 $standby_mode = "on"
343
344 concat { "$pg_path/recovery.conf":
345 owner => $pg_user,
346 group => $pg_group,
347 mode => '0640',
348 warn => true,
349 }
350 concat::fragment { "$pg_path/recovery.conf":
351 target => "$pg_path/recovery.conf",
352 content => template('postgresql/recovery.conf.erb'),
353 }
354
355 file { "$pg_path/postgresql.conf":
356 owner => $pg_user,
357 group => $pg_group,
358 mode => '0640',
359 content => template("role/backup/postgresql.conf.erb"),
360 }
361
362 service { "postgresql_backup@$pg_backup_host":
363 enable => true,
364 ensure => "running",
365 require => [
366 File["/etc/systemd/system/postgresql_backup@.service"],
367 Concat["$pg_path/pg_hba.conf"],
368 Concat["$pg_path/recovery.conf"],
369 File["$pg_path/postgresql.conf"],
370 ],
371 subscribe => [
372 Concat["$pg_path/pg_hba.conf"],
373 Concat["$pg_path/recovery.conf"],
374 File["$pg_path/postgresql.conf"],
375 ]
376 }
377 } 19 }
378 20
379} 21}
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 @@
1host <%= @ldap_server %>
2
3base <%= @ldap_base %>
4binddn <%= @ldap_dn %>
5bindpw <%= @ldap_password %>
6pam_login_attribute <%= @pgbouncer_ldap_attribute %>
7pam_filter <%= @ldap_filter %>