Задание 4: Кастомизированная установка
В этом разделе мы изучим процесс настройки кастомизированной установки, включая установку сертификатов. Завершающим этапом станет проверка корректности работы SSL/TLS для подтверждения безопасности соединения.
Настройка параметров SSL/TLS
В установке по умолчанию не настроено использование сертификатов SSL/TLS:
[student@srv1 ~]$ psql -c '\dconfig ssl*' "host=srv1 port=5433 user=postgres password=$(awk '/postgres_db_pass:/{print $2}' /home/student/Distr/distributive/installer/group_vars/custom_config_initial.yml)"
Список параметров конфигурации
Параметр | Значение
----------------------------------------+--------------------------
ssl | off
ssl_ca_file |
ssl_ca_path |
ssl_cert_file | server.crt
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL
ssl_crl_dir |
ssl_crl_file |
ssl_dh_params_file |
ssl_ecdh_curve | prime256v1
ssl_key_file | server.key
ssl_legacy_provider | on
ssl_library | OpenSSL
ssl_max_protocol_version |
ssl_min_protocol_version | TLSv1.2
ssl_passphrase_command |
ssl_passphrase_command_supports_reload | off
ssl_prefer_server_ciphers | on
(17 строк)
Получим из файла custom_config_initial.yml блок настроек, связанных с сертификатами (вся секция 3.2 файла), и добавим этот блок в конфигурационный файл:
[student@srv1 ~]$ sed -n -e '/3.2. SECURITY SETTINGS (SSL)/,$p' \
-e '/ssl_min_protocol_version:/q' \
/home/student/Distr/distributive/installer/group_vars/custom_config_initial.yml \
>> ~/config.yml
Разбор команды:
В этой команде использован потоковый редактор sed с двумя командами, заданными после опций -e. Первая команда печатает строки файла, начиная с той, в которой была обнаружена строка 3.2. SECURITY SETTINGS (SSL), и до конца файла. Опция -n нужна чтобы обрабатываемые строки не выводились дважды.
Вторая команда останавливает печать потока строк при обнаружении в строке ssl_min_protocol_version:. Таким образом, в стандартный поток вывода передается блок строк секции 3.2.
Сохраним оригинальную версию файла ~/config.yml:
[student@srv1 ~]$ cp -v ~/config.yml{,.orig}
'/home/student/config.yml' -> '/home/student/config.yml.orig'
Проверим его содержимое:
[student@srv1 ~]$ less ~/config.yml
Вывод команды:
hosts:
master:
hostname: srv1
username: student
password: student
replica:
hostname: srv2
username: student
password: student
arbiter:
hostname: srv3
username: student
password: student
local_distr_path: /home/student/Distr/distributive
pangolin_license_path: /home/student/license.json
tune_pangolin_local_repo: true
used_fqdn_host: false
####################################################### 3.2. SECURITY SETTINGS (SSL) ##################################################
mtls_support: false # Turn on SSL mode between cluster components
pkcs12_plugin_enable: false # Enable pkcs#12 support
pkcs12_use_ca_chain_from_container: false # If false, then need fill up 'root_ca_file' or 'root_ca_path' parameter
pkcs12_encrypt_passphrase: true # If true, passphrase in config file will encrypt
pkcs12_update_local_cert_files: false # Update local certs from remote server, if true then update
pg_certs_pwd:
root_ca_path: "{{ '' | default('/pg_ssl/root_dir', true) }}" # Root CA certificate dir for all components\nodes
crl_path: "" # Path to dir with CRL files
pg:
crl_file: "" # Path to CRL file
root_ca_file: "{{ '' | default('/pg_ssl/root.crt', true) }}" # Root CA certificate for all components\nodes
server_cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Main server certificate for each node
server_key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Main server private key for each node
server_p12:
use_remote_pki: false # If true, certificate will fetch/generate from SecMan
p12_secman: # Certificate attributes for its generation/search in SecMan
name: "{{ '' | default(ansible_fqdn, true) }}"
common_name: "{{ '' | default(ansible_fqdn, true) }}"
email: "{{ '' | default('test@test.ru', true) }}"
alt_names: "{{ '' | default(ansible_fqdn, true) }}"
ip_sans: "{{ '' | default(ansible_default_ipv4.address, true) }}"
other_sans: "{{ '' | default('', true) }}"
exclude_cn_from_sans: "{{ '' | default(false, true) }}"
p12_path: "{{ '' | default('/pg_ssl/intermediate/server.p12', true) }}" # Path to p12 container for server certificate
p12_pass: "{{ '' | default('test123', true) }}" # Passphrase to p12 container for server certificate
p12_config_path: "{{ '' | default('/pg_ssl/intermediate/server.p12.cfg', true) }}" # Path to config file with p12 container and encrypted passphrase for server certificate
pgbouncer:
crl_file: "" # Path to CRL file
root_ca_file: "{{ '' | default('/pg_ssl/root.crt', true) }}" # Root CA certificate for all components\nodes
server_cert: "{{ '' | default('/pg_ssl/pgbouncer_server.crt', true) }}" # Main server certificate for each node
server_key: "{{ '' | default('/pg_ssl/pgbouncer_server.key', true) }}" # Main server private key for each node
server_p12:
use_remote_pki: false # If true, certificate will fetch/generate from SecMan
p12_secman: # Certificate attributes for its generation/search in SecMan
name: "{{ '' | default(ansible_fqdn, true) }}"
common_name: "{{ '' | default(ansible_fqdn, true) }}"
email: "{{ '' | default('test@test.ru', true) }}"
alt_names: "{{ '' | default(ansible_fqdn, true) }}"
ip_sans: "{{ '' | default(ansible_default_ipv4.address, true) }}"
other_sans: "{{ '' | default('', true) }}"
exclude_cn_from_sans: "{{ '' | default(false, true) }}"
p12_path: "{{ '' | default('/pg_ssl/intermediate/pgbouncer_server.p12', true) }}" # Path to p12 container for server certificate
p12_pass: "{{ '' | default('test123', true) }}" # Passphrase to p12 container for server certificate
p12_config_path: "{{ '' | default('/pg_ssl/intermediate/pgbouncer_server.p12.cfg', true) }}" # Path to config file with p12 container and encrypted passphrase for server certificate
client_cert: "{{ '' | default('/pg_ssl/pgbouncer.crt', true) }}" # Certificate for connect pgbouncer to PGSE
client_key: "{{ '' | default('/pg_ssl/pgbouncer.key', true) }}" # Private key for connect pgbouncert to PGSE
client_p12:
use_remote_pki: false # If true, certificate will fetch/generate from SecMan
p12_secman: # Certificate attributes for its generation/search in SecMan
name: "{{ '' | default('pgbouncer', true) }}"
common_name: pgbouncer
email: "{{ '' | default('test@test.ru', true) }}"
alt_names: "{{ '' | default(ansible_fqdn, true) }}"
ip_sans: "{{ '' | default(ansible_default_ipv4.address, true) }}"
other_sans: "{{ '' | default('', true) }}"
exclude_cn_from_sans: "{{ '' | default(false, true) }}"
p12_path: "{{ '' | default('/pg_ssl/intermediate/pgbouncer.p12', true) }}" # Path to p12 container for pgbouncer certificate
p12_pass: "{{ '' | default('test123', true) }}" # Passphrase to p12 container for pgbouncer certificate
p12_config_path: "{{ '' | default('/pg_ssl/intermediate/pgbouncer.p12.cfg', true) }}" # Path to config file with p12 container and encrypted passphrase for pgbouncer certificate
patroni:
crl_file: "" # Path to CRL file
root_ca_file: "{{ '' | default('/pg_ssl/root.crt', true) }}" # Root CA certificate for all components\nodes
server_cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Main server certificate for each node
server_key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Main server private key for each node
server_p12:
use_remote_pki: false # If true, certificate will fetch/generate from SecMan
p12_secman: # Certificate attributes for its generation/search in SecMan
name: "{{ '' | default(ansible_fqdn, true) }}"
common_name: "{{ '' | default(ansible_fqdn, true) }}"
email: "{{ '' | default('test@test.ru', true) }}"
alt_names: "{{ '' | default(ansible_fqdn, true) }}"
ip_sans: "{{ '' | default(ansible_default_ipv4.address, true) }}"
other_sans: "{{ '' | default('', true) }}"
exclude_cn_from_sans: "{{ '' | default(false, true) }}"
p12_path: "{{ '' | default('/pg_ssl/intermediate/patroni_server.p12', true) }}" # Path to p12 container for server certificate
p12_pass: "{{ '' | default('test123', true) }}" # Passphrase to p12 container for server certificate
p12_config_path: "{{ '' | default('/pg_ssl/intermediate/patroni_server.p12.cfg', true) }}" # Path to config file with p12 container and encrypted passphrase for server certificate
client_cert: "{{ '' | default('/pg_ssl/patroni.crt', true) }}" # User patroni certificate
client_key: "{{ '' | default('/pg_ssl/patroni.key', true) }}" # User patroni private key
client_p12:
use_remote_pki: false # If true, certificate will generation/search from SecMan
p12_secman: # Certificate attributes for its generation/search in SecMan
name: "{{ '' | default('patroni', true) }}"
common_name: patroni
email: "{{ '' | default('test@test.ru', true) }}"
alt_names: "{{ '' | default(ansible_fqdn, true) }}"
ip_sans: "{{ '' | default(ansible_default_ipv4.address, true) }}"
other_sans: "{{ '' | default('', true) }}"
exclude_cn_from_sans: "{{ '' | default(false, true) }}"
p12_path: "{{ '' | default('/pg_ssl/intermediate/patroni.p12', true) }}" # Path to p12 container for patroni certificate
p12_pass: "{{ '' | default('test123', true) }}" # Passphrase to p12 container for patroni certificate
p12_config_path: "{{ '' | default('/pg_ssl/intermediate/patroni.p12.cfg', true) }}" # Path to config file with p12 container and encrypted passphrase for patroni certificate
pangolin_dcs:
crl_file: "{{ '' | default('/pg_ssl/crl/intermediate.crl', true) }}" # Path to CRL file
root_ca_file: "{{ '' | default('/pg_ssl/root.crt', true) }}" # Root CA certificate for all components\nodes
cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Certificate for Pangolin dcs
key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Private key for Pangolin dcs
p12:
use_remote_pki: false # If true, certificate will fetch/generate from SecMan
p12_secman: # Certificate attributes for its generation/search in SecMan
name: "{{ '' | default(ansible_fqdn, true) }}"
common_name: "{{ '' | default(ansible_fqdn, true) }}"
email: "{{ '' | default('test@test.ru', true) }}"
alt_names: "{{ '' | default(ansible_fqdn, true) }}"
ip_sans: "{{ '' | default(ansible_default_ipv4.address, true) }}"
other_sans: "{{ '' | default('', true) }}"
exclude_cn_from_sans: "{{ '' | default(false, true) }}"
p12_path: "{{ '' | default('/pg_ssl/intermediate/patroni_server.p12', true) }}" # Path to p12 container for server certificate
p12_pass: "{{ '' | default('test123', true) }}" # Passphrase to p12 container for server certificate
p12_config_path: "{{ '' | default('/pg_ssl/intermediate/patroni_server.p12.cfg', true) }}" # Path to config file with p12 container and encrypted passphrase for server certificate
pangolin_dcs_rest_api:
crl_file: "{{ '' | default('/pg_ssl/crl/intermediate.crl', true) }}" # Path to CRL file
root_ca_file: "{{ '' | default('/pg_ssl/root.crt', true) }}" # Root CA certificate for all components\nodes
cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Certificate for Pangolin Rest api
key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Private key for Pangolin Rest api
p12:
use_remote_pki: false # If true, certificate will fetch/generate from SecMan
p12_secman: # Certificate attributes for its generation/search in SecMan
name: "{{ '' | default(ansible_fqdn, true) }}"
common_name: "{{ '' | default(ansible_fqdn, true) }}"
email: "{{ '' | default('test@test.ru', true) }}"
alt_names: "{{ '' | default(ansible_fqdn, true) }}"
ip_sans: "{{ '' | default(ansible_default_ipv4.address, true) }}"
other_sans: "{{ '' | default('', true) }}"
exclude_cn_from_sans: "{{ '' | default(false, true) }}"
p12_path: "{{ '' | default('/pg_ssl/intermediate/patroni_server.p12', true) }}" # Path to p12 container for server certificate
p12_pass: "{{ '' | default('test123', true) }}" # Passphrase to p12 container for server certificate
p12_config_path: "{{ '' | default('/pg_ssl/intermediate/patroni_server.p12.cfg', true) }}" # Path to config file with p12 container and encrypted passphrase for server certificate
etcd:
server_cert: "{{ '' | default('/pg_ssl/etcd.crt', true) }}" # Certificate for etcd cluster
server_key: "{{ '' | default('/pg_ssl/etcd.key', true) }}" # Private key for etcd cluster
root_ca: "{{ '' | default('/pg_ssl/root.crt', true) }}" # Root CA certificate for etcd cluster
crl_file: "" # Path to CRL file for etcd
pangolin_certs_rotate:
enable: true # Set up service (actual for only install)
log:
directory: "{{ PGLOGS }}/pangolin-certs-rotate" # Path to log dir
filename: pangolin-certs-rotate-%Y-%m-%d_%H%M%S.log # Log name
level: info # Log level
destination: console,file # Log methods
certs:
enable_update: false # Enable/disable automatic renewal of certificates
poll_period_in_min: 60 # Certificate validity period [min]
retry_interval_in_sec: 30 # Timeout to restart in case of error [sec]
retry_attempts_num: 1 # Number of attempts after a failed launch
watch: # Lots of upgradable certificates
- locations: # Certificate configuration files
paths:
- "{%- if (pg_certs_pwd.pg.server_p12.use_remote_pki or pkcs12_update_local_cert_files) and inventory_hostname in groups['postgres_nodes'] -%}\
{{ pg_certs_pwd.pg.server_p12.p12_config_path }}\
{% endif %}"
on_update: # Commands to run after updating at least one of the certificates
- "{%- if 'patroni' in configuration_type | d('') -%}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-manager\
{% else %}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload postgresql\
{% endif %}"
- "{%- if 'pgbouncer' in configuration_type | d('') and inventory_hostname in groups['postgres_nodes'] -%}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-pooler\
{% elif 'poolermt' in configuration_type | d('') and inventory_hostname in groups['postgres_nodes'] %}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-pooler-mt\
{% endif %}"
- locations: # Certificate configuration files
paths:
- "{%- if ('pgbouncer' in configuration_type | d('') or 'poolermt' in configuration_type | d('')) and (pg_certs_pwd.pgbouncer.server_p12.use_remote_pki or pkcs12_update_local_cert_files) and inventory_hostname in groups['postgres_nodes'] -%}\
{{ pg_certs_pwd.pgbouncer.server_p12.p12_config_path }}\
{% endif %}"
on_update: # Commands to run after updating at least one of the certificates
- "{%- if 'patroni' in configuration_type | d('') -%}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-manager\
{% else %}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload postgresql\
{% endif %}"
- "{%- if 'pgbouncer' in configuration_type | d('') and inventory_hostname in groups['postgres_nodes'] -%}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-pooler\
{% elif 'poolermt' in configuration_type | d('') and inventory_hostname in groups['postgres_nodes'] %}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-pooler-mt\
{% endif %}"
- locations: # Certificate configuration files
paths:
- "{%- if 'patroni' in configuration_type | d('') and (pg_certs_pwd.patroni.server_p12.use_remote_pki or pkcs12_update_local_cert_files) -%}\
{{ pg_certs_pwd.patroni.server_p12.p12_config_path }}\
{% endif %}"
on_update: # Commands to run after updating at least one of the certificates
- "{%- if 'patroni' in configuration_type | d('') -%}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-manager\
{% endif %}"
- locations: # Certificate configuration files
paths:
- "{%- if 'patroni' in configuration_type | d('') and (pg_certs_pwd.patroni.client_p12.use_remote_pki or pkcs12_update_local_cert_files) and inventory_hostname in groups['postgres_nodes'] -%}\
{{ pg_certs_pwd.patroni.client_p12.p12_config_path }}\
{% endif %}"
on_update: # Commands to run after updating at least one of the certificates
- "{%- if 'patroni' in configuration_type | d('') and inventory_hostname in groups['postgres_nodes'] -%}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-manager\
{% endif %}"
- locations: # Certificate configuration files
paths:
- "{%- if ('pgbouncer' in configuration_type | d('') or 'poolermt' in configuration_type | d('')) and (pg_certs_pwd.pgbouncer.client_p12.use_remote_pki or pkcs12_update_local_cert_files) and inventory_hostname in groups['postgres_nodes'] -%}\
{{ pg_certs_pwd.pgbouncer.client_p12.p12_config_path }}\
{% endif %}"
on_update:
- "{%- if 'pgbouncer' in configuration_type | d('') and inventory_hostname in groups['postgres_nodes'] -%}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-pooler\
{% elif 'poolermt' in configuration_type | d('') and inventory_hostname in groups['postgres_nodes'] %}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-pooler-mt\
{% endif %}"
- locations: # Certificate configuration files
paths:
- "{%- if pangolin_dcs_enable -%}\
{{ pg_certs_pwd.pangolin_dcs.p12.p12_path }}\
{% endif %}"
on_update:
- "{%- if pangolin_dcs_enable -%}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-manager\
{% endif %}"
- locations: # Certificate configuration files
paths:
- "{%- if pangolin_dcs_enable -%}\
{{ pg_certs_pwd.pangolin_dcs_rest_api.p12.p12_path }}\
{% endif %}"
on_update:
- "{%- if pangolin_dcs_enable -%}\
{% if not systemctl_unit_on_user | d(false) %}sudo systemctl{% else %}systemctl --user{% endif %} reload pangolin-manager\
{% endif %}"
crl:
enable_update: false # Enable/disable automatic renewal of CRL files
poll_period_in_min: 60 # Period for checking for updated CRL files
retry_interval_on_expire_in_sec: 30 # Timeout to retry CRL upload after CRL publish date or expiration date
retry_interval_on_failure_in_sec: 30 # Timeout for retrying CRL upload after failure
retry_attempts_num_on_failure: 1 # Number of attempts to download the CRL by URI list for the given CRL file location path after failure
crl_file_perm: 0644 # Permissions set on uploaded CRL files
pgbouncer_client_tls_sslmode: "{{ '' | default('prefer', true) }}" # Ssl mode for conennections from users side (values: prefer,require,verify-ca,verify-full)
etcd_ciphers_suits: "{{ '' | default('TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384', true) }}" # List of supported TLS cipher suites between server/client and peers in etcd
allowlist_rest_api: "" # list hosts with access unsafe request to rest api patroni
ssl_min_protocol_version: 'TLSv1.2'
Посмотрим, какие имена файлов сертификатов и ключей серверов указаны в этом файле:
[student@srv1 ~]$ egrep '(server|etcd)\.(crt|key)' ~/config.yml
server_cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Main server certificate for each node
server_key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Main server private key for each node
server_cert: "{{ '' | default('/pg_ssl/pgbouncer_server.crt', true) }}" # Main server certificate for each node
server_key: "{{ '' | default('/pg_ssl/pgbouncer_server.key', true) }}" # Main server private key for each node
server_cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Main server certificate for each node
server_key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Main server private key for each node
cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Certificate for Pangolin dcs
key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Private key for Pangolin dcs
cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Certificate for Pangolin Rest api
key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Private key for Pangolin Rest api
server_cert: "{{ '' | default('/pg_ssl/etcd.crt', true) }}" # Certificate for etcd cluster
server_key: "{{ '' | default('/pg_ssl/etcd.key', true) }}" # Private key for etcd cluster
Итак, выделены следующие имена (без расширений):
[student@srv1 ~]$ egrep -o '[^/]*(server|etcd)\.(crt|key)' ~/config.yml | sort | uniq
etcd.crt
etcd.key
patroni_server.crt
patroni_server.key
pgbouncer_server.crt
pgbouncer_server.key
server.crt
server.key
Так как для всех компонентов выпускаются сертификаты с одинаковыми параметрами, для упрощения мы можем использовать один сертификат. Поэтому все сертификаты серверов будут server.crt, а ключи - server.key.
Отредактируем файл конфигурации соответствующим образом:
[student@srv1 ~]$ sed -i.bak 's/\/pg_ssl\/\(pgbouncer_server\|patroni_server\|etcd\)\.\(crt\|key\)/\/pg_ssl\/server.\2/' ~/config.yml
Потоковый редактор sed изменил содержимое файла ~/config.yml, сохранив при этом резервную копию ~/config.yml.bak.
Сравним содержимое этих файлов:
[student@srv1 ~]$ diff ~/config.yml{.bak,}
Вывод команды:
48,49c48,49
< server_cert: "{{ '' | default('/pg_ssl/pgbouncer_server.crt', true) }}" # Main server certificate for each node
< server_key: "{{ '' | default('/pg_ssl/pgbouncer_server.key', true) }}" # Main server private key for each node
---
> server_cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Main server certificate for each node
> server_key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Main server private key for each node
81,82c81,82
< server_cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Main server certificate for each node
< server_key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Main server private key for each node
---
> server_cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Main server certificate for each node
> server_key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Main server private key for each node
114,115c114,115
< cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Certificate for Pangolin dcs
< key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Private key for Pangolin dcs
---
> cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Certificate for Pangolin dcs
> key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Private key for Pangolin dcs
132,133c132,133
< cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Certificate for Pangolin Rest api
< key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Private key for Pangolin Rest api
---
> cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Certificate for Pangolin Rest api
> key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Private key for Pangolin Rest api
148,149c148,149
< server_cert: "{{ '' | default('/pg_ssl/etcd.crt', true) }}" # Certificate for etcd cluster
< server_key: "{{ '' | default('/pg_ssl/etcd.key', true) }}" # Private key for etcd cluster
---
> server_cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Certificate for etcd cluster
> server_key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Private key for etcd cluster
Заметно, что имена файлов заменены.
Осталось лишь включить поддержку SSL/TLS. За нее отвечает параметр mtls_support. Изменим его значение на true в config.yml:
mtls_support: true # Turn on SSL mode between cluster components
Выполним финальную проверку изменений в файле конфигурации:
[student@srv1 ~]$ diff ~/config.yml{.orig,}
Вывод команды:
18c18
< mtls_support: false # Turn on SSL mode between cluster components
---
> mtls_support: true # Turn on SSL mode between cluster components
48,49c48,49
< server_cert: "{{ '' | default('/pg_ssl/pgbouncer_server.crt', true) }}" # Main server certificate for each node
< server_key: "{{ '' | default('/pg_ssl/pgbouncer_server.key', true) }}" # Main server private key for each node
---
> server_cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Main server certificate for each node
> server_key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Main server private key for each node
81,82c81,82
< server_cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Main server certificate for each node
< server_key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Main server private key for each node
---
> server_cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Main server certificate for each node
> server_key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Main server private key for each node
114,115c114,115
< cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Certificate for Pangolin dcs
< key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Private key for Pangolin dcs
---
> cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Certificate for Pangolin dcs
> key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Private key for Pangolin dcs
132,133c132,133
< cert: "{{ '' | default('/pg_ssl/patroni_server.crt', true) }}" # Certificate for Pangolin Rest api
< key: "{{ '' | default('/pg_ssl/patroni_server.key', true) }}" # Private key for Pangolin Rest api
---
> cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Certificate for Pangolin Rest api
> key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Private key for Pangolin Rest api
148,149c148,149
< server_cert: "{{ '' | default('/pg_ssl/etcd.crt', true) }}" # Certificate for etcd cluster
< server_key: "{{ '' | default('/pg_ssl/etcd.key', true) }}" # Private key for etcd cluster
---
> server_cert: "{{ '' | default('/pg_ssl/server.crt', true) }}" # Certificate for etcd cluster
> server_key: "{{ '' | default('/pg_ssl/server.key', true) }}" # Private key for etcd cluster
Установка сертификатов
Ранее в разделе «Задание 3: Сертификаты TLS» на всех узлах кластера уже была сделана установка всех сертификатов.
Запуск кастомизированной установки
Простейшая кастомизация – добавление имени кластера перед его установкой. Для этого достаточно добавить в файл config.yml настройку clustername:. Например, так:
clustername: 'Pobeda'
Также мы настроили использование сертификатов TLS.
Конфигурация подготовлена, сертификаты находятся в каталоге, указанном в настройках.
Для запуска установки выполним ту же команду, что и в первый раз. Но теперь будет обнаружен работающий кластер, поэтому Pangolin Installer выдаст соответствующее предупреждение.
Примечание
Если согласиться с удалением предыдущей установки кластера, программа установки выполнит всю необходимую работу по удалению и очистке, а далее установит кластер заново.
На всех трех серверах от имени student сделайте:
[student@srv1 ~]$ for comp in srv{1..3}; do echo -e "\n$comp"; ssh $comp sudo rm -rf /etc/yum.repos.d/pangolin_local_repo_*.repo; done
Перед запуском установки необходимо активировать виртуальное окружение Python:
[student@srv1 ~]$ . /opt/pangolin-ansible-venv-controller/bin/activate
(pangolin-ansible-venv-controller) [student@srv1 ~]$ /opt/pangolin-ansible-venv-controller/bin/pangolin-installer terminal -c config.yml -a install
Вывод команды:
Запуск предварительных проверок.
Предварительные проверки пройдены.
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| clean |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Node | Action | CPU | MEM | Disk available | OS | Version OS | Packages Removed | Packages Installed | Configuration |
|----------------|---------|-----|------|--------------------------|--------|------------|------------------------------------------|--------------------|---------------|
| node: master | install | 1 | 4 GB | / 14G | RED OS | 8.0 | pangolin-ansible-venv-controller (6.7.0) | | cluster |
| ip/dns: (srv1) | | | | | | | pangolin-installer (1.0.3) | | |
| | | | | | | | pangolin-ansible-venv-controlled (6.7.0) | | |
| | | | | | | | pangolin-auth-password (6.7.0) | | |
| | | | | | | | pangolin-certs-rotate (6.7.0) | | |
| | | | | | | | pangolin-security-utilities (6.7.0) | | |
| | | | | | | | pangolin-dbms-6.7 (6.7.0) | | |
| | | | | | | | pangolin-dbms-6.7-client (6.7.0) | | |
| | | | | | | | pangolin-timescaledb-6.7-apache (2.25.0) | | |
| | | | | | | | pangolin-manager-venv (2.1.13) | | |
| | | | | | | | pangolin-manager (2.1.13) | | |
| | | | | | | | pangolin-backup-tools-venv (6.7.0) | | |
| | | | | | | | pangolin-backup-tools (6.7.0) | | |
| | | | | | | | pangolin-pooler (1.5.6) | | |
| | | | | | | | pangolin-diagnostic-tool (6.7.0) | | |
| | | | | | | | pangolin-auth-reencrypt (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-pltcl (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-plperl (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-jsonb-plperl (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-hstore-plperl (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-bool-plperl (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-plpython3 (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-ltree-plpython3 (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-jsonb-plpython3 (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-hstore-plpython3 (6.7.0) | | |
| | | | | | | | pangolin-tuner (1.1.1) | | |
| | | | | | | | | | |
| node: replica | install | 1 | 4 GB | / 19G | RED OS | 8.0 | pangolin-ansible-venv-controlled (6.7.0) | | cluster |
| ip/dns: (srv2) | | | | | | | pangolin-auth-password (6.7.0) | | |
| | | | | | | | pangolin-certs-rotate (6.7.0) | | |
| | | | | | | | pangolin-security-utilities (6.7.0) | | |
| | | | | | | | pangolin-dbms-6.7 (6.7.0) | | |
| | | | | | | | pangolin-dbms-6.7-client (6.7.0) | | |
| | | | | | | | pangolin-timescaledb-6.7-apache (2.25.0) | | |
| | | | | | | | pangolin-manager-venv (2.1.13) | | |
| | | | | | | | pangolin-manager (2.1.13) | | |
| | | | | | | | pangolin-backup-tools-venv (6.7.0) | | |
| | | | | | | | pangolin-backup-tools (6.7.0) | | |
| | | | | | | | pangolin-pooler (1.5.6) | | |
| | | | | | | | pangolin-diagnostic-tool (6.7.0) | | |
| | | | | | | | pangolin-auth-reencrypt (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-pltcl (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-plperl (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-jsonb-plperl (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-hstore-plperl (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-bool-plperl (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-plpython3 (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-ltree-plpython3 (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-jsonb-plpython3 (6.7.0) | | |
| | | | | | | | pangolin-dbms-6-hstore-plpython3 (6.7.0) | | |
| | | | | | | | pangolin-tuner (1.1.1) | | |
| | | | | | | | | | |
| node: arbiter | install | 1 | 4 GB | / 19G | RED OS | 8.0 | | | cluster |
| ip/dns: (srv3) | | | | | | | | | |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Процедура очистит стенд от компонентов Pangolin. Продолжить? (Y/N): Y
Выполняемая задача: clean
🟢 Роль MAIN PLAY - Завершена! [Время выполнения: 0:02:43] Задач выполнено: 43 | | |#####################################|
Очистка стенда(ов) прошла успешно!
Запуск предварительных проверок.
Предварительные проверки пройдены.
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| install |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Node | Action | CPU | MEM | Disk available | OS | Version OS | Packages Removed | Packages Installed | Configuration |
|----------------|---------|-----|------|--------------------------|--------|------------|------------------------------------------|--------------------|---------------|
| node: master | install | 1 | 4 GB | / 15G | RED OS | 8.0 | pangolin-ansible-venv-controller (6.7.0) | | cluster |
| ip/dns: (srv1) | | | | | | | pangolin-installer (1.0.3) | | |
| | | | | | | | pangolin-timescaledb-6.7-apache (2.25.0) | | |
| | | | | | | | | | |
| node: replica | install | 1 | 4 GB | / 20G | RED OS | 8.0 | pangolin-timescaledb-6.7-apache (2.25.0) | | cluster |
| ip/dns: (srv2) | | | | | | | | | |
| node: arbiter | install | 1 | 4 GB | / 20G | RED OS | 8.0 | | | cluster |
| ip/dns: (srv3) | | | | | | | | | |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Выполняемая задача: install
🟢 Роль MAIN PLAY - Завершена! [Время выполнения: 0:00:10] Задач выполнено: 20 | | |#####################################|
🟢 Роль PANGOLIN_CHECKS - Завершена! [Время выполнения: 0:07:42] Задач выполнено: 662 | | |#####################################|
🟢 Роль PANGOLIN_LICENSE - Завершена! [Время выполнения: 0:00:04] Задач выполнено: 7 | | |#####################################|
🟢 Роль PANGOLIN_AUTH_PASSWORD - Завершена! [Время выполнения: 0:00:05] Задач выполнено: 8 | | |#####################################|
🟢 Роль ETCD - Завершена! [Время выполнения: 0:00:34] Задач выполнено: 34 | | |#####################################|
🟢 Роль PANGOLIN_CERTS_ROTATE - Завершена! [Время выполнения: 0:00:24] Задач выполнено: 26 | | |#####################################|
🟢 Роль PANGOLIN_SECURITY_UTILITIES - Завершена! [Время выполнения: 0:00:08] Задач выполнено: 18 | | |#####################################|
🟢 Роль PANGOLIN_DBMS - Завершена! [Время выполнения: 0:00:56] Задач выполнено: 96 | | |#####################################|
🟢 Роль PANGOLIN_MANAGER - Завершена! [Время выполнения: 0:01:07] Задач выполнено: 31 | | |#####################################|
🟢 Роль PANGOLIN_BACKUP_TOOLS - Завершена! [Время выполнения: 0:00:15] Задач выполнено: 37 | | |#####################################|
🟢 Роль PANGOLIN_POOLER - Завершена! [Время выполнения: 0:00:18] Задач выполнено: 16 | | |#####################################|
🟢 Роль PANGOLIN_POOLER_MT - Завершена! [Время выполнения: 0:00:00] Задач выполнено: 5 | | |#####################################|
🟢 Роль PANGOLIN_DIAGNOSTIC_TOOL - Завершена! [Время выполнения: 0:00:06] Задач выполнено: 12 | | |#####################################|
🟢 Роль PANGOLIN_AUTH_REENCRYPT - Завершена! [Время выполнения: 0:00:18] Задач выполнено: 20 | | |#####################################|
🟢 Роль PANGOLIN_UNTRUSTED_EXTENSIONS - Завершена! [Время выполнения: 0:01:06] Задач выполнено: 151 | | |#####################################|
🟢 Роль CONFIGURE - Завершена! [Время выполнения: 0:01:58] Задач выполнено: 167 | | |#####################################|
Установка Pangolin прошла успешно!
Запуск предварительных проверок.
Предварительные проверки пройдены.
|---------------------------------------------------------------------------|
| after install |
|---------------------------------------------------------------------------|
| Node | Action | Packages Installed |
|----------------|---------------|------------------------------------------|
| node: master | after install | pangolin-ansible-venv-controller (6.7.0) |
| ip/dns: (srv1) | | pangolin-installer (1.0.3) |
| | | pangolin-timescaledb-6.7-apache (2.25.0) |
| | | pangolin-ansible-venv-controlled (6.7.0) |
| | | pangolin-auth-password (6.7.0) |
| | | pangolin-certs-rotate (6.7.0) |
| | | pangolin-security-utilities (6.7.0) |
| | | pangolin-dbms-6.7 (6.7.0) |
| | | pangolin-dbms-6.7-client (6.7.0) |
| | | pangolin-manager-venv (2.1.13) |
| | | pangolin-manager (2.1.13) |
| | | pangolin-backup-tools-venv (6.7.0) |
| | | pangolin-backup-tools (6.7.0) |
| | | pangolin-pooler (1.5.6) |
| | | pangolin-diagnostic-tool (6.7.0) |
| | | pangolin-auth-reencrypt (6.7.0) |
| | | pangolin-dbms-6-pltcl (6.7.0) |
| | | pangolin-dbms-6-plperl (6.7.0) |
| | | pangolin-dbms-6-jsonb-plperl (6.7.0) |
| | | pangolin-dbms-6-hstore-plperl (6.7.0) |
| | | pangolin-dbms-6-bool-plperl (6.7.0) |
| | | pangolin-dbms-6-plpython3 (6.7.0) |
| | | pangolin-dbms-6-ltree-plpython3 (6.7.0) |
| | | pangolin-dbms-6-jsonb-plpython3 (6.7.0) |
| | | pangolin-dbms-6-hstore-plpython3 (6.7.0) |
| | | pangolin-tuner (1.1.1) |
| | | |
| node: replica | after install | pangolin-timescaledb-6.7-apache (2.25.0) |
| ip/dns: (srv2) | | pangolin-ansible-venv-controlled (6.7.0) |
| | | pangolin-auth-password (6.7.0) |
| | | pangolin-certs-rotate (6.7.0) |
| | | pangolin-security-utilities (6.7.0) |
| | | pangolin-dbms-6.7 (6.7.0) |
| | | pangolin-dbms-6.7-client (6.7.0) |
| | | pangolin-manager-venv (2.1.13) |
| | | pangolin-manager (2.1.13) |
| | | pangolin-backup-tools-venv (6.7.0) |
| | | pangolin-backup-tools (6.7.0) |
| | | pangolin-pooler (1.5.6) |
| | | pangolin-diagnostic-tool (6.7.0) |
| | | pangolin-auth-reencrypt (6.7.0) |
| | | pangolin-dbms-6-pltcl (6.7.0) |
| | | pangolin-dbms-6-plperl (6.7.0) |
| | | pangolin-dbms-6-jsonb-plperl (6.7.0) |
| | | pangolin-dbms-6-hstore-plperl (6.7.0) |
| | | pangolin-dbms-6-bool-plperl (6.7.0) |
| | | pangolin-dbms-6-plpython3 (6.7.0) |
| | | pangolin-dbms-6-ltree-plpython3 (6.7.0) |
| | | pangolin-dbms-6-jsonb-plpython3 (6.7.0) |
| | | pangolin-dbms-6-hstore-plpython3 (6.7.0) |
| | | pangolin-tuner (1.1.1) |
| | | |
| node: arbiter | after install | |
| ip/dns: (srv3) | | |
|---------------------------------------------------------------------------|
Теперь можно деактивировать виртуальное окружение:
(pangolin-ansible-venv-controller) [student@srv1 ~]$ deactivate
[student@srv1 ~]$
Проверим состояние кластера:
[student@srv1 ~]$ sudo su - postgres
===============================================================
You are connected to Pangolin Cluster (Pobeda)
- Hostname : srv1
- IP Address : <IP-Address1>
---------------------------------------------------------------
- node ( master ) : <IP-Address1>
- node ( replica ) : <IP-Address2>
- arbiter : <IP-Address3>
- Manager version : 2.1.13
- Pangolin version : 6.7.0
===============================================================
[postgres@srv1 ~]$ list
+ Cluster: Pobeda (7629810776015440620) ----------------------------------|
| Member | Host | Role | State | TL | Lag in MB |
|-----------------|-----------|--------------|-----------|----|-----------|
| <IP-Address1> | srv1:5433 | Leader | running | 3 | |
| <IP-Address2> | srv2:5433 | Sync Standby | streaming | 3 | 0 |
|-------------------------------------------------------------------------|
[postgres@srv1 ~]$ members
|------------------------------------------------------------------------------------------|
| ID | STATUS | NAME | PEER ADDRS | CLIENT ADDRS | IS LEARNER |
|------------------|---------|------|--------------------|--------------------|------------|
| 54ec0c37709f3054 | started | srv1 | <IP-Address1>:2380 | <IP-Address1>:2379 | false |
| 8c95e735c5b6384c | started | srv2 | <IP-Address2>:2380 | <IP-Address2>:2379 | false |
| c80310d8dc193330 | started | srv3 | <IP-Address3>:2380 | <IP-Address3>:2379 | false |
|------------------------------------------------------------------------------------------|
[postgres@srv1 ~]$ exit
Проверка SSL/TLS
Проверим настройки SSL/TLS, подключившись к кластеру:
[student@srv1 ~]$ psql -c '\dconfig ssl*' "host=srv1 port=5433 user=postgres password=$(awk '/postgres_db_pass:/{print $2}' Distr/distributive/installer/group_vars/custom_config_initial.yml)"
Список параметров конфигурации
Параметр | Значение
----------------------------------------|--------------------------
ssl | on
ssl_ca_file | /pg_ssl/root.crt
ssl_ca_path | /pg_ssl/root_dir
ssl_cert_file | /pg_ssl/server.crt
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL
ssl_crl_dir |
ssl_crl_file |
ssl_dh_params_file |
ssl_ecdh_curve | prime256v1
ssl_key_file | /pg_ssl/server.key
ssl_legacy_provider | on
ssl_library | OpenSSL
ssl_max_protocol_version |
ssl_min_protocol_version | TLSv1.2
ssl_passphrase_command |
ssl_passphrase_command_supports_reload | off
ssl_prefer_server_ciphers | on
(17 строк)
Подключимся:
[student@srv1 ~]$ psql "host=srv1 port=5433 user=postgres password=$(awk '/postgres_db_pass:/{print $2}' Distr/distributive/installer/group_vars/custom_config_initial.yml)"
psql (15.15)
Введите "help", чтобы получить справку.
Проверим, какие приложения подключились, а также их TLS состояние:
postgres=# SELECT s.pid, a.application_name,
s.ssl, s.version, s.cipher, s.bits, s.client_dn,
s.client_serial, s.issuer_dn FROM pg_stat_ssl s
JOIN pg_stat_activity a
ON s.pid = a.pid \gx
-[ RECORD 1 ]----|-------------------------------------------------
pid | 83821
application_name | Patroni heartbeat
ssl | t
version | TLSv1.3
cipher | TLS_AES_256_GCM_SHA384
bits | 256
client_dn | /CN=patroni
client_serial | 358834973879495873522635658706975484713542217744
issuer_dn | /C=RU/ST=Moscow/L=Moscow/O=Sbt/CN=CA_DBP2
-[ RECORD 2 ]----|-------------------------------------------------
pid | 83825
application_name | srv2
ssl | t
version | TLSv1.3
cipher | TLS_AES_256_GCM_SHA384
bits | 256
client_dn | /CN=patroni
client_serial | 453601307538392109840440975688900289404160089881
issuer_dn | /C=RU/ST=Moscow/L=Moscow/O=Sbt/CN=CA_DBP2
-[ RECORD 3 ]----|-------------------------------------------------
pid | 84476
application_name | psql
ssl | f
version |
cipher |
bits |
client_dn |
client_serial |
issuer_dn |
Очевидно, что SSL/TLS работает, однако текущий сеанс не защищен.
postgres=# /q
Проверим защиту SSL/TLS в сеансе ОС postgres:
[student@srv1 ~]$ sudo su - postgres
===============================================================
You are connected to Pangolin Cluster (Pobeda)
- Hostname : srv1
- IP Address : <IP-Address1>
---------------------------------------------------------------
- node ( master ) : <IP-Address1>
- node ( replica ) : <IP-Address2>
- arbiter : <IP-Address3>
- Manager version : 2.1.13
- Pangolin version : 6.7.0
===============================================================
[postgres@srv1 ~]$ psql "port=5433 host=srv1 password=<password>"
postgres=# \conninfo
Вы подключены к базе данных "postgres" как пользователь "postgres" (компьютер "srv1": адрес "<IP-Address1>", порт "5433").
Проверим состояние TLS:
postgres=# SELECT s.pid, a.application_name,
s.ssl, s.version, s.cipher, s.bits, s.client_dn,
s.client_serial, s.issuer_dn FROM pg_stat_ssl s
JOIN pg_stat_activity a
ON s.pid = a.pid WHERE s.pid = pg_backend_pid() \gx
-[ RECORD 1 ]----|-----------------------
pid | 90519
application_name | psql
ssl | t
version | TLSv1.3
cipher | TLS_AES_256_GCM_SHA384
bits | 256
client_dn |
client_serial |
issuer_dn |
Сеанс защищен.
Подведем итоги
Вопрос 1
Вопрос 1: В какой файл конфигурации должны быть помещены кастомизированные настройки для Pangolin Installer?
Вопрос 2
Вопрос 2: Что произойдет по умолчанию, если программа установки Pangolin Installer обнаружит работающий экземпляр Pangolin?
Вопрос 3
Вопрос 3: Какая кастомизированная настройка включит SSL/TLS?
Вопрос 4
Вопрос 4: Исходя из следующего:
postgres=# SELECT s.pid, a.application_name, s.ssl, s.version, s.cipher, s.bits, s.client_dn, s.client_serial, s.issuer_dn FROM pg_stat_ssl s
JOIN pg_stat_activity a
ON s.pid = a.pid WHERE s.pid = pg_backend_pid() \gx
-[ RECORD 1 ]----+-----------------------
pid | 90519
application_name | psql
ssl | t
version | TLSv1.3
cipher | TLS_AES_256_GCM_SHA384
bits | 256'
client_dn |
client_serial |
issuer_dn |
Выберите все верные утверждения: