From unknown Sun Jul 27 00:26:05 2025 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Mailer: MIME-tools 5.509 (Entity 5.509) Content-Type: text/plain; charset=utf-8 From: bug#73196 <73196@debbugs.gnu.org> To: bug#73196 <73196@debbugs.gnu.org> Subject: Status: [PATCH] services: postgresql-role: Add support for password files. Reply-To: bug#73196 <73196@debbugs.gnu.org> Date: Sun, 27 Jul 2025 07:26:05 +0000 retitle 73196 [PATCH] services: postgresql-role: Add support for password f= iles. reassign 73196 guix-patches submitter 73196 Giacomo Leidi severity 73196 normal tag 73196 patch thanks From debbugs-submit-bounces@debbugs.gnu.org Thu Sep 12 07:25:27 2024 Received: (at submit) by debbugs.gnu.org; 12 Sep 2024 11:25:27 +0000 Received: from localhost ([127.0.0.1]:40286 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1sohx0-0003fA-Gq for submit@debbugs.gnu.org; Thu, 12 Sep 2024 07:25:27 -0400 Received: from lists.gnu.org ([209.51.188.17]:57052) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1sohwx-0003f1-Rc for submit@debbugs.gnu.org; Thu, 12 Sep 2024 07:25:25 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1sohwk-0007Lj-7t for guix-patches@gnu.org; Thu, 12 Sep 2024 07:25:14 -0400 Received: from confino.investici.org ([2a11:7980:1::2:0]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1sohwf-0000Jt-J9 for guix-patches@gnu.org; Thu, 12 Sep 2024 07:25:09 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1726140298; bh=yeUpy3gClHsNxgAZK2Nh4z9W+RtKQm9ma/RprEyWPwk=; h=From:To:Cc:Subject:Date:From; b=nWTCsZhQryKuvEC8LyirGzTTWtV7iHiCwvslSXJ8GfKpVIYNYCxIcADJrOTYj5B9m aO0vaAH3W/cM6CYLiRMLZh+URCcOzQGeN3e4z6U73aH9mdrpeFFb8weFFu7NVTKT9Z swGm2DmSI+aKUtYI+8Q8OjS+CflRP4OzTVR6oGWY= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4X4FTk6280z119n; Thu, 12 Sep 2024 11:24:58 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4X4FTk5Fsnz1195; Thu, 12 Sep 2024 11:24:58 +0000 (UTC) From: Giacomo Leidi To: guix-patches@gnu.org Subject: [PATCH] services: postgresql-role: Add support for password files. Date: Thu, 12 Sep 2024 13:24:23 +0200 Message-ID: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@autistici.org> X-Mailer: git-send-email 2.46.0 MIME-Version: 1.0 X-Debbugs-Cc: Florian Pelz , Ludovic Courtès , Maxim Cournoyer Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Received-SPF: pass client-ip=2a11:7980:1::2:0; envelope-from=goodoldpaul@autistici.org; helo=confino.investici.org X-Spam_score_int: -27 X-Spam_score: -2.8 X-Spam_bar: -- X-Spam_report: (-2.8 / 5.0 requ) BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, RCVD_IN_DNSWL_LOW=-0.7, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-Spam-Score: -1.4 (-) X-Debbugs-Envelope-To: submit Cc: Giacomo Leidi X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -2.4 (--) This commit adds a password-file to the postgresql-role field. It allows users to provision Postgres roles with a set password. * gnu/services/databases.scm (postgresql-role): Add password-file field; (postgresql-role-configuration): add requirement field; (postgresql-create-roles): add support for setting passwords from a file without leaking passwords to the command line; (postgresql-role-shepherd-service): add support for customizable requirements. * gnu/tests/databases.scm: Test it. * doc/guix.texi: Document it. Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585 --- doc/guix.texi | 15 +++++++++--- gnu/services/databases.scm | 47 +++++++++++++++++++++++++++++++++----- gnu/tests/databases.scm | 38 +++++++++++++++++++++++++++--- 3 files changed, 88 insertions(+), 12 deletions(-) diff --git a/doc/guix.texi b/doc/guix.texi index 981ffb8c58..8e6f1b8b2a 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -26294,9 +26294,10 @@ Database Services @lisp (service-extension postgresql-role-service-type - (const (postgresql-role - (name "alice") - (create-database? #t)))) + (const (list + (postgresql-role + (name "alice") + (create-database? #t))))) @end lisp @end defvar @@ -26319,6 +26320,10 @@ Database Services @item @code{create-database?} (default: @code{#f}) whether to create a database with the same name as the role. +@item @code{password-file} (default: @code{#f}) +A string representing the path of a file that contains the password to be set +for the role. + @item @code{encoding} (default: @code{"UTF8"}) The character set to use for storing text in the database. @@ -26347,6 +26352,10 @@ Database Services @item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) File name of the log file. +@item @code{requirement} (default: @code{'()}) (type: list-of-symbols) +Set additional Shepherd services dependencies to the provisioned +Shepherd service. + @item @code{roles} (default: @code{'()}) The initial PostgreSQL roles to create. @end table diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index fa332d7978..d23dba60e3 100644 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -9,6 +9,7 @@ ;;; Copyright © 2020, 2022 Marius Bakke ;;; Copyright © 2021 David Larsson ;;; Copyright © 2021 Aljosha Papsch +;;; Copyright © 2024 Giacomo Leidi ;;; ;;; This file is part of GNU Guix. ;;; @@ -31,6 +32,7 @@ (define-module (gnu services databases) #:use-module (gnu system shadow) #:use-module (gnu packages admin) #:use-module (gnu packages base) + #:use-module (gnu packages bash) #:use-module (gnu packages databases) #:use-module (guix build-system trivial) #:use-module (guix build union) @@ -65,11 +67,13 @@ (define-module (gnu services databases) postgresql-role postgresql-role? postgresql-role-name + postgresql-role-password-file postgresql-role-permissions postgresql-role-create-database? postgresql-role-configuration postgresql-role-configuration? postgresql-role-configuration-host + postgresql-role-configuration-requirement postgresql-role-configuration-roles postgresql-role-service-type @@ -372,6 +376,8 @@ (define-record-type* postgresql-role make-postgresql-role postgresql-role? (name postgresql-role-name) ;string + (password-file postgresql-role-password-file ;string + (default #f)) (permissions postgresql-role-permissions (default '(createdb login))) ;list (create-database? postgresql-role-create-database? ;boolean @@ -390,6 +396,8 @@ (define-record-type* postgresql-role-configuration? (host postgresql-role-configuration-host ;string (default "/var/run/postgresql")) + (requirement postgresql-role-configuration-requirement ;list-of-symbols + (default '())) (log postgresql-role-configuration-log ;string (default "/var/log/postgresql_roles.log")) (roles postgresql-role-configuration-roles @@ -407,19 +415,36 @@ (define (postgresql-create-roles config) permissions) " "))) + (define (password-value role) + (string-append "password_" (postgresql-role-name role))) + + (define (role->password-variable role) + (define file-name + (postgresql-role-password-file role)) + (if (string? file-name) + ;; This way passwords do not leak to the command line + (string-append "-v \"" (password-value role) + "=$(cat " file-name ")\"") + "")) + (define (roles->queries roles) (apply mixed-text-file "queries" (append-map (lambda (role) (match-record role (name permissions create-database? encoding collation ctype - template) + template password-file) `("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) +,(if (and (string? password-file) + (not (string-null? password-file))) + (string-append + "\nPASSWORD :'" (password-value role) "'") + "") ";\n" ,@(if create-database? `("CREATE DATABASE \"" ,name "\"" @@ -434,20 +459,30 @@ (define (postgresql-create-roles config) (let ((host (postgresql-role-configuration-host config)) (roles (postgresql-role-configuration-roles config))) - #~(let ((psql #$(file-append postgresql "/bin/psql"))) - (list psql "-a" "-h" #$host "-f" #$(roles->queries roles))))) + (program-file "run-queries" + #~(let ((bash #$(file-append bash-minimal "/bin/bash")) + (psql #$(file-append postgresql "/bin/psql"))) + (define command + (string-append + "set -e; exec " psql " -c -a -h " #$host " -f " + #$(roles->queries roles) " " + (string-join + (list + #$@(map role->password-variable roles)) + " "))) + (execlp bash bash "-c" command))))) (define (postgresql-role-shepherd-service config) (match-record config - (log) + (log requirement) (list (shepherd-service - (requirement '(postgres)) + (requirement `(postgres ,@requirement)) (provision '(postgres-roles)) (one-shot? #t) (start #~(lambda args (let ((pid (fork+exec-command - #$(postgresql-create-roles config) + (list #$(postgresql-create-roles config)) #:user "postgres" #:group "postgres" #:log-file #$log))) diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index 7c8b87942f..81484b2954 100644 --- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -142,6 +142,8 @@ (define %role-log-file (define %postgresql-os (simple-operating-system + (extra-special-file "/password" + (plain-file "password" "hello")) (service postgresql-service-type (postgresql-configuration (postgresql postgresql) @@ -158,6 +160,10 @@ (define %postgresql-os (roles (list (postgresql-role (name "root") + (create-database? #t)) + (postgresql-role + (name "alice") + (password-file "/password") (create-database? #t)))))))) (define (run-postgresql-test) @@ -230,14 +236,40 @@ (define (run-postgresql-test) (marionette-eval '(begin (use-modules (gnu services herd) + (srfi srfi-1) (ice-9 popen)) (current-output-port (open-file "/dev/console" "w0")) + (every + (lambda (role) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tA" "-c" + (string-append + "SELECT 1 FROM pg_database WHERE" + " datname='" role "'"))) + (output (get-string-all port))) + (close-pipe port) + (string-contains output "1"))) + '("root" "alice"))) + marionette)) + + (test-assert "database passwords are set" + (marionette-eval + '(begin + (use-modules (gnu services herd) + (ice-9 match) + (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (setgid (passwd:gid (getpwnam "alice"))) + (setuid (passwd:uid (getpw "alice"))) + (setenv "PGPASSWORD" "hello") (let* ((port (open-pipe* OPEN_READ - #$(file-append postgresql "/bin/psql") - "-tA" "-c" "SELECT 1 FROM pg_database WHERE - datname='root'")) + #$(file-append postgresql "/bin/psql") "-tA" "-c" + "SELECT 1 FROM pg_database WHERE datname='alice'")) (output (get-string-all port))) (close-pipe port) (string-contains output "1"))) base-commit: 590904cca15922e6474fbd3a71af9b3a45b268af -- 2.46.0 From debbugs-submit-bounces@debbugs.gnu.org Thu Sep 12 08:18:30 2024 Received: (at 73196) by debbugs.gnu.org; 12 Sep 2024 12:18:30 +0000 Received: from localhost ([127.0.0.1]:40379 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1soimM-0006Mj-2x for submit@debbugs.gnu.org; Thu, 12 Sep 2024 08:18:30 -0400 Received: from confino.investici.org ([93.190.126.19]:45331) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1soimI-0006MZ-Uw for 73196@debbugs.gnu.org; Thu, 12 Sep 2024 08:18:27 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1726143497; bh=LTdUKkxVdfuOrpU08sZgL6S37S+NnyvZ6e1nWDOtWAQ=; h=Date:To:From:Subject:From; b=efA2z35/W4ZXMJ5cIU31rBQp6UoYId+JYmzVR6/6UXXyQzOF+dgnklGyKfVQCyhte lSj8/tFInjim+F8kL6zhO2Tb4WM1jGBQeVxib28PcyN/bZ38Yd3p4TUrgl787uH2EO 9pRzr8wPlK5+2Lv8AVrNUoPTIzNmhInJW7JyPK/U= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4X4GgF1DFTz11B2 for <73196@debbugs.gnu.org>; Thu, 12 Sep 2024 12:18:17 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4X4GgF0qpzz117s for <73196@debbugs.gnu.org>; Thu, 12 Sep 2024 12:18:17 +0000 (UTC) Message-ID: <8fd9fb42-e6b6-5485-b437-23063dfd5627@autistici.org> Date: Thu, 12 Sep 2024 14:18:16 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.15.0 To: 73196@debbugs.gnu.org Content-Language: en-US From: paul Subject: Re: [PATCH] services: postgresql-role: Add support for password files. Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 73196 X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.7 (-) Hi Guix, I'm sending a v2 with a small fix in the Guix system service extension logic. Thank you for all your work, giacomo From debbugs-submit-bounces@debbugs.gnu.org Thu Sep 12 08:18:54 2024 Received: (at 73196) by debbugs.gnu.org; 12 Sep 2024 12:18:54 +0000 Received: from localhost ([127.0.0.1]:40382 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1soimj-0006NR-CA for submit@debbugs.gnu.org; Thu, 12 Sep 2024 08:18:54 -0400 Received: from confino.investici.org ([93.190.126.19]:24121) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1soimh-0006NJ-PK for 73196@debbugs.gnu.org; Thu, 12 Sep 2024 08:18:52 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1726143523; bh=/+BZJGkpZte5PEs5Q8crEzeVPdPbRSsPqnz/YiFs0jw=; h=From:To:Cc:Subject:Date:From; b=brKGBIgFKTDr8Kx25Z2VQXlahNb9eGg/jUs5DdCSiAYWVDTirjk9jSfUfcABadK3l VKqAsC/e61dtrboA3SibxIy4qi9W7bckLxJGUx5JUDAqRNfulmn3WZTiL53YN1rKYa c883Lo+hFgiOGeUI2s1I9iCaIEzKvN1Qh6s3WGl4= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4X4Ggl3xlKz11B2; Thu, 12 Sep 2024 12:18:43 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4X4Ggl3Bs3z117s; Thu, 12 Sep 2024 12:18:43 +0000 (UTC) From: Giacomo Leidi To: 73196@debbugs.gnu.org Subject: [PATCH v2] services: postgresql-role: Add support for password files. Date: Thu, 12 Sep 2024 14:18:28 +0200 Message-ID: X-Mailer: git-send-email 2.46.0 MIME-Version: 1.0 X-Debbugs-Cc: Florian Pelz , Ludovic Courtès , Maxim Cournoyer Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 73196 Cc: Giacomo Leidi X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.7 (-) This commit adds a password-file to the postgresql-role field. It allows users to provision Postgres roles with a set password. * gnu/services/databases.scm (postgresql-role): Add password-file field; (postgresql-role-configuration): add requirement field; (postgresql-create-roles): add support for setting passwords from a file without leaking passwords to the command line; (postgresql-role-shepherd-service): add support for customizable requirements. * gnu/tests/databases.scm: Test it. * doc/guix.texi: Document it. Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585 --- doc/guix.texi | 15 ++++++++--- gnu/services/databases.scm | 51 ++++++++++++++++++++++++++++++++------ gnu/tests/databases.scm | 38 +++++++++++++++++++++++++--- 3 files changed, 90 insertions(+), 14 deletions(-) diff --git a/doc/guix.texi b/doc/guix.texi index 981ffb8c58..8e6f1b8b2a 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -26294,9 +26294,10 @@ Database Services @lisp (service-extension postgresql-role-service-type - (const (postgresql-role - (name "alice") - (create-database? #t)))) + (const (list + (postgresql-role + (name "alice") + (create-database? #t))))) @end lisp @end defvar @@ -26319,6 +26320,10 @@ Database Services @item @code{create-database?} (default: @code{#f}) whether to create a database with the same name as the role. +@item @code{password-file} (default: @code{#f}) +A string representing the path of a file that contains the password to be set +for the role. + @item @code{encoding} (default: @code{"UTF8"}) The character set to use for storing text in the database. @@ -26347,6 +26352,10 @@ Database Services @item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) File name of the log file. +@item @code{requirement} (default: @code{'()}) (type: list-of-symbols) +Set additional Shepherd services dependencies to the provisioned +Shepherd service. + @item @code{roles} (default: @code{'()}) The initial PostgreSQL roles to create. @end table diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index fa332d7978..d77988d8c5 100644 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -9,6 +9,7 @@ ;;; Copyright © 2020, 2022 Marius Bakke ;;; Copyright © 2021 David Larsson ;;; Copyright © 2021 Aljosha Papsch +;;; Copyright © 2024 Giacomo Leidi ;;; ;;; This file is part of GNU Guix. ;;; @@ -31,6 +32,7 @@ (define-module (gnu services databases) #:use-module (gnu system shadow) #:use-module (gnu packages admin) #:use-module (gnu packages base) + #:use-module (gnu packages bash) #:use-module (gnu packages databases) #:use-module (guix build-system trivial) #:use-module (guix build union) @@ -65,11 +67,13 @@ (define-module (gnu services databases) postgresql-role postgresql-role? postgresql-role-name + postgresql-role-password-file postgresql-role-permissions postgresql-role-create-database? postgresql-role-configuration postgresql-role-configuration? postgresql-role-configuration-host + postgresql-role-configuration-requirement postgresql-role-configuration-roles postgresql-role-service-type @@ -372,6 +376,8 @@ (define-record-type* postgresql-role make-postgresql-role postgresql-role? (name postgresql-role-name) ;string + (password-file postgresql-role-password-file ;string + (default #f)) (permissions postgresql-role-permissions (default '(createdb login))) ;list (create-database? postgresql-role-create-database? ;boolean @@ -390,6 +396,8 @@ (define-record-type* postgresql-role-configuration? (host postgresql-role-configuration-host ;string (default "/var/run/postgresql")) + (requirement postgresql-role-configuration-requirement ;list-of-symbols + (default '())) (log postgresql-role-configuration-log ;string (default "/var/log/postgresql_roles.log")) (roles postgresql-role-configuration-roles @@ -407,19 +415,36 @@ (define (postgresql-create-roles config) permissions) " "))) + (define (password-value role) + (string-append "password_" (postgresql-role-name role))) + + (define (role->password-variable role) + (define file-name + (postgresql-role-password-file role)) + (if (string? file-name) + ;; This way passwords do not leak to the command line + (string-append "-v \"" (password-value role) + "=$(cat " file-name ")\"") + "")) + (define (roles->queries roles) (apply mixed-text-file "queries" (append-map (lambda (role) (match-record role (name permissions create-database? encoding collation ctype - template) + template password-file) `("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) +,(if (and (string? password-file) + (not (string-null? password-file))) + (string-append + "\nPASSWORD :'" (password-value role) "'") + "") ";\n" ,@(if create-database? `("CREATE DATABASE \"" ,name "\"" @@ -434,20 +459,30 @@ (define (postgresql-create-roles config) (let ((host (postgresql-role-configuration-host config)) (roles (postgresql-role-configuration-roles config))) - #~(let ((psql #$(file-append postgresql "/bin/psql"))) - (list psql "-a" "-h" #$host "-f" #$(roles->queries roles))))) + (program-file "run-queries" + #~(let ((bash #$(file-append bash-minimal "/bin/bash")) + (psql #$(file-append postgresql "/bin/psql"))) + (define command + (string-append + "set -e; exec " psql " -c -a -h " #$host " -f " + #$(roles->queries roles) " " + (string-join + (list + #$@(map role->password-variable roles)) + " "))) + (execlp bash bash "-c" command))))) (define (postgresql-role-shepherd-service config) (match-record config - (log) + (log requirement) (list (shepherd-service - (requirement '(postgres)) + (requirement `(postgres ,@requirement)) (provision '(postgres-roles)) (one-shot? #t) (start #~(lambda args (let ((pid (fork+exec-command - #$(postgresql-create-roles config) + (list #$(postgresql-create-roles config)) #:user "postgres" #:group "postgres" #:log-file #$log))) @@ -462,9 +497,9 @@ (define postgresql-role-service-type (compose concatenate) (extend (lambda (config extended-roles) (match-record config - (host roles) + (roles) (postgresql-role-configuration - (host host) + (inherit config) (roles (append roles extended-roles)))))) (default-value (postgresql-role-configuration)) (description "Ensure the specified PostgreSQL roles are diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index 7c8b87942f..81484b2954 100644 --- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -142,6 +142,8 @@ (define %role-log-file (define %postgresql-os (simple-operating-system + (extra-special-file "/password" + (plain-file "password" "hello")) (service postgresql-service-type (postgresql-configuration (postgresql postgresql) @@ -158,6 +160,10 @@ (define %postgresql-os (roles (list (postgresql-role (name "root") + (create-database? #t)) + (postgresql-role + (name "alice") + (password-file "/password") (create-database? #t)))))))) (define (run-postgresql-test) @@ -230,14 +236,40 @@ (define (run-postgresql-test) (marionette-eval '(begin (use-modules (gnu services herd) + (srfi srfi-1) (ice-9 popen)) (current-output-port (open-file "/dev/console" "w0")) + (every + (lambda (role) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tA" "-c" + (string-append + "SELECT 1 FROM pg_database WHERE" + " datname='" role "'"))) + (output (get-string-all port))) + (close-pipe port) + (string-contains output "1"))) + '("root" "alice"))) + marionette)) + + (test-assert "database passwords are set" + (marionette-eval + '(begin + (use-modules (gnu services herd) + (ice-9 match) + (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (setgid (passwd:gid (getpwnam "alice"))) + (setuid (passwd:uid (getpw "alice"))) + (setenv "PGPASSWORD" "hello") (let* ((port (open-pipe* OPEN_READ - #$(file-append postgresql "/bin/psql") - "-tA" "-c" "SELECT 1 FROM pg_database WHERE - datname='root'")) + #$(file-append postgresql "/bin/psql") "-tA" "-c" + "SELECT 1 FROM pg_database WHERE datname='alice'")) (output (get-string-all port))) (close-pipe port) (string-contains output "1"))) base-commit: 590904cca15922e6474fbd3a71af9b3a45b268af -- 2.46.0 From debbugs-submit-bounces@debbugs.gnu.org Sun Oct 20 19:00:13 2024 Received: (at 73196) by debbugs.gnu.org; 20 Oct 2024 23:00:13 +0000 Received: from localhost ([127.0.0.1]:49276 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1t2euD-0002Ml-Ip for submit@debbugs.gnu.org; Sun, 20 Oct 2024 19:00:13 -0400 Received: from confino.investici.org ([93.190.126.19]:36549) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1t2euB-0002Mc-EP for 73196@debbugs.gnu.org; Sun, 20 Oct 2024 19:00:11 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1729465185; bh=CoJA1rcevulCVfRuwLHF0qlFmYhYB4PZU9rBymmXfEw=; h=Date:To:From:Subject:From; b=BMeeruLv90v7/tCYysEMlQg0axA/jKuMuDo1wgJKdKNDUrkIHxjJT3K/I6+6rmUep BVkTqy4jlgo2SeI3fDDYRz4rBszLpqcznVjikAM+chwDpp580hw4YmY2J3xOX9Qw4k pm1VQ4+5rREhgyBRDl1+/o55kxdnDZ991r10dqb4= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4XWv5s1TBDz10y2 for <73196@debbugs.gnu.org>; Sun, 20 Oct 2024 22:59:45 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4XWv5r6cTLz10xr for <73196@debbugs.gnu.org>; Sun, 20 Oct 2024 22:59:44 +0000 (UTC) Message-ID: <9fcbb384-3b89-487b-9a8f-4289b9f5e174@autistici.org> Date: Mon, 21 Oct 2024 00:59:44 +0200 MIME-Version: 1.0 User-Agent: Icedove Daily Content-Language: en-US To: 73196@debbugs.gnu.org From: paul Subject: Re: [PATCH] services: postgresql-role: Add support for password files. Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 73196 X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.7 (-) Hi Guix , this is a friendly ping. I'm sending a patchset rebased on current master. Thank you for your work, giacomo From debbugs-submit-bounces@debbugs.gnu.org Sun Oct 20 19:00:32 2024 Received: (at 73196) by debbugs.gnu.org; 20 Oct 2024 23:00:32 +0000 Received: from localhost ([127.0.0.1]:49280 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1t2euV-0002Od-Rx for submit@debbugs.gnu.org; Sun, 20 Oct 2024 19:00:32 -0400 Received: from confino.investici.org ([93.190.126.19]:44445) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1t2euU-0002NN-9K for 73196@debbugs.gnu.org; Sun, 20 Oct 2024 19:00:31 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1729465196; bh=pprV30MwbqrnAYj3+RqbtfBqx3PuSqCd+5clpQ1jkH4=; h=From:To:Cc:Subject:Date:From; b=OdZQRmw7xHhcBbY2LmYTrOaNAF5jiMfPCQDPBrvIKkGhlYvV8z292HXwPmPbcc202 KS939oeVW28Nq7upm++MdmfspX8jT+RztDc3BnDwW11vMArkxjrTTnmaWS1+cu3fSc YzB6NXGMO1O4rO2nBWlXvH+D1rXjkvm/MHAyjpBk= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4XWv646PXXz10y2; Sun, 20 Oct 2024 22:59:56 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4XWv644cD7z10xr; Sun, 20 Oct 2024 22:59:56 +0000 (UTC) From: Giacomo Leidi To: 73196@debbugs.gnu.org Subject: [PATCH v2] services: postgresql-role: Add support for password files. Date: Mon, 21 Oct 2024 00:59:47 +0200 Message-ID: X-Mailer: git-send-email 2.46.0 MIME-Version: 1.0 X-Debbugs-Cc: Ludovic Courtès , Maxim Cournoyer Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 73196 Cc: Giacomo Leidi X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.7 (-) This commit adds a password-file to the postgresql-role field. It allows users to provision Postgres roles with a set password. * gnu/services/databases.scm (postgresql-role): Add password-file field; (postgresql-role-configuration): add requirement field; (postgresql-create-roles): add support for setting passwords from a file without leaking passwords to the command line; (postgresql-role-shepherd-service): add support for customizable requirements. * gnu/tests/databases.scm: Test it. * doc/guix.texi: Document it. Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585 --- doc/guix.texi | 15 ++++++++--- gnu/services/databases.scm | 51 ++++++++++++++++++++++++++++++++------ gnu/tests/databases.scm | 38 +++++++++++++++++++++++++--- 3 files changed, 90 insertions(+), 14 deletions(-) diff --git a/doc/guix.texi b/doc/guix.texi index ac3a7adef0..71c717e161 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -26379,9 +26379,10 @@ Database Services @lisp (service-extension postgresql-role-service-type - (const (postgresql-role - (name "alice") - (create-database? #t)))) + (const (list + (postgresql-role + (name "alice") + (create-database? #t))))) @end lisp @end defvar @@ -26404,6 +26405,10 @@ Database Services @item @code{create-database?} (default: @code{#f}) whether to create a database with the same name as the role. +@item @code{password-file} (default: @code{#f}) +A string representing the path of a file that contains the password to be set +for the role. + @item @code{encoding} (default: @code{"UTF8"}) The character set to use for storing text in the database. @@ -26432,6 +26437,10 @@ Database Services @item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) File name of the log file. +@item @code{requirement} (default: @code{'()}) (type: list-of-symbols) +Set additional Shepherd services dependencies to the provisioned +Shepherd service. + @item @code{roles} (default: @code{'()}) The initial PostgreSQL roles to create. @end table diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index fa332d7978..d77988d8c5 100644 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -9,6 +9,7 @@ ;;; Copyright © 2020, 2022 Marius Bakke ;;; Copyright © 2021 David Larsson ;;; Copyright © 2021 Aljosha Papsch +;;; Copyright © 2024 Giacomo Leidi ;;; ;;; This file is part of GNU Guix. ;;; @@ -31,6 +32,7 @@ (define-module (gnu services databases) #:use-module (gnu system shadow) #:use-module (gnu packages admin) #:use-module (gnu packages base) + #:use-module (gnu packages bash) #:use-module (gnu packages databases) #:use-module (guix build-system trivial) #:use-module (guix build union) @@ -65,11 +67,13 @@ (define-module (gnu services databases) postgresql-role postgresql-role? postgresql-role-name + postgresql-role-password-file postgresql-role-permissions postgresql-role-create-database? postgresql-role-configuration postgresql-role-configuration? postgresql-role-configuration-host + postgresql-role-configuration-requirement postgresql-role-configuration-roles postgresql-role-service-type @@ -372,6 +376,8 @@ (define-record-type* postgresql-role make-postgresql-role postgresql-role? (name postgresql-role-name) ;string + (password-file postgresql-role-password-file ;string + (default #f)) (permissions postgresql-role-permissions (default '(createdb login))) ;list (create-database? postgresql-role-create-database? ;boolean @@ -390,6 +396,8 @@ (define-record-type* postgresql-role-configuration? (host postgresql-role-configuration-host ;string (default "/var/run/postgresql")) + (requirement postgresql-role-configuration-requirement ;list-of-symbols + (default '())) (log postgresql-role-configuration-log ;string (default "/var/log/postgresql_roles.log")) (roles postgresql-role-configuration-roles @@ -407,19 +415,36 @@ (define (postgresql-create-roles config) permissions) " "))) + (define (password-value role) + (string-append "password_" (postgresql-role-name role))) + + (define (role->password-variable role) + (define file-name + (postgresql-role-password-file role)) + (if (string? file-name) + ;; This way passwords do not leak to the command line + (string-append "-v \"" (password-value role) + "=$(cat " file-name ")\"") + "")) + (define (roles->queries roles) (apply mixed-text-file "queries" (append-map (lambda (role) (match-record role (name permissions create-database? encoding collation ctype - template) + template password-file) `("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) +,(if (and (string? password-file) + (not (string-null? password-file))) + (string-append + "\nPASSWORD :'" (password-value role) "'") + "") ";\n" ,@(if create-database? `("CREATE DATABASE \"" ,name "\"" @@ -434,20 +459,30 @@ (define (postgresql-create-roles config) (let ((host (postgresql-role-configuration-host config)) (roles (postgresql-role-configuration-roles config))) - #~(let ((psql #$(file-append postgresql "/bin/psql"))) - (list psql "-a" "-h" #$host "-f" #$(roles->queries roles))))) + (program-file "run-queries" + #~(let ((bash #$(file-append bash-minimal "/bin/bash")) + (psql #$(file-append postgresql "/bin/psql"))) + (define command + (string-append + "set -e; exec " psql " -c -a -h " #$host " -f " + #$(roles->queries roles) " " + (string-join + (list + #$@(map role->password-variable roles)) + " "))) + (execlp bash bash "-c" command))))) (define (postgresql-role-shepherd-service config) (match-record config - (log) + (log requirement) (list (shepherd-service - (requirement '(postgres)) + (requirement `(postgres ,@requirement)) (provision '(postgres-roles)) (one-shot? #t) (start #~(lambda args (let ((pid (fork+exec-command - #$(postgresql-create-roles config) + (list #$(postgresql-create-roles config)) #:user "postgres" #:group "postgres" #:log-file #$log))) @@ -462,9 +497,9 @@ (define postgresql-role-service-type (compose concatenate) (extend (lambda (config extended-roles) (match-record config - (host roles) + (roles) (postgresql-role-configuration - (host host) + (inherit config) (roles (append roles extended-roles)))))) (default-value (postgresql-role-configuration)) (description "Ensure the specified PostgreSQL roles are diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index 7c8b87942f..81484b2954 100644 --- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -142,6 +142,8 @@ (define %role-log-file (define %postgresql-os (simple-operating-system + (extra-special-file "/password" + (plain-file "password" "hello")) (service postgresql-service-type (postgresql-configuration (postgresql postgresql) @@ -158,6 +160,10 @@ (define %postgresql-os (roles (list (postgresql-role (name "root") + (create-database? #t)) + (postgresql-role + (name "alice") + (password-file "/password") (create-database? #t)))))))) (define (run-postgresql-test) @@ -230,14 +236,40 @@ (define (run-postgresql-test) (marionette-eval '(begin (use-modules (gnu services herd) + (srfi srfi-1) (ice-9 popen)) (current-output-port (open-file "/dev/console" "w0")) + (every + (lambda (role) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tA" "-c" + (string-append + "SELECT 1 FROM pg_database WHERE" + " datname='" role "'"))) + (output (get-string-all port))) + (close-pipe port) + (string-contains output "1"))) + '("root" "alice"))) + marionette)) + + (test-assert "database passwords are set" + (marionette-eval + '(begin + (use-modules (gnu services herd) + (ice-9 match) + (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (setgid (passwd:gid (getpwnam "alice"))) + (setuid (passwd:uid (getpw "alice"))) + (setenv "PGPASSWORD" "hello") (let* ((port (open-pipe* OPEN_READ - #$(file-append postgresql "/bin/psql") - "-tA" "-c" "SELECT 1 FROM pg_database WHERE - datname='root'")) + #$(file-append postgresql "/bin/psql") "-tA" "-c" + "SELECT 1 FROM pg_database WHERE datname='alice'")) (output (get-string-all port))) (close-pipe port) (string-contains output "1"))) base-commit: 5ab3c4c1e43ebb637551223791db0ea3519986e1 -- 2.46.0 From debbugs-submit-bounces@debbugs.gnu.org Thu Apr 03 14:37:26 2025 Received: (at 73196) by debbugs.gnu.org; 3 Apr 2025 18:37:26 +0000 Received: from localhost ([127.0.0.1]:35665 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1u0PRO-0003Dh-46 for submit@debbugs.gnu.org; Thu, 03 Apr 2025 14:37:26 -0400 Received: from confino.investici.org ([2a11:7980:1::2:0]:56003) by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1u0PRK-0003DH-8r for 73196@debbugs.gnu.org; Thu, 03 Apr 2025 14:37:23 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1743705439; bh=0ktoCRubU8xkVJbushc9fmQSnESsuECRX6nbPTjkl+Q=; h=Date:Subject:From:To:References:In-Reply-To:From; b=skS/Xs2FQAy68IRF2RlOY/TWsYys4dyY8dj/iiFYZ7DQ+AanDkXrGLUgx2NvU7/wK NfWPIkPxcrwwtwnUnHlT9fXJdbKK04HnwbixKmhULFAVSjnaAPQCAXrmrrepGK5HL2 k259CC2scm7k2SXt1tlIWfaw6Be1oNJZePUR1uDI= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4ZT9Sv0kQPz113f for <73196@debbugs.gnu.org>; Thu, 3 Apr 2025 18:37:19 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4ZT9Sv0F3Qz113B for <73196@debbugs.gnu.org>; Thu, 3 Apr 2025 18:37:18 +0000 (UTC) Message-ID: <651ac684-fb8a-4b20-b4ec-f3868fd95d29@autistici.org> Date: Thu, 3 Apr 2025 20:37:18 +0200 MIME-Version: 1.0 User-Agent: Icedove Daily Subject: Re: [PATCH] services: postgresql-role: Add support for password files. From: paul To: 73196@debbugs.gnu.org References: <9fcbb384-3b89-487b-9a8f-4289b9f5e174@autistici.org> Content-Language: en-US In-Reply-To: <9fcbb384-3b89-487b-9a8f-4289b9f5e174@autistici.org> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Score: -0.0 (/) X-Debbugs-Envelope-To: 73196 X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) Hi I'm sending a an updated patch rebased on current master. cheers, giacomo From debbugs-submit-bounces@debbugs.gnu.org Thu Apr 03 15:27:37 2025 Received: (at 73196) by debbugs.gnu.org; 3 Apr 2025 19:27:37 +0000 Received: from localhost ([127.0.0.1]:35747 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1u0QDx-0005ZL-9Z for submit@debbugs.gnu.org; Thu, 03 Apr 2025 15:27:37 -0400 Received: from confino.investici.org ([93.190.126.19]:28385) by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1u0QDt-0005Z5-CI for 73196@debbugs.gnu.org; Thu, 03 Apr 2025 15:27:34 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1743708450; bh=BHH7gPlHOQ7jnkG6Ci2RSyI/hh4S+wA89kht0zOY9iw=; h=From:To:Cc:Subject:Date:In-Reply-To:References:From; b=gajyiANRVRqqMk7EzKE99aartdt84Arw+XQp3zWObP4DCOSPDefSHdasz42qQZvdk 6ZC2FzXX5d2LcNApq1CYeYaMYmj9ZKkVV2ZMu9gaBBd9/SATLqJCv1sR5rfXfaSnOz +XnYONlBgHCnTVrt8n3HTCEaV/7+jot2UgmIqPTE= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4ZTBZp6qhVz113w; Thu, 3 Apr 2025 19:27:30 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4ZTBZp5q0nz111q; Thu, 3 Apr 2025 19:27:30 +0000 (UTC) From: Giacomo Leidi To: 73196@debbugs.gnu.org Subject: [PATCH v3 2/2] as Date: Thu, 3 Apr 2025 21:26:52 +0200 Message-ID: <96415235c2737702dbdfa3377724bf52e67598e9.1743708412.git.goodoldpaul@autistici.org> X-Mailer: git-send-email 2.49.0 In-Reply-To: <5f19d1f0b95e83ed5e2a5a1b8d9c281520631ef8.1743708412.git.goodoldpaul@autistici.org> References: <5f19d1f0b95e83ed5e2a5a1b8d9c281520631ef8.1743708412.git.goodoldpaul@autistici.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 73196 Cc: Giacomo Leidi X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.7 (-) Change-Id: Ia7ea0c1bc75189beb20e3d41f0d861cbd7c22b62 --- gnu/services/databases.scm | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index 8c677a1ef66..b845327f89e 100644 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -501,9 +501,10 @@ (define postgresql-role-service-type (compose concatenate) (extend (lambda (config extended-roles) (match-record config - (roles) + (host roles) (postgresql-role-configuration (inherit config) + (host host) (roles (append roles extended-roles)))))) (default-value (postgresql-role-configuration)) (description "Ensure the specified PostgreSQL roles are -- 2.49.0 From debbugs-submit-bounces@debbugs.gnu.org Thu Apr 03 15:27:38 2025 Received: (at 73196) by debbugs.gnu.org; 3 Apr 2025 19:27:38 +0000 Received: from localhost ([127.0.0.1]:35749 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1u0QDx-0005ZT-Un for submit@debbugs.gnu.org; Thu, 03 Apr 2025 15:27:38 -0400 Received: from confino.investici.org ([2a11:7980:1::2:0]:53433) by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1u0QDt-0005Z4-RM for 73196@debbugs.gnu.org; Thu, 03 Apr 2025 15:27:35 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1743708450; bh=Ny3+BrqIjeDM2M8FCH0EkmgYY2G0nNVP5iba3575oP0=; h=From:To:Cc:Subject:Date:From; b=BvGAznqIuFa65hPZYlHpB7lCu4or7YZPqzV/WOk2nPEUAaFcSdI3n8BkbY//RetSs CKZ6OyXW36IWdJ0vezx/I/bGJ/pve9o7h0FpdNKliJ2zlIuy6/UicBolynL0r0gohu Gr9Bm0CPXWwZCl+1MPE80XErXj3QySpYXDbqReRM= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4ZTBZp4NW1z113r; Thu, 3 Apr 2025 19:27:30 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4ZTBZp3KWXz111q; Thu, 3 Apr 2025 19:27:30 +0000 (UTC) From: Giacomo Leidi To: 73196@debbugs.gnu.org Subject: [PATCH v3 1/2] services: postgresql-role: Add support for password files. Date: Thu, 3 Apr 2025 21:26:51 +0200 Message-ID: <5f19d1f0b95e83ed5e2a5a1b8d9c281520631ef8.1743708412.git.goodoldpaul@autistici.org> X-Mailer: git-send-email 2.49.0 MIME-Version: 1.0 X-Debbugs-Cc: Ludovic Courtès , Maxim Cournoyer Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Spam-Score: -0.0 (/) X-Debbugs-Envelope-To: 73196 Cc: Giacomo Leidi X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) This commit adds a password-file to the postgresql-role field. It allows users to provision Postgres roles with a set password. * gnu/services/databases.scm (postgresql-role): Add password-file field; (postgresql-role-configuration): add requirement field; (postgresql-create-roles): add support for setting passwords from a file without leaking passwords to the command line; (postgresql-role-shepherd-service): add support for customizable requirements. * gnu/tests/databases.scm: Test it. * doc/guix.texi: Document it. Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585 --- doc/guix.texi | 15 ++++++++--- gnu/services/databases.scm | 51 ++++++++++++++++++++++++++++++++------ gnu/tests/databases.scm | 38 +++++++++++++++++++++++++--- 3 files changed, 90 insertions(+), 14 deletions(-) diff --git a/doc/guix.texi b/doc/guix.texi index 3d6080dbaa5..eb0b94409c2 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -27586,9 +27586,10 @@ Database Services @lisp (service-extension postgresql-role-service-type - (const (postgresql-role - (name "alice") - (create-database? #t)))) + (const (list + (postgresql-role + (name "alice") + (create-database? #t))))) @end lisp @end defvar @@ -27611,6 +27612,10 @@ Database Services @item @code{create-database?} (default: @code{#f}) whether to create a database with the same name as the role. +@item @code{password-file} (default: @code{#f}) +A string representing the path of a file that contains the password to be set +for the role. + @item @code{encoding} (default: @code{"UTF8"}) The character set to use for storing text in the database. @@ -27639,6 +27644,10 @@ Database Services @item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) File name of the log file. +@item @code{requirement} (default: @code{'()}) (type: list-of-symbols) +Set additional Shepherd services dependencies to the provisioned +Shepherd service. + @item @code{roles} (default: @code{'()}) The initial PostgreSQL roles to create. @end table diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index 6d80376d90d..8c677a1ef66 100644 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -9,6 +9,7 @@ ;;; Copyright © 2020, 2022 Marius Bakke ;;; Copyright © 2021 David Larsson ;;; Copyright © 2021 Aljosha Papsch +;;; Copyright © 2025 Giacomo Leidi ;;; ;;; This file is part of GNU Guix. ;;; @@ -31,6 +32,7 @@ (define-module (gnu services databases) #:use-module (gnu system shadow) #:use-module (gnu packages admin) #:use-module (gnu packages base) + #:use-module (gnu packages bash) #:use-module (gnu packages databases) #:use-module (guix build-system trivial) #:use-module (guix build union) @@ -65,11 +67,13 @@ (define-module (gnu services databases) postgresql-role postgresql-role? postgresql-role-name + postgresql-role-password-file postgresql-role-permissions postgresql-role-create-database? postgresql-role-configuration postgresql-role-configuration? postgresql-role-configuration-host + postgresql-role-configuration-requirement postgresql-role-configuration-roles postgresql-role-service-type @@ -374,6 +378,8 @@ (define-record-type* postgresql-role make-postgresql-role postgresql-role? (name postgresql-role-name) ;string + (password-file postgresql-role-password-file ;string + (default #f)) (permissions postgresql-role-permissions (default '(createdb login))) ;list (create-database? postgresql-role-create-database? ;boolean @@ -392,6 +398,8 @@ (define-record-type* postgresql-role-configuration? (host postgresql-role-configuration-host ;string (default "/var/run/postgresql")) + (requirement postgresql-role-configuration-requirement ;list-of-symbols + (default '())) (log postgresql-role-configuration-log ;string (default "/var/log/postgresql_roles.log")) (roles postgresql-role-configuration-roles @@ -409,19 +417,36 @@ (define (postgresql-create-roles config) permissions) " "))) + (define (password-value role) + (string-append "password_" (postgresql-role-name role))) + + (define (role->password-variable role) + (define file-name + (postgresql-role-password-file role)) + (if (string? file-name) + ;; This way passwords do not leak to the command line + (string-append "-v \"" (password-value role) + "=$(cat " file-name ")\"") + "")) + (define (roles->queries roles) (apply mixed-text-file "queries" (append-map (lambda (role) (match-record role (name permissions create-database? encoding collation ctype - template) + template password-file) `("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) +,(if (and (string? password-file) + (not (string-null? password-file))) + (string-append + "\nPASSWORD :'" (password-value role) "'") + "") ";\n" ,@(if create-database? `("CREATE DATABASE \"" ,name "\"" @@ -436,20 +461,30 @@ (define (postgresql-create-roles config) (let ((host (postgresql-role-configuration-host config)) (roles (postgresql-role-configuration-roles config))) - #~(let ((psql #$(file-append postgresql "/bin/psql"))) - (list psql "-a" "-h" #$host "-f" #$(roles->queries roles))))) + (program-file "run-queries" + #~(let ((bash #$(file-append bash-minimal "/bin/bash")) + (psql #$(file-append postgresql "/bin/psql"))) + (define command + (string-append + "set -e; exec " psql " -c -a -h " #$host " -f " + #$(roles->queries roles) " " + (string-join + (list + #$@(map role->password-variable roles)) + " "))) + (execlp bash bash "-c" command))))) (define (postgresql-role-shepherd-service config) (match-record config - (log) + (log requirement) (list (shepherd-service - (requirement '(user-processes postgres)) + (requirement `(user-processes postgres ,@requirement)) (provision '(postgres-roles)) (one-shot? #t) (start #~(lambda args (zero? (spawn-command - #$(postgresql-create-roles config) + (list #$(postgresql-create-roles config)) #:user "postgres" #:group "postgres" ;; XXX: As of Shepherd 1.0.2, #:log-file is not @@ -466,9 +501,9 @@ (define postgresql-role-service-type (compose concatenate) (extend (lambda (config extended-roles) (match-record config - (host roles) + (roles) (postgresql-role-configuration - (host host) + (inherit config) (roles (append roles extended-roles)))))) (default-value (postgresql-role-configuration)) (description "Ensure the specified PostgreSQL roles are diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index fd5041344b6..c5da603565d 100644 --- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -142,6 +142,8 @@ (define %role-log-file (define %postgresql-os (simple-operating-system + (extra-special-file "/password" + (plain-file "password" "hello")) (service postgresql-service-type (postgresql-configuration (postgresql postgresql) @@ -158,6 +160,10 @@ (define %postgresql-os (roles (list (postgresql-role (name "root") + (create-database? #t)) + (postgresql-role + (name "alice") + (password-file "/password") (create-database? #t)))))))) (define (run-postgresql-test) @@ -230,14 +236,40 @@ (define (run-postgresql-test) (marionette-eval '(begin (use-modules (gnu services herd) + (srfi srfi-1) (ice-9 popen)) (current-output-port (open-file "/dev/console" "w0")) + (every + (lambda (role) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tA" "-c" + (string-append + "SELECT 1 FROM pg_database WHERE" + " datname='" role "'"))) + (output (get-string-all port))) + (close-pipe port) + (string-contains output "1"))) + '("root" "alice"))) + marionette)) + + (test-assert "database passwords are set" + (marionette-eval + '(begin + (use-modules (gnu services herd) + (ice-9 match) + (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (setgid (passwd:gid (getpwnam "alice"))) + (setuid (passwd:uid (getpw "alice"))) + (setenv "PGPASSWORD" "hello") (let* ((port (open-pipe* OPEN_READ - #$(file-append postgresql "/bin/psql") - "-tA" "-c" "SELECT 1 FROM pg_database WHERE - datname='root'")) + #$(file-append postgresql "/bin/psql") "-tA" "-c" + "SELECT 1 FROM pg_database WHERE datname='alice'")) (output (get-string-all port))) (close-pipe port) (string-contains output "1"))) base-commit: 6a2a78fde19683f07c8b10f492cda67447bc99eb -- 2.49.0 From debbugs-submit-bounces@debbugs.gnu.org Thu Apr 03 15:28:12 2025 Received: (at 73196) by debbugs.gnu.org; 3 Apr 2025 19:28:12 +0000 Received: from localhost ([127.0.0.1]:35755 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1u0QEV-0005b6-T0 for submit@debbugs.gnu.org; Thu, 03 Apr 2025 15:28:12 -0400 Received: from confino.investici.org ([93.190.126.19]:62259) by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1u0QET-0005at-Gl for 73196@debbugs.gnu.org; Thu, 03 Apr 2025 15:28:10 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1743708488; bh=u5XuEyaJGPCrAcndGpN5pYwXy6PGwaAwkmGqCW3eFlo=; h=From:To:Cc:Subject:Date:From; b=S7AFUyWdCHbpyj9niF0Qv4J8zacPEo180MG/pQMeV20MPa0uLrTfeqNNdBnIgTKiF iHi3Ep7m+8sTNh3RZJfjZIKcKQniO3DISh+4+6Cy3iX6diuSSNVAphiAatSI2O96sg bm443PC/4OovA1Fisz5nFcOs6CMMdxMV1BeaW0OY= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4ZTBbX42V2z111q; Thu, 3 Apr 2025 19:28:08 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4ZTBbX339Rz113j; Thu, 3 Apr 2025 19:28:08 +0000 (UTC) From: Giacomo Leidi To: 73196@debbugs.gnu.org Subject: [PATCH v4] services: postgresql-role: Add support for password files. Date: Thu, 3 Apr 2025 21:28:01 +0200 Message-ID: <501cabc35da6425c5d5fc02f3d7f47c242cc6463.1743708481.git.goodoldpaul@autistici.org> X-Mailer: git-send-email 2.49.0 MIME-Version: 1.0 X-Debbugs-Cc: Ludovic Courtès , Maxim Cournoyer Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 73196 Cc: Giacomo Leidi X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.7 (-) This commit adds a password-file to the postgresql-role field. It allows users to provision Postgres roles with a set password. * gnu/services/databases.scm (postgresql-role): Add password-file field; (postgresql-role-configuration): add requirement field; (postgresql-create-roles): add support for setting passwords from a file without leaking passwords to the command line; (postgresql-role-shepherd-service): add support for customizable requirements. * gnu/tests/databases.scm: Test it. * doc/guix.texi: Document it. Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585 --- doc/guix.texi | 15 +++++++++--- gnu/services/databases.scm | 48 +++++++++++++++++++++++++++++++++----- gnu/tests/databases.scm | 38 +++++++++++++++++++++++++++--- 3 files changed, 89 insertions(+), 12 deletions(-) diff --git a/doc/guix.texi b/doc/guix.texi index 3d6080dbaa5..eb0b94409c2 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -27586,9 +27586,10 @@ Database Services @lisp (service-extension postgresql-role-service-type - (const (postgresql-role - (name "alice") - (create-database? #t)))) + (const (list + (postgresql-role + (name "alice") + (create-database? #t))))) @end lisp @end defvar @@ -27611,6 +27612,10 @@ Database Services @item @code{create-database?} (default: @code{#f}) whether to create a database with the same name as the role. +@item @code{password-file} (default: @code{#f}) +A string representing the path of a file that contains the password to be set +for the role. + @item @code{encoding} (default: @code{"UTF8"}) The character set to use for storing text in the database. @@ -27639,6 +27644,10 @@ Database Services @item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) File name of the log file. +@item @code{requirement} (default: @code{'()}) (type: list-of-symbols) +Set additional Shepherd services dependencies to the provisioned +Shepherd service. + @item @code{roles} (default: @code{'()}) The initial PostgreSQL roles to create. @end table diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index 6d80376d90d..b845327f89e 100644 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -9,6 +9,7 @@ ;;; Copyright © 2020, 2022 Marius Bakke ;;; Copyright © 2021 David Larsson ;;; Copyright © 2021 Aljosha Papsch +;;; Copyright © 2025 Giacomo Leidi ;;; ;;; This file is part of GNU Guix. ;;; @@ -31,6 +32,7 @@ (define-module (gnu services databases) #:use-module (gnu system shadow) #:use-module (gnu packages admin) #:use-module (gnu packages base) + #:use-module (gnu packages bash) #:use-module (gnu packages databases) #:use-module (guix build-system trivial) #:use-module (guix build union) @@ -65,11 +67,13 @@ (define-module (gnu services databases) postgresql-role postgresql-role? postgresql-role-name + postgresql-role-password-file postgresql-role-permissions postgresql-role-create-database? postgresql-role-configuration postgresql-role-configuration? postgresql-role-configuration-host + postgresql-role-configuration-requirement postgresql-role-configuration-roles postgresql-role-service-type @@ -374,6 +378,8 @@ (define-record-type* postgresql-role make-postgresql-role postgresql-role? (name postgresql-role-name) ;string + (password-file postgresql-role-password-file ;string + (default #f)) (permissions postgresql-role-permissions (default '(createdb login))) ;list (create-database? postgresql-role-create-database? ;boolean @@ -392,6 +398,8 @@ (define-record-type* postgresql-role-configuration? (host postgresql-role-configuration-host ;string (default "/var/run/postgresql")) + (requirement postgresql-role-configuration-requirement ;list-of-symbols + (default '())) (log postgresql-role-configuration-log ;string (default "/var/log/postgresql_roles.log")) (roles postgresql-role-configuration-roles @@ -409,19 +417,36 @@ (define (postgresql-create-roles config) permissions) " "))) + (define (password-value role) + (string-append "password_" (postgresql-role-name role))) + + (define (role->password-variable role) + (define file-name + (postgresql-role-password-file role)) + (if (string? file-name) + ;; This way passwords do not leak to the command line + (string-append "-v \"" (password-value role) + "=$(cat " file-name ")\"") + "")) + (define (roles->queries roles) (apply mixed-text-file "queries" (append-map (lambda (role) (match-record role (name permissions create-database? encoding collation ctype - template) + template password-file) `("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) +,(if (and (string? password-file) + (not (string-null? password-file))) + (string-append + "\nPASSWORD :'" (password-value role) "'") + "") ";\n" ,@(if create-database? `("CREATE DATABASE \"" ,name "\"" @@ -436,20 +461,30 @@ (define (postgresql-create-roles config) (let ((host (postgresql-role-configuration-host config)) (roles (postgresql-role-configuration-roles config))) - #~(let ((psql #$(file-append postgresql "/bin/psql"))) - (list psql "-a" "-h" #$host "-f" #$(roles->queries roles))))) + (program-file "run-queries" + #~(let ((bash #$(file-append bash-minimal "/bin/bash")) + (psql #$(file-append postgresql "/bin/psql"))) + (define command + (string-append + "set -e; exec " psql " -c -a -h " #$host " -f " + #$(roles->queries roles) " " + (string-join + (list + #$@(map role->password-variable roles)) + " "))) + (execlp bash bash "-c" command))))) (define (postgresql-role-shepherd-service config) (match-record config - (log) + (log requirement) (list (shepherd-service - (requirement '(user-processes postgres)) + (requirement `(user-processes postgres ,@requirement)) (provision '(postgres-roles)) (one-shot? #t) (start #~(lambda args (zero? (spawn-command - #$(postgresql-create-roles config) + (list #$(postgresql-create-roles config)) #:user "postgres" #:group "postgres" ;; XXX: As of Shepherd 1.0.2, #:log-file is not @@ -468,6 +503,7 @@ (define postgresql-role-service-type (match-record config (host roles) (postgresql-role-configuration + (inherit config) (host host) (roles (append roles extended-roles)))))) (default-value (postgresql-role-configuration)) diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index fd5041344b6..c5da603565d 100644 --- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -142,6 +142,8 @@ (define %role-log-file (define %postgresql-os (simple-operating-system + (extra-special-file "/password" + (plain-file "password" "hello")) (service postgresql-service-type (postgresql-configuration (postgresql postgresql) @@ -158,6 +160,10 @@ (define %postgresql-os (roles (list (postgresql-role (name "root") + (create-database? #t)) + (postgresql-role + (name "alice") + (password-file "/password") (create-database? #t)))))))) (define (run-postgresql-test) @@ -230,14 +236,40 @@ (define (run-postgresql-test) (marionette-eval '(begin (use-modules (gnu services herd) + (srfi srfi-1) (ice-9 popen)) (current-output-port (open-file "/dev/console" "w0")) + (every + (lambda (role) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tA" "-c" + (string-append + "SELECT 1 FROM pg_database WHERE" + " datname='" role "'"))) + (output (get-string-all port))) + (close-pipe port) + (string-contains output "1"))) + '("root" "alice"))) + marionette)) + + (test-assert "database passwords are set" + (marionette-eval + '(begin + (use-modules (gnu services herd) + (ice-9 match) + (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (setgid (passwd:gid (getpwnam "alice"))) + (setuid (passwd:uid (getpw "alice"))) + (setenv "PGPASSWORD" "hello") (let* ((port (open-pipe* OPEN_READ - #$(file-append postgresql "/bin/psql") - "-tA" "-c" "SELECT 1 FROM pg_database WHERE - datname='root'")) + #$(file-append postgresql "/bin/psql") "-tA" "-c" + "SELECT 1 FROM pg_database WHERE datname='alice'")) (output (get-string-all port))) (close-pipe port) (string-contains output "1"))) base-commit: 6a2a78fde19683f07c8b10f492cda67447bc99eb -- 2.49.0 From debbugs-submit-bounces@debbugs.gnu.org Tue Apr 08 16:38:17 2025 Received: (at 73196) by debbugs.gnu.org; 8 Apr 2025 20:38:17 +0000 Received: from localhost ([127.0.0.1]:35718 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1u2Fi5-0005Q0-1W for submit@debbugs.gnu.org; Tue, 08 Apr 2025 16:38:17 -0400 Received: from confino.investici.org ([2a11:7980:1::2:0]:40791) by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1u2Fi0-0005Pg-UG for 73196@debbugs.gnu.org; Tue, 08 Apr 2025 16:38:14 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1744144690; bh=ahiZb7B4BI/YZD32C3PRXsYmyCqfupkh6+a1S2ZtP4w=; h=Date:Subject:From:To:References:In-Reply-To:From; b=dA0a9xSyiP2VtoC4PAgiWEwwvY5MnYHP2KSTaGn4jqClZPuVAVEok7U8UpeVHXbRw TiDTyyrC6X7AT4h0m6AMuq5MUp5mgGlChowMozWE26CRUZSGrZLPI3fmtYH4p1G0aG c39ZbMp5mw5ql/PZxHRtQvKJGHuAaGxh9+1Dnvkg= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4ZXHw24pbMz120N for <73196@debbugs.gnu.org>; Tue, 8 Apr 2025 20:38:10 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4ZXHw24Hrdz120H for <73196@debbugs.gnu.org>; Tue, 8 Apr 2025 20:38:10 +0000 (UTC) Message-ID: <10862a5e-b767-4fd7-a512-7888d50c8674@autistici.org> Date: Tue, 8 Apr 2025 22:38:09 +0200 MIME-Version: 1.0 User-Agent: Icedove Daily Subject: Re: [PATCH] services: postgresql-role: Add support for password files. From: paul To: 73196@debbugs.gnu.org References: <9fcbb384-3b89-487b-9a8f-4289b9f5e174@autistici.org> <651ac684-fb8a-4b20-b4ec-f3868fd95d29@autistici.org> Content-Language: en-US In-Reply-To: <651ac684-fb8a-4b20-b4ec-f3868fd95d29@autistici.org> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Score: -0.0 (/) X-Debbugs-Envelope-To: 73196 X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) Hi I'm sending a v5 rebased on current master that uses a gexp to reference cat. cheers, giacomo From debbugs-submit-bounces@debbugs.gnu.org Tue Apr 08 16:39:13 2025 Received: (at 73196) by debbugs.gnu.org; 8 Apr 2025 20:39:13 +0000 Received: from localhost ([127.0.0.1]:35725 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1u2Fiy-0005Ru-Us for submit@debbugs.gnu.org; Tue, 08 Apr 2025 16:39:13 -0400 Received: from confino.investici.org ([93.190.126.19]:43399) by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1u2Fiu-0005Rg-DR for 73196@debbugs.gnu.org; Tue, 08 Apr 2025 16:39:09 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1744144747; bh=MqG6/lbH/U3xaq/96/NIrPy9o50Nq+yaYPTgpb+J4Q0=; h=From:To:Cc:Subject:Date:From; b=gHZvylQT2LoX/jDmfe6Csp8f43alyiwbDCOZtTfm0E4KW1mM8FjpQ1mS6eK9Jm6rm ZHK9XTYtWDpFj2SytGQByF3Xdn37b6/bpGMTF4q7gVXc2q+AUL2tckifxJhrQ4/weH UG6ApUMutGSoXVQSdWvP+ncVUG5dQ6HZupGdVHxM= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4ZXHx70KmMz1203; Tue, 8 Apr 2025 20:39:07 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4ZXHx66MP9z11wk; Tue, 8 Apr 2025 20:39:06 +0000 (UTC) From: Giacomo Leidi To: 73196@debbugs.gnu.org Subject: [PATCH v5] services: postgresql-role: Add support for password files. Date: Tue, 8 Apr 2025 22:38:56 +0200 Message-ID: <412e365651408bbc2f0e13822138ccf6612a57ba.1744144736.git.goodoldpaul@autistici.org> X-Mailer: git-send-email 2.49.0 MIME-Version: 1.0 X-Debbugs-Cc: Ludovic Courtès , Maxim Cournoyer Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 73196 Cc: Giacomo Leidi X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.7 (-) This commit adds a password-file to the postgresql-role field. It allows users to provision Postgres roles with a set password. * gnu/services/databases.scm (postgresql-role): Add password-file field; (postgresql-role-configuration): add requirement field; (postgresql-create-roles): add support for setting passwords from a file without leaking passwords to the command line; (postgresql-role-shepherd-service): add support for customizable requirements. * gnu/tests/databases.scm: Test it. * doc/guix.texi: Document it. Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585 --- doc/guix.texi | 15 +++++++++--- gnu/services/databases.scm | 48 +++++++++++++++++++++++++++++++++----- gnu/tests/databases.scm | 38 +++++++++++++++++++++++++++--- 3 files changed, 89 insertions(+), 12 deletions(-) diff --git a/doc/guix.texi b/doc/guix.texi index bee80cd4e2b..8b32557f76a 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -27588,9 +27588,10 @@ Database Services @lisp (service-extension postgresql-role-service-type - (const (postgresql-role - (name "alice") - (create-database? #t)))) + (const (list + (postgresql-role + (name "alice") + (create-database? #t))))) @end lisp @end defvar @@ -27613,6 +27614,10 @@ Database Services @item @code{create-database?} (default: @code{#f}) whether to create a database with the same name as the role. +@item @code{password-file} (default: @code{#f}) +A string representing the path of a file that contains the password to be set +for the role. + @item @code{encoding} (default: @code{"UTF8"}) The character set to use for storing text in the database. @@ -27641,6 +27646,10 @@ Database Services @item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) File name of the log file. +@item @code{requirement} (default: @code{'()}) (type: list-of-symbols) +Set additional Shepherd services dependencies to the provisioned +Shepherd service. + @item @code{roles} (default: @code{'()}) The initial PostgreSQL roles to create. @end table diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index 6d80376d90d..9242fa62642 100644 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -9,6 +9,7 @@ ;;; Copyright © 2020, 2022 Marius Bakke ;;; Copyright © 2021 David Larsson ;;; Copyright © 2021 Aljosha Papsch +;;; Copyright © 2025 Giacomo Leidi ;;; ;;; This file is part of GNU Guix. ;;; @@ -31,6 +32,7 @@ (define-module (gnu services databases) #:use-module (gnu system shadow) #:use-module (gnu packages admin) #:use-module (gnu packages base) + #:use-module (gnu packages bash) #:use-module (gnu packages databases) #:use-module (guix build-system trivial) #:use-module (guix build union) @@ -65,11 +67,13 @@ (define-module (gnu services databases) postgresql-role postgresql-role? postgresql-role-name + postgresql-role-password-file postgresql-role-permissions postgresql-role-create-database? postgresql-role-configuration postgresql-role-configuration? postgresql-role-configuration-host + postgresql-role-configuration-requirement postgresql-role-configuration-roles postgresql-role-service-type @@ -374,6 +378,8 @@ (define-record-type* postgresql-role make-postgresql-role postgresql-role? (name postgresql-role-name) ;string + (password-file postgresql-role-password-file ;string + (default #f)) (permissions postgresql-role-permissions (default '(createdb login))) ;list (create-database? postgresql-role-create-database? ;boolean @@ -392,6 +398,8 @@ (define-record-type* postgresql-role-configuration? (host postgresql-role-configuration-host ;string (default "/var/run/postgresql")) + (requirement postgresql-role-configuration-requirement ;list-of-symbols + (default '())) (log postgresql-role-configuration-log ;string (default "/var/log/postgresql_roles.log")) (roles postgresql-role-configuration-roles @@ -409,19 +417,36 @@ (define (postgresql-create-roles config) permissions) " "))) + (define (password-value role) + (string-append "password_" (postgresql-role-name role))) + + (define (role->password-variable role) + (define file-name + (postgresql-role-password-file role)) + (if (string? file-name) + ;; This way passwords do not leak to the command line + #~(string-append "-v \"" #$(password-value role) + "=$(" #+coreutils "/bin/cat " #$file-name ")\"") + "")) + (define (roles->queries roles) (apply mixed-text-file "queries" (append-map (lambda (role) (match-record role (name permissions create-database? encoding collation ctype - template) + template password-file) `("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) +,(if (and (string? password-file) + (not (string-null? password-file))) + (string-append + "\nPASSWORD :'" (password-value role) "'") + "") ";\n" ,@(if create-database? `("CREATE DATABASE \"" ,name "\"" @@ -436,20 +461,30 @@ (define (postgresql-create-roles config) (let ((host (postgresql-role-configuration-host config)) (roles (postgresql-role-configuration-roles config))) - #~(let ((psql #$(file-append postgresql "/bin/psql"))) - (list psql "-a" "-h" #$host "-f" #$(roles->queries roles))))) + (program-file "run-queries" + #~(let ((bash #$(file-append bash-minimal "/bin/bash")) + (psql #$(file-append postgresql "/bin/psql"))) + (define command + (string-append + "set -e; exec " psql " -c -a -h " #$host " -f " + #$(roles->queries roles) " " + (string-join + (list + #$@(map role->password-variable roles)) + " "))) + (execlp bash bash "-c" command))))) (define (postgresql-role-shepherd-service config) (match-record config - (log) + (log requirement) (list (shepherd-service - (requirement '(user-processes postgres)) + (requirement `(user-processes postgres ,@requirement)) (provision '(postgres-roles)) (one-shot? #t) (start #~(lambda args (zero? (spawn-command - #$(postgresql-create-roles config) + (list #$(postgresql-create-roles config)) #:user "postgres" #:group "postgres" ;; XXX: As of Shepherd 1.0.2, #:log-file is not @@ -468,6 +503,7 @@ (define postgresql-role-service-type (match-record config (host roles) (postgresql-role-configuration + (inherit config) (host host) (roles (append roles extended-roles)))))) (default-value (postgresql-role-configuration)) diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index fd5041344b6..c5da603565d 100644 --- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -142,6 +142,8 @@ (define %role-log-file (define %postgresql-os (simple-operating-system + (extra-special-file "/password" + (plain-file "password" "hello")) (service postgresql-service-type (postgresql-configuration (postgresql postgresql) @@ -158,6 +160,10 @@ (define %postgresql-os (roles (list (postgresql-role (name "root") + (create-database? #t)) + (postgresql-role + (name "alice") + (password-file "/password") (create-database? #t)))))))) (define (run-postgresql-test) @@ -230,14 +236,40 @@ (define (run-postgresql-test) (marionette-eval '(begin (use-modules (gnu services herd) + (srfi srfi-1) (ice-9 popen)) (current-output-port (open-file "/dev/console" "w0")) + (every + (lambda (role) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tA" "-c" + (string-append + "SELECT 1 FROM pg_database WHERE" + " datname='" role "'"))) + (output (get-string-all port))) + (close-pipe port) + (string-contains output "1"))) + '("root" "alice"))) + marionette)) + + (test-assert "database passwords are set" + (marionette-eval + '(begin + (use-modules (gnu services herd) + (ice-9 match) + (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (setgid (passwd:gid (getpwnam "alice"))) + (setuid (passwd:uid (getpw "alice"))) + (setenv "PGPASSWORD" "hello") (let* ((port (open-pipe* OPEN_READ - #$(file-append postgresql "/bin/psql") - "-tA" "-c" "SELECT 1 FROM pg_database WHERE - datname='root'")) + #$(file-append postgresql "/bin/psql") "-tA" "-c" + "SELECT 1 FROM pg_database WHERE datname='alice'")) (output (get-string-all port))) (close-pipe port) (string-contains output "1"))) base-commit: b413d1ea6ae5198becaaeed73495d5c0341e38f7 -- 2.49.0 From debbugs-submit-bounces@debbugs.gnu.org Tue Apr 08 16:57:21 2025 Received: (at 73196) by debbugs.gnu.org; 8 Apr 2025 20:57:21 +0000 Received: from localhost ([127.0.0.1]:35772 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1u2G0X-0006R8-BY for submit@debbugs.gnu.org; Tue, 08 Apr 2025 16:57:21 -0400 Received: from confino.investici.org ([2a11:7980:1::2:0]:30207) by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1u2G0R-0006Ql-SQ for 73196@debbugs.gnu.org; Tue, 08 Apr 2025 16:57:17 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1744145832; bh=IJs4R6A5w3ZUnymkBOnpMg6IDpKZvrswRsuqyGwieSA=; h=Date:Subject:From:To:References:In-Reply-To:From; b=nxlQRX4p9drty3YX4e3Q/1HTsC7bWeSGZi7WrcppGDWs8kp2ypmrC3sQg6/Ot6bTK STnXlHop62ildV4hqck4JyAzqJpR5oreJ7BNR8JeJLWvbAMsmV5c2FVTv7WKlEcexo SKmygK7MW85HIo+j8edIr3v5wzXvjBRRxZMNqnUk= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4ZXJL055Mtz122H for <73196@debbugs.gnu.org>; Tue, 8 Apr 2025 20:57:12 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4ZXJL04Zzvz122F for <73196@debbugs.gnu.org>; Tue, 8 Apr 2025 20:57:12 +0000 (UTC) Message-ID: <069ec834-9a93-4ee5-bdb6-5194973e89de@autistici.org> Date: Tue, 8 Apr 2025 22:57:12 +0200 MIME-Version: 1.0 User-Agent: Icedove Daily Subject: Re: [PATCH] services: postgresql-role: Add support for password files. From: paul To: 73196@debbugs.gnu.org References: <9fcbb384-3b89-487b-9a8f-4289b9f5e174@autistici.org> <651ac684-fb8a-4b20-b4ec-f3868fd95d29@autistici.org> <10862a5e-b767-4fd7-a512-7888d50c8674@autistici.org> Content-Language: en-US In-Reply-To: <10862a5e-b767-4fd7-a512-7888d50c8674@autistici.org> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Score: -0.0 (/) X-Debbugs-Envelope-To: 73196 X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) I'm sending a v6 fixing psql command. cheers, giacomo From debbugs-submit-bounces@debbugs.gnu.org Tue Apr 08 16:58:02 2025 Received: (at 73196) by debbugs.gnu.org; 8 Apr 2025 20:58:02 +0000 Received: from localhost ([127.0.0.1]:35775 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1u2G1B-0006SR-Ci for submit@debbugs.gnu.org; Tue, 08 Apr 2025 16:58:02 -0400 Received: from confino.investici.org ([2a11:7980:1::2:0]:55883) by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1u2G0r-0006Rj-O0 for 73196@debbugs.gnu.org; Tue, 08 Apr 2025 16:57:42 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1744145860; bh=FY5sOmudeJcVM/6mRcP7GN3snBVlhvzV5tEd4cvYgdM=; h=From:To:Cc:Subject:Date:From; b=DwMOEk67U2b2Qn8RhIa9cc+KHsrL47oELjA5pgXhPsfpSIa2lcpyTOs0GC/5/H52k Mh3eZwnuhzb+XO39sxAjZj3EV1AwswJvT0fLtiWOHomiJTPYhUZWGOrHm43SPojQhK QD7baR1NlJvnI9xoj8xkUldvBkli2h6i4+uGx2zo= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4ZXJLX66Bbz122G; Tue, 8 Apr 2025 20:57:40 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4ZXJLX54g1z122F; Tue, 8 Apr 2025 20:57:40 +0000 (UTC) From: Giacomo Leidi To: 73196@debbugs.gnu.org Subject: [PATCH v6] services: postgresql-role: Add support for password files. Date: Tue, 8 Apr 2025 22:57:26 +0200 Message-ID: <5b895115a5c8ff93242251a8d71718bc4e8b2fed.1744145846.git.goodoldpaul@autistici.org> X-Mailer: git-send-email 2.49.0 MIME-Version: 1.0 X-Debbugs-Cc: Ludovic Courtès , Maxim Cournoyer Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Spam-Score: -0.0 (/) X-Debbugs-Envelope-To: 73196 Cc: Giacomo Leidi X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) This commit adds a password-file to the postgresql-role field. It allows users to provision Postgres roles with a set password. * gnu/services/databases.scm (postgresql-role): Add password-file field; (postgresql-role-configuration): add requirement field; (postgresql-create-roles): add support for setting passwords from a file without leaking passwords to the command line; (postgresql-role-shepherd-service): add support for customizable requirements. * gnu/tests/databases.scm: Test it. * doc/guix.texi: Document it. Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585 --- doc/guix.texi | 15 +++++++++--- gnu/services/databases.scm | 48 +++++++++++++++++++++++++++++++++----- gnu/tests/databases.scm | 38 +++++++++++++++++++++++++++--- 3 files changed, 89 insertions(+), 12 deletions(-) diff --git a/doc/guix.texi b/doc/guix.texi index bee80cd4e2b..8b32557f76a 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -27588,9 +27588,10 @@ Database Services @lisp (service-extension postgresql-role-service-type - (const (postgresql-role - (name "alice") - (create-database? #t)))) + (const (list + (postgresql-role + (name "alice") + (create-database? #t))))) @end lisp @end defvar @@ -27613,6 +27614,10 @@ Database Services @item @code{create-database?} (default: @code{#f}) whether to create a database with the same name as the role. +@item @code{password-file} (default: @code{#f}) +A string representing the path of a file that contains the password to be set +for the role. + @item @code{encoding} (default: @code{"UTF8"}) The character set to use for storing text in the database. @@ -27641,6 +27646,10 @@ Database Services @item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) File name of the log file. +@item @code{requirement} (default: @code{'()}) (type: list-of-symbols) +Set additional Shepherd services dependencies to the provisioned +Shepherd service. + @item @code{roles} (default: @code{'()}) The initial PostgreSQL roles to create. @end table diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index 6d80376d90d..28b69bca3cb 100644 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -9,6 +9,7 @@ ;;; Copyright © 2020, 2022 Marius Bakke ;;; Copyright © 2021 David Larsson ;;; Copyright © 2021 Aljosha Papsch +;;; Copyright © 2025 Giacomo Leidi ;;; ;;; This file is part of GNU Guix. ;;; @@ -31,6 +32,7 @@ (define-module (gnu services databases) #:use-module (gnu system shadow) #:use-module (gnu packages admin) #:use-module (gnu packages base) + #:use-module (gnu packages bash) #:use-module (gnu packages databases) #:use-module (guix build-system trivial) #:use-module (guix build union) @@ -65,11 +67,13 @@ (define-module (gnu services databases) postgresql-role postgresql-role? postgresql-role-name + postgresql-role-password-file postgresql-role-permissions postgresql-role-create-database? postgresql-role-configuration postgresql-role-configuration? postgresql-role-configuration-host + postgresql-role-configuration-requirement postgresql-role-configuration-roles postgresql-role-service-type @@ -374,6 +378,8 @@ (define-record-type* postgresql-role make-postgresql-role postgresql-role? (name postgresql-role-name) ;string + (password-file postgresql-role-password-file ;string + (default #f)) (permissions postgresql-role-permissions (default '(createdb login))) ;list (create-database? postgresql-role-create-database? ;boolean @@ -392,6 +398,8 @@ (define-record-type* postgresql-role-configuration? (host postgresql-role-configuration-host ;string (default "/var/run/postgresql")) + (requirement postgresql-role-configuration-requirement ;list-of-symbols + (default '())) (log postgresql-role-configuration-log ;string (default "/var/log/postgresql_roles.log")) (roles postgresql-role-configuration-roles @@ -409,19 +417,36 @@ (define (postgresql-create-roles config) permissions) " "))) + (define (password-value role) + (string-append "password_" (postgresql-role-name role))) + + (define (role->password-variable role) + (define file-name + (postgresql-role-password-file role)) + (if (string? file-name) + ;; This way passwords do not leak to the command line + #~(string-append "-v \"" #$(password-value role) + "=$(" #+coreutils "/bin/cat " #$file-name ")\"") + "")) + (define (roles->queries roles) (apply mixed-text-file "queries" (append-map (lambda (role) (match-record role (name permissions create-database? encoding collation ctype - template) + template password-file) `("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) +,(if (and (string? password-file) + (not (string-null? password-file))) + (string-append + "\nPASSWORD :'" (password-value role) "'") + "") ";\n" ,@(if create-database? `("CREATE DATABASE \"" ,name "\"" @@ -436,20 +461,30 @@ (define (postgresql-create-roles config) (let ((host (postgresql-role-configuration-host config)) (roles (postgresql-role-configuration-roles config))) - #~(let ((psql #$(file-append postgresql "/bin/psql"))) - (list psql "-a" "-h" #$host "-f" #$(roles->queries roles))))) + (program-file "run-queries" + #~(let ((bash #$(file-append bash-minimal "/bin/bash")) + (psql #$(file-append postgresql "/bin/psql"))) + (define command + (string-append + "set -e; exec " psql " -a -h " #$host " -f " + #$(roles->queries roles) " " + (string-join + (list + #$@(map role->password-variable roles)) + " "))) + (execlp bash bash "-c" command))))) (define (postgresql-role-shepherd-service config) (match-record config - (log) + (log requirement) (list (shepherd-service - (requirement '(user-processes postgres)) + (requirement `(user-processes postgres ,@requirement)) (provision '(postgres-roles)) (one-shot? #t) (start #~(lambda args (zero? (spawn-command - #$(postgresql-create-roles config) + (list #$(postgresql-create-roles config)) #:user "postgres" #:group "postgres" ;; XXX: As of Shepherd 1.0.2, #:log-file is not @@ -468,6 +503,7 @@ (define postgresql-role-service-type (match-record config (host roles) (postgresql-role-configuration + (inherit config) (host host) (roles (append roles extended-roles)))))) (default-value (postgresql-role-configuration)) diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index fd5041344b6..c5da603565d 100644 --- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -142,6 +142,8 @@ (define %role-log-file (define %postgresql-os (simple-operating-system + (extra-special-file "/password" + (plain-file "password" "hello")) (service postgresql-service-type (postgresql-configuration (postgresql postgresql) @@ -158,6 +160,10 @@ (define %postgresql-os (roles (list (postgresql-role (name "root") + (create-database? #t)) + (postgresql-role + (name "alice") + (password-file "/password") (create-database? #t)))))))) (define (run-postgresql-test) @@ -230,14 +236,40 @@ (define (run-postgresql-test) (marionette-eval '(begin (use-modules (gnu services herd) + (srfi srfi-1) (ice-9 popen)) (current-output-port (open-file "/dev/console" "w0")) + (every + (lambda (role) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tA" "-c" + (string-append + "SELECT 1 FROM pg_database WHERE" + " datname='" role "'"))) + (output (get-string-all port))) + (close-pipe port) + (string-contains output "1"))) + '("root" "alice"))) + marionette)) + + (test-assert "database passwords are set" + (marionette-eval + '(begin + (use-modules (gnu services herd) + (ice-9 match) + (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (setgid (passwd:gid (getpwnam "alice"))) + (setuid (passwd:uid (getpw "alice"))) + (setenv "PGPASSWORD" "hello") (let* ((port (open-pipe* OPEN_READ - #$(file-append postgresql "/bin/psql") - "-tA" "-c" "SELECT 1 FROM pg_database WHERE - datname='root'")) + #$(file-append postgresql "/bin/psql") "-tA" "-c" + "SELECT 1 FROM pg_database WHERE datname='alice'")) (output (get-string-all port))) (close-pipe port) (string-contains output "1"))) base-commit: b413d1ea6ae5198becaaeed73495d5c0341e38f7 -- 2.49.0 From debbugs-submit-bounces@debbugs.gnu.org Fri Apr 25 09:27:39 2025 Received: (at 73196) by debbugs.gnu.org; 25 Apr 2025 13:27:39 +0000 Received: from localhost ([127.0.0.1]:48886 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1u8J5e-0003wZ-LJ for submit@debbugs.gnu.org; Fri, 25 Apr 2025 09:27:39 -0400 Received: from mail-pf1-x430.google.com ([2607:f8b0:4864:20::430]:61632) by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.84_2) (envelope-from ) id 1u8J5c-0003wG-HM for 73196@debbugs.gnu.org; Fri, 25 Apr 2025 09:27:37 -0400 Received: by mail-pf1-x430.google.com with SMTP id d2e1a72fcca58-736c3e7b390so2287185b3a.2 for <73196@debbugs.gnu.org>; Fri, 25 Apr 2025 06:27:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1745587650; x=1746192450; darn=debbugs.gnu.org; h=mime-version:user-agent:message-id:date:references:in-reply-to :subject:cc:to:from:from:to:cc:subject:date:message-id:reply-to; bh=t8OlXQHa/vzP7PtN8RfFi8YZjwRPH/Vvrx1+UQvswN0=; b=QrFa9MPbPle1u8Kic66b9oEipFKqy3lXOBDAn1XzgPYhujSUd00dhSO9uysEoZNXOk DYc1pygWwzPP6uXGCvQElwxuOH8ztNfKjUmoJ+Er5HxLNtIbVOVeNJsy1Qdcs1/jMvSU GWLxr3AZssAV0F3TxUTZJCTUVgLdrG9kVCuA9Sl1axyZcy1XVrLiUGttWb/vMiT2WN2n E89+JsxQVWeUk0siIqLpNplp3EcrJtTPRemGR9ZZFESheKG061AvcRVF2DFns2tTGTl3 JM0pVguAowrsQBnD1Dr3rWhdICxoQ1KsX0bMM5c79udz8x5d7L9Q36fiBzxOSFficyNi GJrA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1745587650; x=1746192450; h=mime-version:user-agent:message-id:date:references:in-reply-to :subject:cc:to:from:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=t8OlXQHa/vzP7PtN8RfFi8YZjwRPH/Vvrx1+UQvswN0=; b=V+Z11HA87wSuzMvCMh4oEJRPDLqse8PIgLnTjNl1v4Yz6Rv7Ni8aCTaxUay6Z0cKFc NtDavIQhJGZ5tkPcSSIzJkdSXiJfooVB9pAMYMwKQrP7I6gmI2NYd9y+bwyUGWm9N6mw zE+3XYF9Ed0Wf+27f8XTe58Cm3H/fZVCpKt1qNBUu5iqArTOBOcMYTOHTy+09ClzVlHz QB4OnVocBVJlCwCuf/35TEs0wmdYriOzpdptIzQbNJ6cFUcA76A1yKjKZUPSY1PI+Xu8 X+zppA+KcQZNFNOawfqErdnDQ2o+R1+QMgMKZzrp5nIrDaUIwGIu1D+lfMleJXcrGmuY N+fQ== X-Gm-Message-State: AOJu0Yyr3HGpkSjgjgxbrxzTemTibWljK8Z+QYexfbk0XLg9zqzRjSqW aZ0LEN2D4jYhc4yCjQVZmDuAkTbFOMeQcTMzR39Qt/jzz4IUn7pJ X-Gm-Gg: ASbGncvWc5rt2DkUL6tA7eD/1a+XsSxosrdDCZy44egcIS3fAgtgF3hNAqQu43gjh8W Jl1jMdkJNB4Jw6aTRDQ5SpIxojdEI//Vf6s7ASfjaZPxfoU/tkS7hwrWSgGHocYECP+pHiY9P4y 4GEudmEjwQhoY3NYgngLgzu2ADOJrIQLdoV2eeyq3+qc6lZ/rzSMt3gOBxvBq94mEcqa6QELqcy zUmnpDzXWYge/FrBGzZP3H4vxPkcpikrxvbztvF9Nf/LZfe7OYG89RjTkIOhiP2PdE5GMoA1k85 Pv+C2PByUC7yHMv/uvbfzzvvH27vBY9HCPtqMWk= X-Google-Smtp-Source: AGHT+IFSFf/9VcRCwJEQzk9WWoVlZoEOOrq8/M5jYPmI+mzZ/ULltcjmym/6ix3Pqy1cjyAiqvT7/A== X-Received: by 2002:a05:6a00:2e83:b0:736:fff2:9ac with SMTP id d2e1a72fcca58-73fd8f4e0d2mr3133896b3a.23.1745587650272; Fri, 25 Apr 2025 06:27:30 -0700 (PDT) Received: from terra ([2405:6586:be0:0:83c8:d31d:2cec:f542]) by smtp.gmail.com with ESMTPSA id d2e1a72fcca58-73e25a9a308sm3184684b3a.136.2025.04.25.06.27.28 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 25 Apr 2025 06:27:29 -0700 (PDT) From: Maxim Cournoyer To: Giacomo Leidi Subject: Re: bug#73196: [PATCH] services: postgresql-role: Add support for password files. In-Reply-To: <5b895115a5c8ff93242251a8d71718bc4e8b2fed.1744145846.git.goodoldpaul@autistici.org> (Giacomo Leidi's message of "Tue, 8 Apr 2025 22:57:26 +0200") References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@autistici.org> <5b895115a5c8ff93242251a8d71718bc4e8b2fed.1744145846.git.goodoldpaul@autistici.org> Date: Fri, 25 Apr 2025 22:27:27 +0900 Message-ID: <87v7qspe0w.fsf@gmail.com> User-Agent: Gnus/5.13 (Gnus v5.13) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: 0.0 (/) X-Debbugs-Envelope-To: 73196 Cc: 73196@debbugs.gnu.org, Ludovic =?utf-8?Q?Court=C3=A8s?= X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) Hi, Giacomo Leidi writes: > This commit adds a password-file to the postgresql-role field. It > allows users to provision Postgres roles with a set password. > > * gnu/services/databases.scm (postgresql-role): Add password-file field; > (postgresql-role-configuration): add requirement field; > (postgresql-create-roles): add support for setting passwords from a > file without leaking passwords to the command line; > (postgresql-role-shepherd-service): add support for customizable > requirements. > * gnu/tests/databases.scm: Test it. > * doc/guix.texi: Document it. Looks useful! Nitpick: Each change can be a sentence, starting with a capitalized letter and ending with a period. > Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585 > --- > doc/guix.texi | 15 +++++++++--- > gnu/services/databases.scm | 48 +++++++++++++++++++++++++++++++++----- > gnu/tests/databases.scm | 38 +++++++++++++++++++++++++++--- > 3 files changed, 89 insertions(+), 12 deletions(-) > > diff --git a/doc/guix.texi b/doc/guix.texi > index bee80cd4e2b..8b32557f76a 100644 > --- a/doc/guix.texi > +++ b/doc/guix.texi > @@ -27588,9 +27588,10 @@ Database Services > > @lisp > (service-extension postgresql-role-service-type > - (const (postgresql-role > - (name "alice") > - (create-database? #t)))) > + (const (list > + (postgresql-role > + (name "alice") > + (create-database? #t))))) Are you sure this is correct? If it's a bug fix, it's not listed in the changelog. > @end lisp > @end defvar > > @@ -27613,6 +27614,10 @@ Database Services > @item @code{create-database?} (default: @code{#f}) > whether to create a database with the same name as the role. > > +@item @code{password-file} (default: @code{#f}) > +A string representing the path of a file that contains the password to be set > +for the role. > + > @item @code{encoding} (default: @code{"UTF8"}) > The character set to use for storing text in the database. > > @@ -27641,6 +27646,10 @@ Database Services > @item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) > File name of the log file. > > +@item @code{requirement} (default: @code{'()}) (type: list-of-symbols) > +Set additional Shepherd services dependencies to the provisioned > +Shepherd service. It seems the canonical/most conventional configuration field name to use for this is 'shepherd-requirement', not 'requirement'. [...] > postgresql-role? > postgresql-role-name > + postgresql-role-password-file > postgresql-role-permissions > postgresql-role-create-database? > postgresql-role-configuration > postgresql-role-configuration? > postgresql-role-configuration-host > + postgresql-role-configuration-requirement > postgresql-role-configuration-roles > > postgresql-role-service-type > @@ -374,6 +378,8 @@ (define-record-type* > postgresql-role make-postgresql-role > postgresql-role? > (name postgresql-role-name) ;string > + (password-file postgresql-role-password-file ;string > + (default #f)) > (permissions postgresql-role-permissions > (default '(createdb login))) ;list > (create-database? postgresql-role-create-database? ;boolean > @@ -392,6 +398,8 @@ (define-record-type* > postgresql-role-configuration? > (host postgresql-role-configuration-host ;string > (default "/var/run/postgresql")) > + (requirement postgresql-role-configuration-requirement ;list-of-symbols > + (default '())) as mentioned above, this should be 'shepherd-requirement'. > (log postgresql-role-configuration-log ;string > (default "/var/log/postgresql_roles.log")) > (roles postgresql-role-configuration-roles > @@ -409,19 +417,36 @@ (define (postgresql-create-roles config) > permissions) > " "))) > > + (define (password-value role) > + (string-append "password_" (postgresql-role-name role))) > + > + (define (role->password-variable role) > + (define file-name > + (postgresql-role-password-file role)) I'd maybe use a let here, just for stylistic preferences. > + (if (string? file-name) > + ;; This way passwords do not leak to the command line Please use complete sentences, including the trailing period, for stand-alone comments. > + #~(string-append "-v \"" #$(password-value role) > + "=$(" #+coreutils "/bin/cat " #$file-name ")\"") Are you sure this should use ungexp-native (#+) on coreutils? If this runs in a snippet, it should use the regular ungexp (#$). It could also use file-append. Only if it runs as part of a derivation build should it use #+. > + "")) > + > (define (roles->queries roles) > (apply mixed-text-file "queries" > (append-map > (lambda (role) > (match-record role > (name permissions create-database? encoding collation ctype > - template) > + template password-file) > `("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) > +,(if (and (string? password-file) > + (not (string-null? password-file))) Since you already check for the string-null? case, perhaps the default password value should be "" instead of #f? And then you could do away with the string? check (though ideally there could be a sanitizer to verify that a string is passed -- perhaps best leaving it for the day this gets ported to define-configuration, which auto-generates sanitizers based on the type specified for the field in its form. > + (string-append > + "\nPASSWORD :'" (password-value role) "'") > + "") > ";\n" > ,@(if create-database? > `("CREATE DATABASE \"" ,name "\"" > @@ -436,20 +461,30 @@ (define (postgresql-create-roles config) > > (let ((host (postgresql-role-configuration-host config)) > (roles (postgresql-role-configuration-roles config))) > - #~(let ((psql #$(file-append postgresql "/bin/psql"))) > - (list psql "-a" "-h" #$host "-f" #$(roles->queries roles))))) > + (program-file "run-queries" > + #~(let ((bash #$(file-append bash-minimal "/bin/bash")) > + (psql #$(file-append postgresql "/bin/psql"))) > + (define command > + (string-append > + "set -e; exec " psql " -a -h " #$host " -f " > + #$(roles->queries roles) " " > + (string-join > + (list > + #$@(map role->password-variable roles)) > + " "))) > + (execlp bash bash "-c" command))))) > > (define (postgresql-role-shepherd-service config) > (match-record config > - (log) > + (log requirement) > (list (shepherd-service > - (requirement '(user-processes postgres)) > + (requirement `(user-processes postgres ,@requirement)) I see two styles being used in Guix services: 'shepherd-requirement' extend the requirements, or overrides it, with the 'user-processes at least normally found in the default values. I think at this point in time the later fits better in Guix (think of arguments such as #:modules and #:imported-modules, etc. the user has full control on them, for the better and worst). > (provision '(postgres-roles)) > (one-shot? #t) > (start > #~(lambda args > (zero? (spawn-command > - #$(postgresql-create-roles config) > + (list #$(postgresql-create-roles config)) Why is this change now necessary? I didn't follow. > #:user "postgres" > #:group "postgres" > ;; XXX: As of Shepherd 1.0.2, #:log-file is not > @@ -468,6 +503,7 @@ (define postgresql-role-service-type > (match-record config > (host roles) > (postgresql-role-configuration > + (inherit config) and this one? Was it a preexisting bug? > (host host) > (roles (append roles extended-roles)))))) > (default-value (postgresql-role-configuration)) > diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm > index fd5041344b6..c5da603565d 100644 > --- a/gnu/tests/databases.scm > +++ b/gnu/tests/databases.scm > @@ -142,6 +142,8 @@ (define %role-log-file > > (define %postgresql-os > (simple-operating-system > + (extra-special-file "/password" > + (plain-file "password" "hello")) > (service postgresql-service-type > (postgresql-configuration > (postgresql postgresql) > @@ -158,6 +160,10 @@ (define %postgresql-os > (roles > (list (postgresql-role > (name "root") > + (create-database? #t)) > + (postgresql-role > + (name "alice") > + (password-file "/password") > (create-database? #t)))))))) > > (define (run-postgresql-test) > @@ -230,14 +236,40 @@ (define (run-postgresql-test) > (marionette-eval > '(begin > (use-modules (gnu services herd) > + (srfi srfi-1) > (ice-9 popen)) > (current-output-port > (open-file "/dev/console" "w0")) > + (every > + (lambda (role) > + (let* ((port (open-pipe* > + OPEN_READ > + #$(file-append postgresql "/bin/psql") > + "-tA" "-c" > + (string-append > + "SELECT 1 FROM pg_database WHERE" > + " datname='" role "'"))) > + (output (get-string-all port))) > + (close-pipe port) > + (string-contains output "1"))) > + '("root" "alice"))) > + marionette)) > + > + (test-assert "database passwords are set" > + (marionette-eval > + '(begin > + (use-modules (gnu services herd) > + (ice-9 match) > + (ice-9 popen)) > + (current-output-port > + (open-file "/dev/console" "w0")) > + (setgid (passwd:gid (getpwnam "alice"))) > + (setuid (passwd:uid (getpw "alice"))) > + (setenv "PGPASSWORD" "hello") > (let* ((port (open-pipe* > OPEN_READ > - #$(file-append postgresql "/bin/psql") > - "-tA" "-c" "SELECT 1 FROM pg_database WHERE > - datname='root'")) > + #$(file-append postgresql "/bin/psql") "-tA" "-c" > + "SELECT 1 FROM pg_database WHERE datname='alice'")) Nice to have tests! Could you please send a v7 taking care of my comments? Then I guess it'd be good to go. -- Thanks, Maxim From debbugs-submit-bounces@debbugs.gnu.org Sun Apr 27 11:07:32 2025 Received: (at 73196) by debbugs.gnu.org; 27 Apr 2025 15:07:32 +0000 Received: from localhost ([127.0.0.1]:43533 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1u93bP-0006wv-A1 for submit@debbugs.gnu.org; Sun, 27 Apr 2025 11:07:32 -0400 Received: from latitanza.investici.org ([82.94.249.234]:62693) by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1u93bL-0006wg-EH for 73196@debbugs.gnu.org; Sun, 27 Apr 2025 11:07:29 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1745766445; bh=LO+ODbuLmtkeukczzgtNN8SW3LsGZawEp7wrYEea/7g=; h=Date:Subject:To:Cc:References:From:In-Reply-To:From; b=NiJw0X7541suYy4hKZ0fI9IumrLZ9TFECv/5pEAwYDjS+7Xm02cuYMngMIjJQN7wP PrpOpLz+I9I+vAOM/9edEwAYi4063eKvH5GRJcRpW9j4ktyE+2xZ2LbsJUL/QGnCZL KpQJQgmowNPSI3RAAbJ8FDg8VjuVc3LjvRYK4+MQ= Received: from mx3.investici.org (unknown [127.0.0.1]) by latitanza.investici.org (Postfix) with ESMTP id 4Zlqgd1qrrzGp40; Sun, 27 Apr 2025 15:07:25 +0000 (UTC) Received: from [82.94.249.234] (mx3.investici.org [82.94.249.234]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4Zlqgc50tXzGp3x; Sun, 27 Apr 2025 15:07:24 +0000 (UTC) Content-Type: multipart/alternative; boundary="------------OVCR37pzCqyB4smZZ7mtlAUw" Message-ID: <1c2945a7-79c3-4abe-81ff-c0f2b2fbe62a@autistici.org> Date: Sun, 27 Apr 2025 17:07:01 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: bug#73196: [PATCH] services: postgresql-role: Add support for password files. To: Maxim Cournoyer References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@autistici.org> <5b895115a5c8ff93242251a8d71718bc4e8b2fed.1744145846.git.goodoldpaul@autistici.org> <87v7qspe0w.fsf@gmail.com> Content-Language: en-US From: paul In-Reply-To: <87v7qspe0w.fsf@gmail.com> X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 73196 Cc: 73196@debbugs.gnu.org, =?UTF-8?Q?Ludovic_Court=C3=A8s?= X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.7 (-) This is a multi-part message in MIME format. --------------OVCR37pzCqyB4smZZ7mtlAUw Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi Maxim, On 4/25/25 3:27 PM, Maxim Cournoyer wrote: > Hi, > > Giacomo Leidi writes: > >> This commit adds a password-file to the postgresql-role field. It >> allows users to provision Postgres roles with a set password. >> >> * gnu/services/databases.scm (postgresql-role): Add password-file field; >> (postgresql-role-configuration): add requirement field; >> (postgresql-create-roles): add support for setting passwords from a >> file without leaking passwords to the command line; >> (postgresql-role-shepherd-service): add support for customizable >> requirements. >> * gnu/tests/databases.scm: Test it. >> * doc/guix.texi: Document it. > Looks useful! > > Nitpick: Each change can be a sentence, starting with a capitalized > letter and ending with a period. Should be fixed now. > >> Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585 >> --- >> doc/guix.texi | 15 +++++++++--- >> gnu/services/databases.scm | 48 +++++++++++++++++++++++++++++++++----- >> gnu/tests/databases.scm | 38 +++++++++++++++++++++++++++--- >> 3 files changed, 89 insertions(+), 12 deletions(-) >> >> diff --git a/doc/guix.texi b/doc/guix.texi >> index bee80cd4e2b..8b32557f76a 100644 >> --- a/doc/guix.texi >> +++ b/doc/guix.texi >> @@ -27588,9 +27588,10 @@ Database Services >> >> @lisp >> (service-extension postgresql-role-service-type >> - (const (postgresql-role >> - (name "alice") >> - (create-database? #t)))) >> + (const (list >> + (postgresql-role >> + (name "alice") >> + (create-database? #t))))) > Are you sure this is correct? If it's a bug fix, it's not listed in the > changelog. Yes I am sure, the service type has (compose concatenate) this is now mentioned in the changelog. >> postgresql-role? >> postgresql-role-name >> + postgresql-role-password-file >> postgresql-role-permissions >> postgresql-role-create-database? >> postgresql-role-configuration >> postgresql-role-configuration? >> postgresql-role-configuration-host >> + postgresql-role-configuration-requirement >> postgresql-role-configuration-roles >> >> postgresql-role-service-type >> @@ -374,6 +378,8 @@ (define-record-type* >> postgresql-role make-postgresql-role >> postgresql-role? >> (name postgresql-role-name) ;string >> + (password-file postgresql-role-password-file ;string >> + (default #f)) >> (permissions postgresql-role-permissions >> (default '(createdb login))) ;list >> (create-database? postgresql-role-create-database? ;boolean >> @@ -392,6 +398,8 @@ (define-record-type* >> postgresql-role-configuration? >> (host postgresql-role-configuration-host ;string >> (default "/var/run/postgresql")) >> + (requirement postgresql-role-configuration-requirement ;list-of-symbols >> + (default '())) > as mentioned above, this should be 'shepherd-requirement'. I know at least two examples that are already in master which use 'requirement' (I may be biased as I authored both of them): the restic-backup-service-type and the oci-container-service-type. If you don't find this to be a blocker I would defer this change to a specific commit setting a convention for the whole codebase after an analysis of which services use simply 'requirement' and which use 'shepherd-requirement'. Is it ok for you? >> (log postgresql-role-configuration-log ;string >> (default "/var/log/postgresql_roles.log")) >> (roles postgresql-role-configuration-roles >> @@ -409,19 +417,36 @@ (define (postgresql-create-roles config) >> permissions) >> " "))) >> >> + (define (password-value role) >> + (string-append "password_" (postgresql-role-name role))) >> + >> + (define (role->password-variable role) >> + (define file-name >> + (postgresql-role-password-file role)) > I'd maybe use a let here, just for stylistic preferences. I changed the define to let > >> + (if (string? file-name) >> + ;; This way passwords do not leak to the command line > Please use complete sentences, including the trailing period, for > stand-alone comments. I added the period > >> + #~(string-append "-v \"" #$(password-value role) >> + "=$(" #+coreutils "/bin/cat " #$file-name ")\"") > Are you sure this should use ungexp-native (#+) on coreutils? If this > runs in a snippet, it should use the regular ungexp (#$). It could also > use file-append. Only if it runs as part of a derivation build should > it use #+. this runs at system activation so in my understanding it should be #$ . Thank you for explaining the difference, I was not aware :) > >> + "")) >> + >> (define (roles->queries roles) >> (apply mixed-text-file "queries" >> (append-map >> (lambda (role) >> (match-record role >> (name permissions create-database? encoding collation ctype >> - template) >> + template password-file) >> `("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) >> +,(if (and (string? password-file) >> + (not (string-null? password-file))) > Since you already check for the string-null? case, perhaps the default > password value should be "" instead of #f? And then you could do away > with the string? check (though ideally there could be a sanitizer to > verify that a string is passed -- perhaps best leaving it for the day > this gets ported to define-configuration, which auto-generates sanitizers > based on the type specified for the field in its form. in its ideal form (using define-configuration) this would be a 'maybe-string' type, as it represents an optional path. if you don't find this a blocker I would leave this for a later change where we could use 'maybe-value-set?' and leave the type check to a sanitizer. What do you think? >> + (string-append >> + "\nPASSWORD :'" (password-value role) "'") >> + "") >> ";\n" >> ,@(if create-database? >> `("CREATE DATABASE \"" ,name "\"" >> @@ -436,20 +461,30 @@ (define (postgresql-create-roles config) >> >> (let ((host (postgresql-role-configuration-host config)) >> (roles (postgresql-role-configuration-roles config))) >> - #~(let ((psql #$(file-append postgresql "/bin/psql"))) >> - (list psql "-a" "-h" #$host "-f" #$(roles->queries roles))))) >> + (program-file "run-queries" >> + #~(let ((bash #$(file-append bash-minimal "/bin/bash")) >> + (psql #$(file-append postgresql "/bin/psql"))) >> + (define command >> + (string-append >> + "set -e; exec " psql " -a -h " #$host " -f " >> + #$(roles->queries roles) " " >> + (string-join >> + (list >> + #$@(map role->password-variable roles)) >> + " "))) >> + (execlp bash bash "-c" command))))) >> >> (define (postgresql-role-shepherd-service config) >> (match-record config >> - (log) >> + (log requirement) >> (list (shepherd-service >> - (requirement '(user-processes postgres)) >> + (requirement `(user-processes postgres ,@requirement)) > I see two styles being used in Guix services: 'shepherd-requirement' > extend the requirements, or overrides it, with the 'user-processes at > least normally found in the default values. I think at this point in > time the later fits better in Guix (think of arguments such as #:modules > and #:imported-modules, etc. the user has full control on them, for the > better and worst). Thank you for pointing this out, I created a %postgresql-role-shepherd-requirement value and exported it to allow users which want to override the Guix defaults, while retaining them can simply append to this list. > >> (provision '(postgres-roles)) >> (one-shot? #t) >> (start >> #~(lambda args >> (zero? (spawn-command >> - #$(postgresql-create-roles config) >> + (list #$(postgresql-create-roles config)) > Why is this change now necessary? I didn't follow. the return value of postgresql-create-roles changed, before this change it returned a list containing a psql command line, after this change it returns a gexp which will evaluate to the entrypoint responsible for reading password files and then calling psql. > >> #:user "postgres" >> #:group "postgres" >> ;; XXX: As of Shepherd 1.0.2, #:log-file is not >> @@ -468,6 +503,7 @@ (define postgresql-role-service-type >> (match-record config >> (host roles) >> (postgresql-role-configuration >> + (inherit config) > and this one? Was it a preexisting bug? Yes, for example without this change we would lose the 'log' field's value upon extension. > >> (host host) >> (roles (append roles extended-roles)))))) >> (default-value (postgresql-role-configuration)) >> diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm >> index fd5041344b6..c5da603565d 100644 >> --- a/gnu/tests/databases.scm >> +++ b/gnu/tests/databases.scm >> @@ -142,6 +142,8 @@ (define %role-log-file >> >> (define %postgresql-os >> (simple-operating-system >> + (extra-special-file "/password" >> + (plain-file "password" "hello")) >> (service postgresql-service-type >> (postgresql-configuration >> (postgresql postgresql) >> @@ -158,6 +160,10 @@ (define %postgresql-os >> (roles >> (list (postgresql-role >> (name "root") >> + (create-database? #t)) >> + (postgresql-role >> + (name "alice") >> + (password-file "/password") >> (create-database? #t)))))))) >> >> (define (run-postgresql-test) >> @@ -230,14 +236,40 @@ (define (run-postgresql-test) >> (marionette-eval >> '(begin >> (use-modules (gnu services herd) >> + (srfi srfi-1) >> (ice-9 popen)) >> (current-output-port >> (open-file "/dev/console" "w0")) >> + (every >> + (lambda (role) >> + (let* ((port (open-pipe* >> + OPEN_READ >> + #$(file-append postgresql "/bin/psql") >> + "-tA" "-c" >> + (string-append >> + "SELECT 1 FROM pg_database WHERE" >> + " datname='" role "'"))) >> + (output (get-string-all port))) >> + (close-pipe port) >> + (string-contains output "1"))) >> + '("root" "alice"))) >> + marionette)) >> + >> + (test-assert "database passwords are set" >> + (marionette-eval >> + '(begin >> + (use-modules (gnu services herd) >> + (ice-9 match) >> + (ice-9 popen)) >> + (current-output-port >> + (open-file "/dev/console" "w0")) >> + (setgid (passwd:gid (getpwnam "alice"))) >> + (setuid (passwd:uid (getpw "alice"))) >> + (setenv "PGPASSWORD" "hello") >> (let* ((port (open-pipe* >> OPEN_READ >> - #$(file-append postgresql "/bin/psql") >> - "-tA" "-c" "SELECT 1 FROM pg_database WHERE >> - datname='root'")) >> + #$(file-append postgresql "/bin/psql") "-tA" "-c" >> + "SELECT 1 FROM pg_database WHERE datname='alice'")) > Nice to have tests! Could you please send a v7 taking care of my > comments? Then I guess it'd be good to go. I should have addressed all your comments, I'm about to send a v7. Thank you for your help! cheers giacomo --------------OVCR37pzCqyB4smZZ7mtlAUw Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Hi Maxim,

On 4/25/25 3:27 PM, Maxim Cournoyer wrote:
Hi,

Giacomo Leidi <goodoldpaul@autistici.org> writes:


This commit adds a password-file to the postgresql-role field.  It
allows users to provision Postgres roles with a set password.

* gnu/services/databases.scm (postgresql-role): Add password-file field;
(postgresql-role-configuration): add requirement field;
(postgresql-create-roles): add support for setting passwords from a
file without leaking passwords to the command line;
(postgresql-role-shepherd-service): add support for customizable
requirements.
* gnu/tests/databases.scm: Test it.
* doc/guix.texi: Document it.

Looks useful!

Nitpick: Each change can be a sentence, starting with a capitalized
letter and ending with a period.
Should be fixed now.


Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585
---
 doc/guix.texi              | 15 +++++++++---
 gnu/services/databases.scm | 48 +++++++++++++++++++++++++++++++++-----
 gnu/tests/databases.scm    | 38 +++++++++++++++++++++++++++---
 3 files changed, 89 insertions(+), 12 deletions(-)

diff --git a/doc/guix.texi b/doc/guix.texi
index bee80cd4e2b..8b32557f76a 100644
--- a/doc/guix.texi
+++ b/doc/guix.texi
@@ -27588,9 +27588,10 @@ Database Services
 
 @lisp
 (service-extension postgresql-role-service-type
-                   (const (postgresql-role
-                           (name "alice")
-                           (create-database? #t))))
+                   (const (list
+                           (postgresql-role
+                            (name "alice")
+                            (create-database? #t)))))

Are you sure this is correct?  If it's a bug fix, it's not listed in the
changelog.

Yes I am sure, the service type has


(compose concatenate)


this is now mentioned in the changelog. 

             postgresql-role?
             postgresql-role-name
+            postgresql-role-password-file
             postgresql-role-permissions
             postgresql-role-create-database?
             postgresql-role-configuration
             postgresql-role-configuration?
             postgresql-role-configuration-host
+            postgresql-role-configuration-requirement
             postgresql-role-configuration-roles
 
             postgresql-role-service-type
@@ -374,6 +378,8 @@ (define-record-type* <postgresql-role>
   postgresql-role make-postgresql-role
   postgresql-role?
   (name             postgresql-role-name) ;string
+  (password-file    postgresql-role-password-file  ;string
+                    (default #f))
   (permissions      postgresql-role-permissions
                     (default '(createdb login))) ;list
   (create-database? postgresql-role-create-database?  ;boolean
@@ -392,6 +398,8 @@ (define-record-type* <postgresql-role-configuration>
   postgresql-role-configuration?
   (host             postgresql-role-configuration-host ;string
                     (default "/var/run/postgresql"))
+  (requirement      postgresql-role-configuration-requirement ;list-of-symbols
+                    (default '()))

as mentioned above, this should be 'shepherd-requirement'.
I know at least two examples that are already in master which use 'requirement' (I may be biased as I authored both of them): the restic-backup-service-type and the oci-container-service-type. If you don't find this to be a blocker I would defer this change to a specific commit setting a convention for the whole codebase after an analysis of which services use simply 'requirement' and which use 'shepherd-requirement'. Is it ok for you? 

      

        
   (log              postgresql-role-configuration-log ;string
                     (default "/var/log/postgresql_roles.log"))
   (roles            postgresql-role-configuration-roles
@@ -409,19 +417,36 @@ (define (postgresql-create-roles config)
                                permissions)
                    " ")))
 
+  (define (password-value role)
+    (string-append "password_" (postgresql-role-name role)))
+
+  (define (role->password-variable role)
+    (define file-name
+      (postgresql-role-password-file role))


      

      
I'd maybe use a let here, just for stylistic preferences.

    

    I changed the define to let

    

      



      

        
+    (if (string? file-name)
+        ;; This way passwords do not leak to the command line


      

      
Please use complete sentences, including the trailing period, for
stand-alone comments.

    

    I added the period

    

      



      

        
+        #~(string-append "-v \"" #$(password-value role)
+                         "=$(" #+coreutils "/bin/cat " #$file-name ")\"")


      

      
Are you sure this should use ungexp-native (#+) on coreutils?  If this
runs in a snippet, it should use the regular ungexp (#$). It could also
use file-append.  Only if it runs as part of a derivation build should
it use #+.

    

    this runs at system activation so in my understanding it should be
    #$ . Thank you for explaining the difference, I was not aware :)

    

      



      

        
+        ""))
+
   (define (roles->queries roles)
     (apply mixed-text-file "queries"
            (append-map
             (lambda (role)
               (match-record role <postgresql-role>
                 (name permissions create-database? encoding collation ctype
-                      template)
+                      template password-file)
                 `("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)
+,(if (and (string? password-file)
+          (not (string-null? password-file)))


      

      
Since you already check for the string-null? case, perhaps the default
password value should be "" instead of #f?  And then you could do away
with the string? check (though ideally there could be a sanitizer to
verify that a string is passed -- perhaps best leaving it for the day
this gets ported to define-configuration, which auto-generates sanitizers
based on the type specified for the field in its form.


    

    in its ideal form (using define-configuration) this would be a
    'maybe-string' type, as it represents an optional path. if you don't
    find this a blocker I would leave this for a later change where we
    could use 'maybe-value-set?' and leave the type check to a
    sanitizer. What do you think?

    

      

      

        
+     (string-append
+      "\nPASSWORD :'" (password-value role) "'")
+     "")
 ";\n"
 ,@(if create-database?
       `("CREATE DATABASE \"" ,name "\""
@@ -436,20 +461,30 @@ (define (postgresql-create-roles config)
 
   (let ((host (postgresql-role-configuration-host config))
         (roles (postgresql-role-configuration-roles config)))
-    #~(let ((psql #$(file-append postgresql "/bin/psql")))
-        (list psql "-a" "-h" #$host "-f" #$(roles->queries roles)))))
+    (program-file "run-queries"
+      #~(let ((bash #$(file-append bash-minimal "/bin/bash"))
+              (psql #$(file-append postgresql "/bin/psql")))
+          (define command
+            (string-append
+             "set -e; exec " psql " -a -h " #$host " -f "
+             #$(roles->queries roles) " "
+             (string-join
+              (list
+               #$@(map role->password-variable roles))
+              " ")))
+          (execlp bash bash "-c" command)))))
 
 (define (postgresql-role-shepherd-service config)
   (match-record config <postgresql-role-configuration>
-    (log)
+    (log requirement)
     (list (shepherd-service
-           (requirement '(user-processes postgres))
+           (requirement `(user-processes postgres ,@requirement))


      

      
I see two styles being used in Guix services: 'shepherd-requirement'
extend the requirements, or overrides it, with the 'user-processes at
least normally found in the default values.  I think at this point in
time the later fits better in Guix (think of arguments such as #:modules
and #:imported-modules, etc.  the user has full control on them, for the
better and worst).


    

    Thank you for pointing this out, I created a
    %postgresql-role-shepherd-requirement value and exported it to allow
    users which want to override the Guix defaults, while retaining them
    can simply append to this list.

    

      
            

      

        
            (provision '(postgres-roles))
            (one-shot? #t)
            (start
             #~(lambda args
                 (zero? (spawn-command
-                        #$(postgresql-create-roles config)
+                        (list #$(postgresql-create-roles config))


      

      
Why is this change now necessary?  I didn't follow.

    

    
the return value of postgresql-create-roles changed, before this
      change it returned a list containing a psql command line, after
      this change it returns a gexp which will evaluate to the
      entrypoint responsible for reading password files and then calling
      psql.

    

    

      



      

        
                         #:user "postgres"
                         #:group "postgres"
                         ;; XXX: As of Shepherd 1.0.2, #:log-file is not
@@ -468,6 +503,7 @@ (define postgresql-role-service-type
                           (match-record config <postgresql-role-configuration>
                             (host roles)
                             (postgresql-role-configuration
+                             (inherit config)


      

      
and this one?  Was it a preexisting bug?

    

    Yes, for example without this change we would lose the 'log' field's
    value upon extension.

    

      



      

        
                              (host host)
                              (roles (append roles extended-roles))))))
                 (default-value (postgresql-role-configuration))
diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm
index fd5041344b6..c5da603565d 100644
--- a/gnu/tests/databases.scm
+++ b/gnu/tests/databases.scm
@@ -142,6 +142,8 @@ (define %role-log-file
 
 (define %postgresql-os
   (simple-operating-system
+   (extra-special-file "/password"
+                       (plain-file "password" "hello"))
    (service postgresql-service-type
             (postgresql-configuration
              (postgresql postgresql)
@@ -158,6 +160,10 @@ (define %postgresql-os
              (roles
               (list (postgresql-role
                      (name "root")
+                     (create-database? #t))
+                    (postgresql-role
+                     (name "alice")
+                     (password-file "/password")
                      (create-database? #t))))))))
 
 (define (run-postgresql-test)
@@ -230,14 +236,40 @@ (define (run-postgresql-test)


      

      

      

        
             (marionette-eval
              '(begin
                 (use-modules (gnu services herd)
+                             (srfi srfi-1)
                              (ice-9 popen))
                 (current-output-port
                  (open-file "/dev/console" "w0"))
+                (every
+                 (lambda (role)
+                   (let* ((port (open-pipe*
+                                 OPEN_READ
+                                 #$(file-append postgresql "/bin/psql")
+                                 "-tA" "-c"
+                                 (string-append
+                                  "SELECT 1 FROM pg_database WHERE"
+                                  " datname='" role "'")))
+                          (output (get-string-all port)))
+                     (close-pipe port)
+                     (string-contains output "1")))
+                 '("root" "alice")))
+             marionette))
+
+          (test-assert "database passwords are set"
+            (marionette-eval
+             '(begin
+                (use-modules (gnu services herd)
+                             (ice-9 match)
+                             (ice-9 popen))
+                (current-output-port
+                 (open-file "/dev/console" "w0"))
+                (setgid (passwd:gid (getpwnam "alice")))
+                (setuid (passwd:uid (getpw "alice")))
+                (setenv "PGPASSWORD" "hello")
                 (let* ((port (open-pipe*
                               OPEN_READ
-                              #$(file-append postgresql "/bin/psql")
-                              "-tA" "-c" "SELECT 1 FROM pg_database WHERE
- datname='root'"))
+                              #$(file-append postgresql "/bin/psql") "-tA" "-c"
+                              "SELECT 1 FROM pg_database WHERE datname='alice'"))


      

      
Nice to have tests!  Could you please send a v7 taking care of my
comments?  Then I guess it'd be good to go.


    

    


    

    
I should have addressed all your comments, I'm about to send a
      v7. Thank you for your help!

    


    

    
cheers

    

    
giacomo

    

  


--------------OVCR37pzCqyB4smZZ7mtlAUw--




From debbugs-submit-bounces@debbugs.gnu.org Sun Apr 27 11:13:20 2025
Received: (at 73196) by debbugs.gnu.org; 27 Apr 2025 15:13:20 +0000
Received: from localhost ([127.0.0.1]:43566 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from )
	id 1u93h1-0007IS-79
	for submit@debbugs.gnu.org; Sun, 27 Apr 2025 11:13:20 -0400
Received: from latitanza.investici.org ([82.94.249.234]:48279)
 by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
 (Exim 4.84_2) (envelope-from )
 id 1u93gx-0007IG-U6
 for 73196@debbugs.gnu.org; Sun, 27 Apr 2025 11:13:16 -0400
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org;
 s=stigmate; t=1745766795;
 bh=mKDxSHxmZGJCZWsYRzzNhb7XqhXNi8pYhhhVAvJnGqQ=;
 h=From:To:Cc:Subject:Date:From;
 b=iLMJa3ZGGk3O3oQixp24rHE5K7Q8dmV/GzXctkVzt93e43d3FG1ZDL+FzcmdeFMvd
 YPtomOpx3YlOJSQpR6NxxQ/HL+NZRMkX85Fsz45Vtwi55kKD2PNG+MqQenYAAjUvvf
 3Rq1FhOhbW69qcQfp3gvIocdhZuxXeTY0emD5kK0=
Received: from mx3.investici.org (unknown [127.0.0.1])
 by latitanza.investici.org (Postfix) with ESMTP id 4ZlqpM117CzGp40;
 Sun, 27 Apr 2025 15:13:15 +0000 (UTC)
Received: from [82.94.249.234] (mx3.investici.org [82.94.249.234])
 (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with
 ESMTPSA id 4ZlqpD2jPkzGp2d; Sun, 27 Apr 2025 15:13:07 +0000 (UTC)
From: Giacomo Leidi 
To: 73196@debbugs.gnu.org
Subject: [PATCH v7] services: postgresql-role: Add support for password files.
Date: Sun, 27 Apr 2025 17:12:54 +0200
Message-ID: <3109ae6152f4f40edb8722b82b57040ef7b383e3.1745766774.git.goodoldpaul@autistici.org>
X-Mailer: git-send-email 2.49.0
MIME-Version: 1.0
X-Debbugs-Cc: Ludovic Courtès , Maxim Cournoyer 
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Spam-Score: -0.7 (/)
X-Debbugs-Envelope-To: 73196
Cc: Giacomo Leidi 
X-BeenThere: debbugs-submit@debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: 
List-Unsubscribe: , 
 
List-Archive: 
List-Post: 
List-Help: 
List-Subscribe: , 
 
Errors-To: debbugs-submit-bounces@debbugs.gnu.org
Sender: "Debbugs-submit" 
X-Spam-Score: -1.7 (-)

This commit adds a password-file to the postgresql-role field.  It
allows users to provision Postgres roles with a set password.

* gnu/services/databases.scm (postgresql-role): Add password-file field.
(postgresql-role-configuration): Add requirement field.
(postgresql-create-roles): Add support for setting passwords from a
file without leaking passwords to the command line.
(postgresql-role-shepherd-service): Add support for customizable
requirements.
* gnu/tests/databases.scm: Test it.
* doc/guix.texi: Document the new field and fix the extension point example.

Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585
---
 doc/guix.texi              | 15 ++++++++---
 gnu/services/databases.scm | 52 +++++++++++++++++++++++++++++++++-----
 gnu/tests/databases.scm    | 38 +++++++++++++++++++++++++---
 3 files changed, 93 insertions(+), 12 deletions(-)

diff --git a/doc/guix.texi b/doc/guix.texi
index 7b418a4089..ca690c8ace 100644
--- a/doc/guix.texi
+++ b/doc/guix.texi
@@ -27741,9 +27741,10 @@ Database Services
 
 @lisp
 (service-extension postgresql-role-service-type
-                   (const (postgresql-role
-                           (name "alice")
-                           (create-database? #t))))
+                   (const (list
+                           (postgresql-role
+                            (name "alice")
+                            (create-database? #t)))))
 @end lisp
 @end defvar
 
@@ -27766,6 +27767,10 @@ Database Services
 @item @code{create-database?} (default: @code{#f})
 whether to create a database with the same name as the role.
 
+@item @code{password-file} (default: @code{#f})
+A string representing the path of a file that contains the password to be set
+for the role.
+
 @item @code{encoding} (default: @code{"UTF8"})
 The character set to use for storing text in the database.
 
@@ -27794,6 +27799,10 @@ Database Services
 @item @code{log} (default: @code{"/var/log/postgresql_roles.log"})
 File name of the log file.
 
+@item @code{requirement} (default: @code{'(user-processes postgres)}) (type: list-of-symbols)
+Set additional Shepherd services dependencies to the provisioned
+Shepherd service.
+
 @item @code{roles} (default: @code{'()})
 The initial PostgreSQL roles to create.
 @end table
diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm
index edc3198ad5..86d4ecb739 100644
--- a/gnu/services/databases.scm
+++ b/gnu/services/databases.scm
@@ -9,6 +9,7 @@
 ;;; Copyright © 2020, 2022 Marius Bakke 
 ;;; Copyright © 2021 David Larsson 
 ;;; Copyright © 2021 Aljosha Papsch 
+;;; Copyright © 2025 Giacomo Leidi 
 ;;;
 ;;; This file is part of GNU Guix.
 ;;;
@@ -32,6 +33,7 @@ (define-module (gnu services databases)
   #:autoload   (gnu system accounts) (default-shell)
   #:use-module (gnu packages admin)
   #:use-module (gnu packages base)
+  #:use-module (gnu packages bash)
   #:use-module (gnu packages databases)
   #:use-module (guix build-system trivial)
   #:use-module (guix build union)
@@ -68,14 +70,18 @@ (define-module (gnu services databases)
             postgresql-service
             postgresql-service-type
 
+            %postgresql-role-shepherd-requirement
+
             postgresql-role
             postgresql-role?
             postgresql-role-name
+            postgresql-role-password-file
             postgresql-role-permissions
             postgresql-role-create-database?
             postgresql-role-configuration
             postgresql-role-configuration?
             postgresql-role-configuration-host
+            postgresql-role-configuration-requirement
             postgresql-role-configuration-roles
 
             postgresql-role-service-type
@@ -390,6 +396,8 @@ (define-record-type* 
   postgresql-role make-postgresql-role
   postgresql-role?
   (name             postgresql-role-name) ;string
+  (password-file    postgresql-role-password-file  ;string
+                    (default #f))
   (permissions      postgresql-role-permissions
                     (default '(createdb login))) ;list
   (create-database? postgresql-role-create-database?  ;boolean
@@ -403,11 +411,16 @@ (define-record-type* 
   (template postgresql-role-template ;string
             (default "template1")))
 
+(define %postgresql-role-shepherd-requirement
+  '(user-processes postgres))
+
 (define-record-type* 
   postgresql-role-configuration make-postgresql-role-configuration
   postgresql-role-configuration?
   (host             postgresql-role-configuration-host ;string
                     (default "/var/run/postgresql"))
+  (requirement      postgresql-role-configuration-requirement ;list-of-symbols
+                    (default %postgresql-role-shepherd-requirement))
   (log              postgresql-role-configuration-log ;string
                     (default "/var/log/postgresql_roles.log"))
   (roles            postgresql-role-configuration-roles
@@ -425,19 +438,35 @@ (define (postgresql-create-roles config)
                                permissions)
                    " ")))
 
+  (define (password-value role)
+    (string-append "password_" (postgresql-role-name role)))
+
+  (define (role->password-variable role)
+    (let ((file-name (postgresql-role-password-file role)))
+      (if (string? file-name)
+          ;; This way passwords do not leak to the command line.
+          #~(string-append "-v \"" #$(password-value role)
+                           "=$(" #$coreutils "/bin/cat " #$file-name ")\"")
+          "")))
+
   (define (roles->queries roles)
     (apply mixed-text-file "queries"
            (append-map
             (lambda (role)
               (match-record role 
                 (name permissions create-database? encoding collation ctype
-                      template)
+                      template password-file)
                 `("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)
+,(if (and (string? password-file)
+          (not (string-null? password-file)))
+     (string-append
+      "\nPASSWORD :'" (password-value role) "'")
+     "")
 ";\n"
 ,@(if create-database?
       `("CREATE DATABASE \"" ,name "\""
@@ -452,20 +481,30 @@ (define (postgresql-create-roles config)
 
   (let ((host (postgresql-role-configuration-host config))
         (roles (postgresql-role-configuration-roles config)))
-    #~(let ((psql #$(file-append postgresql "/bin/psql")))
-        (list psql "-a" "-h" #$host "-f" #$(roles->queries roles)))))
+    (program-file "run-queries"
+      #~(let ((bash #$(file-append bash-minimal "/bin/bash"))
+              (psql #$(file-append postgresql "/bin/psql")))
+          (define command
+            (string-append
+             "set -e; exec " psql " -a -h " #$host " -f "
+             #$(roles->queries roles) " "
+             (string-join
+              (list
+               #$@(map role->password-variable roles))
+              " ")))
+          (execlp bash bash "-c" command)))))
 
 (define (postgresql-role-shepherd-service config)
   (match-record config 
-    (log)
+    (log requirement)
     (list (shepherd-service
-           (requirement '(user-processes postgres))
+           (requirement requirement)
            (provision '(postgres-roles))
            (one-shot? #t)
            (start
             #~(lambda args
                 (zero? (spawn-command
-                        #$(postgresql-create-roles config)
+                        (list #$(postgresql-create-roles config))
                         #:user "postgres"
                         #:group "postgres"
                         ;; XXX: As of Shepherd 1.0.2, #:log-file is not
@@ -484,6 +523,7 @@ (define postgresql-role-service-type
                           (match-record config 
                             (host roles)
                             (postgresql-role-configuration
+                             (inherit config)
                              (host host)
                              (roles (append roles extended-roles))))))
                 (default-value (postgresql-role-configuration))
diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm
index fd5041344b..c5da603565 100644
--- a/gnu/tests/databases.scm
+++ b/gnu/tests/databases.scm
@@ -142,6 +142,8 @@ (define %role-log-file
 
 (define %postgresql-os
   (simple-operating-system
+   (extra-special-file "/password"
+                       (plain-file "password" "hello"))
    (service postgresql-service-type
             (postgresql-configuration
              (postgresql postgresql)
@@ -158,6 +160,10 @@ (define %postgresql-os
              (roles
               (list (postgresql-role
                      (name "root")
+                     (create-database? #t))
+                    (postgresql-role
+                     (name "alice")
+                     (password-file "/password")
                      (create-database? #t))))))))
 
 (define (run-postgresql-test)
@@ -230,14 +236,40 @@ (define (run-postgresql-test)
             (marionette-eval
              '(begin
                 (use-modules (gnu services herd)
+                             (srfi srfi-1)
                              (ice-9 popen))
                 (current-output-port
                  (open-file "/dev/console" "w0"))
+                (every
+                 (lambda (role)
+                   (let* ((port (open-pipe*
+                                 OPEN_READ
+                                 #$(file-append postgresql "/bin/psql")
+                                 "-tA" "-c"
+                                 (string-append
+                                  "SELECT 1 FROM pg_database WHERE"
+                                  " datname='" role "'")))
+                          (output (get-string-all port)))
+                     (close-pipe port)
+                     (string-contains output "1")))
+                 '("root" "alice")))
+             marionette))
+
+          (test-assert "database passwords are set"
+            (marionette-eval
+             '(begin
+                (use-modules (gnu services herd)
+                             (ice-9 match)
+                             (ice-9 popen))
+                (current-output-port
+                 (open-file "/dev/console" "w0"))
+                (setgid (passwd:gid (getpwnam "alice")))
+                (setuid (passwd:uid (getpw "alice")))
+                (setenv "PGPASSWORD" "hello")
                 (let* ((port (open-pipe*
                               OPEN_READ
-                              #$(file-append postgresql "/bin/psql")
-                              "-tA" "-c" "SELECT 1 FROM pg_database WHERE
- datname='root'"))
+                              #$(file-append postgresql "/bin/psql") "-tA" "-c"
+                              "SELECT 1 FROM pg_database WHERE datname='alice'"))
                        (output (get-string-all port)))
                   (close-pipe port)
                   (string-contains output "1")))

base-commit: 97ea59b846c5267098a019f36c84dcaa55fb123e
-- 
2.49.0





From debbugs-submit-bounces@debbugs.gnu.org Mon Apr 28 00:09:27 2025
Received: (at 73196) by debbugs.gnu.org; 28 Apr 2025 04:09:28 +0000
Received: from localhost ([127.0.0.1]:48996 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from )
	id 1u9Fo5-0003Ek-4I
	for submit@debbugs.gnu.org; Mon, 28 Apr 2025 00:09:27 -0400
Received: from mail-pg1-x533.google.com ([2607:f8b0:4864:20::533]:52701)
 by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128)
 (Exim 4.84_2) (envelope-from )
 id 1u9Fo1-0003DM-FP
 for 73196@debbugs.gnu.org; Mon, 28 Apr 2025 00:09:22 -0400
Received: by mail-pg1-x533.google.com with SMTP id
 41be03b00d2f7-af50f56b862so2882457a12.1
 for <73196@debbugs.gnu.org>; Sun, 27 Apr 2025 21:09:21 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=gmail.com; s=20230601; t=1745813355; x=1746418155; darn=debbugs.gnu.org;
 h=mime-version:user-agent:message-id:date:references:in-reply-to
 :subject:cc:to:from:from:to:cc:subject:date:message-id:reply-to;
 bh=NRRa/X4cLfF/8lZ/XjW/lEQmHpZt/sRHC5bbfJVyB/4=;
 b=C0BEDg6uSSb6T+zXILgsD6EAJzixtPAMH0kiEt/jANrO4bwnCu2/k+D56oYOXG2LUR
 F8JX4OKBoOtfjRCbVdBZDjK2eMzdbvrpfpiZbhVg8WosHtmQQ2YccLeICXi7aWT1ykWz
 +XsFCSGbrHdiSqODvYvhGrs3/oh8CrshTaJbPrMrii28ELFSc938PGGvAH4QQB26pA34
 VH5ao4GIDBd927JCD+qlEGTlJqNExu8f6sAl8BvLbZrNhpHfAfJFQswU+CdNoMvsGOHe
 Fq52F1tKLqv3LWJmB1UyBRgSbWhkens4cA+6ESHLIjXJmwdE/GZfZQa0zwGuvMBbMFca
 HkBQ==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20230601; t=1745813355; x=1746418155;
 h=mime-version:user-agent:message-id:date:references:in-reply-to
 :subject:cc:to:from:x-gm-message-state:from:to:cc:subject:date
 :message-id:reply-to;
 bh=NRRa/X4cLfF/8lZ/XjW/lEQmHpZt/sRHC5bbfJVyB/4=;
 b=jIPUMQfQyMstPZr3wtji9Ks2rEEZW1xcqtvUHU9ANSQ3fVBL9qColUu0KT/ezzUWE/
 6QHGc2KGpvxfZd8Wmw8xhXi6vPmRsT6mCF5ie4j6DvZEfmkuUcy1Oj/n14cc1g/DRDIP
 GFckB+yBc/NpRXvh5AmB9l2WiUD60e0Z60aPhtoAztddnwlwIxQn6dqa6gs37uxT69G+
 31XiUU1cXtn+EY7QmVReCTAi/A6E2EoaT91WoxRiX3qQ7Yk45M1Ip6acenQ9wBIuDIjl
 N3nCMqtI/KRQcQhIhIKvkYUWvi3LHWo0vfWLpIUiOJT9ULnu6NQudb239J6pS7Om1fj0
 6Hgw==
X-Gm-Message-State: AOJu0YyO9axVEtFwYc0+MDfLpZ7vR17twWE5nFwcWL06t267N4LLCwkq
 8CzaV8R59E2W1v+rdHX3eVuVjX6JiZfLcVCkGGoz7VWQ6cLV5rS6
X-Gm-Gg: ASbGnct+G0JExCAXqFT7KQcllOwjWObGc8iqQE+E8PvMZ0ZSp2fG3eo2b+zkzN9YBdZ
 JbVGFEr9+xZDyTQuNsonwkrqPoehQ9tplOHwCpvX3y048pFRjCNb1neMp3CFpHb1BGIs8Aknzrj
 4pN6ozyXHBR80SQPHIRlFMmh/HbTb9vZ3FBkHq3NIE1SKXxWzVEnQiZEUaTZ0Z6Flw2fxBCeskO
 uRfsoFTKI6nLqQbIgq5pGGF2vbZGaUF4E31EoCdKv/bDzA4iGJsV5oWz1UqqRVQxfmtflLmBOI0
 myuP2/PwbPr/HN2xOrLuAdmT3JUgqOOFYLl/psPH0j4n9xbJOg==
X-Google-Smtp-Source: AGHT+IEU5CwvcHci04IeFdp/XaP9hrem2NMtmpqx9fvdntn5jdkyUjgxrNnEXqdYYYTERa4JMovALg==
X-Received: by 2002:a05:6a20:9f4f:b0:1fd:f4df:9a89 with SMTP id
 adf61e73a8af0-2046a5be0b9mr9507220637.25.1745813355091; 
 Sun, 27 Apr 2025 21:09:15 -0700 (PDT)
Received: from terra ([2405:6586:be0:0:83c8:d31d:2cec:f542])
 by smtp.gmail.com with ESMTPSA id
 d2e1a72fcca58-73e25a9a308sm7164791b3a.136.2025.04.27.21.09.13
 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256);
 Sun, 27 Apr 2025 21:09:14 -0700 (PDT)
From: Maxim Cournoyer 
To: paul 
Subject: Re: bug#73196: [PATCH] services: postgresql-role: Add support for
 password files.
In-Reply-To: <1c2945a7-79c3-4abe-81ff-c0f2b2fbe62a@autistici.org> (paul's
 message of "Sun, 27 Apr 2025 17:07:01 +0200")
References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@autistici.org>
 <5b895115a5c8ff93242251a8d71718bc4e8b2fed.1744145846.git.goodoldpaul@autistici.org>
 <87v7qspe0w.fsf@gmail.com>
 <1c2945a7-79c3-4abe-81ff-c0f2b2fbe62a@autistici.org>
Date: Mon, 28 Apr 2025 13:09:11 +0900
Message-ID: <87frhtdj14.fsf@gmail.com>
User-Agent: Gnus/5.13 (Gnus v5.13)
MIME-Version: 1.0
Content-Type: text/plain
X-Spam-Score: 0.0 (/)
X-Debbugs-Envelope-To: 73196
Cc: 73196@debbugs.gnu.org, Ludovic =?utf-8?Q?Court=C3=A8s?= 
X-BeenThere: debbugs-submit@debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: 
List-Unsubscribe: , 
 
List-Archive: 
List-Post: 
List-Help: 
List-Subscribe: , 
 
Errors-To: debbugs-submit-bounces@debbugs.gnu.org
Sender: "Debbugs-submit" 
X-Spam-Score: -1.0 (-)

Hi,

paul  writes:

Thanks for addressing most of my comments.  I have a few more ones,
in light of your last replies.

[...]

>>> +  (requirement      postgresql-role-configuration-requirement ;list-of-symbols
>>> +                    (default '()))
>> as mentioned above, this should be 'shepherd-requirement'.
> I know at least two examples that are already in master which use
> 'requirement' (I may be biased as I authored both of them): the
> restic-backup-service-type and the oci-container-service-type. If you
> don't find this to be a blocker I would defer this change to a
> specific commit setting a convention for the whole codebase after an
> analysis of which services use simply 'requirement' and which use
> 'shepherd-requirement'. Is it ok for you?

OK!  I think we'd want to normalize to shepherd-requirement because
'requirement' could well be an option key in a config file (name clash);
'shepherd-requirement', makes this much less likely, while making it
more obvious that this is a shepherd thing exposed through the
configuration object.  For this reason I'd err on using
'shepherd-requirement', because it seems more likely we settle for this
variant and deprecating fields is a bit annoying.

[...]

>>
>>> +        ""))
>>> +
>>>     (define (roles->queries roles)
>>>       (apply mixed-text-file "queries"
>>>              (append-map
>>>               (lambda (role)
>>>                 (match-record role 
>>>                   (name permissions create-database? encoding collation ctype
>>> -                      template)
>>> +                      template password-file)
>>>                   `("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)
>>> +,(if (and (string? password-file)
>>> +          (not (string-null? password-file)))
>> Since you already check for the string-null? case, perhaps the default
>> password value should be "" instead of #f?  And then you could do away
>> with the string? check (though ideally there could be a sanitizer to
>> verify that a string is passed -- perhaps best leaving it for the day
>> this gets ported to define-configuration, which auto-generates sanitizers
>> based on the type specified for the field in its form.
> in its ideal form (using define-configuration) this would be a
> 'maybe-string' type, as it represents an optional path. if you don't
> find this a blocker I would leave this for a later change where we
> could use 'maybe-value-set?' and leave the type check to a
> sanitizer. What do you think?

That's OK!

[...]

>>> -    (log)
>>> +    (log requirement)
>>>       (list (shepherd-service
>>> -           (requirement '(user-processes postgres))
>>> +           (requirement `(user-processes postgres ,@requirement))
>> I see two styles being used in Guix services: 'shepherd-requirement'
>> extend the requirements, or overrides it, with the 'user-processes at
>> least normally found in the default values.  I think at this point in
>> time the later fits better in Guix (think of arguments such as #:modules
>> and #:imported-modules, etc.  the user has full control on them, for the
>> better and worst).
> Thank you for pointing this out, I created a
> %postgresql-role-shepherd-requirement value and exported it to allow
> users which want to override the Guix defaults, while retaining them
> can simply append to this list.

OK!  In my experience it's (somewhat?) conventional to use %default as a
prefix for default values, so perhaps
%default-postgresql-role-shepherd-requirement could be nicer (it's long
but in Scheme the usual style is to not shy away from being extra
descriptive in general).

>>              
>>>              (provision '(postgres-roles))
>>>              (one-shot? #t)
>>>              (start
>>>               #~(lambda args
>>>                   (zero? (spawn-command
>>> -                        #$(postgresql-create-roles config)
>>> +                        (list #$(postgresql-create-roles config))
>> Why is this change now necessary?  I didn't follow.
>
> the return value of postgresql-create-roles changed, before this
> change it returned a list containing a psql command line, after this
> change it returns a gexp which will evaluate to the entrypoint
> responsible for reading password files and then calling psql.

Thanks for explaining.

>>
>>>                           #:user "postgres"
>>>                           #:group "postgres"
>>>                           ;; XXX: As of Shepherd 1.0.2, #:log-file is not
>>> @@ -468,6 +503,7 @@ (define postgresql-role-service-type
>>>                             (match-record config 
>>>                               (host roles)
>>>                               (postgresql-role-configuration
>>> +                             (inherit config)
>> and this one?  Was it a preexisting bug?
> Yes, for example without this change we would lose the 'log' field's
> value upon extension.

OK, make sure this is mentioned in the changelog (sorry, I didn't
verify if it's already there).

I'm looking forward to the v7 :-)

-- 
Thanks,
Maxim




From debbugs-submit-bounces@debbugs.gnu.org Tue Apr 29 11:50:22 2025
Received: (at 73196) by debbugs.gnu.org; 29 Apr 2025 15:50:23 +0000
Received: from localhost ([127.0.0.1]:58473 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from )
	id 1u9nDy-0000C8-L1
	for submit@debbugs.gnu.org; Tue, 29 Apr 2025 11:50:22 -0400
Received: from confino.investici.org ([2a11:7980:1::2:0]:49003)
 by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
 (Exim 4.84_2) (envelope-from )
 id 1u9nDw-0000Bq-6v
 for 73196@debbugs.gnu.org; Tue, 29 Apr 2025 11:50:21 -0400
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org;
 s=stigmate; t=1745941817;
 bh=WqBM3xia3BmQ4Q/MjhnASLZoVTjF2p8ZrLg8tGjCrdk=;
 h=Date:Subject:To:Cc:References:From:In-Reply-To:From;
 b=kRB8zO8t2ytm+/cP99yXvWhlG9t+T3VninrNu71yhaRF2a13JWAUTlMwrM6kv9IA1
 0Y2bJgJVugptRaKo9QjeU2Ba/65/E7AR/aKDD2aQUoLfhpfnli+2ZA0jq0ARZuNJyj
 mmrvaTp5PLyGREwm6M72mjwv+QbVbQ1DuIPyuxfY=
Received: from mx1.investici.org (unknown [127.0.0.1])
 by confino.investici.org (Postfix) with ESMTP id 4Zn4X91Cxjz115q;
 Tue, 29 Apr 2025 15:50:17 +0000 (UTC)
Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19])
 (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with
 ESMTPSA id 4Zn4X872Ywz114C; Tue, 29 Apr 2025 15:50:16 +0000 (UTC)
Message-ID: 
Date: Tue, 29 Apr 2025 17:50:16 +0200
MIME-Version: 1.0
User-Agent: Icedove Daily
Subject: Re: bug#73196: [PATCH] services: postgresql-role: Add support for
 password files.
To: Maxim Cournoyer 
References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@autistici.org>
 <5b895115a5c8ff93242251a8d71718bc4e8b2fed.1744145846.git.goodoldpaul@autistici.org>
 <87v7qspe0w.fsf@gmail.com>
 <1c2945a7-79c3-4abe-81ff-c0f2b2fbe62a@autistici.org>
 <87frhtdj14.fsf@gmail.com>
Content-Language: en-US
From: paul 
In-Reply-To: <87frhtdj14.fsf@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Spam-Score: -0.0 (/)
X-Debbugs-Envelope-To: 73196
Cc: 73196@debbugs.gnu.org, =?UTF-8?Q?Ludovic_Court=C3=A8s?= 
X-BeenThere: debbugs-submit@debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: 
List-Unsubscribe: , 
 
List-Archive: 
List-Post: 
List-Help: 
List-Subscribe: , 
 
Errors-To: debbugs-submit-bounces@debbugs.gnu.org
Sender: "Debbugs-submit" 
X-Spam-Score: -1.0 (-)

Hi Maxim,

I should have addressed all your remaining comments. I'm about to send a 
v8, hopefully this will be the correct one :) thank you a lot for your help

cheers,
giacomo





From debbugs-submit-bounces@debbugs.gnu.org Tue Apr 29 11:51:25 2025
Received: (at 73196) by debbugs.gnu.org; 29 Apr 2025 15:51:25 +0000
Received: from localhost ([127.0.0.1]:58491 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from )
	id 1u9nEy-0000KF-D6
	for submit@debbugs.gnu.org; Tue, 29 Apr 2025 11:51:25 -0400
Received: from confino.investici.org ([93.190.126.19]:44491)
 by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
 (Exim 4.84_2) (envelope-from )
 id 1u9nEq-0000If-9k
 for 73196@debbugs.gnu.org; Tue, 29 Apr 2025 11:51:22 -0400
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org;
 s=stigmate; t=1745941874;
 bh=2utA7rHtalCAjvHJZ23ro3YK2/mPO7JuS/tPu7/JcWM=;
 h=From:To:Cc:Subject:Date:From;
 b=uYWAD5Lj2j6U7iQdc8NfnF7uE8olBdf47UQqhajQdm3npTiUV2r4UK78oJc8Gmazc
 Gj9y7Fdd9OnIx9H9Z+quJXYO9CYFNiCaoyGvtr5myl8RzQAEY6Syt9cwTL43LvAaEm
 T5Z0mthlugV2odcqV+lDRB3YAByNOM7mJBVdn0ME=
Received: from mx1.investici.org (unknown [127.0.0.1])
 by confino.investici.org (Postfix) with ESMTP id 4Zn4YG5Nkkz114C;
 Tue, 29 Apr 2025 15:51:14 +0000 (UTC)
Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19])
 (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with
 ESMTPSA id 4Zn4YG46Mdz10yC; Tue, 29 Apr 2025 15:51:14 +0000 (UTC)
From: Giacomo Leidi 
To: 73196@debbugs.gnu.org
Subject: [PATCH v8] services: postgresql-role: Add support for password files.
Date: Tue, 29 Apr 2025 17:51:10 +0200
Message-ID: <887817fd48a85da5a6e922e1b9a45799ba6f0908.1745941870.git.goodoldpaul@autistici.org>
X-Mailer: git-send-email 2.49.0
MIME-Version: 1.0
X-Debbugs-Cc: Ludovic Courtès , Maxim Cournoyer 
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Spam-Score: -0.7 (/)
X-Debbugs-Envelope-To: 73196
Cc: Giacomo Leidi 
X-BeenThere: debbugs-submit@debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: 
List-Unsubscribe: , 
 
List-Archive: 
List-Post: 
List-Help: 
List-Subscribe: , 
 
Errors-To: debbugs-submit-bounces@debbugs.gnu.org
Sender: "Debbugs-submit" 
X-Spam-Score: -1.7 (-)

This commit adds a password-file to the postgresql-role field.  It
allows users to provision Postgres roles with a set password.

* gnu/services/databases.scm (postgresql-role): Add password-file field.
(postgresql-role-configuration): Add requirement field.
(postgresql-create-roles): Add support for setting passwords from a
file without leaking passwords to the command line.
(postgresql-role-shepherd-service): Add support for customizable
requirements.
(postgresql-role-service-type): Pass on postgresql-role-configuration
fields values by default, this way user configured fields are not lost.
* gnu/tests/databases.scm: Test it.
* doc/guix.texi: Document the new field and fix the extension point example.

Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585
---
 doc/guix.texi              | 15 +++++++--
 gnu/services/databases.scm | 64 +++++++++++++++++++++++++++++++-------
 gnu/tests/databases.scm    | 51 ++++++++++++++++++++++++++++--
 3 files changed, 112 insertions(+), 18 deletions(-)

diff --git a/doc/guix.texi b/doc/guix.texi
index 7b418a40892..42073e52edb 100644
--- a/doc/guix.texi
+++ b/doc/guix.texi
@@ -27741,9 +27741,10 @@ Database Services
 
 @lisp
 (service-extension postgresql-role-service-type
-                   (const (postgresql-role
-                           (name "alice")
-                           (create-database? #t))))
+                   (const (list
+                           (postgresql-role
+                            (name "alice")
+                            (create-database? #t)))))
 @end lisp
 @end defvar
 
@@ -27766,6 +27767,10 @@ Database Services
 @item @code{create-database?} (default: @code{#f})
 whether to create a database with the same name as the role.
 
+@item @code{password-file} (default: @code{#f})
+A string representing the path of a file that contains the password to be set
+for the role.
+
 @item @code{encoding} (default: @code{"UTF8"})
 The character set to use for storing text in the database.
 
@@ -27794,6 +27799,10 @@ Database Services
 @item @code{log} (default: @code{"/var/log/postgresql_roles.log"})
 File name of the log file.
 
+@item @code{shepherd-requirement} (default: @code{'(user-processes postgres)}) (type: list-of-symbols)
+Set additional Shepherd services dependencies to the provisioned
+Shepherd service.
+
 @item @code{roles} (default: @code{'()})
 The initial PostgreSQL roles to create.
 @end table
diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm
index edc3198ad50..882543ce5f4 100644
--- a/gnu/services/databases.scm
+++ b/gnu/services/databases.scm
@@ -9,6 +9,7 @@
 ;;; Copyright © 2020, 2022 Marius Bakke 
 ;;; Copyright © 2021 David Larsson 
 ;;; Copyright © 2021 Aljosha Papsch 
+;;; Copyright © 2025 Giacomo Leidi 
 ;;;
 ;;; This file is part of GNU Guix.
 ;;;
@@ -32,6 +33,7 @@ (define-module (gnu services databases)
   #:autoload   (gnu system accounts) (default-shell)
   #:use-module (gnu packages admin)
   #:use-module (gnu packages base)
+  #:use-module (gnu packages bash)
   #:use-module (gnu packages databases)
   #:use-module (guix build-system trivial)
   #:use-module (guix build union)
@@ -68,14 +70,18 @@ (define-module (gnu services databases)
             postgresql-service
             postgresql-service-type
 
+            %default-postgresql-role-shepherd-requirement
+
             postgresql-role
             postgresql-role?
             postgresql-role-name
+            postgresql-role-password-file
             postgresql-role-permissions
             postgresql-role-create-database?
             postgresql-role-configuration
             postgresql-role-configuration?
             postgresql-role-configuration-host
+            postgresql-role-configuration-shepherd-requirement
             postgresql-role-configuration-roles
 
             postgresql-role-service-type
@@ -390,6 +396,8 @@ (define-record-type* 
   postgresql-role make-postgresql-role
   postgresql-role?
   (name             postgresql-role-name) ;string
+  (password-file    postgresql-role-password-file  ;string
+                    (default #f))
   (permissions      postgresql-role-permissions
                     (default '(createdb login))) ;list
   (create-database? postgresql-role-create-database?  ;boolean
@@ -403,15 +411,20 @@ (define-record-type* 
   (template postgresql-role-template ;string
             (default "template1")))
 
+(define %default-postgresql-role-shepherd-requirement
+  '(user-processes postgres))
+
 (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
+  (host                  postgresql-role-configuration-host ;string
+                         (default "/var/run/postgresql"))
+  (shepherd-requirement  postgresql-role-configuration-shepherd-requirement ;list-of-symbols
+                         (default %default-postgresql-role-shepherd-requirement))
+  (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
@@ -425,19 +438,35 @@ (define (postgresql-create-roles config)
                                permissions)
                    " ")))
 
+  (define (password-value role)
+    (string-append "password_" (postgresql-role-name role)))
+
+  (define (role->password-variable role)
+    (let ((file-name (postgresql-role-password-file role)))
+      (if (string? file-name)
+          ;; This way passwords do not leak to the command line.
+          #~(string-append "-v \"" #$(password-value role)
+                           "=$(" #$coreutils "/bin/cat " #$file-name ")\"")
+          "")))
+
   (define (roles->queries roles)
     (apply mixed-text-file "queries"
            (append-map
             (lambda (role)
               (match-record role 
                 (name permissions create-database? encoding collation ctype
-                      template)
+                      template password-file)
                 `("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)
+,(if (and (string? password-file)
+          (not (string-null? password-file)))
+     (string-append
+      "\nPASSWORD :'" (password-value role) "'")
+     "")
 ";\n"
 ,@(if create-database?
       `("CREATE DATABASE \"" ,name "\""
@@ -452,20 +481,30 @@ (define (postgresql-create-roles config)
 
   (let ((host (postgresql-role-configuration-host config))
         (roles (postgresql-role-configuration-roles config)))
-    #~(let ((psql #$(file-append postgresql "/bin/psql")))
-        (list psql "-a" "-h" #$host "-f" #$(roles->queries roles)))))
+    (program-file "run-queries"
+      #~(let ((bash #$(file-append bash-minimal "/bin/bash"))
+              (psql #$(file-append postgresql "/bin/psql")))
+          (define command
+            (string-append
+             "set -e; exec " psql " -a -h " #$host " -f "
+             #$(roles->queries roles) " "
+             (string-join
+              (list
+               #$@(map role->password-variable roles))
+              " ")))
+          (execlp bash bash "-c" command)))))
 
 (define (postgresql-role-shepherd-service config)
   (match-record config 
-    (log)
+    (log shepherd-requirement)
     (list (shepherd-service
-           (requirement '(user-processes postgres))
+           (requirement shepherd-requirement)
            (provision '(postgres-roles))
            (one-shot? #t)
            (start
             #~(lambda args
                 (zero? (spawn-command
-                        #$(postgresql-create-roles config)
+                        (list #$(postgresql-create-roles config))
                         #:user "postgres"
                         #:group "postgres"
                         ;; XXX: As of Shepherd 1.0.2, #:log-file is not
@@ -484,6 +523,7 @@ (define postgresql-role-service-type
                           (match-record config 
                             (host roles)
                             (postgresql-role-configuration
+                             (inherit config)
                              (host host)
                              (roles (append roles extended-roles))))))
                 (default-value (postgresql-role-configuration))
diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm
index fd5041344b6..84ec2987d68 100644
--- a/gnu/tests/databases.scm
+++ b/gnu/tests/databases.scm
@@ -1,6 +1,7 @@
 ;;; GNU Guix --- Functional package management for GNU
 ;;; Copyright © 2017 Christopher Baines 
 ;;; Copyright © 2020, 2022 Marius Bakke 
+;;; Copyright © 2025 Giacomo Leidi 
 ;;;
 ;;; This file is part of GNU Guix.
 ;;;
@@ -142,6 +143,8 @@ (define %role-log-file
 
 (define %postgresql-os
   (simple-operating-system
+   (extra-special-file "/password"
+                       (plain-file "password" "hello"))
    (service postgresql-service-type
             (postgresql-configuration
              (postgresql postgresql)
@@ -158,6 +161,10 @@ (define %postgresql-os
              (roles
               (list (postgresql-role
                      (name "root")
+                     (create-database? #t))
+                    (postgresql-role
+                     (name "a_database")
+                     (password-file "/password")
                      (create-database? #t))))))))
 
 (define (run-postgresql-test)
@@ -230,17 +237,55 @@ (define (run-postgresql-test)
             (marionette-eval
              '(begin
                 (use-modules (gnu services herd)
+                             (srfi srfi-1)
                              (ice-9 popen))
                 (current-output-port
                  (open-file "/dev/console" "w0"))
+                (every
+                 (lambda (role)
+                   (let* ((port (open-pipe*
+                                 OPEN_READ
+                                 #$(file-append postgresql "/bin/psql")
+                                 "-tA" "-c"
+                                 (string-append
+                                  "SELECT 1 FROM pg_database WHERE"
+                                  " datname='" role "'")))
+                          (output (get-string-all port)))
+                     (close-pipe port)
+                     (string-contains output "1")))
+                 '("root" "a_database")))
+             marionette))
+
+          (test-assert "database use fails without a password"
+            (marionette-eval
+             '(begin
+                (use-modules (gnu services herd)
+                             (ice-9 popen))
+                (setgid (passwd:gid (getpwnam "alice")))
+                (setuid (passwd:uid (getpw "alice")))
+                (let ((output
+                       (system* #$(file-append postgresql "/bin/psql") "-tA" "-h" "localhost" "-U" "a_database" "-c"
+                                "SELECT 1 FROM pg_database WHERE datname='a_database'")))
+                  (not (= output 0))))
+             marionette))
+
+          (test-assert "database passwords are set"
+            (marionette-eval
+             '(begin
+                (use-modules (gnu services herd)
+                             (ice-9 popen))
+                (setgid (passwd:gid (getpwnam "alice")))
+                (setuid (passwd:uid (getpw "alice")))
+                (setenv "PGPASSWORD"
+                        (call-with-input-file "/password" get-string-all))
                 (let* ((port (open-pipe*
                               OPEN_READ
                               #$(file-append postgresql "/bin/psql")
-                              "-tA" "-c" "SELECT 1 FROM pg_database WHERE
- datname='root'"))
+                              "-U" "a_database" "-tA" "-h" "localhost" "-c"
+                              "SELECT 1 FROM pg_database WHERE datname='a_database'"))
                        (output (get-string-all port)))
                   (close-pipe port)
-                  (string-contains output "1")))
+                  (string=? output "1\n")))
              marionette))
 
           (test-end))))

base-commit: 1710c0941db517453ac2b88c0e854e8348172603
-- 
2.49.0





From debbugs-submit-bounces@debbugs.gnu.org Fri May 02 02:33:55 2025
Received: (at 73196-done) by debbugs.gnu.org; 2 May 2025 06:33:55 +0000
Received: from localhost ([127.0.0.1]:55808 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from )
	id 1uAjy7-0002fb-2J
	for submit@debbugs.gnu.org; Fri, 02 May 2025 02:33:55 -0400
Received: from mail-pl1-x631.google.com ([2607:f8b0:4864:20::631]:43079)
 by debbugs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128)
 (Exim 4.84_2) (envelope-from )
 id 1uAjy1-0002fB-Rg
 for 73196-done@debbugs.gnu.org; Fri, 02 May 2025 02:33:52 -0400
Received: by mail-pl1-x631.google.com with SMTP id
 d9443c01a7336-22e09f57ed4so13110085ad.0
 for <73196-done@debbugs.gnu.org>; Thu, 01 May 2025 23:33:49 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=gmail.com; s=20230601; t=1746167623; x=1746772423; darn=debbugs.gnu.org;
 h=mime-version:user-agent:message-id:date:references:in-reply-to
 :subject:cc:to:from:from:to:cc:subject:date:message-id:reply-to;
 bh=Ii479ymvFzHp5UC1yaN4Ce8+nlCPpnfQijKJQsKDTBo=;
 b=aYhnbXS7UToZ2CDMlSDJN+UgJmy69n5l+crU0O9KmZL8+Dg4oQG+wyZ8VydHOBQDXi
 hLE+Y9qKlOi/K8cPbXJe9QOPaN4ctEwDDDMwvAx8oyUtu4M77eH/TD9eQ4H/Jk+AA8xV
 uIRzl+mQfxyukF8zC4Vv8Ydb/rfcYM9E+KqD4eTR9FxiyrhRPx2vP3QaiYZyYRXy+ug0
 XRSD8kqKnTjX0rzfkbjyBxg5BBImBG3lRUaNJ0PAhNSvBFWDEOdDFRgpOMfRuVeQHQ61
 KQry5POToamhayiejV+O61biYZsa5kmHHJZfgUjtFbcWvcu7mwFb7+8KVf0JL1AVERTs
 LDig==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20230601; t=1746167623; x=1746772423;
 h=mime-version:user-agent:message-id:date:references:in-reply-to
 :subject:cc:to:from:x-gm-message-state:from:to:cc:subject:date
 :message-id:reply-to;
 bh=Ii479ymvFzHp5UC1yaN4Ce8+nlCPpnfQijKJQsKDTBo=;
 b=THYjK3kmdGyonKUvdOBJBOzOT6yUmW1mYu2vU9tXtRHqt5na9A026BeVoOfqKNqWkF
 NK/PZoBKNqgIWkXkW2ohfyBbRY8UW0tUaj2ualKml8pTrVgiWes++POiz5gn0Z3djvBd
 O/eo/odkATNSkPxYBxpmfGwYJ0XjGbpy1t9qirRe345iHWthIHYUDeghW2cehmdCi9kF
 VUrVeTjX4oSFtm1FZITlLzfhedXC4jhN3njCsq2w0VCJsV+1uTqG2aibDnZfjjvrJKei
 r75gK5fAX7vwxmhupcPfBdL1BfwdN1ellfwBYOALBepUAmMPZGpdME1czxocJi3cbQA7
 PHFA==
X-Gm-Message-State: AOJu0Yxfyla2qkWVJ/SdJvNOtFXOniZrcu6o7sgK/BDBmCQYLBl052sD
 l/Kz1UMEsZtg97yl2KqXfyaoE/BHwnsOtU3PClKzvYeKmpnmrFXZ
X-Gm-Gg: ASbGncvCSadRpmITRlvoxxc9OiUC0oMhIhxeSEhivGEOmz4P42uDTKnSPdyRemIDTrt
 hFq40HQeQl0GsruLqyemCoDSS6JyeE0qG90FmC9NU+6+qV8nn+VyZT6rcpLgKGFXDIkS5Yzqebt
 UZHKHOSaSa6QNkDR0WPsTO1bfwd6l/mK+DYF69iF0gtIh0nbwBhCo2+IK9DiTgBRtf1B9zzTiPi
 DrwUHnYEbPyOx3q9pHHLq55DAwY2i5LUkzLSVfNSGpbhjiGao5XABsy9TZRfqx4lYV7L0EMDpeD
 +WY8iUl9duceWKyn9csev6YNuiXn1L93pqLMa00=
X-Google-Smtp-Source: AGHT+IGlqP3aSnmosjuljVtjbjX2uezH9vwqVV+rln46Kz9hZQ1xhOGEm1ytC86mUc2ZZSA560hk0A==
X-Received: by 2002:a17:903:2283:b0:224:7a4:b31 with SMTP id
 d9443c01a7336-22e10040399mr23076705ad.6.1746167623264; 
 Thu, 01 May 2025 23:33:43 -0700 (PDT)
Received: from terra ([2405:6586:be0:0:83c8:d31d:2cec:f542])
 by smtp.gmail.com with ESMTPSA id
 d9443c01a7336-22e150eb6d1sm229465ad.49.2025.05.01.23.33.41
 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256);
 Thu, 01 May 2025 23:33:42 -0700 (PDT)
From: Maxim Cournoyer 
To: paul 
Subject: Re: bug#73196: [PATCH] services: postgresql-role: Add support for
 password files.
In-Reply-To:  (paul's
 message of "Tue, 29 Apr 2025 17:50:16 +0200")
References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@autistici.org>
 <5b895115a5c8ff93242251a8d71718bc4e8b2fed.1744145846.git.goodoldpaul@autistici.org>
 <87v7qspe0w.fsf@gmail.com>
 <1c2945a7-79c3-4abe-81ff-c0f2b2fbe62a@autistici.org>
 <87frhtdj14.fsf@gmail.com>
 
Date: Fri, 02 May 2025 15:33:40 +0900
Message-ID: <87ikmj5xob.fsf@gmail.com>
User-Agent: Gnus/5.13 (Gnus v5.13)
MIME-Version: 1.0
Content-Type: text/plain
X-Spam-Score: 0.0 (/)
X-Debbugs-Envelope-To: 73196-done
Cc: Ludovic =?utf-8?Q?Court=C3=A8s?= , 73196-done@debbugs.gnu.org
X-BeenThere: debbugs-submit@debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: 
List-Unsubscribe: , 
 
List-Archive: 
List-Post: 
List-Help: 
List-Subscribe: , 
 
Errors-To: debbugs-submit-bounces@debbugs.gnu.org
Sender: "Debbugs-submit" 
X-Spam-Score: -1.0 (-)

Hi Paul,

paul  writes:

> Hi Maxim,
>
> I should have addressed all your remaining comments. I'm about to send
> a v8, hopefully this will be the correct one :) thank you a lot for
> your help

Thanks!  I've made a few cosmetic changes, e.g.:

--8<---------------cut here---------------start------------->8---
3 files changed, 18 insertions(+), 18 deletions(-)
doc/guix.texi              |  5 +++--
gnu/services/databases.scm | 17 +++++++++--------
gnu/tests/databases.scm    | 14 ++++++--------

modified   doc/guix.texi
@@ -27805,8 +27805,9 @@ Database Services
 
 @item @code{shepherd-requirement} (default: @code{'(user-processes postgres)})
 
-Set additional Shepherd services dependencies to the provisioned
-Shepherd service.
+The Shepherd services dependencies to use.  Add extra dependencies to
+@code{%default-postgresql-role-shepherd-requirement} to extend its
+value.
 
 @item @code{roles} (default: @code{'()})
 The initial PostgreSQL roles to create.
modified   gnu/services/databases.scm
@@ -417,14 +417,15 @@ (define %default-postgresql-role-shepherd-requirement
 (define-record-type* 
   postgresql-role-configuration make-postgresql-role-configuration
   postgresql-role-configuration?
-  (host                  postgresql-role-configuration-host ;string
-                         (default "/var/run/postgresql"))
-  (shepherd-requirement  postgresql-role-configuration-shepherd-requirement ;list-of-symbols
-                         (default %default-postgresql-role-shepherd-requirement))
-  (log                   postgresql-role-configuration-log ;string
-                         (default "/var/log/postgresql_roles.log"))
-  (roles                 postgresql-role-configuration-roles
-                         (default '()))) ;list
+  (shepherd-requirement
+   postgresql-role-configuration-shepherd-requirement ;list-of-symbols
+   (default %default-postgresql-role-shepherd-requirement))
+  (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
modified   gnu/tests/databases.scm
@@ -259,21 +259,19 @@ (define (run-postgresql-test)
           (test-assert "database use fails without a password"
             (marionette-eval
              '(begin
-                (use-modules (gnu services herd)
-                             (ice-9 popen))
                 (setgid (passwd:gid (getpwnam "alice")))
                 (setuid (passwd:uid (getpw "alice")))
-                (let ((output
-                       (system* #$(file-append postgresql "/bin/psql") "-tA" "-h" "localhost" "-U" "a_database" "-c"
-                                "SELECT 1 FROM pg_database WHERE datname='a_database'")))
-                  (not (= output 0))))
+                (not (zero?
+                      (system* #$(file-append postgresql "/bin/psql")
+                               "-tA" "-h" "localhost" "-U" "a_database" "-c"
+                               (string-append "SELECT 1 FROM pg_database "
+                                              "WHERE datname='a_database'")))))
              marionette))
 
           (test-assert "database passwords are set"
             (marionette-eval
              '(begin
-                (use-modules (gnu services herd)
-                             (ice-9 popen))
+                (use-modules (ice-9 popen))
                 (setgid (passwd:gid (getpwnam "alice")))
                 (setuid (passwd:uid (getpw "alice")))
                 (setenv "PGPASSWORD"
--8<---------------cut here---------------end--------------->8---

ensured 'make check-system TESTS=postgresql' was still happy, and
pushed as commit 9d216d2ae9f.  Thank you!

-- 
Thanks,
Maxim




From unknown Sun Jul 27 00:26:05 2025
Received: (at fakecontrol) by fakecontrolmessage;
To: internal_control@debbugs.gnu.org
From: Debbugs Internal Request 
Subject: Internal Control
Message-Id: bug archived.
Date: Fri, 30 May 2025 11:24:13 +0000
User-Agent: Fakemail v42.6.9

# This is a fake control message.
#
# The action:
# bug archived.
thanks
# This fakemail brought to you by your local debbugs
# administrator