postgres_version: "15"
postgres_daemon: "postgresql@{{ postgres_version }}-main"
postgres_user: "postgres"
postgres_pg_repack_cron_filename: ansible_postgres_pg_repack

postgres_users:
  - name: site
    password: "{{ vault_postgres_user_passwords.site }}"
    roles:

  - name: pixels
    password: "{{ vault_postgres_user_passwords.pixels }}"
    roles:

  - name: pinnwand
    password: "{{ vault_postgres_user_passwords.pinnwand }}"
    roles:

  - name: blackbox
    password: "{{ vault_postgres_user_passwords.blackbox }}"
    roles:
      - pg_read_all_data

  - name: metabase
    password: "{{ vault_postgres_user_passwords.metabase }}"
    roles:
      - pg_read_all_data

  - name: bitwarden
    password: "{{ vault_postgres_user_passwords.bitwarden }}"
    roles:

  - name: blackknight
    password: "{{ vault_postgres_user_passwords.blackknight }}"
    roles:

  - name: codejam_management
    password: "{{ vault_postgres_user_passwords.codejam_management }}"
    roles:

  - name: metricity
    password: "{{ vault_postgres_user_passwords.metricity }}"
    roles:

  - name: keycloak
    password: "{{ vault_postgres_user_passwords.keycloak }}"
    roles:

  - name: polonium
    password: "{{ vault_postgres_user_passwords.polonium }}"
    roles:

  - name: thallium
    password: "{{ vault_postgres_user_passwords.thallium }}"
    roles:

  - name: metabase
    password: "{{ vault_postgres_user_passwords.metabase }}"
    roles:
      - pg_read_all_data

  - name: grafana
    password: "{{ vault_postgres_user_passwords.grafana }}"
    roles:
      - pg_read_all_data

  - name: devops
    password: "{{ vault_postgres_user_passwords.devops }}"
    roles:
    role_attr_flags: SUPERUSER

  - name: rrdtool
    password: "{{ vault_postgres_user_passwords.rrdtool }}"
    roles:

postgres_hba_rules:
  #
  # Service HBA rules
  #
  - conn_type: hostssl
    database: pinnwand
    user: pinnwand
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: site
    user: grafana
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: site
    user: site
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: bitwarden
    user: bitwarden
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: blackknight
    user: blackknight
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: codejam_management
    user: codejam_management
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: pixels
    user: pixels
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: metricity
    user: metricity
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: metabase
    user: metabase
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: keycloak
    user: keycloak
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: polonium
    user: polonium
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: thallium
    user: thallium
    address: all
    method: scram-sha-256

  #
  # Backup service HBA rules
  #
  - conn_type: hostssl
    database: all
    user: blackbox
    address: all
    method: scram-sha-256

  #
  # Analytics HBA rules
  #
  - conn_type: hostssl
    database: metricity
    user: metabase
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: site
    user: metabase
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: metricity
    user: grafana
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: pinnwand
    user: grafana
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: pixels
    user: metabase
    address: all
    method: scram-sha-256

  - conn_type: hostssl
    database: polonium
    user: metabase
    address: all
    method: scram-sha-256

  #
  # DevOps Administration rules
  #
  - conn_type: hostssl
    database: all
    user: devops
    address: 127.0.0.1/32
    method: cert
    options: map=mtls_cn_map

  - conn_type: hostssl
    database: all
    user: devops
    address: "::1/128"
    method: cert
    options: map=mtls_cn_map

postgres_databases:
  - name: pinnwand
    owner: pinnwand
  - name: bitwarden
    owner: bitwarden
  - name: blackknight
    owner: blackknight
  - name: codejam_management
    owner: codejam_management
  - name: metricity
    owner: metricity
  - name: site
    owner: site
  - name: metabase
    owner: metabase
  - name: pixels
    owner: pixels
  - name: devops
    owner: devops
  - name: keycloak
    owner: keycloak
  - name: polonium
    owner: polonium
  - name: thallium
    owner: thallium

postgres_grants:
  - objs: api_nomination,api_infraction,api_user,api_nominationentry,pg_stat_statements,pg_stat_statements_info
    privs: SELECT
    state: present
    roles: grafana
    database: site
    type: table

  - objs: site
    privs: CONNECT
    state: present
    roles: grafana
    database: site
    type: database

  - objs: metricity
    privs: CONNECT
    state: present
    roles: rrdtool
    database: metricity
    type: database

  # Unfortunately we cannot be more granular here.
  # https://github.com/ansible/ansible-modules-core/issues/1529
  # -> https://github.com/ansible/ansible/issues/18955
  # -> https://github.com/ansible-collections/community.general/issues/118
  # -> https://github.com/ansible-collections/community.postgresql/issues/15
  # Otherwise, we would need:
  # - messages: id, channel_id, created_at
  # - threads: id, archived
  # - users: id, in_guild
  # The `id` columns are almost purely needed for counting.
  - objs: messages,threads,users
    privs: SELECT
    state: present
    roles: rrdtool
    database: metricity
    type: table