X-Git-Url: https://git.openstreetmap.org./chef.git/blobdiff_plain/57997055a817d2104f605a56141ae26515aabbd2..b7c12630cb9de017d76fe60ff296534d6d061399:/cookbooks/postgresql/libraries/postgresql.rb?ds=sidebyside diff --git a/cookbooks/postgresql/libraries/postgresql.rb b/cookbooks/postgresql/libraries/postgresql.rb index 05bf158f2..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 @@ -63,12 +80,13 @@ class Chef end def users - @users ||= query("SELECT * FROM pg_user").each_with_object({}) do |user, users| + @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] || "{}") } end end @@ -93,9 +111,29 @@ class Chef 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).each_with_object({}) do |table, 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 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] = { @@ -105,20 +143,38 @@ class Chef end end - private + 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]}" + + sequences[name] = { + :owner => sequence[:usename], + :permissions => parse_acl(sequence[:relacl] || "{}") + } + end + end + + private + + def parse_array(array) + array.sub(/^\{(.*)\}$/, "\\1").split(",") + end def parse_acl(acl) - acl.sub(/^\{(.*)\}$/, "\\1").split(",").each_with_object({}) do |entry, permissions| - entry = entry.sub(/^"(.*)"$/) { Regexp.last_match[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 + "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