From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp0 ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms11 with LMTPS id qPiJIJZhBWBiSwAA0tVLHw (envelope-from ) for ; Mon, 18 Jan 2021 10:23:18 +0000 Received: from aspmx1.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp0 with LMTPS id 4KRoHJZhBWDYUgAA1q6Kng (envelope-from ) for ; Mon, 18 Jan 2021 10:23:18 +0000 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 0E6C49402D6 for ; Mon, 18 Jan 2021 10:23:18 +0000 (UTC) Received: from localhost ([::1]:48396 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1l1Rgq-0001Qp-RE for larch@yhetil.org; Mon, 18 Jan 2021 05:23:16 -0500 Received: from eggs.gnu.org ([2001:470:142:3::10]:33208) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1l1Rbr-0005xH-3o for guix-patches@gnu.org; Mon, 18 Jan 2021 05:18:08 -0500 Received: from debbugs.gnu.org ([209.51.188.43]:34880) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1l1Rbo-0006kk-0k for guix-patches@gnu.org; Mon, 18 Jan 2021 05:18:06 -0500 Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1l1Rbn-0005Ke-SK for guix-patches@gnu.org; Mon, 18 Jan 2021 05:18:03 -0500 X-Loop: help-debbugs@gnu.org Subject: [bug#45860] [PATCH v2 5/5] services: postgresql: Add postgresql-role-service-type. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-CC: guix-patches@gnu.org Resent-Date: Mon, 18 Jan 2021 10:18:03 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 45860 X-GNU-PR-Package: guix-patches X-GNU-PR-Keywords: To: 45860@debbugs.gnu.org Received: via spool by 45860-submit@debbugs.gnu.org id=B45860.161096502920391 (code B ref 45860); Mon, 18 Jan 2021 10:18:03 +0000 Received: (at 45860) by debbugs.gnu.org; 18 Jan 2021 10:17:09 +0000 Received: from localhost ([127.0.0.1]:46418 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1l1Rau-0005Ik-W0 for submit@debbugs.gnu.org; Mon, 18 Jan 2021 05:17:09 -0500 Received: from eggs.gnu.org ([209.51.188.92]:39592) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1l1Rap-0005HE-1G for 45860@debbugs.gnu.org; Mon, 18 Jan 2021 05:17:03 -0500 Received: from fencepost.gnu.org ([2001:470:142:3::e]:52248) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1l1Raj-0006Ei-SP; Mon, 18 Jan 2021 05:16:57 -0500 Received: from [2a01:e0a:19b:d9a0:1538:87ab:3a95:7600] (port=55432 helo=localhost.localdomain) by fencepost.gnu.org with esmtpsa (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1l1Rai-0001QJ-UW; Mon, 18 Jan 2021 05:16:57 -0500 From: Mathieu Othacehe Date: Mon, 18 Jan 2021 11:16:28 +0100 Message-Id: <20210118101628.202607-6-othacehe@gnu.org> X-Mailer: git-send-email 2.29.2 In-Reply-To: <20210118101628.202607-1-othacehe@gnu.org> References: <20210118101628.202607-1-othacehe@gnu.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list X-BeenThere: guix-patches@gnu.org List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Cc: Mathieu Othacehe Errors-To: guix-patches-bounces+larch=yhetil.org@gnu.org Sender: "Guix-patches" X-Migadu-Flow: FLOW_IN X-Migadu-Spam-Score: 3.64 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=pass (policy=none) header.from=gnu.org; spf=pass (aspmx1.migadu.com: domain of guix-patches-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=guix-patches-bounces@gnu.org X-Migadu-Queue-Id: 0E6C49402D6 X-Spam-Score: 3.64 X-Migadu-Scanner: scn0.migadu.com X-TUID: gLhl8yjLr/wh * gnu/services/databases.scm (postgresql-role, postgresql-role?, postgresql-role-name, postgresql-role-permissions, postgresql-role-create-database?, postgresql-role-configuration, postgresql-role-configuration?, postgresql-role-configuration-host, postgresql-role-configuration-roles, postgresql-role-service-type): New procedures. * gnu/tests/databases.scm: Test it. * doc/guix.texi: Document it. --- doc/guix.texi | 61 ++++++++++++++++++++++++ gnu/services/databases.scm | 95 ++++++++++++++++++++++++++++++++++++++ gnu/tests/databases.scm | 44 +++++++++++++++++- 3 files changed, 199 insertions(+), 1 deletion(-) diff --git a/doc/guix.texi b/doc/guix.texi index 22674e2804..13d95b36d1 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -19427,6 +19427,67 @@ here}. @end table @end deftp +@deffn {Scheme Variable} postgresql-role-service-type +This service allows to create PostgreSQL roles and databases after +PostgreSQL service start. Here is an example of its use. + +@lisp +(service postgresql-role-service-type + (postgresql-role-configuration + (roles + (list (postgresql-role + (name "test") + (create-database? #t)))))) +@end lisp + +This service can be extended with extra roles, as in this +example: + +@lisp +(service-extension postgresql-role-service-type + (const (postgresql-role + (name "alice") + (create-database? #t)))) +@end lisp +@end deffn + +@deftp {Data Type} postgresql-role +PostgreSQL manages database access permissions using the concept of +roles. A role can be thought of as either a database user, or a group +of database users, depending on how the role is set up. Roles can own +database objects (for example, tables) and can assign privileges on +those objects to other roles to control who has access to which objects. + +@table @asis +@item @code{name} +The role name. + +@item @code{permissions} (default: @code{'(createdb login)}) +The role permissions list. Supported permissions are @code{createdb} +and @code{login}. + +@item @code{create-database?} (default: @code{#f}) +Whether to create a database with the same name as the role. + +@end table +@end deftp + +@deftp {Data Type} postgresql-role-configuration +Data type representing the configuration of +@var{postgresql-role-service-type}. + +@table @asis +@item @code{host} (default: @code{"/var/run/postgresql"}) +The PostgreSQL host to connect to. + +@item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) +File name of the log file. + +@item @code{roles} (default: @code{'()}) +The initial PostgreSQL roles to create. +@end table +@end deftp + @subsubheading MariaDB/MySQL @defvr {Scheme Variable} mysql-service-type diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index 0d60616156..88e4b1813a 100644 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -58,6 +58,18 @@ postgresql-service postgresql-service-type + postgresql-role + postgresql-role? + postgresql-role-name + postgresql-role-permissions + postgresql-role-create-database? + postgresql-role-configuration + postgresql-role-configuration? + postgresql-role-configuration-host + postgresql-role-configuration-roles + + postgresql-role-service-type + memcached-service-type memcached-configuration memcached-configuration? @@ -343,6 +355,89 @@ and stores the database cluster in @var{data-directory}." (data-directory data-directory) (extension-packages extension-packages)))) +(define-record-type* + postgresql-role make-postgresql-role + postgresql-role? + (name postgresql-role-name) ;string + (permissions postgresql-role-permissions + (default '(createdb login))) ;list + (create-database? postgresql-role-create-database? ;boolean + (default #f))) + +(define-record-type* + postgresql-role-configuration make-postgresql-role-configuration + postgresql-role-configuration? + (host postgresql-role-configuration-host ;string + (default "/var/run/postgresql")) + (log postgresql-role-configuration-log ;string + (default "/var/log/postgresql_roles.log")) + (roles postgresql-role-configuration-roles + (default '()))) ;list + +(define (postgresql-create-roles config) + ;; See: https://www.postgresql.org/docs/current/sql-createrole.html for the + ;; complete permissions list. + (define (format-permissions permissions) + (let ((dict '((createdb . "CREATEDB") + (login . "LOGIN")))) + (string-join (map (lambda (permission) + (assq-ref dict permission)) + permissions) + " "))) + + (define (roles->queries roles) + (apply mixed-text-file "queries" + (append-map (lambda (role) + (match-record role + (name permissions create-database?) + `("CREATE ROLE " ,name + " WITH " ,(format-permissions permissions) + ";\n" + ,@(if create-database? + `("CREATE DATABASE " ,name + " OWNER " ,name ";\n") + '())))) + roles))) + + (let ((host (postgresql-role-configuration-host config)) + (roles (postgresql-role-configuration-roles config))) + (program-file + "postgresql-create-roles" + #~(begin + (let ((psql #$(file-append postgresql "/bin/psql"))) + (execl psql psql "-a" + "-h" #$host + "-f" #$(roles->queries roles))))))) + +(define (postgresql-role-shepherd-service config) + (match-record config + (log) + (list (shepherd-service + (requirement '(postgres)) + (provision '(postgres-roles)) + (one-shot? #t) + (start #~(make-forkexec-constructor + (list #$(postgresql-create-roles config)) + #:user "postgres" #:group "postgres" + #:log-file #$log)) + (documentation "Create PostgreSQL roles."))))) + +(define postgresql-role-service-type + (service-type (name 'postgresql-role) + (extensions + (list (service-extension shepherd-root-service-type + postgresql-role-shepherd-service))) + (compose concatenate) + (extend (lambda (config extended-roles) + (match-record config + (host roles) + (postgresql-role-configuration + (host host) + (roles (append roles extended-roles)))))) + (default-value (postgresql-role-configuration)) + (description "Ensure the specified PostgreSQL roles are +created after the PostgreSQL database is started."))) + ;;; ;;; Memcached diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index d881a8c3ee..e831d69f5a 100644 --- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -217,6 +217,9 @@ (define %postgresql-log-directory "/var/log/postgresql") +(define %role-log-file + "/var/log/postgresql_roles.log") + (define %postgresql-os (simple-operating-system (service postgresql-service-type @@ -229,7 +232,13 @@ ("random_page_cost" 2) ("auto_explain.log_min_duration" "100 ms") ("work_mem" "500 MB") - ("debug_print_plan" #t))))))))) + ("debug_print_plan" #t))))))) + (service postgresql-role-service-type + (postgresql-role-configuration + (roles + (list (postgresql-role + (name "root") + (create-database? #t)))))))) (define (run-postgresql-test) "Run tests in %POSTGRESQL-OS." @@ -282,6 +291,39 @@ #t)) marionette)) + (test-assert "database ready" + (begin + (marionette-eval + '(begin + (let loop ((i 10)) + (unless (or (zero? i) + (and (file-exists? #$%role-log-file) + (string-contains + (call-with-input-file #$%role-log-file + get-string-all) + ";\nCREATE DATABASE"))) + (sleep 1) + (loop (- i 1))))) + marionette))) + + (test-assert "database creation" + (marionette-eval + '(begin + (use-modules (gnu services herd) + (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tAh" "/var/run/postgresql" + "-c" "SELECT 1 FROM pg_database WHERE + datname='root'")) + (output (get-string-all port))) + (close-pipe port) + (string-contains output "1"))) + marionette)) + (test-end) (exit (= (test-runner-fail-count (test-runner-current)) 0))))) -- 2.29.2