X-Git-Url: https://git.openstreetmap.org/chef.git/blobdiff_plain/93c098cdc4f22b1fe69ecd88b3ac2d2b211780ff..d3d97882fa4b21d3d896f4362cc9cdf337981824:/cookbooks/db/recipes/master.rb

diff --git a/cookbooks/db/recipes/master.rb b/cookbooks/db/recipes/master.rb
index 3fcdd7f15..eafc7db13 100644
--- a/cookbooks/db/recipes/master.rb
+++ b/cookbooks/db/recipes/master.rb
@@ -1,8 +1,8 @@
 #
-# Cookbook Name:: db
+# Cookbook:: db
 # Recipe:: master
 #
-# Copyright 2011, OpenStreetMap Foundation
+# Copyright:: 2011, OpenStreetMap Foundation
 #
 # Licensed under the Apache License, Version 2.0 (the "License");
 # you may not use this file except in compliance with the License.
@@ -41,6 +41,11 @@ postgresql_user "rails" do
   password passwords["rails"]
 end
 
+postgresql_user "cgimap" do
+  cluster node[:db][:cluster]
+  password passwords["cgimap"]
+end
+
 postgresql_user "planetdump" do
   cluster node[:db][:cluster]
   password passwords["planetdump"]
@@ -49,6 +54,7 @@ end
 postgresql_user "planetdiff" do
   cluster node[:db][:cluster]
   password passwords["planetdiff"]
+  replication true
 end
 
 postgresql_user "backup" do
@@ -56,11 +62,6 @@ postgresql_user "backup" do
   password passwords["backup"]
 end
 
-postgresql_user "gpximport" do
-  cluster node[:db][:cluster]
-  password passwords["gpximport"]
-end
-
 postgresql_user "munin" do
   cluster node[:db][:cluster]
   password passwords["munin"]
@@ -83,6 +84,231 @@ postgresql_extension "btree_gist" do
   only_if { node[:postgresql][:clusters][node[:db][:cluster]] && node[:postgresql][:clusters][node[:db][:cluster]][:version] >= 9.0 }
 end
 
-file "/etc/cron.daily/rails-db" do
-  action :delete
+CGIMAP_PERMISSIONS = {
+  "changeset_comments" => [:select],
+  "changeset_tags" => [:select],
+  "changesets" => [:select, :update],
+  "client_applications" => [:select],
+  "current_node_tags" => [:select, :insert, :delete],
+  "current_nodes" => [:select, :insert, :update],
+  "current_nodes_id_seq" => [:update],
+  "current_relation_members" => [:select, :insert, :delete],
+  "current_relation_tags" => [:select, :insert, :delete],
+  "current_relations" => [:select, :insert, :update],
+  "current_relations_id_seq" => [:update],
+  "current_way_nodes" => [:select, :insert, :delete],
+  "current_way_tags" => [:select, :insert, :delete],
+  "current_ways" => [:select, :insert, :update],
+  "current_ways_id_seq" => [:update],
+  "node_tags" => [:select, :insert],
+  "nodes" => [:select, :insert],
+  "oauth_access_grants" => [:select],
+  "oauth_access_tokens" => [:select],
+  "oauth_applications" => [:select],
+  "oauth_nonces" => [:select, :insert],
+  "oauth_nonces_id_seq" => [:update],
+  "oauth_tokens" => [:select],
+  "relation_members" => [:select, :insert],
+  "relation_tags" => [:select, :insert],
+  "relations" => [:select, :insert],
+  "user_blocks" => [:select],
+  "user_roles" => [:select],
+  "users" => [:select],
+  "way_nodes" => [:select, :insert],
+  "way_tags" => [:select, :insert],
+  "ways" => [:select, :insert]
+}.freeze
+
+PLANETDUMP_PERMISSIONS = {
+  "note_comments" => :select,
+  "notes" => :select,
+  "users" => :select
+}.freeze
+
+PLANETDIFF_PERMISSIONS = {
+  "changeset_comments" => :select,
+  "changeset_tags" => :select,
+  "changesets" => :select,
+  "node_tags" => :select,
+  "nodes" => :select,
+  "relation_members" => :select,
+  "relation_tags" => :select,
+  "relations" => :select,
+  "users" => :select,
+  "way_nodes" => :select,
+  "way_tags" => :select,
+  "ways" => :select
+}.freeze
+
+PROMETHEUS_PERMISSIONS = {
+  "delayed_jobs" => :select
+}.freeze
+
+%w[
+  acls
+  active_storage_attachments
+  active_storage_blobs
+  active_storage_variant_records
+  ar_internal_metadata
+  changeset_comments
+  changeset_tags
+  changesets
+  changesets_subscribers
+  client_applications
+  current_node_tags
+  current_nodes
+  current_relation_members
+  current_relation_tags
+  current_relations
+  current_way_nodes
+  current_way_tags
+  current_ways
+  delayed_jobs
+  diary_comments
+  diary_entries
+  diary_entry_subscriptions
+  friends
+  gps_points
+  gpx_file_tags
+  gpx_files
+  issue_comments
+  issues
+  languages
+  messages
+  node_tags
+  nodes
+  note_comments
+  notes
+  oauth_access_grants
+  oauth_access_tokens
+  oauth_applications
+  oauth_nonces
+  oauth_openid_requests
+  oauth_tokens
+  redactions
+  relation_members
+  relation_tags
+  relations
+  reports
+  schema_migrations
+  user_blocks
+  user_preferences
+  user_roles
+  user_tokens
+  users
+  way_nodes
+  way_tags
+  ways
+].each do |table|
+  postgresql_table table do
+    cluster node[:db][:cluster]
+    database "openstreetmap"
+    owner "openstreetmap"
+    permissions "openstreetmap" => [:all],
+                "rails" => [:select, :insert, :update, :delete],
+                "cgimap" => CGIMAP_PERMISSIONS[table],
+                "planetdump" => PLANETDUMP_PERMISSIONS[table],
+                "planetdiff" => PLANETDIFF_PERMISSIONS[table],
+                "prometheus" => PROMETHEUS_PERMISSIONS[table],
+                "backup" => [:select]
+  end
+end
+
+%w[
+  acls_id_seq
+  active_storage_attachments_id_seq
+  active_storage_blobs_id_seq
+  active_storage_variant_records_id_seq
+  changeset_comments_id_seq
+  changesets_id_seq
+  client_applications_id_seq
+  current_nodes_id_seq
+  current_relations_id_seq
+  current_ways_id_seq
+  delayed_jobs_id_seq
+  diary_comments_id_seq
+  diary_entries_id_seq
+  friends_id_seq
+  gpx_file_tags_id_seq
+  gpx_files_id_seq
+  issue_comments_id_seq
+  issues_id_seq
+  messages_id_seq
+  note_comments_id_seq
+  notes_id_seq
+  oauth_access_grants_id_seq
+  oauth_access_tokens_id_seq
+  oauth_applications_id_seq
+  oauth_nonces_id_seq
+  oauth_openid_requests_id_seq
+  oauth_tokens_id_seq
+  redactions_id_seq
+  reports_id_seq
+  user_blocks_id_seq
+  user_roles_id_seq
+  user_tokens_id_seq
+  users_id_seq
+].each do |sequence|
+  postgresql_sequence sequence do
+    cluster node[:db][:cluster]
+    database "openstreetmap"
+    owner "openstreetmap"
+    permissions "openstreetmap" => [:all],
+                "rails" => [:usage],
+                "cgimap" => CGIMAP_PERMISSIONS[sequence],
+                "backup" => [:select]
+  end
+end
+
+cookbook_file "/usr/local/share/monthly-reindex.sql" do
+  owner "root"
+  group "root"
+  mode "644"
+end
+
+systemd_service "monthly-reindex" do
+  description "Monthly database reindex"
+  exec_start "/usr/bin/psql -f /usr/local/share/monthly-reindex.sql openstreetmap"
+  user "postgres"
+  sandbox true
+  restrict_address_families "AF_UNIX"
+end
+
+systemd_timer "monthly-reindex" do
+  description "Monthly database reindex"
+  on_calendar "Sun *-*-1..7 02:00"
+end
+
+service "monthly-reindex.timer" do
+  action [:enable, :start]
+end
+
+cookbook_file "/usr/local/share/yearly-reindex.sql" do
+  owner "root"
+  group "root"
+  mode "644"
+end
+
+systemd_service "yearly-reindex" do
+  description "Yearly database reindex"
+  exec_start "/usr/bin/psql -f /usr/local/share/yearly-reindex.sql openstreetmap"
+  user "postgres"
+  sandbox true
+  restrict_address_families "AF_UNIX"
+end
+
+systemd_timer "yearly-reindex" do
+  description "Yearly database reindex"
+  on_calendar "Fri *-1-8..14 02:00"
+end
+
+service "yearly-reindex.timer" do
+  action [:enable, :start]
+end
+
+template "/etc/prometheus/exporters/sql_rails.collector.yml" do
+  source "sql_rails.yml.erb"
+  owner "root"
+  group "root"
+  mode "0644"
 end