]> git.openstreetmap.org Git - chef.git/blob - cookbooks/db/recipes/master.rb
7d6643dd1c9f8244a5eca65c33e8f347a3064dc1
[chef.git] / cookbooks / db / recipes / master.rb
1 #
2 # Cookbook:: db
3 # Recipe:: master
4 #
5 # Copyright:: 2011, OpenStreetMap Foundation
6 #
7 # Licensed under the Apache License, Version 2.0 (the "License");
8 # you may not use this file except in compliance with the License.
9 # You may obtain a copy of the License at
10 #
11 #     https://www.apache.org/licenses/LICENSE-2.0
12 #
13 # Unless required by applicable law or agreed to in writing, software
14 # distributed under the License is distributed on an "AS IS" BASIS,
15 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16 # See the License for the specific language governing permissions and
17 # limitations under the License.
18 #
19
20 include_recipe "db::base"
21
22 passwords = data_bag_item("db", "passwords")
23
24 postgresql_user "tomh" do
25   cluster node[:db][:cluster]
26   superuser true
27 end
28
29 postgresql_user "matt" do
30   cluster node[:db][:cluster]
31   superuser true
32 end
33
34 postgresql_user "openstreetmap" do
35   cluster node[:db][:cluster]
36   password passwords["openstreetmap"]
37 end
38
39 postgresql_user "rails" do
40   cluster node[:db][:cluster]
41   password passwords["rails"]
42 end
43
44 postgresql_user "cgimap" do
45   cluster node[:db][:cluster]
46   password passwords["cgimap"]
47 end
48
49 postgresql_user "planetdump" do
50   cluster node[:db][:cluster]
51   password passwords["planetdump"]
52 end
53
54 postgresql_user "planetdiff" do
55   cluster node[:db][:cluster]
56   password passwords["planetdiff"]
57   replication true
58 end
59
60 postgresql_user "backup" do
61   cluster node[:db][:cluster]
62   password passwords["backup"]
63 end
64
65 postgresql_user "munin" do
66   cluster node[:db][:cluster]
67   action :drop
68 end
69
70 postgresql_user "replication" do
71   cluster node[:db][:cluster]
72   password passwords["replication"]
73   replication true
74 end
75
76 postgresql_database "openstreetmap" do
77   cluster node[:db][:cluster]
78   owner "openstreetmap"
79 end
80
81 postgresql_extension "btree_gist" do
82   cluster node[:db][:cluster]
83   database "openstreetmap"
84   only_if { node[:postgresql][:clusters][node[:db][:cluster]] && node[:postgresql][:clusters][node[:db][:cluster]][:version] >= 9.0 }
85 end
86
87 CGIMAP_PERMISSIONS = {
88   "changeset_comments" => [:select],
89   "changeset_tags" => [:select],
90   "changesets" => [:select, :update],
91   "client_applications" => [:select],
92   "current_node_tags" => [:select, :insert, :delete],
93   "current_nodes" => [:select, :insert, :update],
94   "current_nodes_id_seq" => [:update],
95   "current_relation_members" => [:select, :insert, :delete],
96   "current_relation_tags" => [:select, :insert, :delete],
97   "current_relations" => [:select, :insert, :update],
98   "current_relations_id_seq" => [:update],
99   "current_way_nodes" => [:select, :insert, :delete],
100   "current_way_tags" => [:select, :insert, :delete],
101   "current_ways" => [:select, :insert, :update],
102   "current_ways_id_seq" => [:update],
103   "issues" => [:select],
104   "node_tags" => [:select, :insert],
105   "nodes" => [:select, :insert],
106   "oauth_access_grants" => [:select],
107   "oauth_access_tokens" => [:select],
108   "oauth_applications" => [:select],
109   "oauth_nonces" => [:select, :insert],
110   "oauth_nonces_id_seq" => [:update],
111   "oauth_tokens" => [:select],
112   "relation_members" => [:select, :insert],
113   "relation_tags" => [:select, :insert],
114   "relations" => [:select, :insert],
115   "reports" => [:select],
116   "user_blocks" => [:select],
117   "user_roles" => [:select],
118   "users" => [:select],
119   "way_nodes" => [:select, :insert],
120   "way_tags" => [:select, :insert],
121   "ways" => [:select, :insert]
122 }.freeze
123
124 PLANETDUMP_PERMISSIONS = {
125   "note_comments" => :select,
126   "notes" => :select,
127   "users" => :select
128 }.freeze
129
130 PLANETDIFF_PERMISSIONS = {
131   "changeset_comments" => :select,
132   "changeset_tags" => :select,
133   "changesets" => :select,
134   "node_tags" => :select,
135   "nodes" => :select,
136   "relation_members" => :select,
137   "relation_tags" => :select,
138   "relations" => :select,
139   "users" => :select,
140   "way_nodes" => :select,
141   "way_tags" => :select,
142   "ways" => :select
143 }.freeze
144
145 PROMETHEUS_PERMISSIONS = {
146   "delayed_jobs" => :select
147 }.freeze
148
149 %w[
150   acls
151   active_storage_attachments
152   active_storage_blobs
153   active_storage_variant_records
154   ar_internal_metadata
155   changeset_comments
156   changeset_tags
157   changesets
158   changesets_subscribers
159   client_applications
160   current_node_tags
161   current_nodes
162   current_relation_members
163   current_relation_tags
164   current_relations
165   current_way_nodes
166   current_way_tags
167   current_ways
168   delayed_jobs
169   diary_comments
170   diary_entries
171   diary_entry_subscriptions
172   friends
173   gps_points
174   gpx_file_tags
175   gpx_files
176   issue_comments
177   issues
178   languages
179   messages
180   node_tags
181   nodes
182   note_comments
183   notes
184   oauth_access_grants
185   oauth_access_tokens
186   oauth_applications
187   oauth_nonces
188   oauth_openid_requests
189   oauth_tokens
190   redactions
191   relation_members
192   relation_tags
193   relations
194   reports
195   schema_migrations
196   user_blocks
197   user_mutes
198   user_preferences
199   user_roles
200   users
201   way_nodes
202   way_tags
203   ways
204 ].each do |table|
205   postgresql_table table do
206     cluster node[:db][:cluster]
207     database "openstreetmap"
208     owner "openstreetmap"
209     permissions "openstreetmap" => [:all],
210                 "rails" => [:select, :insert, :update, :delete],
211                 "cgimap" => CGIMAP_PERMISSIONS[table],
212                 "planetdump" => PLANETDUMP_PERMISSIONS[table],
213                 "planetdiff" => PLANETDIFF_PERMISSIONS[table],
214                 "prometheus" => PROMETHEUS_PERMISSIONS[table],
215                 "backup" => [:select]
216   end
217 end
218
219 %w[
220   acls_id_seq
221   active_storage_attachments_id_seq
222   active_storage_blobs_id_seq
223   active_storage_variant_records_id_seq
224   changeset_comments_id_seq
225   changesets_id_seq
226   client_applications_id_seq
227   current_nodes_id_seq
228   current_relations_id_seq
229   current_ways_id_seq
230   delayed_jobs_id_seq
231   diary_comments_id_seq
232   diary_entries_id_seq
233   friends_id_seq
234   gpx_file_tags_id_seq
235   gpx_files_id_seq
236   issue_comments_id_seq
237   issues_id_seq
238   messages_id_seq
239   note_comments_id_seq
240   notes_id_seq
241   oauth_access_grants_id_seq
242   oauth_access_tokens_id_seq
243   oauth_applications_id_seq
244   oauth_nonces_id_seq
245   oauth_openid_requests_id_seq
246   oauth_tokens_id_seq
247   redactions_id_seq
248   reports_id_seq
249   user_blocks_id_seq
250   user_mutes_id_seq
251   user_roles_id_seq
252   users_id_seq
253 ].each do |sequence|
254   postgresql_sequence sequence do
255     cluster node[:db][:cluster]
256     database "openstreetmap"
257     owner "openstreetmap"
258     permissions "openstreetmap" => [:all],
259                 "rails" => [:usage],
260                 "cgimap" => CGIMAP_PERMISSIONS[sequence],
261                 "backup" => [:select]
262   end
263 end
264
265 cookbook_file "/usr/local/share/monthly-reindex.sql" do
266   owner "root"
267   group "root"
268   mode "644"
269 end
270
271 systemd_service "monthly-reindex" do
272   description "Monthly database reindex"
273   exec_start "/usr/bin/psql -f /usr/local/share/monthly-reindex.sql openstreetmap"
274   user "postgres"
275   sandbox true
276   restrict_address_families "AF_UNIX"
277   remove_ipc false
278 end
279
280 systemd_timer "monthly-reindex" do
281   description "Monthly database reindex"
282   on_calendar "Sun *-*-1..7 02:00"
283 end
284
285 service "monthly-reindex.timer" do
286   action [:enable, :start]
287 end
288
289 cookbook_file "/usr/local/share/yearly-reindex.sql" do
290   owner "root"
291   group "root"
292   mode "644"
293 end
294
295 systemd_service "yearly-reindex" do
296   description "Yearly database reindex"
297   exec_start "/usr/bin/psql -f /usr/local/share/yearly-reindex.sql openstreetmap"
298   user "postgres"
299   sandbox true
300   restrict_address_families "AF_UNIX"
301   remove_ipc false
302 end
303
304 systemd_timer "yearly-reindex" do
305   description "Yearly database reindex"
306   on_calendar "Thu *-1-8..14 02:00"
307 end
308
309 service "yearly-reindex.timer" do
310   action [:enable, :start]
311 end
312
313 template "/etc/prometheus/exporters/sql_rails.collector.yml" do
314   source "sql_rails.yml.erb"
315   owner "root"
316   group "root"
317   mode "0644"
318 end