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 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   user_tokens
 201   users
 202   way_nodes
 203   way_tags
 204   ways
 205 ].each do |table|
 206   postgresql_table table do
 207     cluster node[:db][:cluster]
 208     database "openstreetmap"
 209     owner "openstreetmap"
 210     permissions "openstreetmap" => [:all],
 211                 "rails" => [:select, :insert, :update, :delete],
 212                 "cgimap" => CGIMAP_PERMISSIONS[table],
 213                 "planetdump" => PLANETDUMP_PERMISSIONS[table],
 214                 "planetdiff" => PLANETDIFF_PERMISSIONS[table],
 215                 "prometheus" => PROMETHEUS_PERMISSIONS[table],
 216                 "backup" => [:select]
 217   end
 218 end
 219
 220 %w[
 221   acls_id_seq
 222   active_storage_attachments_id_seq
 223   active_storage_blobs_id_seq
 224   active_storage_variant_records_id_seq
 225   changeset_comments_id_seq
 226   changesets_id_seq
 227   client_applications_id_seq
 228   current_nodes_id_seq
 229   current_relations_id_seq
 230   current_ways_id_seq
 231   delayed_jobs_id_seq
 232   diary_comments_id_seq
 233   diary_entries_id_seq
 234   friends_id_seq
 235   gpx_file_tags_id_seq
 236   gpx_files_id_seq
 237   issue_comments_id_seq
 238   issues_id_seq
 239   messages_id_seq
 240   note_comments_id_seq
 241   notes_id_seq
 242   oauth_access_grants_id_seq
 243   oauth_access_tokens_id_seq
 244   oauth_applications_id_seq
 245   oauth_nonces_id_seq
 246   oauth_openid_requests_id_seq
 247   oauth_tokens_id_seq
 248   redactions_id_seq
 249   reports_id_seq
 250   user_blocks_id_seq
 251   user_mutes_id_seq
 252   user_roles_id_seq
 253   user_tokens_id_seq
 254   users_id_seq
 255 ].each do |sequence|
 256   postgresql_sequence sequence do
 257     cluster node[:db][:cluster]
 258     database "openstreetmap"
 259     owner "openstreetmap"
 260     permissions "openstreetmap" => [:all],
 261                 "rails" => [:usage],
 262                 "cgimap" => CGIMAP_PERMISSIONS[sequence],
 263                 "backup" => [:select]
 264   end
 265 end
 266
 267 cookbook_file "/usr/local/share/monthly-reindex.sql" do
 268   owner "root"
 269   group "root"
 270   mode "644"
 271 end
 272
 273 systemd_service "monthly-reindex" do
 274   description "Monthly database reindex"
 275   exec_start "/usr/bin/psql -f /usr/local/share/monthly-reindex.sql openstreetmap"
 276   user "postgres"
 277   sandbox true
 278   restrict_address_families "AF_UNIX"
 279   remove_ipc false
 280 end
 281
 282 systemd_timer "monthly-reindex" do
 283   description "Monthly database reindex"
 284   on_calendar "Sun *-*-1..7 02:00"
 285 end
 286
 287 service "monthly-reindex.timer" do
 288   action [:enable, :start]
 289 end
 290
 291 cookbook_file "/usr/local/share/yearly-reindex.sql" do
 292   owner "root"
 293   group "root"
 294   mode "644"
 295 end
 296
 297 systemd_service "yearly-reindex" do
 298   description "Yearly database reindex"
 299   exec_start "/usr/bin/psql -f /usr/local/share/yearly-reindex.sql openstreetmap"
 300   user "postgres"
 301   sandbox true
 302   restrict_address_families "AF_UNIX"
 303   remove_ipc false
 304 end
 305
 306 systemd_timer "yearly-reindex" do
 307   description "Yearly database reindex"
 308   on_calendar "Thu *-1-8..14 02:00"
 309 end
 310
 311 service "yearly-reindex.timer" do
 312   action [:enable, :start]
 313 end
 314
 315 template "/etc/prometheus/exporters/sql_rails.collector.yml" do
 316   source "sql_rails.yml.erb"
 317   owner "root"
 318   group "root"
 319   mode "0644"
 320 end