add postgresql

7 files changed, 134 insertions, 0 deletions

diff --git a/files/etc/pam.d/postgresql.postgresql_server b/files/etc/pam.d/postgresql.postgresql_server
new file mode 100644
index 0000000..8475a53
--- /dev/null
+++ b/files/etc/pam.d/postgresql.postgresql_server
@@ -0,0 +1,2 @@
+auth  required  /usr/local/lib/security/pam_krb5.so try_first_pass keytab=${postgres_keytab} no_ccache ignore_k5login no_update_user minimum_uid=0
+account required pam_permit.so
diff --git a/files/var/db/postgres/data16/pg_hba.conf.postgresql_server b/files/var/db/postgres/data16/pg_hba.conf.postgresql_server
new file mode 100644
index 0000000..0e98783
--- /dev/null
+++ b/files/var/db/postgres/data16/pg_hba.conf.postgresql_server
@@ -0,0 +1,5 @@
+# TYPE      DATABASE        USER        ADDRESS   METHOD
+local       all             postgres              peer map=postgres
+local       all             all                   peer
+hostgssenc  all             all         all       gss  include_realm=0 krb_realm=${realm}
+hostssl     all             all         all       pam
diff --git a/files/var/db/postgres/data16/pg_ident.conf.postgresql_server b/files/var/db/postgres/data16/pg_ident.conf.postgresql_server
new file mode 100644
index 0000000..1076453
--- /dev/null
+++ b/files/var/db/postgres/data16/pg_ident.conf.postgresql_server
@@ -0,0 +1,3 @@
+# MAPNAME     SYSTEM-USERNAME     PG-USERNAME
+postgres      postgres            postgres
+postgres      root                postgres
diff --git a/files/var/db/postgres/data16/postgresql.conf.postgresql_server b/files/var/db/postgres/data16/postgresql.conf.postgresql_server
new file mode 100644
index 0000000..e95104f
--- /dev/null
+++ b/files/var/db/postgres/data16/postgresql.conf.postgresql_server
@@ -0,0 +1,43 @@
+listen_addresses = '*'
+max_connections = ${postgres_max_connections}
+
+krb_server_keyfile = 'FILE:${postgres_keytab}'
+krb_caseins_users = on
+
+ssl = on
+ssl_ca_file = '${ca_cert}'
+ssl_cert_file = '${postgres_tls_cert}'
+ssl_key_file = '${postgres_tls_key}'
+ssl_min_protocol_version = 'TLSv1.3'
+
+shared_buffers = '${postgres_shared_buffers}B'
+temp_buffers = '${postgres_temp_buffers}B'
+work_mem = '${postgres_work_mem}B'
+maintenance_work_mem = '${postgres_maintenance_work_mem}B'
+dynamic_shared_memory_type = posix
+
+wal_sync_method = fdatasync
+full_page_writes = off
+wal_compression = off
+wal_init_zero = off
+wal_recycle = off
+max_wal_size = 1GB
+min_wal_size = 80MB
+
+effective_cache_size = '${postgres_effective_cache_size}B'
+
+log_destination = 'syslog'
+syslog_sequence_numbers = off
+
+log_min_messages = info
+log_min_error_statement = warning
+log_line_prefix = '[%p] %q%u@%d '
+log_timezone = 'US/Eastern'
+
+datestyle = 'iso, mdy'
+timezone = 'US/Eastern'
+lc_messages = 'en_US.UTF-8'
+lc_monetary = 'en_US.UTF-8'
+lc_numeric = 'en_US.UTF-8'
+lc_time = 'en_US.UTF-8'
+default_text_search_config = 'pg_catalog.english'
diff --git a/scripts/hostclass/postgresql_server b/scripts/hostclass/postgresql_server
new file mode 100644
index 0000000..d92baa4
--- /dev/null
+++ b/scripts/hostclass/postgresql_server
@@ -0,0 +1,75 @@
+#!/bin/sh
+
+: ${postgres_max_connections:='128'}
+: ${postgres_shared_buffers:="$(( memsize  / 2 ))"}
+: ${postgres_work_mem:="$(( memsize / 4 / ${postgres_max_connections} ))"}
+: ${postgres_maintenance_work_mem:="$(( memsize / 20 ))"}
+: ${postgres_temp_buffers:="$((32 * 1024 * 1024))"}
+: ${postgres_effective_cache_size:="$(( memsize * 3 / 4 ))"}
+
+postgres_user=postgres
+postgres_home=/var/db/postgres
+postgres_data_dir="${postgres_home}/data${postgres_version}"
+postgres_tls_cert="${postgres_home}/postgres.crt"
+postgres_tls_key="${postgres_home}/postgres.key"
+postgres_keytab="${keytab_dir}/postgres.keytab"
+
+psql(){
+  command psql --quiet --no-align --echo-all --tuples-only --no-password --username=postgres --dbname=postgres "$@"
+}
+
+pkg install -y postgresql${postgresql_version}-server
+
+# Create ZFS dataset for postgresql data.
+create_dataset \
+  -o "mountpoint=${postgres_home}" \
+  -o recordsize=16k \
+  -o primarycache=metadata \
+  -o atime=off \
+  "${state_dataset}/postgres"
+install_directory -m 0755 -o "$postgres_user" -g "$postgres_user" "$postgres_home"
+
+# Initialize the database.
+sysrc -v postgresql_enable=YES
+[ -d "${postgres_data_dir}" ] || service postgresql initdb
+
+# Create service principal and keytab.
+add_principal -nokey -x "containerdn=${services_basedn}" "postgres/${fqdn}"
+
+ktadd -k "$postgres_keytab" "postgres/${fqdn}"
+chgrp "$postgres_user" "$postgres_keytab"
+chmod 640 "$postgres_keytab"
+
+postgres_uid=$(id -u "$postgres_user")
+install_directory -o "$postgres_user" -m 0700 "/var/krb5/user/${postgres_uid}"
+ln -snfv "$postgres_keytab" "/var/krb5/user/${postgres_uid}/keytab"
+
+# Create postgresql PAM service.
+install_template -m 0644 /etc/pam.d/postgresql
+
+# Copy TLS certificate for postgres.
+install_certificate     -m 0644 -o root -g "$postgres_user" postgres "$postgres_tls_cert"
+install_certificate_key -m 0640 -o root -g "$postgres_user" postgres "$postgres_tls_key"
+
+# Generate postgresql configuration.
+install_template -m 0600 -o "$postgres_user" -g "$postgres_user" \
+  "${postgres_data_dir}/postgresql.conf"                         \
+  "${postgres_data_dir}/pg_hba.conf"
+install_file -m 0600 -o "$postgres_user" -g "$postgres_user" \
+  "${postgres_data_dir}/pg_ident.conf"
+
+# The postgresql rc script seems to hold onto open descriptors, which causes
+# the parent boxconf SSH process to never close.
+echo 'Restarting postgresql.'
+service postgresql restart > /dev/null 2>&1 < /dev/null
+
+# Create boxconf admin user.
+psql -c "DO
+\$$
+BEGIN
+  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '${boxconf_user}') THEN
+    CREATE ROLE \"${boxconf_user}\" WITH LOGIN SUPERUSER;
+  END IF;
+END
+\$$"
+
diff --git a/vars/hostclass/postgres1 b/vars/hostclass/postgres1
new file mode 100644
index 0000000..a38ba94
--- /dev/null
+++ b/vars/hostclass/postgres1
@@ -0,0 +1,4 @@
+#!/bin/sh
+
+memsize=$(( 8 * 1024 * 1024 * 1024))
+cnames=postgres
diff --git a/vars/os/freebsd b/vars/os/freebsd
index 12d3938..5fae2d6 100644
--- a/vars/os/freebsd
+++ b/vars/os/freebsd
@@ -8,6 +8,8 @@ install_packages='sudo tmux vim'
 intel_epp=50
 see_other_uids=0
 
+memsize=$(sysctl -n hw.physmem)
+
 export ASSUME_ALWAYS_YES=yes
 keytab_dir=/var/db/keytabs
 nfscbd_port=7745