From a38d0b0137400b4d54bab5181538fd7eeeefa49a Mon Sep 17 00:00:00 2001 From: Mathieu Othacehe Date: Mon, 18 Jan 2021 10:25:18 +0100 Subject: services: postgresql: Use Guile datatypes. * gnu/services/databases.scm (postgresql-config-file-compiler): Support Guile datatypes in the "extra-config" field. * gnu/tests/databases.scm (%postgresql-os): Test it. * doc/guix.texi (Database Services): Document it. --- gnu/tests/databases.scm | 10 +++++++++- 1 file changed, 9 insertions(+), 1 deletion(-) (limited to 'gnu/tests/databases.scm') diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index 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." -- cgit v1.2.3 From fe4b8823505c3271fc3eacaa93c30a5cec078739 Mon Sep 17 00:00:00 2001 From: Mathieu Othacehe Date: Mon, 18 Jan 2021 10:52:25 +0100 Subject: services: postgresql: Add log directory support. * gnu/services/databases.scm (postgresql-configuration-log-directory): New procedure. ()[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(-) (limited to 'gnu/tests/databases.scm') diff --git a/doc/guix.texi b/doc/guix.texi index b01bbf0d96..f4fbe6b0e6 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -19344,6 +19344,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.scm index 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 port locale config-file data-directory - extension-packages) + (($ 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 port locale config-file data-directory - extension-packages) + (($ 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 args diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index 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))))) -- cgit v1.2.3 From ec145a2ff9f6a69234ddd2f3656ac324b53d30d3 Mon Sep 17 00:00:00 2001 From: Mathieu Othacehe Date: Mon, 18 Jan 2021 11:10:28 +0100 Subject: services: postgresql: Add postgresql-role-service-type. * 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 | 62 +++++++++++++++++++++++++++ gnu/services/databases.scm | 102 +++++++++++++++++++++++++++++++++++++++++++++ gnu/tests/databases.scm | 44 ++++++++++++++++++- 3 files changed, 207 insertions(+), 1 deletion(-) (limited to 'gnu/tests/databases.scm') diff --git a/doc/guix.texi b/doc/guix.texi index f4fbe6b0e6..6ea782fd23 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -19455,6 +19455,68 @@ 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{bypassrls}, +@code{createdb}, @code{createrole}, @code{login}, @code{replication} and +@code{superuser}. + +@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..c11898693f 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,96 @@ 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 '(bypassrls createdb createrole login replication superuser))) + (string-join (filter-map (lambda (permission) + (and (member permission dict) + (string-upcase + (symbol->string permission)))) + permissions) + " "))) + + (define (roles->queries roles) + (apply mixed-text-file "queries" + (append-map + (lambda (role) + (match-record role + (name permissions create-database?) + `("SELECT NOT(EXISTS(SELECT 1 FROM pg_catalog.pg_roles WHERE \ +rolname = '" ,name "')) as not_exists;\n" +"\\gset\n" +"\\if :not_exists\n" +"CREATE ROLE " ,name +" WITH " ,(format-permissions permissions) +";\n" +,@(if create-database? + `("CREATE DATABASE " ,name + " OWNER " ,name ";\n") + '()) +"\\endif\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))))) -- cgit v1.2.3