diff options
author | David Bremner <david@tethera.net> | 2022-01-30 12:44:26 -0400 |
---|---|---|
committer | Sean Whitton <spwhitton@spwhitton.name> | 2022-02-02 13:43:53 -0700 |
commit | 1ccfc5894a8b23375315d52f460b9f045e896a7a (patch) | |
tree | c5338c2e132e4ffa75b97bcf72892a082c4b1b18 | |
parent | f72fdc66e3ce29b7306e9da3803a2e226259fc0f (diff) | |
download | consfigurator-1ccfc5894a8b23375315d52f460b9f045e896a7a.tar.gz |
Add some properties to install and configure Postgresql
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@tethera.net>
-rw-r--r-- | consfigurator.asd | 1 | ||||
-rw-r--r-- | src/package.lisp | 12 | ||||
-rw-r--r-- | src/property/postgres.lisp | 92 |
3 files changed, 105 insertions, 0 deletions
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@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;")) |