From 0261e875679f1bf63c8d689da7fc7e014597885d Mon Sep 17 00:00:00 2001 From: Stonewall Jackson Date: Sat, 4 Feb 2023 01:23:43 -0500 Subject: initial commit --- roles/postgresql_server/defaults/main.yml | 5 ++ .../system/postgresql.service.d/override.conf | 6 +++ roles/postgresql_server/handlers/main.yml | 4 ++ roles/postgresql_server/tasks/freeipa.yml | 49 ++++++++++++++++++++ roles/postgresql_server/tasks/main.yml | 53 ++++++++++++++++++++++ .../templates/var/lib/pgsql/data/pg_hba.conf.j2 | 7 +++ .../var/lib/pgsql/data/postgresql.conf.j2 | 34 ++++++++++++++ roles/postgresql_server/vars/main.yml | 40 ++++++++++++++++ 8 files changed, 198 insertions(+) create mode 100644 roles/postgresql_server/defaults/main.yml create mode 100644 roles/postgresql_server/files/etc/systemd/system/postgresql.service.d/override.conf create mode 100644 roles/postgresql_server/handlers/main.yml create mode 100644 roles/postgresql_server/tasks/freeipa.yml create mode 100644 roles/postgresql_server/tasks/main.yml create mode 100644 roles/postgresql_server/templates/var/lib/pgsql/data/pg_hba.conf.j2 create mode 100644 roles/postgresql_server/templates/var/lib/pgsql/data/postgresql.conf.j2 create mode 100644 roles/postgresql_server/vars/main.yml (limited to 'roles/postgresql_server') 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; -- cgit