5 # Copyright:: 2011, OpenStreetMap Foundation
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
11 # https://www.apache.org/licenses/LICENSE-2.0
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.
20 include_recipe "db::base"
22 passwords = data_bag_item("db", "passwords")
24 postgresql_user "tomh" do
25 cluster node[:db][:cluster]
29 postgresql_user "grant" do
30 cluster node[:db][:cluster]
33 postgresql_user "openstreetmap" do
34 cluster node[:db][:cluster]
35 password passwords["openstreetmap"]
38 postgresql_user "rails" do
39 cluster node[:db][:cluster]
40 password passwords["rails"]
43 postgresql_user "cgimap" do
44 cluster node[:db][:cluster]
45 password passwords["cgimap"]
48 postgresql_user "planetdump" do
49 cluster node[:db][:cluster]
50 password passwords["planetdump"]
53 postgresql_user "planetdiff" do
54 cluster node[:db][:cluster]
55 password passwords["planetdiff"]
59 postgresql_user "backup" do
60 cluster node[:db][:cluster]
61 password passwords["backup"]
64 postgresql_user "replication" do
65 cluster node[:db][:cluster]
66 password passwords["replication"]
70 postgresql_database "openstreetmap" do
71 cluster node[:db][:cluster]
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 }
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]
114 PLANETDUMP_PERMISSIONS = {
115 "note_comments" => :select,
120 PLANETDIFF_PERMISSIONS = {
121 "changeset_comments" => :select,
122 "changeset_tags" => :select,
123 "changesets" => :select,
124 "node_tags" => :select,
126 "relation_members" => :select,
127 "relation_tags" => :select,
128 "relations" => :select,
130 "way_nodes" => :select,
131 "way_tags" => :select,
135 PROMETHEUS_PERMISSIONS = {
136 "delayed_jobs" => :select
141 active_storage_attachments
143 active_storage_variant_records
148 changesets_subscribers
151 current_relation_members
152 current_relation_tags
160 diary_entry_subscriptions
177 oauth_openid_requests
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],
211 active_storage_attachments_id_seq
212 active_storage_blobs_id_seq
213 active_storage_variant_records_id_seq
214 changeset_comments_id_seq
217 current_relations_id_seq
220 diary_comments_id_seq
225 issue_comments_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
242 postgresql_sequence sequence do
243 cluster node[:db][:cluster]
244 database "openstreetmap"
245 owner "openstreetmap"
246 permissions "openstreetmap" => [:all],
248 "cgimap" => CGIMAP_PERMISSIONS[sequence],
249 "backup" => [:select],
254 cookbook_file "/usr/local/share/monthly-reindex.sql" do
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"
265 restrict_address_families "AF_UNIX"
269 systemd_timer "monthly-reindex" do
270 description "Monthly database reindex"
271 on_calendar "Sun *-*-1..7 02:00"
274 service "monthly-reindex.timer" do
275 action [:enable, :start]
278 cookbook_file "/usr/local/share/yearly-reindex.sql" do
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"
289 restrict_address_families "AF_UNIX"
293 systemd_timer "yearly-reindex" do
294 description "Yearly database reindex"
295 on_calendar "Thu *-1-8..14 02:00"
298 service "yearly-reindex.timer" do
299 action [:enable, :start]
302 template "/etc/prometheus/exporters/sql_rails.collector.yml" do
303 source "sql_rails.yml.erb"