roles/server/postgresql/tasks/main.yml

   1 #####################################
   2 ### someone's ansible provisioner ###
   3 #####################################
   4 # Part of: https://git.somenet.org/root/pub/somesible.git
   5 # 2017-2024 by someone <someone@somenet.org>
   6 #
   7 ###########################################################
   8 # DO NOT FORGET TO UPDATE POSTGRESQL.CONF - CLUSTER PATHS #
   9 ###########################################################
  10 # YOU WILL KILL THE LIVE-CLUSTER OTHERWISE! #
  11 #############################################
  12 ---
  13 - name: install postgresql
  14   apt:
  15     pkg:
  16     - postgresql-15
  17     - postgresql-15-postgis-3
  18     - pg-activity
  19     - python3-psycopg2
  20     - libdbd-pg-perl
  21     state: present
  22     policy_rc_d: 101
  23   tags: "online"
  24   ignore_errors: "{{ignore_online_errors | bool}}"
  25
  26
  27 - name: copy postgresql.conf
  28   copy:
  29     src: "{{item}}"
  30     dest: "/etc/postgresql/15/main/postgresql.conf"
  31     mode: 0644
  32     owner: "postgres"
  33     group: "postgres"
  34   with_first_found:
  35     - "{{lookup('env','PWD')}}/host_files/{{inventory_hostname}}/{{role_name}}/postgresql.conf"
  36     - "{{lookup('env','PWD')}}/group_files/{{group_files_group}}/{{role_name}}/postgresql.conf"
  37     - "{{lookup('env','PWD')}}/group_files/all/{{role_name}}/postgresql.conf"
  38     - "default/postgresql.conf"
  39   notify: restart postgresql.service
  40
  41
  42 - name: copy pg_hba.conf
  43   copy:
  44     src: "{{item}}"
  45     dest: "/etc/postgresql/15/main/pg_hba.conf"
  46     mode: 0644
  47     owner: "postgres"
  48     group: "postgres"
  49   with_first_found:
  50     - "{{lookup('env','PWD')}}/host_files/{{inventory_hostname}}/{{role_name}}/pg_hba.conf"
  51     - "{{lookup('env','PWD')}}/group_files/{{group_files_group}}/{{role_name}}/pg_hba.conf"
  52     - "{{lookup('env','PWD')}}/group_files/all/{{role_name}}/pg_hba.conf"
  53     - "default/pg_hba.conf"
  54   notify: restart postgresql.service
  55
  56
  57 - name: create logrotate entry for postgresql
  58   copy:
  59     src: "{{item}}"
  60     dest: "/etc/logrotate.d/postgresql-common"
  61     mode: 0644
  62     owner: "root"
  63     group: "root"
  64   with_first_found:
  65     - "{{lookup('env','PWD')}}/host_files/{{inventory_hostname}}/{{role_name}}/postgresql-common.logrotate"
  66     - "{{lookup('env','PWD')}}/group_files/{{group_files_group}}/{{role_name}}/postgresql-common.logrotate"
  67     - "{{lookup('env','PWD')}}/group_files/all/{{role_name}}/postgresql-common.logrotate"
  68     - "default/postgresql-common.logrotate"
  69
  70
  71 - name: enable and start postgresql.service
  72   include_role: name="base/systemd/enable-and-start"
  73   vars:
  74     service_name: postgresql.service
  75
  76
  77 - name: set superuser password
  78   become_user: postgres
  79   postgresql_user:
  80     name: "postgres"
  81     password: "{{postgresql_postgres_pw}}"
  82     db: "postgres"
  83     priv: "ALL"
  84   when: postgresql_postgres_pw != ""
  85
  86
  87 - name: ensure group grp_spectator exists and grant necessary privs on db postgres
  88   become_user: postgres
  89   postgresql_user:
  90     name: "grp_spectator"
  91     role_attr_flags: "NOLOGIN,NOSUPERUSER,INHERIT,NOCREATEDB,NOCREATEROLE,NOREPLICATION"
  92     db: "postgres"
  93     priv: CONNECT,TEMPORARY
  94
  95
  96 - name: revoke unnecessary privs for PUBLIC on db postgres
  97   become_user: postgres
  98   postgresql_privs:
  99     db: "postgres"
 100     state: absent
 101     privs: CREATE
 102     type: database
 103     role: public
 104
 105
 106 - name: grant necessary privs for PUBLIC on db postgres
 107   become_user: postgres
 108   postgresql_privs:
 109     db: "postgres"
 110     privs: CONNECT,TEMPORARY
 111     type: database
 112     role: public
 113
 114
 115 - name: revoke ALL privs for PUBLIC on schema "postgres.public"
 116   become_user: postgres
 117   postgresql_privs:
 118     db: "postgres"
 119     state: absent
 120     privs: ALL
 121     type: schema
 122     role: public
 123     objs: public