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