Improve PostgreSQL service.

DoneSubmitted by Mathieu Othacehe.
Details
2 participants
  • Christopher Baines
  • Mathieu Othacehe
Owner
unassigned
Severity
normal
M
M
Mathieu Othacehe wrote on 14 Jan 14:36 +0100
(address . guix-patches@gnu.org)
87h7nj4p3g.fsf@gnu.org
Hello,
Here's a patch to improve PostgreSQL service. It merges<postgresql-configuration> and <postgresql-config-file> records. It alsosanitises parameters conversion and logging.
Thanks,
Mathieu
From 87703b749631acd8ddc2b9eeb36a5be7189a019b Mon Sep 17 00:00:00 2001From: Mathieu Othacehe <othacehe@gnu.org>Date: Thu, 14 Jan 2021 14:13:30 +0100Subject: [PATCH] Improve PostgreSQL service.
Merge <postgresql-configuration> and <postgresql-config-file> records,sanitize parameters convertion and logging.
* gnu/services/databases.scm (postgresql-config-file,postgresql-config-file?, postgresql-config-file-log-destination,postgresql-config-file-hba-file, postgresql-config-file-ident-file,postgresql-config-file-extra-config, postgresql-configuration): Remove them.(postgresql-configuration-log-destination,postgresql-configuration-hba-file,postgresql-configuration-ident-file,postgresql-configuration-socket-directory,postgresql-configuration-extra-config,postgresql-configuration-extension-packages): New exported procedures.(<postgresql-config-file>): Merge it with ...(<postgresql-configuration>): ... this record, and add a "socket-directory"field.(postgresql-config-file-compiler): Replace it with ...(postgresql-config-file): ... this procedure.(postgresql-activation): Use "match-record" instead of "match". Create the"socket-directory" if needed.(postgresql-shepherd-service): Use "match-record" intead of "match". Pass the"log-destination" argument to "pg_ctl" if needed.(postgresql-service): Remove it.* gnu/tests/databases.scm (%postgresql-log-directory): New variable.(%postgresql-os): Pass "log-destination" and "extra-config" fields.(log-file): New test case.* gnu/tests/guix.scm (%guix-data-service-os): Adapt accordingly.* doc/guix.texi (Database Services): Ditto.--- doc/guix.texi | 89 +++++----- gnu/services/databases.scm | 332 +++++++++++++++++++------------------ gnu/tests/databases.scm | 30 +++- gnu/tests/guix.scm | 10 +- 4 files changed, 245 insertions(+), 216 deletions(-)
Toggle diff (586 lines)diff --git a/doc/guix.texi b/doc/guix.texiindex f38e018dff..7fb7652166 100644--- a/doc/guix.texi+++ b/doc/guix.texi@@ -19302,14 +19302,41 @@ Port on which PostgreSQL should listen. @item @code{locale} (default: @code{"en_US.utf8"}) Locale to use as the default when creating the database cluster. -@item @code{config-file} (default: @code{(postgresql-config-file)})-The configuration file to use when running PostgreSQL. The default-behaviour uses the postgresql-config-file record with the default values-for the fields.- @item @code{data-directory} (default: @code{"/var/lib/postgresql/data"}) Directory in which to store the data. +@item @code{log-destination} (default: @code{'syslog})+The logging method to use for PostgreSQL. It can be set to a directory,+such as @code{"/var/log/postgresql"}. In that case, PostgreSQL will+write log files to that directory. The @command{pg_ctl} output will+also be written to a file named @code{"pg_ctl.log"} in that very+directory. This file can be useful to debug PostgreSQL configuration+errors for instance.++@item @code{hba-file} (default: @code{%default-postgres-hba})+Filename or G-expression for the host-based authentication+configuration.++@item @code{ident-file} (default: @code{%default-postgres-ident})+Filename or G-expression for the user name mapping configuration.++@item @code{socket-directory} (default: @code{"/var/lib/postgresql"})+Specifies the directory of the Unix-domain socket(s) on which PostgreSQL+is to listen for connections from client applications. If set to+@code{#false} PostgreSQL does not listen on any Unix-domain sockets, in+which case only TCP/IP sockets can be used to connect to the server.++@item @code{extra-config} (default: @code{'()})+List of additional keys and values to include in the PostgreSQL config+file. Each entry in the list should be a list where the first element+is the key, and the remaining elements are the values.++The values can be numbers, booleans or strings and will be mapped to+PostgreSQL parameters types @code{Boolean}, @code{String},+@code{Numeric}, @code{Numeric with Unit} and @code{Enumerated} described+@uref{https://www.postgresql.org/docs/current/config-setting.html,+here}.+ @item @code{extension-packages} (default: @code{'()}) @cindex postgresql extension-packages Additional extensions are loaded from packages listed in@@ -19351,54 +19378,28 @@ dblink as they are already loadable by postgresql. This field is only required to add extensions provided by other packages. @end table-@end deftp -@deftp {Data Type} postgresql-config-file-Data type representing the PostgreSQL configuration file. As shown in-the following example, this can be used to customize the configuration-of PostgreSQL. Note that you can use any G-expression or filename in-place of this record, if you already have a configuration file you'd-like to use for example.+Here is an example of PostgreSQL configuration, with the log destination+set to @code{"/var/log/postgresql"} directory. A few random extra+config parameters types are passed. @lisp (service postgresql-service-type (postgresql-configuration- (config-file- (postgresql-config-file- (log-destination "stderr")- (hba-file- (plain-file "pg_hba.conf"- "+ (log-destination "/var/log/postgresql")+ (hba-file+ (plain-file "pg_hba.conf"+ " local all all trust host all all 127.0.0.1/32 md5 host all all ::1/128 md5"))- (extra-config- '(("session_preload_libraries" "'auto_explain'")- ("random_page_cost" "2")- ("auto_explain.log_min_duration" "'100ms'")- ("work_mem" "'500MB'")- ("logging_collector" "on")- ("log_directory" "'/var/log/postgresql'")))))))+ (extra-config+ '(("session_preload_libraries" "auto_explain")+ ("random_page_cost" 2)+ ("auto_explain.log_min_duration" "100 ms")+ ("work_mem" "500 MB")+ ("debug_print_plan" #t))))) @end lisp--@table @asis-@item @code{log-destination} (default: @code{"syslog"})-The logging method to use for PostgreSQL. Multiple values are accepted,-separated by commas.--@item @code{hba-file} (default: @code{%default-postgres-hba})-Filename or G-expression for the host-based authentication-configuration.--@item @code{ident-file} (default: @code{%default-postgres-ident})-Filename or G-expression for the user name mapping configuration.--@item @code{extra-config} (default: @code{'()})-List of additional keys and values to include in the PostgreSQL config-file. Each entry in the list should be a list where the first element-is the key, and the remaining elements are the values.--@end table @end deftp @subsubheading MariaDB/MySQLdiff --git a/gnu/services/databases.scm b/gnu/services/databases.scmindex d2dc5f0da8..013ca97227 100644--- a/gnu/services/databases.scm+++ b/gnu/services/databases.scm@@ -38,22 +38,19 @@ #:use-module (guix gexp) #:use-module (srfi srfi-1) #:use-module (ice-9 match)- #:export (postgresql-config-file- postgresql-config-file?- postgresql-config-file-log-destination- postgresql-config-file-hba-file- postgresql-config-file-ident-file- postgresql-config-file-extra-config-- postgresql-configuration+ #:export (postgresql-configuration postgresql-configuration? postgresql-configuration-postgresql postgresql-configuration-port postgresql-configuration-locale- postgresql-configuration-file postgresql-configuration-data-directory+ postgresql-configuration-log-destination+ postgresql-configuration-hba-file+ postgresql-configuration-ident-file+ postgresql-configuration-socket-directory+ postgresql-configuration-extra-config+ postgresql-configuration-extension-packages - postgresql-service postgresql-service-type memcached-service-type@@ -98,49 +95,6 @@ host all all ::1/128 md5")) (plain-file "pg_ident.conf" "# MAPNAME SYSTEM-USERNAME PG-USERNAME")) -(define-record-type* <postgresql-config-file>- postgresql-config-file make-postgresql-config-file- postgresql-config-file?- (log-destination postgresql-config-file-log-destination- (default "syslog"))- (hba-file postgresql-config-file-hba-file- (default %default-postgres-hba))- (ident-file postgresql-config-file-ident-file- (default %default-postgres-ident))- (extra-config postgresql-config-file-extra-config- (default '())))--(define-gexp-compiler (postgresql-config-file-compiler- (file <postgresql-config-file>) system target)- (match file- (($ <postgresql-config-file> log-destination hba-file- ident-file extra-config)- (define (single-quote string)- (if string- (list "'" string "'")- '()))-- (define contents- (append-map- (match-lambda- ((key) '())- ((key . #f) '())- ((key values ...) `(,key " = " ,@values "\n")))-- `(("log_destination" ,@(single-quote log-destination))- ("hba_file" ,@(single-quote hba-file))- ("ident_file" ,@(single-quote ident-file))- ,@extra-config)))-- (gexp->derivation- "postgresql.conf"- #~(call-with-output-file (ungexp output "out")- (lambda (port)- (display- (string-append #$@contents)- port)))- #:local-build? #t))))- (define-record-type* <postgresql-configuration> postgresql-configuration make-postgresql-configuration postgresql-configuration?@@ -149,13 +103,59 @@ host all all ::1/128 md5")) (default 5432)) (locale postgresql-configuration-locale (default "en_US.utf8"))- (config-file postgresql-configuration-file- (default (postgresql-config-file))) (data-directory postgresql-configuration-data-directory (default "/var/lib/postgresql/data"))+ (log-destination postgresql-configuration-log-destination+ (default 'syslog))+ (hba-file postgresql-configuration-hba-file+ (default %default-postgres-hba))+ (ident-file postgresql-configuration-ident-file+ (default %default-postgres-ident))+ (socket-directory postgresql-configuration-socket-directory+ (default "/var/run/postgresql"))+ (extra-config postgresql-configuration-extra-config+ (default '())) (extension-packages postgresql-configuration-extension-packages (default '()))) +(define (postgresql-config-file config)+ (match-record config <postgresql-configuration>+ (log-destination hba-file ident-file socket-directory extra-config)+ ;; See: https://www.postgresql.org/docs/current/config-setting.html.+ (define (format-value value)+ (cond+ ((boolean? value)+ (list (if value "on" "off")))+ ((number? value)+ (list (number->string value)))+ (else+ (list "'" value "'"))))++ (define contents+ (append-map+ (match-lambda+ ((key) '())+ ((key . #f) '())+ ((key values ...)+ `(,key " = " ,@(append-map format-value values) "\n")))++ `(,@(cond+ ((eq? log-destination 'syslog)+ '(("log_destination" "syslog")))+ ((string? log-destination)+ `(("log_destination" "stderr")+ ("logging_collector" #t)+ ("log_directory" ,log-destination)))+ (else '()))+ ("hba_file" ,hba-file)+ ("ident_file" ,ident-file)+ ,@(if socket-directory+ `(("unix_socket_directories" ,socket-directory))+ '())+ ,@extra-config)))++ (apply mixed-text-file "postgresql.conf" contents)))+ (define %postgresql-accounts (list (user-group (name "postgres") (system? #t)) (user-account@@ -178,124 +178,126 @@ host all all ::1/128 md5")) #:builder (begin (use-modules (guix build utils) (guix build union) (srfi srfi-26))- (union-build (assoc-ref %outputs "out") (map (lambda (input) (cdr input)) %build-inputs))+ (union-build (assoc-ref %outputs "out")+ (map (lambda (input) (cdr input)) %build-inputs)) #t))) (inputs `(("postgresql" ,postgresql) ,@(map (lambda (extension) (list "extension" extension)) extension-packages)))))) -(define postgresql-activation- (match-lambda- (($ <postgresql-configuration> postgresql port locale config-file data-directory- extension-packages)- #~(begin- (use-modules (guix build utils)- (ice-9 match))-- (let ((user (getpwnam "postgres"))- (initdb (string-append #$(final-postgresql postgresql extension-packages)- "/bin/initdb"))- (initdb-args- (append- (if #$locale- (list (string-append "--locale=" #$locale))- '()))))- ;; Create db state directory.- (mkdir-p #$data-directory)- (chown #$data-directory (passwd:uid user) (passwd:gid user))-- ;; Drop privileges and init state directory in a new- ;; process. Wait for it to finish before proceeding.- (match (primitive-fork)- (0- ;; Exit with a non-zero status code if an exception is thrown.- (dynamic-wind- (const #t)- (lambda ()- (setgid (passwd:gid user))- (setuid (passwd:uid user))- (primitive-exit- (apply system*- initdb- "-D"- #$data-directory- initdb-args)))- (lambda ()- (primitive-exit 1))))- (pid (waitpid pid))))))))--(define postgresql-shepherd-service- (match-lambda- (($ <postgresql-configuration> postgresql port locale config-file data-directory- extension-packages)- (let* ((pg_ctl-wrapper- ;; Wrapper script that switches to the 'postgres' user before- ;; launching daemon.- (program-file- "pg_ctl-wrapper"- #~(begin- (use-modules (ice-9 match)- (ice-9 format))- (match (command-line)- ((_ mode)- (let ((user (getpwnam "postgres"))- (pg_ctl #$(file-append (final-postgresql postgresql extension-packages)- "/bin/pg_ctl"))- (options (format #f "--config-file=~a -p ~d"- #$config-file #$port)))- (setgid (passwd:gid user))- (setuid (passwd:uid user))- (execl pg_ctl pg_ctl "-D" #$data-directory "-o" options- mode)))))))- (pid-file (in-vicinity data-directory "postmaster.pid"))- (action (lambda args- #~(lambda _- (invoke #$pg_ctl-wrapper #$@args)- (match '#$args- (("start")- (call-with-input-file #$pid-file read))- (_ #t))))))- (list (shepherd-service- (provision '(postgres))- (documentation "Run the PostgreSQL daemon.")- (requirement '(user-processes loopback syslogd))- (modules `((ice-9 match)- ,@%default-modules))- (start (action "start"))- (stop (action "stop"))))))))+(define (postgresql-activation config)+ (match-record config <postgresql-configuration>+ (postgresql port locale data-directory log-destination socket-directory+ extension-packages)+ #~(begin+ (use-modules (guix build utils)+ (ice-9 match))++ (let ((user (getpwnam "postgres"))+ (initdb (string-append+ #$(final-postgresql postgresql extension-packages)+ "/bin/initdb"))+ (initdb-args+ (append+ (if #$locale+ (list (string-append "--locale=" #$locale))+ '()))))+ ;; Create db state directory.+ (mkdir-p #$data-directory)+ (chown #$data-directory (passwd:uid user) (passwd:gid user))++ (when (string? #$socket-directory)+ (mkdir-p #$socket-directory)+ (chown #$socket-directory (passwd:uid user) (passwd:gid user)))++ (when (string? #$log-destination)+ (mkdir-p #$log-destination)+ (chown #$log-destination (passwd:uid user) (passwd:gid user)))++ ;; Drop privileges and init state directory in a new+ ;; process. Wait for it to finish before proceeding.+ (match (primitive-fork)+ (0+ ;; Exit with a non-zero status code if an exception is thrown.+ (dynamic-wind+ (const #t)+ (lambda ()+ (setgid (passwd:gid user))+ (setuid (passwd:uid user))+ (primitive-exit+ (apply system*+ initdb+ "-D"+ #$data-directory+ initdb-args)))+ (lambda ()+ (primitive-exit 1))))+ (pid (waitpid pid)))))))++(define (postgresql-shepherd-service config)+ (match-record config <postgresql-configuration>+ (postgresql port locale data-directory log-destination extension-packages)+ (let* ((config-file (postgresql-config-file config))+ (pg_ctl-wrapper+ ;; Wrapper script that switches to the 'postgres' user before+ ;; launching daemon.+ (program-file+ "pg_ctl-wrapper"+ #~(begin+ (use-modules (ice-9 match)+ (ice-9 format))+ (match (command-line)+ ((_ mode)+ (let ((user (getpwnam "postgres"))+ (pg_ctl #$(file-append+ (final-postgresql postgresql+ extension-packages)+ "/bin/pg_ctl"))+ (options+ (format #f "--config-file=~a -p ~d"+ #$config-file+ #$port)))+ (setgid (passwd:gid user))+ (setuid (passwd:uid user))+ (execl pg_ctl pg_ctl "-D" #$data-directory+ #$@(if (string? log-destination)+ (list "-l"+ (string-append log-destination+ "/pg_ctl.log"))+ '())+ "-o" options+ mode)))))))+ (pid-file (in-vicinity data-directory "postmaster.pid"))+ (action (lambda args+ #~(lambda _+ (invoke #$pg_ctl-wrapper #$@args)+ (match '#$args+ (("start")+ (call-with-input-file #$pid-file read))+ (_ #t))))))+ (list (shepherd-service+ (provision '(postgres))+ (documentation "Run the PostgreSQL daemon.")+ (requirement '(user-processes loopback syslogd))+ (modules `((ice-9 match)+ ,@%default-modules))+ (start (action "start"))+ (stop (action "stop"))))))) (define postgresql-service-type- (service-type (name 'postgresql)- (extensions- (list (service-extension shepherd-root-service-type- postgresql-shepherd-service)- (service-extension activation-service-type- postgresql-activation)- (service-extension account-service-type- (const %postgresql-accounts))- (service-extension profile-service-type- (compose list postgresql-configuration-postgresql))))))--(define-deprecated (postgresql-service #:key (postgresql postgresql)- (port 5432)- (locale "en_US.utf8")- (config-file (postgresql-config-file))- (data-directory "/var/lib/postgresql/data")- (extension-packages '()))- postgresql-service-type- "Return a service that runs @var{postgresql}, the PostgreSQL database server.--The PostgreSQL daemon loads its runtime configuration from @var{config-file}-and stores the database cluster in @var{data-directory}."- (service postgresql-service-type- (postgresql-configuration- (postgresql postgresql)- (port port)- (locale locale)- (config-file config-file)- (data-directory data-directory)- (extension-packages extension-packages))))+ (service-type+ (name 'postgresql)+ (extensions+ (list (service-extension shepherd-root-service-type+ postgresql-shepherd-service)+ (service-extension activation-service-type+ postgresql-activation)+ (service-extension account-service-type+ (const %postgresql-accounts))+ (service-extension+ profile-service-type+ (compose list postgresql-configuration-postgresql)))))) ;;;diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scmindex 31d5ae4c6a..499ab8c9d1 100644--- a/gnu/tests/databases.scm+++ b/gnu/tests/databases.scm@@ -24,6 +24,7 @@ #:use-module (gnu system shadow) #:use-module (gnu system vm) #:use-module (gnu services)+ #:use-module (gnu services base) #:use-module (gnu services databases) #:use-module (gnu services networking) #:use-module (gnu packages databases)@@ -214,11 +215,21 @@ ;;; The PostgreSQL service. ;;; +(define %postgresql-log-directory+ "/var/log/postgresql")+ (define %postgresql-os (simple-operating-system (service postgresql-service-type (postgresql-configuration- (postgresql postgresql-10)))))+ (postgresql postgresql-10)+ (log-destination %postgresql-log-directory)+ (extra-config+ '(("session_preload_libraries" "auto_explain")+ ("random_page_cost" 2)+ ("auto_explain.log_min_duration" "100 ms")+ ("work_mem" "500 MB")+ ("debug_print_plan" #t))))))) (define (run-postgresql-test) "Run tests in %POSTGRESQL-OS."@@ -254,6 +265,23 @@ (start-service 'postgres)) marionette)) + (test-assert "log-file"+ (marionette-eval+ '(begin+ (use-modules (ice-9 ftw)+ (ice-9 match))+ (current-output-port+ (open-file "/dev/console" "w0"))+ (let ((server-log-file+ (string-append #$%postgresql-log-directory+ "/pg_ctl.log")))+ (and (file-exists? server-log-file)+ (display+ (call-with-input-file server-log-file+ get-string-all)))+ #t))+ marionette))+ (test-end) (exit (= (test-runner-fail-count (test-runner-current)) 0))))) diff --git a/gnu/tests/guix.scm b/gnu/tests/guix.scmindex af7d8f0b21..4446c4e36b 100644--- a/gnu/tests/guix.scm+++ b/gnu/tests/guix.scm@@ -157,14 +157,12 @@ (service postgresql-service-type (postgresql-configuration (postgresql postgresql-10)- (config-file- (postgresql-config-file- (hba-file- (plain-file "pg_hba.conf"- "+ (hba-file+ (plain-file "pg_hba.conf"+ " local all all trust host all all 127.0.0.1/32 trust-host all all ::1/128 trust"))))))+host all all ::1/128 trust")))) (service guix-data-service-type (guix-data-service-configuration (host "0.0.0.0")))-- 2.29.2
C
C
Christopher Baines wrote on 14 Jan 22:56 +0100
(name . Mathieu Othacehe)(address . othacehe@gnu.org)(address . 45860@debbugs.gnu.org)
87a6tb9o84.fsf@cbaines.net
Mathieu Othacehe <othacehe@gnu.org> writes:
Toggle quote (43 lines)> Hello,>> Here's a patch to improve PostgreSQL service. It merges> <postgresql-configuration> and <postgresql-config-file> records. It also> sanitises parameters conversion and logging.>> Thanks,>> Mathieu> From 87703b749631acd8ddc2b9eeb36a5be7189a019b Mon Sep 17 00:00:00 2001> From: Mathieu Othacehe <othacehe@gnu.org>> Date: Thu, 14 Jan 2021 14:13:30 +0100> Subject: [PATCH] Improve PostgreSQL service.>> Merge <postgresql-configuration> and <postgresql-config-file> records,> sanitize parameters convertion and logging.>> * gnu/services/databases.scm (postgresql-config-file,> postgresql-config-file?, postgresql-config-file-log-destination,> postgresql-config-file-hba-file, postgresql-config-file-ident-file,> postgresql-config-file-extra-config, postgresql-configuration): Remove them.> (postgresql-configuration-log-destination,> postgresql-configuration-hba-file,> postgresql-configuration-ident-file,> postgresql-configuration-socket-directory,> postgresql-configuration-extra-config,> postgresql-configuration-extension-packages): New exported procedures.> (<postgresql-config-file>): Merge it with ...> (<postgresql-configuration>): ... this record, and add a "socket-directory"> field.> (postgresql-config-file-compiler): Replace it with ...> (postgresql-config-file): ... this procedure.> (postgresql-activation): Use "match-record" instead of "match". Create the> "socket-directory" if needed.> (postgresql-shepherd-service): Use "match-record" intead of "match". Pass the> "log-destination" argument to "pg_ctl" if needed.> (postgresql-service): Remove it.> * gnu/tests/databases.scm (%postgresql-log-directory): New variable.> (%postgresql-os): Pass "log-destination" and "extra-config" fields.> (log-file): New test case.> * gnu/tests/guix.scm (%guix-data-service-os): Adapt accordingly.> * doc/guix.texi (Database Services): Ditto.
I haven't read through these changes in detail, but the mixing of therecord describing the config file, and the record for configuring theservice introduces the limitation that you can no longer specify anylowerable object (like a file) or something like a string to use aconfig file outside of the store. Did you have a reason for mixing therecords together?
-----BEGIN PGP SIGNATURE-----
iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAmAAvhtfFIAAAAAALgAoaXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNFODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2JhaW5lcy5uZXQACgkQXiijOwuE9XcdsA/+LgX1i2Ue74w12mYSxTEnfsArIY5c1M2q4fiTsNKPYyAjl5LOS8xYGguEC2dyXxjdJxhuTmJAUTaaglf8eewwAsAavipbPrNwJ9rGITtlb2uSVaxox2IUpF1KaVFLyGKBxvCiOe6Th2zV2lP1cSTjRHPgjAmd3+gQEPvL9w7IG5RXJUCMcJ6TuHZTUYpvO2OvBouEZT8CU7Gj1UGBkj9z+mx0e/7QPyBRXV4MrBhrJg8nSRt8VoZBWrFnVhlhHuvJZ6zimSctgsC18p8sL+1qeIdHtR8l54YP7wC5ae4wdWlAGjG9A353E0m22rhzvoph654EUOldLmtWHo4uQcNwgi2NqZEPLAtkoFD9LJNxL2Usu1fFxBuRSygiHH4qpS3uy6hFpFwDXLhu1Tgv1F1rslx/AsUOn5f22AwgBomzjWXGKPymuBRK7p07OIqERXM9Cx37e+9ipKyJg1qW68IZFvFACvkYsy1vdN+2oPAwAjOClUjoLdVFRMsRXbJiu9sEPB0iwGRqTIhm9MeZkDnjpfyjp4dbnjku/PEOftgw0c8DP3iXt99D7XRaAFHzlMs99JmklB0eW0xU3t+jcc5JmH5Q8tVLY7Q0q1fuHZpKI/xQw/GWUD3NGhpdci+viLJuww4wlUTDFZr0q4zRkQ5KK0+0mxYNdI8T1mUeEMh1NJc==2m4f-----END PGP SIGNATURE-----
M
M
Mathieu Othacehe wrote on 15 Jan 09:56 +0100
(name . Christopher Baines)(address . mail@cbaines.net)(address . 45860@debbugs.gnu.org)
87v9byo9x2.fsf@gnu.org
Hello Chris,
Toggle quote (7 lines)> I haven't read through these changes in detail, but the mixing of the> record describing the config file, and the record for configuring the> service introduces the limitation that you can no longer specify any> lowerable object (like a file) or something like a string to use a> config file outside of the store. Did you have a reason for mixing the> records together?
I must admit I overlooked that possibility. The reason for merging therecords is that the "log-destination" is now needed both to enable"pg_ctl" logging in "postgresql-shepherd-service" and in"postgresql-config-file" to be written in PostgreSQL configuration.
Plus having a record called <postgresql-configuration> that does notcontain some of the configuration field feels weird.
Is passing a lowerable config file a use case of yours? In that case Icould still add a "raw-config" field to override the configuration filecreation.
Thanks,
Mathieu
C
C
Christopher Baines wrote on 16 Jan 12:44 +0100
(name . Mathieu Othacehe)(address . othacehe@gnu.org)(address . 45860@debbugs.gnu.org)
87v9bx85ss.fsf@cbaines.net
Mathieu Othacehe <othacehe@gnu.org> writes:
Toggle quote (21 lines)> Hello Chris,>>> I haven't read through these changes in detail, but the mixing of the>> record describing the config file, and the record for configuring the>> service introduces the limitation that you can no longer specify any>> lowerable object (like a file) or something like a string to use a>> config file outside of the store. Did you have a reason for mixing the>> records together?>> I must admit I overlooked that possibility. The reason for merging the> records is that the "log-destination" is now needed both to enable> "pg_ctl" logging in "postgresql-shepherd-service" and in> "postgresql-config-file" to be written in PostgreSQL configuration.>> Plus having a record called <postgresql-configuration> that does not> contain some of the configuration field feels weird.>> Is passing a lowerable config file a use case of yours? In that case I> could still add a "raw-config" field to override the configuration file> creation.
It's not, however given I'm able to make changes to the servicedefinition, that's what I generally do when I have a problem with it,rather than sidestepping the Guix configuration layer. It's hard to tellif anyone is doing that or not.
This pattern of using a record with a gexp-compiler is used for quite afew services now, but mostly because I've implemented quite a fewservices (I think there's one case where someone else did similarly).
I get that there's some value in trying to help users by creating therelevant directory for logs, but I'm not sure it requires all thesechanges.
I'm also unsure about using the same names for configuration parameters,but picking different semantics. log-destination [1] can be a list(comma separated string), which I reasonably could be "stderr,syslog" or'(stderr syslog) in the Guix configuration (just as an example), andwith the service changes proposed here the string value would mean thatlog_directory gets set to "stderr,syslog" which seems wrong.
1: https://www.postgresql.org/docs/13/runtime-config-logging.html#GUC-LOG-DESTINATION
-----BEGIN PGP SIGNATURE-----
iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAmAC0aNfFIAAAAAALgAoaXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNFODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2JhaW5lcy5uZXQACgkQXiijOwuE9XegJQ/6Ar2RV8Pc7iHq1LqnxdB6oyEky9Ud2U0/jJMWC30pFBjkKpnDLZUyTfq8rmyaqSPKAWWsSN3iK4tFeYno4AHLnY36ZthzMJw6IOyPgHl0pFD2iExxaLp1AZ/h4My7T1J9rIuevPvk4LIPWxvLu3dYyCjH2ai+DcOmqzTuF2uMDageHtRFad0HkSvd3nv9JwzhH4yzvL9PC8OzlowYdwcZq72ghtj49SUs44dr3GLvbDZ8ZNltknui3Au10Lh8wB9LB52v5K89tHFq6hb5C9IocHDvjQ9gpdPhH11HCzIifDr712akpU9eps+mbmzvyVhV5/voA3VlC+AMHhxVa8O4owaJeZFENmL7Mow96/Vl72E/OFxH80EpcF7S9uK7no6hWz6HL64InBjBZuNZUwcgRq7zB8Rd4BeBY5rLQZLd/7uQ99pgo3Asds0Z0MmtH8FKr3hLJkPl2WgK9tUW5xPIq3cuwN1Lowr+SQ62mbxkpbJKizkc13BlYjZ2kXZDIrpbV0V/aQCVB3zSQAJEBBf/MteSF+z93RBFLWwd/60kGBSlpq4RMOU6qvyMFloCdZacJ3qAR5ENF7gvFa/h2jplxzuxkUnouUp+I6+dPpFgMPqI3Psh/0BTo3c5O8urWb813dra0eHg0g+8eG0lsda3HRPUjEH8wKNxXZKXXLWqkw8==ZKyB-----END PGP SIGNATURE-----
M
M
Mathieu Othacehe wrote on 18 Jan 11:16 +0100
[PATCH v2 0/5] services: postgresql: Improve service.
(address . 45860@debbugs.gnu.org)
20210118101628.202607-1-othacehe@gnu.org
Hello,
Here's a v2 of the patchset. Following Chris advises, I did not merge the twoconfiguration records. I also break the patch into four smaller patches.
I also added a 'postgresql-role-service-type' that allows to create databaseroles in a declarative fashion.
Thanks,
Mathieu
Mathieu Othacehe (5): services: postgresql: Use Guile datatypes. services: postgresql: Add socket directory support. services: postgresql: Add log directory support. services: postgresql: Wrap long lines. services: postgresql: Add postgresql-role-service-type.
doc/guix.texi | 90 +++++++++++++- gnu/services/databases.scm | 233 +++++++++++++++++++++++++++++-------- gnu/tests/databases.scm | 72 +++++++++++- 3 files changed, 342 insertions(+), 53 deletions(-)
-- 2.29.2
M
M
Mathieu Othacehe wrote on 18 Jan 11:16 +0100
[PATCH v2 1/5] services: postgresql: Use Guile datatypes.
(address . 45860@debbugs.gnu.org)
20210118101628.202607-2-othacehe@gnu.org
* gnu/services/databases.scm (postgresql-config-file-compiler): Support Guiledatatypes in the "extra-config" field.* gnu/tests/databases.scm (%postgresql-os): Test it.* doc/guix.texi (Database Services): Document it.--- doc/guix.texi | 18 ++++++++++++------ gnu/services/databases.scm | 38 ++++++++++++++++++++++---------------- gnu/tests/databases.scm | 10 +++++++++- 3 files changed, 43 insertions(+), 23 deletions(-)
Toggle diff (108 lines)diff --git a/doc/guix.texi b/doc/guix.texiindex dc41fe9aea..3ec5e3be15 100644--- a/doc/guix.texi+++ b/doc/guix.texi@@ -19382,12 +19382,12 @@ local all all trust host all all 127.0.0.1/32 md5 host all all ::1/128 md5")) (extra-config- '(("session_preload_libraries" "'auto_explain'")- ("random_page_cost" "2")- ("auto_explain.log_min_duration" "'100ms'")- ("work_mem" "'500MB'")- ("logging_collector" "on")- ("log_directory" "'/var/log/postgresql'")))))))+ '(("session_preload_libraries" "auto_explain")+ ("random_page_cost" 2)+ ("auto_explain.log_min_duration" "100 ms")+ ("work_mem" "500 MB")+ ("logging_collector" #t)+ ("log_directory" "/var/log/postgresql"))))))) @end lisp @table @asis@@ -19407,6 +19407,12 @@ List of additional keys and values to include in the PostgreSQL config file. Each entry in the list should be a list where the first element is the key, and the remaining elements are the values. +The values can be numbers, booleans or strings and will be mapped to+PostgreSQL parameters types @code{Boolean}, @code{String},+@code{Numeric}, @code{Numeric with Unit} and @code{Enumerated} described+@uref{https://www.postgresql.org/docs/current/config-setting.html,+here}.+ @end table @end deftp diff --git a/gnu/services/databases.scm b/gnu/services/databases.scmindex d2dc5f0da8..bb0e40632e 100644--- a/gnu/services/databases.scm+++ b/gnu/services/databases.scm@@ -115,22 +115,28 @@ host all all ::1/128 md5")) (match file (($ <postgresql-config-file> log-destination hba-file ident-file extra-config)- (define (single-quote string)- (if string- (list "'" string "'")- '()))-- (define contents- (append-map- (match-lambda- ((key) '())- ((key . #f) '())- ((key values ...) `(,key " = " ,@values "\n")))-- `(("log_destination" ,@(single-quote log-destination))- ("hba_file" ,@(single-quote hba-file))- ("ident_file" ,@(single-quote ident-file))- ,@extra-config)))+ ;; See: https://www.postgresql.org/docs/current/config-setting.html.+ (define (format-value value)+ (cond+ ((boolean? value)+ (list (if value "on" "off")))+ ((number? value)+ (list (number->string value)))+ (else+ (list "'" value "'"))))++ (define contents+ (append-map+ (match-lambda+ ((key) '())+ ((key . #f) '())+ ((key values ...)+ `(,key " = " ,@(append-map format-value values) "\n")))++ `(("log_destination" ,log-destination)+ ("hba_file" ,hba-file)+ ("ident_file" ,ident-file)+ ,@extra-config))) (gexp->derivation "postgresql.conf"diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scmindex 31d5ae4c6a..7338007919 100644--- a/gnu/tests/databases.scm+++ b/gnu/tests/databases.scm@@ -218,7 +218,15 @@ (simple-operating-system (service postgresql-service-type (postgresql-configuration- (postgresql postgresql-10)))))+ (postgresql postgresql-10)+ (config-file+ (postgresql-config-file+ (extra-config+ '(("session_preload_libraries" "auto_explain")+ ("random_page_cost" 2)+ ("auto_explain.log_min_duration" "100 ms")+ ("work_mem" "500 MB")+ ("debug_print_plan" #t))))))))) (define (run-postgresql-test) "Run tests in %POSTGRESQL-OS."-- 2.29.2
M
M
Mathieu Othacehe wrote on 18 Jan 11:16 +0100
[PATCH v2 2/5] services: postgresql: Add socket directory support.
(address . 45860@debbugs.gnu.org)
20210118101628.202607-3-othacehe@gnu.org
* gnu/services/databases.scm (postgresql-config-file-socket-directory): Newprocedure.(<postgresql-config-file>)[socket-directory]: New field.(postgresql-config-file-compiler): Honor it.(postgresql-activation): Create the socket directory if needed.* doc/guix.texi (Database Services): Document it.--- doc/guix.texi | 6 ++++++ gnu/services/databases.scm | 32 +++++++++++++++++++++++--------- 2 files changed, 29 insertions(+), 9 deletions(-)
Toggle diff (88 lines)diff --git a/doc/guix.texi b/doc/guix.texiindex 3ec5e3be15..46039d26d0 100644--- a/doc/guix.texi+++ b/doc/guix.texi@@ -19402,6 +19402,12 @@ configuration. @item @code{ident-file} (default: @code{%default-postgres-ident}) Filename or G-expression for the user name mapping configuration. +@item @code{socket-directory} (default: @code{"/var/lib/postgresql"})+Specifies the directory of the Unix-domain socket(s) on which PostgreSQL+is to listen for connections from client applications. If set to+@code{#false} PostgreSQL does not listen on any Unix-domain sockets, in+which case only TCP/IP sockets can be used to connect to the server.+ @item @code{extra-config} (default: @code{'()}) List of additional keys and values to include in the PostgreSQL config file. Each entry in the list should be a list where the first elementdiff --git a/gnu/services/databases.scm b/gnu/services/databases.scmindex bb0e40632e..83dee52cf3 100644--- a/gnu/services/databases.scm+++ b/gnu/services/databases.scm@@ -43,6 +43,7 @@ postgresql-config-file-log-destination postgresql-config-file-hba-file postgresql-config-file-ident-file+ postgresql-config-file-socket-directory postgresql-config-file-extra-config postgresql-configuration@@ -101,20 +102,23 @@ host all all ::1/128 md5")) (define-record-type* <postgresql-config-file> postgresql-config-file make-postgresql-config-file postgresql-config-file?- (log-destination postgresql-config-file-log-destination- (default "syslog"))- (hba-file postgresql-config-file-hba-file- (default %default-postgres-hba))- (ident-file postgresql-config-file-ident-file- (default %default-postgres-ident))- (extra-config postgresql-config-file-extra-config- (default '())))+ (log-destination postgresql-config-file-log-destination+ (default "syslog"))+ (hba-file postgresql-config-file-hba-file+ (default %default-postgres-hba))+ (ident-file postgresql-config-file-ident-file+ (default %default-postgres-ident))+ (socket-directory postgresql-config-file-socket-directory+ (default "/var/run/postgresql"))+ (extra-config postgresql-config-file-extra-config+ (default '()))) (define-gexp-compiler (postgresql-config-file-compiler (file <postgresql-config-file>) system target) (match file (($ <postgresql-config-file> log-destination hba-file- ident-file extra-config)+ ident-file socket-directory+ extra-config) ;; See: https://www.postgresql.org/docs/current/config-setting.html. (define (format-value value) (cond@@ -136,6 +140,9 @@ host all all ::1/128 md5")) `(("log_destination" ,log-destination) ("hba_file" ,hba-file) ("ident_file" ,ident-file)+ ,@(if socket-directory+ `(("unix_socket_directories" ,socket-directory))+ '()) ,@extra-config))) (gexp->derivation@@ -211,6 +218,13 @@ host all all ::1/128 md5")) (mkdir-p #$data-directory) (chown #$data-directory (passwd:uid user) (passwd:gid user)) + ;; Create the socket directory.+ (let ((socket-directory+ #$(postgresql-config-file-socket-directory config-file)))+ (when (string? socket-directory)+ (mkdir-p socket-directory)+ (chown socket-directory (passwd:uid user) (passwd:gid user))))+ ;; Drop privileges and init state directory in a new ;; process. Wait for it to finish before proceeding. (match (primitive-fork)-- 2.29.2
M
M
Mathieu Othacehe wrote on 18 Jan 11:16 +0100
[PATCH v2 4/5] services: postgresql: Wrap long lines.
(address . 45860@debbugs.gnu.org)
20210118101628.202607-5-othacehe@gnu.org
* gnu/services/databases.scm: Wrap long lines, no functional change.--- gnu/services/databases.scm | 32 +++++++++++++++++++------------- 1 file changed, 19 insertions(+), 13 deletions(-)
Toggle diff (59 lines)diff --git a/gnu/services/databases.scm b/gnu/services/databases.scmindex c387a7da6c..0d60616156 100644--- a/gnu/services/databases.scm+++ b/gnu/services/databases.scm@@ -194,7 +194,9 @@ host all all ::1/128 md5")) #:builder (begin (use-modules (guix build utils) (guix build union) (srfi srfi-26))- (union-build (assoc-ref %outputs "out") (map (lambda (input) (cdr input)) %build-inputs))+ (union-build (assoc-ref %outputs "out")+ (map (lambda (input) (cdr input))+ %build-inputs)) #t))) (inputs `(("postgresql" ,postgresql)@@ -306,25 +308,29 @@ host all all ::1/128 md5")) (stop (action "stop")))))))) (define postgresql-service-type- (service-type (name 'postgresql)- (extensions- (list (service-extension shepherd-root-service-type- postgresql-shepherd-service)- (service-extension activation-service-type- postgresql-activation)- (service-extension account-service-type- (const %postgresql-accounts))- (service-extension profile-service-type- (compose list postgresql-configuration-postgresql))))))+ (service-type+ (name 'postgresql)+ (extensions+ (list (service-extension shepherd-root-service-type+ postgresql-shepherd-service)+ (service-extension activation-service-type+ postgresql-activation)+ (service-extension account-service-type+ (const %postgresql-accounts))+ (service-extension+ profile-service-type+ (compose list postgresql-configuration-postgresql)))))) (define-deprecated (postgresql-service #:key (postgresql postgresql) (port 5432) (locale "en_US.utf8") (config-file (postgresql-config-file))- (data-directory "/var/lib/postgresql/data")+ (data-directory+ "/var/lib/postgresql/data") (extension-packages '())) postgresql-service-type- "Return a service that runs @var{postgresql}, the PostgreSQL database server.+ "Return a service that runs @var{postgresql}, the PostgreSQL database+server. The PostgreSQL daemon loads its runtime configuration from @var{config-file} and stores the database cluster in @var{data-directory}."-- 2.29.2
M
M
Mathieu Othacehe wrote on 18 Jan 11:16 +0100
[PATCH v2 3/5] services: postgresql: Add log directory support.
(address . 45860@debbugs.gnu.org)
20210118101628.202607-4-othacehe@gnu.org
* gnu/services/databases.scm (postgresql-configuration-log-directory): Newprocedure.(<postgresql-configuration>)[log-directory]: New field.(postgresql-activation): Create the log directory.(postgresql-shepherd-service): Honor it.* gnu/tests/databases.scm (%postgresql-log-directory): New variable.(log-file): New test case.* doc/guix.texi (Database Services): Document it.--- doc/guix.texi | 5 +++++ gnu/services/databases.scm | 36 ++++++++++++++++++++++++++++-------- gnu/tests/databases.scm | 20 ++++++++++++++++++++ 3 files changed, 53 insertions(+), 8 deletions(-)
Toggle diff (148 lines)diff --git a/doc/guix.texi b/doc/guix.texiindex 46039d26d0..22674e2804 100644--- a/doc/guix.texi+++ b/doc/guix.texi@@ -19316,6 +19316,11 @@ The configuration file to use when running PostgreSQL. The default behaviour uses the postgresql-config-file record with the default values for the fields. +@item @code{log-directory} (default: @code{"/var/log/postgresql"})+The directory where @command{pg_ctl} output will be written in a file+named @code{"pg_ctl.log"}. This file can be useful to debug PostgreSQL+configuration errors for instance.+ @item @code{data-directory} (default: @code{"/var/lib/postgresql/data"}) Directory in which to store the data. diff --git a/gnu/services/databases.scm b/gnu/services/databases.scmindex 83dee52cf3..c387a7da6c 100644--- a/gnu/services/databases.scm+++ b/gnu/services/databases.scm@@ -52,6 +52,7 @@ postgresql-configuration-port postgresql-configuration-locale postgresql-configuration-file+ postgresql-configuration-log-directory postgresql-configuration-data-directory postgresql-service@@ -164,6 +165,8 @@ host all all ::1/128 md5")) (default "en_US.utf8")) (config-file postgresql-configuration-file (default (postgresql-config-file)))+ (log-directory postgresql-configuration-log-directory+ (default "/var/log/postgresql")) (data-directory postgresql-configuration-data-directory (default "/var/lib/postgresql/data")) (extension-packages postgresql-configuration-extension-packages@@ -200,15 +203,18 @@ host all all ::1/128 md5")) (define postgresql-activation (match-lambda- (($ <postgresql-configuration> postgresql port locale config-file data-directory- extension-packages)+ (($ <postgresql-configuration> postgresql port locale config-file+ log-directory data-directory+ extension-packages) #~(begin (use-modules (guix build utils) (ice-9 match)) (let ((user (getpwnam "postgres"))- (initdb (string-append #$(final-postgresql postgresql extension-packages)- "/bin/initdb"))+ (initdb (string-append+ #$(final-postgresql postgresql+ extension-packages)+ "/bin/initdb")) (initdb-args (append (if #$locale@@ -225,6 +231,11 @@ host all all ::1/128 md5")) (mkdir-p socket-directory) (chown socket-directory (passwd:uid user) (passwd:gid user)))) + ;; Create the log directory.+ (when (string? #$log-directory)+ (mkdir-p #$log-directory)+ (chown #$log-directory (passwd:uid user) (passwd:gid user)))+ ;; Drop privileges and init state directory in a new ;; process. Wait for it to finish before proceeding. (match (primitive-fork)@@ -247,8 +258,9 @@ host all all ::1/128 md5")) (define postgresql-shepherd-service (match-lambda- (($ <postgresql-configuration> postgresql port locale config-file data-directory- extension-packages)+ (($ <postgresql-configuration> postgresql port locale config-file+ log-directory data-directory+ extension-packages) (let* ((pg_ctl-wrapper ;; Wrapper script that switches to the 'postgres' user before ;; launching daemon.@@ -260,13 +272,21 @@ host all all ::1/128 md5")) (match (command-line) ((_ mode) (let ((user (getpwnam "postgres"))- (pg_ctl #$(file-append (final-postgresql postgresql extension-packages)+ (pg_ctl #$(file-append+ (final-postgresql postgresql+ extension-packages) "/bin/pg_ctl")) (options (format #f "--config-file=~a -p ~d" #$config-file #$port))) (setgid (passwd:gid user)) (setuid (passwd:uid user))- (execl pg_ctl pg_ctl "-D" #$data-directory "-o" options+ (execl pg_ctl pg_ctl "-D" #$data-directory+ #$@(if (string? log-directory)+ (list "-l"+ (string-append log-directory+ "/pg_ctl.log"))+ '())+ "-o" options mode))))))) (pid-file (in-vicinity data-directory "postmaster.pid")) (action (lambda argsdiff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scmindex 7338007919..d881a8c3ee 100644--- a/gnu/tests/databases.scm+++ b/gnu/tests/databases.scm@@ -214,6 +214,9 @@ ;;; The PostgreSQL service. ;;; +(define %postgresql-log-directory+ "/var/log/postgresql")+ (define %postgresql-os (simple-operating-system (service postgresql-service-type@@ -262,6 +265,23 @@ (start-service 'postgres)) marionette)) + (test-assert "log-file"+ (marionette-eval+ '(begin+ (use-modules (ice-9 ftw)+ (ice-9 match))+ (current-output-port+ (open-file "/dev/console" "w0"))+ (let ((server-log-file+ (string-append #$%postgresql-log-directory+ "/pg_ctl.log")))+ (and (file-exists? server-log-file)+ (display+ (call-with-input-file server-log-file+ get-string-all)))+ #t))+ marionette))+ (test-end) (exit (= (test-runner-fail-count (test-runner-current)) 0))))) -- 2.29.2
M
M
Mathieu Othacehe wrote on 18 Jan 11:16 +0100
[PATCH v2 5/5] services: postgresql: Add postgresql-role-service-type.
(address . 45860@debbugs.gnu.org)
20210118101628.202607-6-othacehe@gnu.org
* 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(-)
Toggle diff (256 lines)diff --git a/doc/guix.texi b/doc/guix.texiindex 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-typediff --git a/gnu/services/databases.scm b/gnu/services/databases.scmindex 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>+ 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>+ 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 <postgresql-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 <postgresql-role-configuration>+ (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 <postgresql-role-configuration>+ (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.")))+ ;;; ;;; Memcacheddiff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scmindex 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
C
C
Christopher Baines wrote on 27 Jan 09:35 +0100
Re: [PATCH v2 2/5] services: postgresql: Add socket directory support.
(name . Mathieu Othacehe)(address . othacehe@gnu.org)(address . 45860@debbugs.gnu.org)
87h7n2hj5g.fsf@cbaines.net
Mathieu Othacehe <othacehe@gnu.org> writes:
Toggle quote (11 lines)> * gnu/services/databases.scm (postgresql-config-file-socket-directory): New> procedure.> (<postgresql-config-file>)[socket-directory]: New field.> (postgresql-config-file-compiler): Honor it.> (postgresql-activation): Create the socket directory if needed.> * doc/guix.texi (Database Services): Document it.> ---> doc/guix.texi | 6 ++++++> gnu/services/databases.scm | 32 +++++++++++++++++++++++---------> 2 files changed, 29 insertions(+), 9 deletions(-)
This looks good, I did spot some system tests fail though, and I'mguessing it's this patch in the series that's the reason [1]
1: https://data.guix-patches.cbaines.net/compare/system-test-derivations?base_commit=2d9c6542c804eb2ef3d8934e1e3ab8b24e9bbafb&target_commit=f8a367281daaad998059dd657818c49ed5beac30&locale=en_US.UTF-8
Looking at the patchwork system test log, I'm guessing it's using thedefault socket directory on the client side, which is now not working.
I think patching the postgresql package to adjust [2] accordingly wouldbe useful to keep PostgreSQL connecting with the default socketdirectory.
2:#define DEFAULT_PGSOCKET_DIR "/tmp"
-----BEGIN PGP SIGNATURE-----
iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAmARJctfFIAAAAAALgAoaXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNFODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2JhaW5lcy5uZXQACgkQXiijOwuE9XfHyg/9EaXBSE9joxJF5POlV0YDd5R8giq9Dn40gIjKLd6FZVHSDXJL5fFXGDDzHSzxWgASLJdj9x4KUDirR83HhadAr+pGm75MKwtIJlj7piwa+jSjF4dkRbbYoazdEaWe7Ob8zfgfS/UeLPwegZ0jysl2WGLftOiCKetRhQpoOukCalz5vMA+SdyQp+rdBKabqoQFrkUNFwkXei4yhZpJhDfZnmBj9DBBs7m5k7n0Ts0TGakQUqaJ37Zlyx5BF7yt05IeVXse5onhWDHRfNCdUs3zVGdw2xU/lHoN0bOCxamWBJs6AKKJnR00ZaOVDmayA3oOhmytM7uz+jxnZRLnCc4BBWcLP7QgnucCmlOlT+Bc3wlRWiFV5AJd7IAwVLe8Pw9QJUoxmLosMYEluEkE0z6zXgVlViwTkVPSs3A7pIM5KnpTace5rToFxbChuy8So2xoliOStsFJu/2zii5SezuVMC6Hw48qDNnq3st22dNqQ4oPxtQ47YwGyrI50aec2qv+LRlj+krNTy4V1SneqY8i9J5ivB7x1gd7nDgzuk0qPlBKCSce66lv01i+rd1rlp78T+dYFReQJBttAClBXjrPg21gonM/A0n8s3NjNG8bvXu4izNL6M7aBYrFdWDurh38wJ2nlCNCnSMmWBRgsIwCJkt7Iaz5ZbMt+ocM2L18c0M==cN9e-----END PGP SIGNATURE-----
C
C
Christopher Baines wrote on 27 Jan 09:48 +0100
Re: [PATCH v2 5/5] services: postgresql: Add postgresql-role-service-type.
(name . Mathieu Othacehe)(address . othacehe@gnu.org)(address . 45860@debbugs.gnu.org)
87eei6hiiy.fsf@cbaines.net
Mathieu Othacehe <othacehe@gnu.org> writes:
Toggle quote (61 lines)> * 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}.
Why only support these two permissions/options? Accepting strings orsymbols, and then just converting to an upper case string would allowall the permission options to be specified.
Toggle quote (119 lines)> +@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>> + 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>> + 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 <postgresql-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 <postgresql-role-configuration>> + (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.")))))
I'm guessing this service will fail if it's run twice, as therole/database will already exist?
-----BEGIN PGP SIGNATURE-----
iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAmARKPZfFIAAAAAALgAoaXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNFODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2JhaW5lcy5uZXQACgkQXiijOwuE9XfAeA//cE5M76YBC2t0L8+Yzxq/9zqxVJZNaWr2Xp5W1HxUuc5HKw/yxb4nsctOU9RByfHIJ4SpITYr/+v9Gb3pxRgXJODKWKegnqoU/PnTPBauhpW7x7QI2gLRtnhzywwqVjQ0DucGfWXO0uVMh/uxLdcI/x0Zi9Rp4FWZnXIyj/upN9+wZE6GNSsoxwAqp0Br4bKTuYOw9NycHGmi6hNpBd9YgTGjdFI428qY5RJ5N/tmvvvoFhyx9EUjrG75gYhKw9N6Rfv9hwWZcOJjCGsHF8cnLCvXm+543R3q5+15pvLVJZA8EzNIKY0am3yOF/9Cz045oFw+4xs6PDk1PsEBF/5CTCX0h/aAw/4RpEqFAa1PT11JuoXn32iT4ofVKX+xxSIXGiinSvFhSM7VSE3QU3I9ulZ2VWilAcHZpOnCK3Z0XGc2niy7qoQiLs1CxSdyBMUwdY5UdYjdZOEun96DLsDiB4oSGMUNTLlXmwvGu2Z9af/nUB2IfEhXBWpBMUp45bqcJwP8CSRn/yQh7L+JOLHfPAXyymwZZpxAbNLWdXWJztTEpy3Cx7ygWTAAoGG4daw5n5z/1GhJ2NDPV0fbNndTKvZB9aGQ7qJwgw0IbNbbDhltN+/eWhjKwUgtqzKh8cVxtIiu69k+kYtOGKGq3le/IbjJgCM33eWKBjJL9rQ/vxI==TZDd-----END PGP SIGNATURE-----
M
M
Mathieu Othacehe wrote on 28 Jan 13:04 +0100
Re: [PATCH v2 2/5] services: postgresql: Add socket directory support.
(name . Christopher Baines)(address . mail@cbaines.net)(address . 45860@debbugs.gnu.org)
87pn1p8dyd.fsf@gnu.org
Hello Chris,
Toggle quote (4 lines)> I think patching the postgresql package to adjust [2] accordingly would> be useful to keep PostgreSQL connecting with the default socket> directory.
Thanks for having a look! I fixed the tests that were broken by thesocket directory introduction.
I also updated the postgresql default socket directory on core-updatesas you suggested.
Thanks,
Mathieu
M
M
Mathieu Othacehe wrote on 28 Jan 13:05 +0100
Re: [PATCH v2 5/5] services: postgresql: Add postgresql-role-service-type.
(name . Christopher Baines)(address . mail@cbaines.net)(address . 45860-done@debbugs.gnu.org)
87lfcd8dwk.fsf@gnu.org
Hey,
Toggle quote (4 lines)> Why only support these two permissions/options? Accepting strings or> symbols, and then just converting to an upper case string would allow> all the permission options to be specified.
Sure, fixed.
Toggle quote (3 lines)> I'm guessing this service will fail if it's run twice, as the> role/database will already exist?
Yes, I added a check for already existing roles before pushing.
Thanks,
Mathieu
Closed
?