Robert Helewka
b4d60f2f38
Replaces the minimal project description with a comprehensive README including a component overview table, quick start instructions, common Ansible operations, and links to detailed documentation. Aligns with Red Panda Approval™ standards.
218 lines
7.7 KiB
YAML
218 lines
7.7 KiB
YAML
---
|
|
# -----------------------------------------------------------------------------
|
|
# PostgreSQL with SSL Deployment Playbook
|
|
# -----------------------------------------------------------------------------
|
|
# Deploys PostgreSQL 17 with SSL enabled for secure external connections
|
|
# Used for security-critical services like Casdoor identity provider
|
|
#
|
|
# Features:
|
|
# - Native PostgreSQL 17 installation (no Docker)
|
|
# - Self-signed SSL certificates for external access
|
|
# - Local connections without SSL for same-host services
|
|
# - Single-purpose database (not shared with other applications)
|
|
# -----------------------------------------------------------------------------
|
|
|
|
- name: Deploy PostgreSQL with SSL
|
|
hosts: ubuntu
|
|
become: true
|
|
tasks:
|
|
- name: Check if host has postgresql_ssl service
|
|
ansible.builtin.set_fact:
|
|
has_postgresql_ssl_service: "{{ 'postgresql_ssl' in services | default([]) }}"
|
|
|
|
- name: Skip hosts without postgresql_ssl service
|
|
ansible.builtin.meta: end_host
|
|
when: not has_postgresql_ssl_service
|
|
|
|
# -------------------------------------------------------------------------
|
|
# Install PostgreSQL
|
|
# -------------------------------------------------------------------------
|
|
|
|
- name: Install build dependencies
|
|
ansible.builtin.apt:
|
|
name:
|
|
- curl
|
|
- python3-psycopg2
|
|
- python3-cryptography
|
|
state: present
|
|
update_cache: true
|
|
|
|
- name: Install PostgreSQL Common
|
|
ansible.builtin.apt:
|
|
name: postgresql-common
|
|
state: present
|
|
|
|
- name: Install the public key for the PostgreSQL repository
|
|
ansible.builtin.shell: /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
|
|
args:
|
|
creates: /etc/apt/sources.list.d/pgdg.sources
|
|
register: pg_repo_output
|
|
|
|
- name: Install PostgreSQL packages
|
|
ansible.builtin.apt:
|
|
name:
|
|
- postgresql-client-17
|
|
- postgresql-17
|
|
state: present
|
|
update_cache: true
|
|
|
|
- name: Ensure PostgreSQL is running
|
|
ansible.builtin.systemd:
|
|
name: postgresql
|
|
state: started
|
|
enabled: true
|
|
|
|
# -------------------------------------------------------------------------
|
|
# Generate SSL Certificates
|
|
# -------------------------------------------------------------------------
|
|
|
|
- name: Create PostgreSQL SSL directory
|
|
ansible.builtin.file:
|
|
path: /etc/postgresql/17/main/ssl
|
|
state: directory
|
|
owner: postgres
|
|
group: postgres
|
|
mode: '0700'
|
|
|
|
- name: Generate PostgreSQL SSL private key
|
|
community.crypto.openssl_privatekey:
|
|
path: /etc/postgresql/17/main/ssl/server.key
|
|
size: 4096
|
|
owner: postgres
|
|
group: postgres
|
|
mode: '0600'
|
|
|
|
- name: Generate PostgreSQL SSL certificate signing request
|
|
community.crypto.openssl_csr:
|
|
path: /etc/postgresql/17/main/ssl/server.csr
|
|
privatekey_path: /etc/postgresql/17/main/ssl/server.key
|
|
common_name: "{{ inventory_hostname }}"
|
|
subject_alt_name:
|
|
- "DNS:{{ inventory_hostname }}"
|
|
- "DNS:localhost"
|
|
- "IP:127.0.0.1"
|
|
owner: postgres
|
|
group: postgres
|
|
mode: '0600'
|
|
|
|
- name: Generate self-signed PostgreSQL SSL certificate
|
|
community.crypto.x509_certificate:
|
|
path: /etc/postgresql/17/main/ssl/server.crt
|
|
privatekey_path: /etc/postgresql/17/main/ssl/server.key
|
|
csr_path: /etc/postgresql/17/main/ssl/server.csr
|
|
provider: selfsigned
|
|
selfsigned_not_after: "+3650d" # 10 years
|
|
owner: postgres
|
|
group: postgres
|
|
mode: '0644'
|
|
|
|
# -------------------------------------------------------------------------
|
|
# Configure PostgreSQL
|
|
# -------------------------------------------------------------------------
|
|
|
|
- name: Configure PostgreSQL to listen on all addresses
|
|
ansible.builtin.lineinfile:
|
|
path: /etc/postgresql/17/main/postgresql.conf
|
|
regexp: "^#?listen_addresses"
|
|
line: "listen_addresses = '*'"
|
|
backup: true
|
|
notify: restart postgresql
|
|
|
|
- name: Enable SSL in PostgreSQL
|
|
ansible.builtin.lineinfile:
|
|
path: /etc/postgresql/17/main/postgresql.conf
|
|
regexp: "^#?ssl ="
|
|
line: "ssl = on"
|
|
backup: true
|
|
notify: restart postgresql
|
|
|
|
- name: Configure SSL certificate file
|
|
ansible.builtin.lineinfile:
|
|
path: /etc/postgresql/17/main/postgresql.conf
|
|
regexp: "^#?ssl_cert_file"
|
|
line: "ssl_cert_file = '/etc/postgresql/17/main/ssl/server.crt'"
|
|
backup: true
|
|
notify: restart postgresql
|
|
|
|
- name: Configure SSL key file
|
|
ansible.builtin.lineinfile:
|
|
path: /etc/postgresql/17/main/postgresql.conf
|
|
regexp: "^#?ssl_key_file"
|
|
line: "ssl_key_file = '/etc/postgresql/17/main/ssl/server.key'"
|
|
backup: true
|
|
notify: restart postgresql
|
|
|
|
# -------------------------------------------------------------------------
|
|
# Configure Client Authentication (pg_hba.conf)
|
|
# -------------------------------------------------------------------------
|
|
|
|
- name: Configure local connections (no SSL, Unix socket)
|
|
ansible.builtin.lineinfile:
|
|
path: /etc/postgresql/17/main/pg_hba.conf
|
|
regexp: "^local\\s+all\\s+all\\s+peer"
|
|
line: "local all all peer"
|
|
backup: true
|
|
notify: restart postgresql
|
|
|
|
- name: Configure localhost connections (no SSL required)
|
|
ansible.builtin.lineinfile:
|
|
path: /etc/postgresql/17/main/pg_hba.conf
|
|
line: "host all all 127.0.0.1/32 md5"
|
|
insertafter: "^local"
|
|
backup: true
|
|
notify: restart postgresql
|
|
|
|
- name: Configure Incus private network connections (no SSL required)
|
|
ansible.builtin.lineinfile:
|
|
path: /etc/postgresql/17/main/pg_hba.conf
|
|
line: "host all all 10.10.0.0/16 md5"
|
|
insertafter: "^host.*127.0.0.1"
|
|
backup: true
|
|
notify: restart postgresql
|
|
|
|
- name: Configure external connections (SSL required)
|
|
ansible.builtin.lineinfile:
|
|
path: /etc/postgresql/17/main/pg_hba.conf
|
|
line: "hostssl all all 0.0.0.0/0 md5"
|
|
insertafter: "^host.*10.10.0.0"
|
|
backup: true
|
|
notify: restart postgresql
|
|
|
|
# -------------------------------------------------------------------------
|
|
# Set Admin Password
|
|
# -------------------------------------------------------------------------
|
|
|
|
- name: Set postgres user password
|
|
ansible.builtin.shell: |
|
|
sudo -u postgres psql -c "ALTER USER postgres PASSWORD '{{ postgresql_ssl_postgres_password }}'"
|
|
changed_when: false
|
|
no_log: true
|
|
|
|
# -------------------------------------------------------------------------
|
|
# Create Application Database and User
|
|
# -------------------------------------------------------------------------
|
|
|
|
- name: Create Casdoor database user
|
|
community.postgresql.postgresql_user:
|
|
name: "{{ casdoor_db_user }}"
|
|
password: "{{ casdoor_db_password }}"
|
|
state: present
|
|
login_user: postgres
|
|
login_password: "{{ postgresql_ssl_postgres_password }}"
|
|
login_host: localhost
|
|
no_log: true
|
|
|
|
- name: Create Casdoor database
|
|
community.postgresql.postgresql_db:
|
|
name: "{{ casdoor_db_name }}"
|
|
owner: "{{ casdoor_db_user }}"
|
|
state: present
|
|
login_user: postgres
|
|
login_password: "{{ postgresql_ssl_postgres_password }}"
|
|
login_host: localhost
|
|
|
|
handlers:
|
|
- name: restart postgresql
|
|
ansible.builtin.systemd:
|
|
name: postgresql
|
|
state: restarted |