[PATCH consfigurator v3] Add some properties to install and configure Postgresql

[David Bremner]

In order 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>
---
 consfigurator.asd          |  1 +
 src/package.lisp           | 12 ++++++
 src/property/postgres.lisp | 75 ++++++++++++++++++++++++++++++++++++++
 3 files changed, 88 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..f50ce6b
--- /dev/null
+++ b/src/property/postgres.lisp
@@ -0,0 +1,75 @@
+;;; Consfigurator -- Lisp declarative configuration management system
+
+;;; Copyright (C) 2021  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)))
+
+(defproplist %run-sql :posix (sql)
+  (as (or (get-hostattrs-car 'postgres-superuser) "postgres")
+    (cmd:single :input sql "psql" "postgres")))
+
+(defproplist has-role :posix (role)
+  "Ensure ROLE exists in the Postgres cluster."
+  (:desc #?"Postgres role ${role} exists")
+  (installed)
+  (%run-sql
+   #?"DO $$
+      BEGIN
+      CREATE ROLE ${role};
+      EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping',
+        SQLERRM USING ERRCODE = SQLSTATE;
+      END
+      $$;"))
+
+(defproplist has-database :posix (db-name)
+  "Ensure Postgres DATABASE exists"
+  (:desc #?"Postgres database ${db-name} exists")
+  (installed)
+  ;; this has a potential race condition between test and creation
+  (%run-sql
+   (strcat
+    #?"SELECT 'CREATE DATABASE ${db-name}' WHERE NOT EXISTS "
+    #?"(SELECT FROM pg_database WHERE datname = '${db-name}')\\gexec")))
+
+(defproplist has-owner :posix (database owner)
+  (:desc #?"Postgres database ${database} has owner ${owner}")
+  (installed)
+  (%run-sql #?"ALTER DATABASE ${database} OWNER TO ${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}")
+  (installed)
+  (%run-sql #?"ALTER GROUP ${group} ADD USER ${user}"))
+
+(defproplist can-login :posix (user)
+  "Ensure USER can login to Postgres."
+  (:desc #?"Postgres role ${user} can login to database")
+  (installed)
+  (%run-sql #?"ALTER USER ${user} WITH LOGIN;"))
-- 
2.34.1