From c29df7cfc3b63c8ecd388edc82da52606f7f95e4 Mon Sep 17 00:00:00 2001
From: shtlrs <amrbellalouna@gmail.com>
Date: Thu, 6 Jun 2024 21:31:37 +0200
Subject: use host issued certs for postgres server instead of snakeoil's

---
 ansible/roles/postgres/files/postgresql.conf       | 53 ----------------------
 ansible/roles/postgres/tasks/main.yml              |  4 +-
 .../roles/postgres/templates/postgresql.conf.j2    | 53 ++++++++++++++++++++++
 3 files changed, 55 insertions(+), 55 deletions(-)
 delete mode 100644 ansible/roles/postgres/files/postgresql.conf
 create mode 100644 ansible/roles/postgres/templates/postgresql.conf.j2

(limited to 'ansible/roles/postgres')

diff --git a/ansible/roles/postgres/files/postgresql.conf b/ansible/roles/postgres/files/postgresql.conf
deleted file mode 100644
index 9127fc7..0000000
--- a/ansible/roles/postgres/files/postgresql.conf
+++ /dev/null
@@ -1,53 +0,0 @@
-data_directory = '/var/lib/postgresql/15/main'
-hba_file = '/etc/postgresql/15/main/pg_hba.conf'
-ident_file = '/etc/postgresql/15/main/pg_ident.conf'
-external_pid_file = '/var/run/postgresql/15-main.pid'
-listen_addresses = '89.58.26.118,localhost'
-port = 5432
-unix_socket_directories = '/var/run/postgresql'
-
-ssl = on
-ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
-ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
-ssl_ca_file = '/opt/pydis/ca.pem'
-
-dynamic_shared_memory_type = posix
-
-log_line_prefix = '%m [%p] %q%u@%d '
-log_timezone = 'Etc/UTC'
-datestyle = 'iso, mdy'
-timezone = 'Etc/UTC'
-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'
-include_dir = 'conf.d'
-
-cluster_name = 'schweinehund'
-
-# The following is from https://pgtune.leopard.in.ua/
-# DB Version: 15
-# OS Type: linux
-# DB Type: mixed
-# Total Memory (RAM): 64 GB
-# CPUs num: 10
-# Connections num: 100
-# Data Storage: ssd
-max_connections = 100
-shared_buffers = 16GB
-effective_cache_size = 48GB
-maintenance_work_mem = 2GB
-checkpoint_completion_target = 0.9
-wal_buffers = 16MB
-default_statistics_target = 100
-random_page_cost = 1.1
-effective_io_concurrency = 200
-work_mem = 20971kB
-huge_pages = try
-min_wal_size = 1GB
-max_wal_size = 4GB
-max_worker_processes = 10
-max_parallel_workers_per_gather = 4
-max_parallel_workers = 10
-max_parallel_maintenance_workers = 4
diff --git a/ansible/roles/postgres/tasks/main.yml b/ansible/roles/postgres/tasks/main.yml
index 6ba787d..fc57945 100644
--- a/ansible/roles/postgres/tasks/main.yml
+++ b/ansible/roles/postgres/tasks/main.yml
@@ -99,8 +99,8 @@
     - role::postgres
 
 - name: Import postgresql.conf
-  copy:
-    src: postgresql.conf
+  template:
+    src: postgresql.conf.j2
     dest: /etc/postgresql/{{ postgres_version }}/main/postgresql.conf
     owner: postgres
     group: postgres
diff --git a/ansible/roles/postgres/templates/postgresql.conf.j2 b/ansible/roles/postgres/templates/postgresql.conf.j2
new file mode 100644
index 0000000..4285ba5
--- /dev/null
+++ b/ansible/roles/postgres/templates/postgresql.conf.j2
@@ -0,0 +1,53 @@
+data_directory = '/var/lib/postgresql/15/main'
+hba_file = '/etc/postgresql/15/main/pg_hba.conf'
+ident_file = '/etc/postgresql/15/main/pg_ident.conf'
+external_pid_file = '/var/run/postgresql/15-main.pid'
+listen_addresses = '89.58.26.118,localhost'
+port = 5432
+unix_socket_directories = '/var/run/postgresql'
+
+ssl = on
+ssl_cert_file = '/etc/letsencrypt/live/{{ inventory_hostname }}.box.pydis.wtf/fullchain.pem'
+ssl_key_file = '/etc/letsencrypt/live/{{ inventory_hostname }}.box.pydis.wtf/privkey.pem'
+ssl_ca_file = '/opt/pydis/ca.pem'
+
+dynamic_shared_memory_type = posix
+
+log_line_prefix = '%m [%p] %q%u@%d '
+log_timezone = 'Etc/UTC'
+datestyle = 'iso, mdy'
+timezone = 'Etc/UTC'
+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'
+include_dir = 'conf.d'
+
+cluster_name = 'schweinehund'
+
+# The following is from https://pgtune.leopard.in.ua/
+# DB Version: 15
+# OS Type: linux
+# DB Type: mixed
+# Total Memory (RAM): 64 GB
+# CPUs num: 10
+# Connections num: 100
+# Data Storage: ssd
+max_connections = 100
+shared_buffers = 16GB
+effective_cache_size = 48GB
+maintenance_work_mem = 2GB
+checkpoint_completion_target = 0.9
+wal_buffers = 16MB
+default_statistics_target = 100
+random_page_cost = 1.1
+effective_io_concurrency = 200
+work_mem = 20971kB
+huge_pages = try
+min_wal_size = 1GB
+max_wal_size = 4GB
+max_worker_processes = 10
+max_parallel_workers_per_gather = 4
+max_parallel_workers = 10
+max_parallel_maintenance_workers = 4
-- 
cgit v1.2.3