initial commit

8 files changed, 198 insertions, 0 deletions

diff --git a/roles/postgresql_server/defaults/main.yml b/roles/postgresql_server/defaults/main.yml
new file mode 100644
index 0000000..ccb398a
--- /dev/null
+++ b/roles/postgresql_server/defaults/main.yml
@@ -0,0 +1,5 @@
+postgresql_timezone: '{{ timezone }}'
+postgresql_max_connections: 100
+postgresql_shared_buffers_mb: '{{ (ansible_memory_mb.real.total / 4) | int }}'
+
+postgresql_password_users: []
diff --git a/roles/postgresql_server/files/etc/systemd/system/postgresql.service.d/override.conf b/roles/postgresql_server/files/etc/systemd/system/postgresql.service.d/override.conf
new file mode 100644
index 0000000..e355ab5
--- /dev/null
+++ b/roles/postgresql_server/files/etc/systemd/system/postgresql.service.d/override.conf
@@ -0,0 +1,6 @@
+[Unit]
+Wants=gssproxy.service
+After=network.target gssproxy.service
+
+[Service]
+Environment=GSS_USE_PROXY=yes
diff --git a/roles/postgresql_server/handlers/main.yml b/roles/postgresql_server/handlers/main.yml
new file mode 100644
index 0000000..c081f0f
--- /dev/null
+++ b/roles/postgresql_server/handlers/main.yml
@@ -0,0 +1,4 @@
+- name: restart postgresql
+  systemd:
+    name: postgresql
+    state: restarted
diff --git a/roles/postgresql_server/tasks/freeipa.yml b/roles/postgresql_server/tasks/freeipa.yml
new file mode 100644
index 0000000..50ea678
--- /dev/null
+++ b/roles/postgresql_server/tasks/freeipa.yml
@@ -0,0 +1,49 @@
+- name: create postgres service principal
+  ipaservice:
+    ipaadmin_principal: '{{ ipa_user }}'
+    ipaadmin_password: '{{ ipa_pass }}'
+    name: 'postgres/{{ ansible_fqdn }}'
+    state: present
+
+- name: retrieve postgres service keytab
+  include_role:
+    name: freeipa_keytab
+  vars:
+    keytab_principal: 'postgres/{{ ansible_fqdn }}'
+    keytab_path: '{{ postgresql_keytab }}'
+
+- name: create SELinux policy for postgres to access gssproxy
+  include_role:
+    name: selinux_policy
+    apply:
+      tags: selinux
+  vars:
+    selinux_policy_name: postrgres_gssproxy
+    selinux_policy_te: '{{ postgresql_selinux_policy_te }}'
+  tags: selinux
+
+- name: create systemd override directory
+  file:
+    path: /etc/systemd/system/postgresql.service.d/
+    state: directory
+
+- name: create systemd unit override
+  copy:
+    src: etc/systemd/system/postgresql.service.d/override.conf
+    dest: /etc/systemd/system/postgresql.service.d/override.conf
+  register: postgresql_systemd_override
+
+- name: reload systemd units
+  systemd:
+    daemon_reload: yes
+  when: postgresql_systemd_override.changed
+
+- name: configure gssproxy
+  include_role:
+    name: gssproxy_client
+  vars:
+    gssproxy_name: postgres
+    gssproxy_section: service/postgresql
+    gssproxy_keytab: '{{ postgresql_keytab }}'
+    gssproxy_cred_usage: accept
+    gssproxy_euid: postgres
diff --git a/roles/postgresql_server/tasks/main.yml b/roles/postgresql_server/tasks/main.yml
new file mode 100644
index 0000000..96b173c
--- /dev/null
+++ b/roles/postgresql_server/tasks/main.yml
@@ -0,0 +1,53 @@
+- name: install postgresql
+  dnf:
+    name: '{{ postgresql_packages }}'
+    state: present
+
+- name: initialize database
+  command:
+    cmd: postgresql-setup --initdb
+    creates: '{{ postgresql_data_dir }}/PG_VERSION'
+
+- import_tasks: freeipa.yml
+  tags: freeipa
+
+- name: request TLS certificate
+  include_role:
+    name: getcert_request
+  vars:
+    certificate_service: postgres
+    certificate_path: '{{ postgresql_certificate_path }}'
+    certificate_key_path: '{{ postgresql_certificate_key_path }}'
+    certificate_owner: postgres
+    certificate_hook: systemctl reload postgresql
+
+- name: generate dhparams
+  openssl_dhparam:
+    path: '{{ postgresql_dhparams_path }}'
+    size: 2048
+
+- name: generate postgresql configuration
+  template:
+    src: '{{ postgresql_data_dir[1:] }}/{{ item }}.j2'
+    dest: '{{ postgresql_data_dir }}/{{ item }}'
+    owner: postgres
+    group: postgres
+    mode: 0600
+  loop:
+    - postgresql.conf
+    - pg_hba.conf
+  notify: restart postgresql
+
+- name: enable postgresql service
+  systemd:
+    name: postgresql
+    enabled: yes
+    state: started
+
+- name: open firewall ports
+  firewalld:
+    service: postgresql
+    permanent: yes
+    immediate: yes
+    state: enabled
+  tags: firewalld
diff --git a/roles/postgresql_server/templates/var/lib/pgsql/data/pg_hba.conf.j2 b/roles/postgresql_server/templates/var/lib/pgsql/data/pg_hba.conf.j2
new file mode 100644
index 0000000..7768ad8
--- /dev/null
+++ b/roles/postgresql_server/templates/var/lib/pgsql/data/pg_hba.conf.j2
@@ -0,0 +1,7 @@
+# TYPE      DATABASE        USER            ADDRESS                 METHOD
+local       all             all                                     peer
+{% for user in postgresql_password_users %}
+hostssl     all             {{ user }}      all                     scram-sha-256
+{% endfor %}
+hostssl     all             all             all                     gss include_realm=0 krb_realm={{ freeipa_realm }}
+hostgssenc  all             all             all                     gss include_realm=0 krb_realm={{ freeipa_realm }}
diff --git a/roles/postgresql_server/templates/var/lib/pgsql/data/postgresql.conf.j2 b/roles/postgresql_server/templates/var/lib/pgsql/data/postgresql.conf.j2
new file mode 100644
index 0000000..07d198e
--- /dev/null
+++ b/roles/postgresql_server/templates/var/lib/pgsql/data/postgresql.conf.j2
@@ -0,0 +1,34 @@
+listen_addresses = '*'
+max_connections = {{ postgresql_max_connections }}
+
+ssl = on
+ssl_cert_file = '{{ postgresql_certificate_path }}'
+ssl_key_file = '{{ postgresql_certificate_key_path }}'
+ssl_dh_params_file = '{{ postgresql_dhparams_path }}'
+ssl_ciphers = '{{ postgresql_ssl_ciphers }}'
+
+password_encryption = scram-sha-256
+
+krb_caseins_users = on
+
+shared_buffers = {{ postgresql_shared_buffers_mb }}MB
+dynamic_shared_memory_type = posix	# the default is the first option
+
+log_destination = 'stderr'
+logging_collector = off
+log_min_messages = info
+log_min_error_statement = warning
+log_connections = off
+log_disconnections = off
+log_line_prefix = '%q%u@%d '
+log_timezone = '{{ postgresql_timezone }}'
+
+datestyle = 'iso, mdy'
+timezone = '{{ postgresql_timezone }}'
+
+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/roles/postgresql_server/vars/main.yml b/roles/postgresql_server/vars/main.yml
new file mode 100644
index 0000000..52cecc4
--- /dev/null
+++ b/roles/postgresql_server/vars/main.yml
@@ -0,0 +1,40 @@
+postgresql_packages:
+  - postgresql-server
+  - python3-psycopg2
+
+postgresql_user: postgres
+
+postgresql_data_dir: /var/lib/pgsql/data
+postgresql_keytab: /var/lib/gssproxy/postgresql.keytab
+
+postgresql_certificate_path: /etc/pki/tls/certs/postgres.pem
+postgresql_certificate_key_path: /etc/pki/tls/private/postgres.key
+postgresql_dhparams_path: /etc/pki/tls/certs/postgres-dhparams.pem
+postgresql_ssl_ciphers: 'ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384'
+
+postgresql_hbac_hostgroup: postgresql_servers
+postgresql_hbac_service: postgresql
+
+postgresql_archive_shell: >-
+  pg_dumpall | gzip > "pg_dumpall-$(date +%Y%m%d%H%M%S).sql.gz"
+
+postgresql_selinux_policy_te: |
+  require {
+    type postgresql_t;
+    type postgresql_exec_t;
+    type gssproxy_t;
+    type gssproxy_var_lib_t;
+    class dir search;
+    class sock_file write;
+    class unix_stream_socket connectto;
+    class file getattr;
+  }
+
+  #============= postgresql_t ==============
+  allow postgresql_t gssproxy_var_lib_t:dir search;
+  allow postgresql_t gssproxy_var_lib_t:sock_file write;
+  allow postgresql_t gssproxy_t:unix_stream_socket connectto;
+  allow postgresql_t gssproxy_var_lib_t:dir search;
+
+  #============= gssproxy_t ==============
+  allow gssproxy_t postgresql_exec_t:file getattr;