homelab/lab/ansible/roles/postgres/tasks/main.yml

---
- name: Package cache updated
  shell: pkg update -f

- name: PostgreSQL packages installed
  community.general.pkgng:
    name: postgresql13-server,postgresql13-client,postgresql13-contrib,py38-pip,rsync
    state: latest

- name: Psycopg2 installed
  pip: name=psycopg2 executable=pip

- name: Postgres enabled
  shell: sysrc postgresql_enable="YES"

- name: Postgres initialized
  shell: initdb --pgdata=/var/db/postgres/data13
  become: yes
  become_method: su
  become_user: postgres
  ignore_errors: yes

- name: Postgres access file patched
  patch:
    src: pg_hba.conf.patch
    dest: /var/db/postgres/data13/pg_hba.conf
  become: yes
  become_method: su
  become_user: postgres
  notify: PostgreSQL restarted

- name: Postgres config file patched
  patch:
    src: postgresql.conf.patch
    dest: /var/db/postgres/data13/postgresql.conf
  become: yes
  become_method: su
  become_user: postgres
  notify: PostgreSQL restarted

- name: Postgres config file permissions fixed
  file:
    path: /var/db/postgres/data13/postgresql.conf
    owner: postgres
    group: postgres

- name: Pg_hba config file permissions fixed
  file:
    path: /var/db/postgres/data13/pg_hba.conf
    owner: postgres
    group: postgres

- name: Ensure postgres is running
  service:
    name: postgresql
    state: started

- name: Postgres user password set
  community.postgresql.postgresql_user:
    name: postgres
    password: "{{ postgres_db_pass }}"

- name: PBP user exists
  community.postgresql.postgresql_user:
    name: pbp
    password: "{{ pbp_db_pass }}"
    role_attr_flags: CREATEDB,NOSUPERUSER,LOGIN

- name: PBP database exists
  community.postgresql.postgresql_db:
    name: "pbp"
    state: present
    owner: pbp
  become: yes
  become_method: su
  become_user: postgres

- name: 15Five user exists
  community.postgresql.postgresql_user:
    name: fifteenfive
    password: "{{ fifteenfive_db_pass }}"
    role_attr_flags: CREATEDB,NOSUPERUSER,LOGIN
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: 15Five database exists
  community.postgresql.postgresql_db:
    name: "fifteenfive"
    state: present
    owner: fifteenfive
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: 15Five database has btree_gist extension
  community.postgresql.postgresql_ext:
    name: btree_gist
    db: fifteenfive
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: 15Five database has btree_gin extension
  community.postgresql.postgresql_ext:
    name: btree_gin
    db: fifteenfive
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: 15Five database has ltree extension
  community.postgresql.postgresql_ext:
    name: ltree
    db: fifteenfive
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: 15Five database has hstore extension
  community.postgresql.postgresql_ext:
    name: hstore
    db: fifteenfive
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: gogs user exists
  community.postgresql.postgresql_user:
    name: gogs
    password: "{{ gogs_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  tags:
    - create_dbs

- name: gogs database exists
  community.postgresql.postgresql_db:
    name: "gogs"
    state: present
    owner: gogs
  become: yes
  become_method: su
  become_user: postgres

- name: miniflux user exists
  community.postgresql.postgresql_user:
    name: miniflux
    password: "{{ miniflux_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: miniflux database exists
  community.postgresql.postgresql_db:
    name: "miniflux"
    state: present
    owner: miniflux
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: photoview user exists
  community.postgresql.postgresql_user:
    name: photoview
    password: "{{ photoview_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: photoview database exists
  community.postgresql.postgresql_db:
    name: "photoview"
    state: present
    owner: photoview
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: synapse user exists
  community.postgresql.postgresql_user:
    name: synapse
    password: "{{ synapse_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: synapse database exists
  community.postgresql.postgresql_db:
    name: "synapse"
    state: present
    owner: synapse
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: grafana user exists
  community.postgresql.postgresql_user:
    name: grafana
    password: "{{ grafana_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  tags:
    - create_dbs

- name: grafana_read_only user exists
  community.postgresql.postgresql_user:
    name: grafana_read_only
    password: "{{ grafana_read_only_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  tags:
    - create_dbs

- name: grafana database exists
  community.postgresql.postgresql_db:
    name: "grafana"
    state: present
    owner: grafana
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: sentry user exists
  community.postgresql.postgresql_user:
    name: sentry
    password: "{{ sentry_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: sentry database exists
  community.postgresql.postgresql_db:
    name: "sentry"
    state: present
    owner: sentry
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: engagement user exists
  community.postgresql.postgresql_user:
    name: engagement
    password: "{{ engagement_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: engagement database exists
  community.postgresql.postgresql_db:
    name: "engagement"
    state: present
    owner: engagement
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - create_dbs

- name: sentry database has citext extension
  community.postgresql.postgresql_ext:
    name: citext
    db: sentry
  become_user: postgres
  become_method: su
  become: yes
  tags:
    - create_dbs