use host issued certs for postgres server instead of snakeoil's

1 files changed, 53 insertions, 0 deletions

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