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