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
  27 - name: upgrade cluster
  28   command: "pg_upgradecluster --locale='C.UTF8' -v 17 {{postgresql_server_upgrade_from}} main"
  29   args:
  30     creates: "/etc/postgresql/17/main/postgresql.conf"
  31   when: postgresql_server_upgrade_from != ""
  32
  33
  34 - name: init cluster
  35   command: "pg_createcluster --locale='C.UTF8' 17 main"
  36   args:
  37     creates: "/etc/postgresql/17/main/postgresql.conf"
  38   when: postgresql_server_upgrade_from == ""
  39
  40
  41 - name: copy postgresql.conf
  42   copy:
  43     src: "{{item}}"
  44     dest: "/etc/postgresql/17/main/postgresql.conf"
  45     mode: 0644
  46     owner: "postgres"
  47     group: "postgres"
  48   with_first_found:
  49     - "{{lookup('env','PWD')}}/host_files/{{inventory_hostname}}/{{role_name}}/postgresql.conf"
  50     - "{{lookup('env','PWD')}}/group_files/{{group_files_group}}/{{role_name}}/postgresql.conf"
  51     - "{{lookup('env','PWD')}}/group_files/all/{{role_name}}/postgresql.conf"
  52     - "default/postgresql.conf"
  53   notify: restart postgresql.service
  54
  55
  56 - name: copy pg_hba.conf
  57   copy:
  58     src: "{{item}}"
  59     dest: "/etc/postgresql/17/main/pg_hba.conf"
  60     mode: 0644
  61     owner: "postgres"
  62     group: "postgres"
  63   with_first_found:
  64     - "{{lookup('env','PWD')}}/host_files/{{inventory_hostname}}/{{role_name}}/pg_hba.conf"
  65     - "{{lookup('env','PWD')}}/group_files/{{group_files_group}}/{{role_name}}/pg_hba.conf"
  66     - "{{lookup('env','PWD')}}/group_files/all/{{role_name}}/pg_hba.conf"
  67     - "default/pg_hba.conf"
  68   notify: restart postgresql.service
  69
  70
  71 - name: create logrotate entry for postgresql
  72   copy:
  73     src: "{{item}}"
  74     dest: "/etc/logrotate.d/postgresql-common"
  75     mode: 0644
  76     owner: "root"
  77     group: "root"
  78   with_first_found:
  79     - "{{lookup('env','PWD')}}/host_files/{{inventory_hostname}}/{{role_name}}/postgresql-common.logrotate"
  80     - "{{lookup('env','PWD')}}/group_files/{{group_files_group}}/{{role_name}}/postgresql-common.logrotate"
  81     - "{{lookup('env','PWD')}}/group_files/all/{{role_name}}/postgresql-common.logrotate"
  82     - "default/postgresql-common.logrotate"
  83
  84
  85 - name: enable and start postgresql.service
  86   include_role: name="base/systemd/enable-and-start"
  87   vars:
  88     service_name: postgresql.service
  89
  90
  91 - name: set superuser password
  92   become_user: postgres
  93   postgresql_user:
  94     name: "postgres"
  95     password: "{{postgresql_postgres_pw}}"
  96     db: "postgres"
  97   when: postgresql_postgres_pw != ""
  98
  99
 100 - name: ensure group grp_spectator exists and grant necessary privs on db postgres
 101   become_user: postgres
 102   postgresql_user:
 103     name: "grp_spectator"
 104     role_attr_flags: "NOLOGIN,NOSUPERUSER,INHERIT,NOCREATEDB,NOCREATEROLE,NOREPLICATION"
 105     db: "postgres"
 106
 107
 108 - name: revoke unnecessary privs for PUBLIC on db postgres
 109   become_user: postgres
 110   postgresql_privs:
 111     db: "postgres"
 112     state: absent
 113     privs: CREATE
 114     type: database
 115     role: public
 116
 117
 118 - name: grant necessary privs for PUBLIC on db postgres
 119   become_user: postgres
 120   postgresql_privs:
 121     db: "postgres"
 122     privs: CONNECT,TEMPORARY
 123     type: database
 124     role: public
 125
 126
 127 - name: revoke ALL privs for PUBLIC on schema "postgres.public"
 128   become_user: postgres
 129   postgresql_privs:
 130     db: "postgres"
 131     state: absent
 132     privs: ALL
 133     type: schema
 134     role: public
 135     objs: public