[PATCH] 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           | 10 +++++
 src/property/postgres.lisp | 92 ++++++++++++++++++++++++++++++++++++++
 3 files changed, 103 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..acc6b85 100644
--- a/src/package.lisp
+++ b/src/package.lisp
@@ -940,6 +940,16 @@
 
                     #:lxc-ls))
 
+  (package :consfigurator.property.postgres
+           (:local-nicknames (#:os          #:consfigurator.property.os)
+                             (#:apt         #:consfigurator.property.apt))
+           (:export #:installed
+                    #:superuser-is
+                    #:has-role
+                    #:has-database
+                    #: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..ed9fe04
--- /dev/null
+++ b/src/property/postgres.lisp
@@ -0,0 +1,92 @@
+;;; 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)
+(named-readtables:in-readtable :interpol-syntax)
+
+;; Currently all of properties in this package require Debian because they use
+;; `runuser' and because of the current implementation of the following
+;; function.
+(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)))
+
+(defun superuser ()
+  "Return Postgres superuser."
+  (or (get-hostattrs-car 'postgres-superuser) "postgres"))
+
+(defprop %run-sql :posix (sql)
+  "Run given sql as the Postgres superuser against the `postgres' database."
+  (:apply
+   (mrun "runuser" "-u" (superuser) "--" "psql" "postgres" :input sql)))
+
+(defproplist has-role :posix (role)
+  "Ensure ROLE exists in the Postgres cluster."
+  (:desc #?"Postgres role ${role} exists")
+  (:hostattrs
+   (os:required 'os:debianlike))
+  (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 &key (owner nil))
+  "Ensure Postgres DATABASE exists, optionally with owner OWNER"
+  (:desc
+   (strcat #?"Postgres database ${db-name} exists"
+           (if owner #?" with owner ${owner}" "")))
+  (:hostattrs
+   (os:required 'os:debianlike))
+  (installed)
+  ;; this has a potential race condition between test and creation
+  (%run-sql
+   (strcat
+    #?"SELECT 'CREATE DATABASE ${db-name}"
+    (if owner #?" WITH OWNER ${owner}" "")
+    "' WHERE NOT EXISTS "
+    #?"(SELECT FROM pg_database WHERE datname = '${db-name}')\\gexec")))
+
+(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}")
+  (:hostattrs
+   ;; relies on runuser
+   (os:required 'os:debianlike))
+  (installed)
+  (%run-sql #?"ALTER GROUP ${group} ADD USER ${user}"))
+
+(defprop can-login :posix (user)
+  "Ensure USER can login to Postgres."
+  (:desc #?"Postgres role ${user} can login to database")
+  (:hostattrs
+   (os:required 'os:debianlike))
+  (installed)
+  (%run-sql #?"ALTER USER ${user} WITH LOGIN;"))
-- 
2.34.1