X-Git-Url: https://git.openstreetmap.org./chef.git/blobdiff_plain/faf8ae12e85eabb050b0f5eceb2cb67ad1de5261..3e9826e33150fe90d73676118a643b642d7736cc:/cookbooks/postgresql/libraries/postgresql.rb?ds=sidebyside diff --git a/cookbooks/postgresql/libraries/postgresql.rb b/cookbooks/postgresql/libraries/postgresql.rb index bd10a0233..b2df4aed6 100644 --- a/cookbooks/postgresql/libraries/postgresql.rb +++ b/cookbooks/postgresql/libraries/postgresql.rb @@ -1,52 +1,69 @@ -require 'chef/mixin/command' +require "chef/mixin/shell_out" -class Chef +module OpenStreetMap class PostgreSQL - include Chef::Mixin::Command + include Chef::Mixin::ShellOut + + SCHEMA_PRIVILEGES = [ + :create, :usage + ].freeze TABLE_PRIVILEGES = [ :select, :insert, :update, :delete, :truncate, :references, :trigger - ] + ].freeze + + SEQUENCE_PRIVILEGES = [ + :usage, :select, :update + ].freeze def initialize(cluster) @cluster = cluster end + def version + @cluster.split("/").first.to_f + end + def execute(options) # Create argument array args = [] - # Build the arguments - args.push("--command=\"#{options[:command].gsub('"', '\\"')}\"") if options[:command] - args.push("--file=#{options[:file]}") if options[:file] + # Add the cluster + args.push("--cluster") + args.push(@cluster) - # Get the database to use - database = options[:database] || "template1" + # Set output format + args.push("--no-align") unless options.fetch(:align, true) - # Build the command to run - command = "/usr/bin/psql --cluster #{@cluster} #{args.join(' ')} #{database}" + # Add any SQL command to execute + if options[:command] + args.push("--command") + args.push(options[:command]) + end + + # Add any file to execute SQL commands from + if options[:file] + args.push("--file") + args.push(options[:file]) + end + + # Add the database name + args.push(options[:database] || "template1") # Get the user and group to run as user = options[:user] || "postgres" group = options[:group] || "postgres" # Run the command - run_command(:command => command, :user => user, :group => group) + shell_out!("/usr/bin/psql", *args, :user => user, :group => group) end def query(sql, options = {}) - # Get the database to use - database = options[:database] || "template1" - - # Construct the command string - command = "/usr/bin/psql --cluster #{@cluster} --no-align --command='#{sql}' #{database}" - # Run the query - status, stdout, stderr = output_of_command(command, :user => "postgres", :group => "postgres") - handle_command_failures(status, "STDOUT: #{stdout}\nSTDERR: #{stderr}", :output_on_failure => true) + result = execute(options.merge(:command => sql, :align => false)) # Split the output into lines - lines = stdout.split("\n") + lines = result.stdout.split("\n") # Remove the "(N rows)" line from the end lines.pop @@ -57,73 +74,107 @@ class Chef # Extract the record data lines.collect do |line| record = {} - fields.zip(line.split("|")) { |name,value| record[name.to_sym] = value } + fields.zip(line.split("|")) { |name, value| record[name.to_sym] = value } record end end def users - @users ||= query("SELECT * FROM pg_user").inject({}) do |users,user| + @users ||= query("SELECT *, ARRAY(SELECT groname FROM pg_group WHERE usesysid = ANY(grolist)) AS roles FROM pg_user").each_with_object({}) do |user, users| users[user[:usename]] = { :superuser => user[:usesuper] == "t", :createdb => user[:usercreatedb] == "t", :createrole => user[:usecatupd] == "t", - :replication => user[:userepl] == "t" + :replication => user[:userepl] == "t", + :roles => parse_array(user[:roles] || "{}") } - users end end def databases - @databases ||= query("SELECT d.datname, u.usename, d.encoding FROM pg_database AS d INNER JOIN pg_user AS u ON d.datdba = u.usesysid").inject({}) do |databases,database| + @databases ||= query("SELECT d.datname, u.usename, d.encoding, d.datcollate, d.datctype FROM pg_database AS d INNER JOIN pg_user AS u ON d.datdba = u.usesysid").each_with_object({}) do |database, databases| databases[database[:datname]] = { :owner => database[:usename], - :encoding => database[:encoding] + :encoding => database[:encoding], + :collate => database[:datcollate], + :ctype => database[:datctype] } - databases end end def extensions(database) @extensions ||= {} - @extensions[database] ||= query("SELECT extname, extversion FROM pg_extension", :database => database).inject({}) do |extensions,extension| + @extensions[database] ||= query("SELECT extname, extversion FROM pg_extension", :database => database).each_with_object({}) do |extension, extensions| extensions[extension[:extname]] = { :version => extension[:extversion] } - databases + end + end + + def tablespaces + @tablespaces ||= query("SELECT spcname, usename FROM pg_tablespace AS t INNER JOIN pg_user AS u ON t.spcowner = u.usesysid").each_with_object({}) do |tablespace, tablespaces| + tablespaces[tablespace[:spcname]] = { + :owner => tablespace[:usename] + } + end + end + + def schemas(database) + @schemas ||= {} + @schemas[database] ||= query("SELECT n.nspname, pg_catalog.pg_get_userbyid(n.nspowner) AS usename, n.nspacl FROM pg_namespace AS n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'", :database => database).each_with_object({}) do |schema, schemas| + name = "#{schema[:nspname]}" + + schemas[name] = { + :owner => schema[:usename], + :permissions => parse_acl(schema[:nspacl] || "{}") + } end end def tables(database) @tables ||= {} - @tables[database] ||= query("SELECT n.nspname, c.relname, u.usename, c.relacl FROM pg_class AS c INNER JOIN pg_user AS u ON c.relowner = u.usesysid INNER JOIN pg_namespace AS n ON c.relnamespace = n.oid", :database => database).inject({}) do |tables,table| + @tables[database] ||= query("SELECT n.nspname, c.relname, u.usename, c.relacl FROM pg_class AS c INNER JOIN pg_user AS u ON c.relowner = u.usesysid INNER JOIN pg_namespace AS n ON c.relnamespace = n.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND c.relkind IN ('r', 'p')", :database => database).each_with_object({}) do |table, tables| name = "#{table[:nspname]}.#{table[:relname]}" tables[name] = { :owner => table[:usename], :permissions => parse_acl(table[:relacl] || "{}") } + end + end + + def sequences(database) + @sequences ||= {} + @sequences[database] ||= query("SELECT n.nspname, c.relname, u.usename, c.relacl FROM pg_class AS c INNER JOIN pg_user AS u ON c.relowner = u.usesysid INNER JOIN pg_namespace AS n ON c.relnamespace = n.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND c.relkind = 'S'", :database => database).each_with_object({}) do |sequence, sequences| + name = "#{sequence[:nspname]}.#{sequence[:relname]}" - tables + sequences[name] = { + :owner => sequence[:usename], + :permissions => parse_acl(sequence[:relacl] || "{}") + } end end - private + private + + def parse_array(array) + array.sub(/^\{(.*)\}$/, "\\1").split(",") + end def parse_acl(acl) - acl.sub(/^\{(.*)\}$/, "\\1").split(",").inject({}) do |permissions, entry| - entry = entry.sub(/^"(.*)"$/) { $1.gsub(/\\"/, '"') }.sub(/\/.*$/, "") + parse_array(acl).each_with_object({}) do |entry, permissions| + entry = entry.sub(/^"(.*)"$/) { Regexp.last_match[1].gsub(/\\"/, '"') }.sub(%r{/.*$}, "") user, privileges = entry.split("=") user = user.sub(/^"(.*)"$/, "\\1") user = "public" if user == "" permissions[user] = { - "a" => :insert, "r" => :select, "w" => :update, "d" => :delete, - "D" => :truncate, "x" => :references, "t" => :trigger - }.values_at(*(privileges.chars)).compact - - permissions + "r" => :select, "a" => :insert, "w" => :update, "d" => :delete, + "D" => :truncate, "x" => :references, "t" => :trigger, + "C" => :create, "c" => :connect, "T" => :temporary, + "X" => :execute, "U" => :usage, "s" => :set, "A" => :alter_system + }.values_at(*privileges.chars).compact end end end