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