[PATCH v5] Add some properties to install and configure Postgresql

[David Bremner]

To do common tasks like adding users in an idempotent way requires some
non-obvious incantations, so it is worth providing properties for these tasks.

Signed-off-by: David Bremner <david at tethera.net>
---

The string-equal is because we are parsing the output of
user/programmer submitted SQL here, so it seems to be better to accept
a limited amount of variation in that output. It is quite possible I
am overthinking this, since the property in question is used only
within the file.


 consfigurator.asd          |  1 +
 src/package.lisp           | 12 +++++
 src/property/postgres.lisp | 92 ++++++++++++++++++++++++++++++++++++++
 3 files changed, 105 insertions(+)
 create mode 100644 src/property/postgres.lisp

diff --git a/consfigurator.asd b/consfigurator.asd
index 2ae9b40..ab31dcc 100644
--- a/consfigurator.asd
+++ b/consfigurator.asd
@@ -76,6 +76,7 @@
                (:file "src/property/timezone")
                (:file "src/property/swap")
                (:file "src/property/lxc")
+               (:file "src/property/postgres")
                (:file "src/connection/shell-wrap")
 	       (:file "src/connection/fork")
                (:file "src/connection/rehome")
diff --git a/src/package.lisp b/src/package.lisp
index d83a2c5..08bde22 100644
--- a/src/package.lisp
+++ b/src/package.lisp
@@ -940,6 +940,18 @@
 
                     #:lxc-ls))
 
+  (package :consfigurator.property.postgres
+           (:local-nicknames (#:apt         #:consfigurator.property.apt)
+                             (#:os          #:consfigurator.property.os)
+                             (#:cmd         #:consfigurator.property.cmd))
+           (:export #:installed
+                    #:superuser-is
+                    #:has-role
+                    #:has-database
+                    #:has-owner
+                    #:has-group
+                    #:can-login))
+
   (package :consfigurator.connection.local
            (:export #:local-connection))
 
diff --git a/src/property/postgres.lisp b/src/property/postgres.lisp
new file mode 100644
index 0000000..dae00e7
--- /dev/null
+++ b/src/property/postgres.lisp
@@ -0,0 +1,92 @@
+;;; Consfigurator -- Lisp declarative configuration management system
+
+;;; Copyright (C) 2021-2022  David Bremner <david at tethera.net>
+
+;;; This file is free software; you can redistribute it and/or modify
+;;; it under the terms of the GNU General Public License as published by
+;;; the Free Software Foundation; either version 3, or (at your option)
+;;; any later version.
+
+;;; This file is distributed in the hope that it will be useful,
+;;; but WITHOUT ANY WARRANTY; without even the implied warranty of
+;;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+;;; GNU General Public License for more details.
+
+;;; You should have received a copy of the GNU General Public License
+;;; along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+(in-package :consfigurator.property.postgres)
+(named-readtables:in-readtable :consfigurator)
+
+(defproplist installed :posix ()
+  "Ensure that postgresql and associated utilities are installed."
+  (:desc "postgresql and associated utilities installed")
+  (os:etypecase
+    (debianlike (apt:installed "postgresql" "postgresql-client"))))
+
+(defprop superuser-is :posix (name)
+  "Record Postgres superuser"
+  (:desc "postgres superuser is ${name}")
+  (:hostattrs
+   (push-hostattrs 'postgres-superuser name)))
+
+(defprop %psql :posix (sql &key unless)
+  (:check
+   (declare (ignore sql))
+   (and
+    unless
+    (let ((result (string-trim '(#\Space #\Newline #\Tab #\Return)
+                               (mrun "psql" "-t" "postgres" :input unless))))
+      (informat 4 "~&PSQL=> ~a" result)
+      ;; this is case insensitive on purpose.
+      (string-equal "yes" result))))
+  (:apply
+   (declare (ignore unless))
+   (mrun :inform "psql" "postgres" :input sql)))
+
+(defproplist %run-sql :posix (sql &key unless)
+  (installed)
+  (as (or (get-hostattrs-car 'postgres-superuser) "postgres")
+    (%psql sql :unless unless)))
+
+(defproplist has-role :posix (role)
+  "Ensure ROLE exists in the Postgres cluster."
+  (:desc #?"Postgres role ${role} exists")
+  (%run-sql
+   #?"DO $$
+      BEGIN
+      CREATE ROLE ${role};
+      EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping',
+        SQLERRM USING ERRCODE = SQLSTATE;
+      END
+      $$;"
+   :unless #?"select 'yes' from pg_roles where rolname='${role}';"))
+
+(defproplist has-database :posix (db-name)
+  "Ensure Postgres DATABASE exists"
+  (:desc #?"Postgres database ${db-name} exists")
+  ;; this has a potential race condition between test and creation
+  (%run-sql
+   #?"CREATE DATABASE ${db-name};"
+   :unless  #?"SELECT 'yes' FROM pg_database WHERE datname = '${db-name}';"))
+
+(defproplist has-owner :posix (database owner)
+  (:desc #?"Postgres database ${database} has owner ${owner}")
+  (%run-sql #?"ALTER DATABASE ${database} OWNER TO ${owner}"
+            :unless  #?"select 'yes' from pg_database d, pg_authid a
+                        where d.datname='${database}' and d.datdba = a.oid
+                        and a.rolname='${owner}';"))
+
+(defproplist has-group :posix (user group)
+  "Ensure Postgres user USER is a member of GROUP."
+  (:desc #?"Postgres role ${user} is a member of group ${group}")
+  (%run-sql #?"ALTER GROUP ${group} ADD USER ${user}"
+            :unless #?"select 'yes' from pg_auth_members m, pg_authid u, pg_authid g
+                       where u.rolname='${user}' and g.rolname='${group}'
+                       and m.member=u.oid and m.roleid=g.oid;"))
+
+(defproplist can-login :posix (user)
+  "Ensure USER can login to Postgres."
+  (:desc #?"Postgres role ${user} can login to database")
+  (%run-sql #?"ALTER USER ${user} WITH LOGIN;"
+            :unless #?"select 'yes' from pg_authid where rolname='${user}' and rolcanlogin=true;"))
-- 
2.34.1