]> git.somenet.org - root/pub/somesible.git/blob - roles/server/postgresql/tasks/main.yml
roles/service/nextcloud/defaults
[root/pub/somesible.git] / 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