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 "grant" do
  30   cluster node[:db][:cluster]
  31 end
  32
  33 postgresql_user "openstreetmap" do
  34   cluster node[:db][:cluster]
  35   password passwords["openstreetmap"]
  36 end
  37
  38 postgresql_user "rails" do
  39   cluster node[:db][:cluster]
  40   password passwords["rails"]
  41 end
  42
  43 postgresql_user "cgimap" do
  44   cluster node[:db][:cluster]
  45   password passwords["cgimap"]
  46 end
  47
  48 postgresql_user "planetdump" do
  49   cluster node[:db][:cluster]
  50   password passwords["planetdump"]
  51 end
  52
  53 postgresql_user "planetdiff" do
  54   cluster node[:db][:cluster]
  55   password passwords["planetdiff"]
  56   replication true
  57 end
  58
  59 postgresql_user "backup" do
  60   cluster node[:db][:cluster]
  61   password passwords["backup"]
  62 end
  63
  64 postgresql_user "replication" do
  65   cluster node[:db][:cluster]
  66   password passwords["replication"]
  67   replication true
  68 end
  69
  70 postgresql_database "openstreetmap" do
  71   cluster node[:db][:cluster]
  72   owner "openstreetmap"
  73 end
  74
  75 postgresql_extension "btree_gist" do
  76   cluster node[:db][:cluster]
  77   database "openstreetmap"
  78   only_if { node[:postgresql][:clusters][node[:db][:cluster]] && node[:postgresql][:clusters][node[:db][:cluster]][:version] >= 9.0 }
  79 end
  80
  81 postgresql_extension "postgis" do
  82   cluster node[:db][:cluster]
  83   database "openstreetmap"
  84   owner "postgres"
  85 end
  86
  87 CGIMAP_PERMISSIONS = {
  88   "changeset_comments" => [:select],
  89   "changeset_tags" => [:select],
  90   "changesets" => [:select, :update],
  91   "current_node_tags" => [:select, :insert, :delete],
  92   "current_nodes" => [:select, :insert, :update],
  93   "current_nodes_id_seq" => [:update],
  94   "current_relation_members" => [:select, :insert, :delete],
  95   "current_relation_tags" => [:select, :insert, :delete],
  96   "current_relations" => [:select, :insert, :update],
  97   "current_relations_id_seq" => [:update],
  98   "current_way_nodes" => [:select, :insert, :delete],
  99   "current_way_tags" => [:select, :insert, :delete],
 100   "current_ways" => [:select, :insert, :update],
 101   "current_ways_id_seq" => [:update],
 102   "issues" => [:select],
 103   "node_tags" => [:select, :insert],
 104   "nodes" => [:select, :insert],
 105   "oauth_access_grants" => [:select],
 106   "oauth_access_tokens" => [:select],
 107   "oauth_applications" => [:select],
 108   "relation_members" => [:select, :insert],
 109   "relation_tags" => [:select, :insert],
 110   "relations" => [:select, :insert],
 111   "reports" => [:select],
 112   "user_blocks" => [:select],
 113   "user_roles" => [:select],
 114   "users" => [:select],
 115   "way_nodes" => [:select, :insert],
 116   "way_tags" => [:select, :insert],
 117   "ways" => [:select, :insert]
 118 }.freeze
 119
 120 PLANETDUMP_PERMISSIONS = {
 121   "changeset_comments" => :select,
 122   "changeset_comments_id_seq" => :select,
 123   "changeset_tags" => :select,
 124   "changesets" => :select,
 125   "changesets_id_seq" => :select,
 126   "node_tags" => :select,
 127   "nodes" => :select,
 128   "note_comments" => :select,
 129   "note_comments_id_seq" => :select,
 130   "notes" => :select,
 131   "notes_id_seq" => :select,
 132   "relation_members" => :select,
 133   "relation_tags" => :select,
 134   "relations" => :select,
 135   "users" => :select,
 136   "users_id_seq" => :select,
 137   "way_nodes" => :select,
 138   "way_tags" => :select,
 139   "ways" => :select
 140 }.freeze
 141
 142 PLANETDIFF_PERMISSIONS = {
 143   "changeset_comments" => :select,
 144   "changeset_tags" => :select,
 145   "changesets" => :select,
 146   "node_tags" => :select,
 147   "nodes" => :select,
 148   "relation_members" => :select,
 149   "relation_tags" => :select,
 150   "relations" => :select,
 151   "users" => :select,
 152   "way_nodes" => :select,
 153   "way_tags" => :select,
 154   "ways" => :select
 155 }.freeze
 156
 157 PROMETHEUS_PERMISSIONS = {
 158   "delayed_jobs" => :select
 159 }.freeze
 160
 161 %w[
 162   acls
 163   active_storage_attachments
 164   active_storage_blobs
 165   active_storage_variant_records
 166   ar_internal_metadata
 167   changeset_comments
 168   changeset_tags
 169   changesets
 170   changesets_subscribers
 171   current_node_tags
 172   current_nodes
 173   current_relation_members
 174   current_relation_tags
 175   current_relations
 176   current_way_nodes
 177   current_way_tags
 178   current_ways
 179   delayed_jobs
 180   diary_comments
 181   diary_entries
 182   diary_entry_subscriptions
 183   friends
 184   gps_points
 185   gpx_file_tags
 186   gpx_files
 187   issue_comments
 188   issues
 189   languages
 190   messages
 191   moderation_zones
 192   node_tags
 193   nodes
 194   note_comments
 195   note_subscriptions
 196   notes
 197   noticed_events
 198   noticed_notifications
 199   oauth_access_grants
 200   oauth_access_tokens
 201   oauth_applications
 202   oauth_openid_requests
 203   redactions
 204   relation_members
 205   relation_tags
 206   relations
 207   reports
 208   schema_migrations
 209   social_links
 210   spammy_phrases
 211   user_blocks
 212   user_mutes
 213   user_preferences
 214   user_roles
 215   users
 216   way_nodes
 217   way_tags
 218   ways
 219 ].each do |table|
 220   postgresql_table table do
 221     cluster node[:db][:cluster]
 222     database "openstreetmap"
 223     owner "openstreetmap"
 224     permissions "openstreetmap" => [:all],
 225                 "rails" => [:select, :insert, :update, :delete],
 226                 "cgimap" => CGIMAP_PERMISSIONS[table],
 227                 "planetdump" => PLANETDUMP_PERMISSIONS[table],
 228                 "planetdiff" => PLANETDIFF_PERMISSIONS[table],
 229                 "prometheus" => PROMETHEUS_PERMISSIONS[table],
 230                 "backup" => [:select],
 231                 "grant" => [:select]
 232   end
 233 end
 234
 235 %w[
 236   acls_id_seq
 237   active_storage_attachments_id_seq
 238   active_storage_blobs_id_seq
 239   active_storage_variant_records_id_seq
 240   changeset_comments_id_seq
 241   changesets_id_seq
 242   current_nodes_id_seq
 243   current_relations_id_seq
 244   current_ways_id_seq
 245   delayed_jobs_id_seq
 246   diary_comments_id_seq
 247   diary_entries_id_seq
 248   friends_id_seq
 249   gpx_file_tags_id_seq
 250   gpx_files_id_seq
 251   issue_comments_id_seq
 252   issues_id_seq
 253   messages_id_seq
 254   moderation_zones_id_seq
 255   note_comments_id_seq
 256   notes_id_seq
 257   noticed_events_id_seq
 258   noticed_notifications_id_seq
 259   oauth_access_grants_id_seq
 260   oauth_access_tokens_id_seq
 261   oauth_applications_id_seq
 262   oauth_openid_requests_id_seq
 263   redactions_id_seq
 264   reports_id_seq
 265   social_links_id_seq
 266   spammy_phrases_id_seq
 267   user_blocks_id_seq
 268   user_mutes_id_seq
 269   user_roles_id_seq
 270   users_id_seq
 271 ].each do |sequence|
 272   postgresql_sequence sequence do
 273     cluster node[:db][:cluster]
 274     database "openstreetmap"
 275     owner "openstreetmap"
 276     permissions "openstreetmap" => [:all],
 277                 "rails" => [:usage],
 278                 "cgimap" => CGIMAP_PERMISSIONS[sequence],
 279                 "planetdump" => PLANETDUMP_PERMISSIONS[sequence],
 280                 "backup" => [:select],
 281                 "grant" => [:select]
 282   end
 283 end
 284
 285 cookbook_file "/usr/local/share/monthly-reindex.sql" do
 286   owner "root"
 287   group "root"
 288   mode "644"
 289 end
 290
 291 systemd_service "monthly-reindex" do
 292   description "Monthly database reindex"
 293   exec_start "/usr/bin/psql -f /usr/local/share/monthly-reindex.sql openstreetmap"
 294   user "postgres"
 295   sandbox true
 296   restrict_address_families "AF_UNIX"
 297   remove_ipc false
 298 end
 299
 300 systemd_timer "monthly-reindex" do
 301   description "Monthly database reindex"
 302   on_calendar "Sun *-*-1..7 02:00"
 303 end
 304
 305 service "monthly-reindex.timer" do
 306   action [:enable, :start]
 307 end
 308
 309 cookbook_file "/usr/local/share/yearly-reindex.sql" do
 310   owner "root"
 311   group "root"
 312   mode "644"
 313 end
 314
 315 systemd_service "yearly-reindex" do
 316   description "Yearly database reindex"
 317   exec_start "/usr/bin/psql -f /usr/local/share/yearly-reindex.sql openstreetmap"
 318   user "postgres"
 319   sandbox true
 320   restrict_address_families "AF_UNIX"
 321   remove_ipc false
 322 end
 323
 324 systemd_timer "yearly-reindex" do
 325   description "Yearly database reindex"
 326   on_calendar "Thu *-1-8..14 02:00"
 327 end
 328
 329 service "yearly-reindex.timer" do
 330   action [:enable, :start]
 331 end
 332
 333 template "/etc/prometheus/exporters/sql_rails.collector.yml" do
 334   source "sql_rails.yml.erb"
 335   owner "root"
 336   group "root"
 337   mode "0644"
 338 end