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

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

- name: PostgreSQL packages installed
  community.general.pkgng:
    name: postgresql12-server,postgresql12-client,postgresql12-contrib,py37-psycopg2
    state: latest

- name: PostgreSQl db initalized
  shell: /usr/local/etc/rc.d/postgresql initdb
  ignore_errors: yes

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

- name: Postgres access file patched
  patch:
    src: pg_hba.conf.patch
    dest: /var/db/postgres/data12/pg_hba.conf
  become_user: postgres
  notify: PostgreSQL restarted

- name: Postgres config file patched
  patch:
    src: postgresql.conf.patch
    dest: /var/db/postgres/data12/postgresql.conf
  become_user: postgres
  notify: PostgreSQL restarted

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

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

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

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

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

- name: PBP database exists
  postgresql_db:
    name: "pbp"
    state: present
    owner: pbp
  become_user: postgres

- name: 15Five user exists
  postgresql_user:
    name: fifteenfive
    password: "{{ fifteenfive_db_pass }}"
    role_attr_flags: CREATEDB,NOSUPERUSER,LOGIN
  become_user: postgres
  tags:
    - create_dbs

- name: 15Five database exists
  postgresql_db:
    name: "fifteenfive"
    state: present
    owner: fifteenfive
  become_user: postgres
  tags:
    - create_dbs

- name: 15Five database has btree_gist extension
  postgresql_ext:
    name: btree_gist
    db: fifteenfive
  become_user: postgres
  tags:
    - create_dbs

- name: 15Five database has btree_gin extension
  postgresql_ext:
    name: btree_gin
    db: fifteenfive
  become_user: postgres
  tags:
    - create_dbs

- name: 15Five database has ltree extension
  postgresql_ext:
    name: ltree
    db: fifteenfive
  become_user: postgres
  tags:
    - create_dbs

- name: 15Five database has hstore extension
  postgresql_ext:
    name: hstore
    db: fifteenfive
  become_user: postgres
  tags:
    - create_dbs

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

- name: gogs database exists
  postgresql_db:
    name: "gogs"
    state: present
    owner: gogs
  become_user: postgres

- name: miniflux user exists
  postgresql_user:
    name: miniflux
    password: "{{ miniflux_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  become_user: postgres
  tags:
    - create_dbs

- name: miniflux database exists
  postgresql_db:
    name: "miniflux"
    state: present
    owner: miniflux
  become_user: postgres
  tags:
    - create_dbs

- name: photoview user exists
  postgresql_user:
    name: photoview
    password: "{{ photoview_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  become_user: postgres
  tags:
    - create_dbs

- name: photoview database exists
  postgresql_db:
    name: "photoview"
    state: present
    owner: photoview
  become_user: postgres
  tags:
    - create_dbs

- name: synapse user exists
  postgresql_user:
    name: synapse
    password: "{{ synapse_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  become_user: postgres
  tags:
    - create_dbs

- name: synapse database exists
  postgresql_db:
    name: "synapse"
    state: present
    owner: synapse
  become_user: postgres
  tags:
    - create_dbs

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

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

- postgresql_privs:
    db: postgres
    privs: SELECT
    type: schema
    objs: public
    role: grafana_read_only

- name: grafana database exists
  postgresql_db:
    name: "grafana"
    state: present
    owner: grafana
  become_user: postgres
  tags:
    - create_dbs

- name: sentry user exists
  postgresql_user:
    name: sentry
    password: "{{ sentry_db_pass }}"
    role_attr_flags: NOSUPERUSER,LOGIN
  become_user: postgres
  tags:
    - create_dbs

- name: sentry database exists
  postgresql_db:
    name: "sentry"
    state: present
    owner: sentry
  become_user: postgres
  tags:
    - create_dbs

- name: sentry database has citext extension
  postgresql_ext:
    name: citext
    db: sentry
  become_user: postgres
  tags:
    - create_dbs