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-2025 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-17
  17     - postgresql-17-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 # TODO: upgrade or init
  27
  28 - name: copy postgresql.conf
  29   copy:
  30     src: "{{item}}"
  31     dest: "/etc/postgresql/17/main/postgresql.conf"
  32     mode: 0644
  33     owner: "postgres"
  34     group: "postgres"
  35   with_first_found:
  36     - "{{lookup('env','PWD')}}/host_files/{{inventory_hostname}}/{{role_name}}/postgresql.conf"
  37     - "{{lookup('env','PWD')}}/group_files/{{group_files_group}}/{{role_name}}/postgresql.conf"
  38     - "{{lookup('env','PWD')}}/group_files/all/{{role_name}}/postgresql.conf"
  39     - "default/postgresql.conf"
  40   notify: restart postgresql.service
  41
  42
  43 - name: copy pg_hba.conf
  44   copy:
  45     src: "{{item}}"
  46     dest: "/etc/postgresql/17/main/pg_hba.conf"
  47     mode: 0644
  48     owner: "postgres"
  49     group: "postgres"
  50   with_first_found:
  51     - "{{lookup('env','PWD')}}/host_files/{{inventory_hostname}}/{{role_name}}/pg_hba.conf"
  52     - "{{lookup('env','PWD')}}/group_files/{{group_files_group}}/{{role_name}}/pg_hba.conf"
  53     - "{{lookup('env','PWD')}}/group_files/all/{{role_name}}/pg_hba.conf"
  54     - "default/pg_hba.conf"
  55   notify: restart postgresql.service
  56
  57
  58 - name: create logrotate entry for postgresql
  59   copy:
  60     src: "{{item}}"
  61     dest: "/etc/logrotate.d/postgresql-common"
  62     mode: 0644
  63     owner: "root"
  64     group: "root"
  65   with_first_found:
  66     - "{{lookup('env','PWD')}}/host_files/{{inventory_hostname}}/{{role_name}}/postgresql-common.logrotate"
  67     - "{{lookup('env','PWD')}}/group_files/{{group_files_group}}/{{role_name}}/postgresql-common.logrotate"
  68     - "{{lookup('env','PWD')}}/group_files/all/{{role_name}}/postgresql-common.logrotate"
  69     - "default/postgresql-common.logrotate"
  70
  71
  72 - name: enable and start postgresql.service
  73   include_role: name="base/systemd/enable-and-start"
  74   vars:
  75     service_name: postgresql.service
  76
  77
  78 - name: set superuser password
  79   become_user: postgres
  80   postgresql_user:
  81     name: "postgres"
  82     password: "{{postgresql_postgres_pw}}"
  83     db: "postgres"
  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
  94
  95 - name: revoke unnecessary privs for PUBLIC on db postgres
  96   become_user: postgres
  97   postgresql_privs:
  98     db: "postgres"
  99     state: absent
 100     privs: CREATE
 101     type: database
 102     role: public
 103
 104
 105 - name: grant necessary privs for PUBLIC on db postgres
 106   become_user: postgres
 107   postgresql_privs:
 108     db: "postgres"
 109     privs: CONNECT,TEMPORARY
 110     type: database
 111     role: public
 112
 113
 114 - name: revoke ALL privs for PUBLIC on schema "postgres.public"
 115   become_user: postgres
 116   postgresql_privs:
 117     db: "postgres"
 118     state: absent
 119     privs: ALL
 120     type: schema
 121     role: public
 122     objs: public