From 2b4843ace5d011913f8e72df157ffd127e9da4a9 Mon Sep 17 00:00:00 2001
From: Tom Hughes <tom@compton.nu>
Date: Sun, 15 Oct 2023 11:16:01 +0100
Subject: [PATCH 1/1] Add support for granting roles to postgres users

---
 cookbooks/postgresql/libraries/postgresql.rb | 11 ++++++++---
 cookbooks/postgresql/resources/user.rb       | 19 +++++++++++++++++++
 2 files changed, 27 insertions(+), 3 deletions(-)

diff --git a/cookbooks/postgresql/libraries/postgresql.rb b/cookbooks/postgresql/libraries/postgresql.rb
index ab8ea6fbe..1a39da25a 100644
--- a/cookbooks/postgresql/libraries/postgresql.rb
+++ b/cookbooks/postgresql/libraries/postgresql.rb
@@ -76,12 +76,13 @@ module OpenStreetMap
     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
@@ -140,8 +141,12 @@ module OpenStreetMap
 
     private
 
+    def parse_array(array)
+      array.sub(/^\{(.*)\}$/, "\\1").split(",")
+    end
+
     def parse_acl(acl)
-      acl.sub(/^\{(.*)\}$/, "\\1").split(",").each_with_object({}) do |entry, permissions|
+      parse_array(acl).each_with_object({}) do |entry, permissions|
         entry = entry.sub(/^"(.*)"$/) { Regexp.last_match[1].gsub(/\\"/, '"') }.sub(%r{/.*$}, "")
         user, privileges = entry.split("=")
 
diff --git a/cookbooks/postgresql/resources/user.rb b/cookbooks/postgresql/resources/user.rb
index 35ff5aa8b..31194fedc 100644
--- a/cookbooks/postgresql/resources/user.rb
+++ b/cookbooks/postgresql/resources/user.rb
@@ -30,6 +30,7 @@ property :superuser, :kind_of => [TrueClass, FalseClass], :default => false
 property :createdb, :kind_of => [TrueClass, FalseClass], :default => false
 property :createrole, :kind_of => [TrueClass, FalseClass], :default => false
 property :replication, :kind_of => [TrueClass, FalseClass], :default => false
+property :roles, :kind_of => [String, Array]
 
 action :create do
   password = new_resource.password ? "ENCRYPTED PASSWORD '#{new_resource.password.shellescape}'" : ""
@@ -70,6 +71,24 @@ action :create do
         end
       end
     end
+
+    roles = Array(new_resource.roles)
+
+    roles.each do |role|
+      next if current_user[:roles].include?(role)
+
+      converge_by "grant #{role} to #{new_resource.user}" do
+        cluster.execute(:command => "GRANT \"#{role}\" TO \"#{new_resource.user}\"")
+      end
+    end
+
+    current_user[:roles].each do |role|
+      next if roles.include?(role)
+
+      converge_by "revoke #{role} from #{new_resource.user}" do
+        cluster.execute(:command => "REVOKE \"#{role}\" FROM \"#{new_resource.user}\"")
+      end
+    end
   end
 end
 
-- 
2.39.5