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   password passwords["munin"]
  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 %w[
  88   active_storage_attachments
  89   active_storage_blobs
  90   active_storage_variant_records
  91   ar_internal_metadata
  92   delayed_jobs
  93   issue_comments
  94   issues
  95   oauth_openid_requests
  96   reports
  97 ].each do |table|
  98   postgresql_table table do
  99     cluster node[:db][:cluster]
 100     database "openstreetmap"
 101     owner "openstreetmap"
 102     permissions "openstreetmap" => [:all],
 103                 "rails" => [:select, :insert, :update, :delete],
 104                 "backup" => [:select]
 105   end
 106 end
 107
 108 %w[
 109   acls
 110   changesets_subscribers
 111   diary_comments
 112   diary_entries
 113   diary_entry_subscriptions
 114   friends
 115   gps_points
 116   gpx_file_tags
 117   gpx_files
 118   languages
 119   messages
 120   note_comments
 121   notes
 122   redactions
 123   schema_migrations
 124   user_preferences
 125   user_tokens
 126 ].each do |table|
 127   postgresql_table table do
 128     cluster node[:db][:cluster]
 129     database "openstreetmap"
 130     owner "openstreetmap"
 131     permissions "openstreetmap" => [:all],
 132                 "rails" => [:select, :insert, :update, :delete],
 133                 "planetdump" => [:select],
 134                 "planetdiff" => [:select],
 135                 "backup" => [:select]
 136   end
 137 end
 138
 139 %w[
 140   changeset_comments
 141   changeset_tags
 142   client_applications
 143   user_blocks
 144   user_roles
 145   users
 146 ].each do |table|
 147   postgresql_table table do
 148     cluster node[:db][:cluster]
 149     database "openstreetmap"
 150     owner "openstreetmap"
 151     permissions "openstreetmap" => [:all],
 152                 "rails" => [:select, :insert, :update, :delete],
 153                 "cgimap" => [:select],
 154                 "planetdump" => [:select],
 155                 "planetdiff" => [:select],
 156                 "backup" => [:select]
 157   end
 158 end
 159
 160 %w[changesets].each do |table|
 161   postgresql_table table do
 162     cluster node[:db][:cluster]
 163     database "openstreetmap"
 164     owner "openstreetmap"
 165     permissions "openstreetmap" => [:all],
 166                 "rails" => [:select, :insert, :update, :delete],
 167                 "cgimap" => [:select, :update],
 168                 "planetdump" => [:select],
 169                 "planetdiff" => [:select],
 170                 "backup" => [:select]
 171   end
 172 end
 173
 174 %w[
 175   current_nodes
 176   current_relations
 177   current_ways
 178 ].each do |table|
 179   postgresql_table table do
 180     cluster node[:db][:cluster]
 181     database "openstreetmap"
 182     owner "openstreetmap"
 183     permissions "openstreetmap" => [:all],
 184                 "rails" => [:select, :insert, :update, :delete],
 185                 "cgimap" => [:select, :insert, :update],
 186                 "planetdump" => [:select],
 187                 "planetdiff" => [:select],
 188                 "backup" => [:select]
 189   end
 190 end
 191
 192 %w[
 193   current_node_tags
 194   current_relation_members
 195   current_relation_tags
 196   current_way_nodes
 197   current_way_tags
 198 ].each do |table|
 199   postgresql_table table do
 200     cluster node[:db][:cluster]
 201     database "openstreetmap"
 202     owner "openstreetmap"
 203     permissions "openstreetmap" => [:all],
 204                 "rails" => [:select, :insert, :update, :delete],
 205                 "cgimap" => [:select, :insert, :delete],
 206                 "planetdump" => [:select],
 207                 "planetdiff" => [:select],
 208                 "backup" => [:select]
 209   end
 210 end
 211
 212 %w[
 213   node_tags
 214   nodes
 215   relation_members
 216   relation_tags
 217   relations
 218   way_nodes
 219   way_tags
 220   ways
 221 ].each do |table|
 222   postgresql_table table do
 223     cluster node[:db][:cluster]
 224     database "openstreetmap"
 225     owner "openstreetmap"
 226     permissions "openstreetmap" => [:all],
 227                 "rails" => [:select, :insert, :update, :delete],
 228                 "cgimap" => [:select, :insert],
 229                 "planetdump" => [:select],
 230                 "planetdiff" => [:select],
 231                 "backup" => [:select]
 232   end
 233 end
 234
 235 %w[
 236   oauth_access_grants
 237   oauth_access_tokens
 238   oauth_applications
 239   oauth_tokens
 240 ].each do |table|
 241   postgresql_table table do
 242     cluster node[:db][:cluster]
 243     database "openstreetmap"
 244     owner "openstreetmap"
 245     permissions "openstreetmap" => [:all],
 246                 "rails" => [:select, :insert, :update, :delete],
 247                 "cgimap" => [:select],
 248                 "backup" => [:select]
 249   end
 250 end
 251
 252 %w[
 253   oauth_nonces
 254 ].each do |table|
 255   postgresql_table table do
 256     cluster node[:db][:cluster]
 257     database "openstreetmap"
 258     owner "openstreetmap"
 259     permissions "openstreetmap" => [:all],
 260                 "rails" => [:select, :insert, :update, :delete],
 261                 "cgimap" => [:select, :insert],
 262                 "backup" => [:select]
 263   end
 264 end
 265
 266 %w[
 267   acls_id_seq
 268   active_storage_attachments_id_seq
 269   active_storage_blobs_id_seq
 270   active_storage_variant_records_id_seq
 271   changeset_comments_id_seq
 272   changesets_id_seq
 273   client_applications_id_seq
 274   delayed_jobs_id_seq
 275   diary_comments_id_seq
 276   diary_entries_id_seq
 277   friends_id_seq
 278   gpx_file_tags_id_seq
 279   gpx_files_id_seq
 280   issue_comments_id_seq
 281   issues_id_seq
 282   messages_id_seq
 283   note_comments_id_seq
 284   notes_id_seq
 285   oauth_access_grants_id_seq
 286   oauth_access_tokens_id_seq
 287   oauth_applications_id_seq
 288   oauth_openid_requests_id_seq
 289   oauth_tokens_id_seq
 290   redactions_id_seq
 291   reports_id_seq
 292   user_blocks_id_seq
 293   user_roles_id_seq
 294   user_tokens_id_seq
 295   users_id_seq
 296 ].each do |sequence|
 297   postgresql_sequence sequence do
 298     cluster node[:db][:cluster]
 299     database "openstreetmap"
 300     owner "openstreetmap"
 301     permissions "openstreetmap" => [:all],
 302                 "rails" => [:usage],
 303                 "backup" => [:select]
 304   end
 305 end
 306
 307 %w[
 308   current_nodes_id_seq
 309   current_relations_id_seq
 310   current_ways_id_seq
 311   oauth_nonces_id_seq
 312 ].each do |sequence|
 313   postgresql_sequence sequence do
 314     cluster node[:db][:cluster]
 315     database "openstreetmap"
 316     owner "openstreetmap"
 317     permissions "openstreetmap" => [:all],
 318                 "rails" => [:usage],
 319                 "cgimap" => [:update],
 320                 "backup" => [:select]
 321   end
 322 end
 323
 324 cookbook_file "/usr/local/share/monthly-reindex.sql" do
 325   owner "root"
 326   group "root"
 327   mode "644"
 328 end
 329
 330 systemd_service "monthly-reindex" do
 331   description "Monthly database reindex"
 332   exec_start "/usr/bin/psql -f /usr/local/share/monthly-reindex.sql openstreetmap"
 333   user "postgres"
 334   sandbox true
 335   restrict_address_families "AF_UNIX"
 336 end
 337
 338 systemd_timer "monthly-reindex" do
 339   description "Monthly database reindex"
 340   on_calendar "Sun *-*-1..7 02:00"
 341 end
 342
 343 service "monthly-reindex.timer" do
 344   action [:enable, :start]
 345 end
 346
 347 cookbook_file "/usr/local/share/yearly-reindex.sql" do
 348   owner "root"
 349   group "root"
 350   mode "644"
 351 end
 352
 353 systemd_service "yearly-reindex" do
 354   description "Yearly database reindex"
 355   exec_start "/usr/bin/psql -f /usr/local/share/yearly-reindex.sql openstreetmap"
 356   user "postgres"
 357   sandbox true
 358   restrict_address_families "AF_UNIX"
 359 end
 360
 361 systemd_timer "yearly-reindex" do
 362   description "Yearly database reindex"
 363   on_calendar "Fri *-1-8..14 02:00"
 364 end
 365
 366 service "yearly-reindex.timer" do
 367   action [:enable, :start]
 368 end