GNU bug report logs - #73196
[PATCH] services: postgresql-role: Add support for password files.

Please note: This is a static page, with minimal formatting, updated once a day.
Click here to see this page with the latest information and nicer formatting.
Package: guix-patches; Reported by: Giacomo Leidi <goodoldpaul@HIDDEN>; Keywords: patch; dated Thu, 12 Sep 2024 11:26:01 UTC; Maintainer for guix-patches is guix-patches@HIDDEN.

Message received at 73196 <at> debbugs.gnu.org:


Received: (at 73196) by debbugs.gnu.org; 20 Oct 2024 23:00:32 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Sun Oct 20 19:00:32 2024
Received: from localhost ([127.0.0.1]:49280 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1t2euV-0002Od-Rx
	for submit <at> 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 <goodoldpaul@HIDDEN>) id 1t2euU-0002NN-9K
 for 73196 <at> 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@HIDDEN) by localhost (Postfix) with
 ESMTPSA id 4XWv644cD7z10xr; Sun, 20 Oct 2024 22:59:56 +0000 (UTC)
From: Giacomo Leidi <goodoldpaul@HIDDEN>
To: 73196 <at> 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: <b3172b812a7429431d9e2eebdb8f5b807fe20311.1729465187.git.goodoldpaul@HIDDEN>
X-Mailer: git-send-email 2.46.0
MIME-Version: 1.0
X-Debbugs-Cc: Ludovic Courtès <ludo@HIDDEN>, Maxim Cournoyer <maxim.cournoyer@HIDDEN>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Spam-Score: -0.7 (/)
X-Debbugs-Envelope-To: 73196
Cc: Giacomo Leidi <goodoldpaul@HIDDEN>
X-BeenThere: debbugs-submit <at> debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: <debbugs-submit.debbugs.gnu.org>
List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe>
List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/>
List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org>
List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help>
List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe>
Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org
Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
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 <marius@HIDDEN>
 ;;; Copyright © 2021 David Larsson <david.larsson@HIDDEN>
 ;;; Copyright © 2021 Aljosha Papsch <ep@HIDDEN>
+;;; Copyright © 2024 Giacomo Leidi <goodoldpaul@HIDDEN>
 ;;;
 ;;; 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>
   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>
   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 <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)))
+     (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 <postgresql-role-configuration>
-    (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 <postgresql-role-configuration>
-                            (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
Information forwarded to ludo@HIDDEN, maxim.cournoyer@HIDDEN, guix-patches@HIDDEN:
bug#73196; Package guix-patches. Full text available.

Message received at 73196 <at> debbugs.gnu.org:


Received: (at 73196) by debbugs.gnu.org; 20 Oct 2024 23:00:13 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Sun Oct 20 19:00:13 2024
Received: from localhost ([127.0.0.1]:49276 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1t2euD-0002Ml-Ip
	for submit <at> 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 <goodoldpaul@HIDDEN>) id 1t2euB-0002Mc-EP
 for 73196 <at> 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 <at> 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@HIDDEN) by localhost (Postfix) with
 ESMTPSA id 4XWv5r6cTLz10xr
 for <73196 <at> debbugs.gnu.org>; Sun, 20 Oct 2024 22:59:44 +0000 (UTC)
Message-ID: <9fcbb384-3b89-487b-9a8f-4289b9f5e174@HIDDEN>
Date: Mon, 21 Oct 2024 00:59:44 +0200
MIME-Version: 1.0
User-Agent: Icedove Daily
Content-Language: en-US
To: 73196 <at> debbugs.gnu.org
From: paul <goodoldpaul@HIDDEN>
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 <at> debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: <debbugs-submit.debbugs.gnu.org>
List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe>
List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/>
List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org>
List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help>
List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe>
Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org
Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
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
Information forwarded to guix-patches@HIDDEN:
bug#73196; Package guix-patches. Full text available.

Message received at 73196 <at> debbugs.gnu.org:


Received: (at 73196) by debbugs.gnu.org; 12 Sep 2024 12:18:54 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Thu Sep 12 08:18:54 2024
Received: from localhost ([127.0.0.1]:40382 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1soimj-0006NR-CA
	for submit <at> 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 <goodoldpaul@HIDDEN>) id 1soimh-0006NJ-PK
 for 73196 <at> 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@HIDDEN) by localhost (Postfix) with
 ESMTPSA id 4X4Ggl3Bs3z117s; Thu, 12 Sep 2024 12:18:43 +0000 (UTC)
From: Giacomo Leidi <goodoldpaul@HIDDEN>
To: 73196 <at> 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: <e9327653512acffeb8ed91c0542b67c0981f2d2e.1726143508.git.goodoldpaul@HIDDEN>
X-Mailer: git-send-email 2.46.0
MIME-Version: 1.0
X-Debbugs-Cc: Florian Pelz <pelzflorian@HIDDEN>, Ludovic Courtès <ludo@HIDDEN>, Maxim Cournoyer <maxim.cournoyer@HIDDEN>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Spam-Score: -0.7 (/)
X-Debbugs-Envelope-To: 73196
Cc: Giacomo Leidi <goodoldpaul@HIDDEN>
X-BeenThere: debbugs-submit <at> debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: <debbugs-submit.debbugs.gnu.org>
List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe>
List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/>
List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org>
List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help>
List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe>
Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org
Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
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 <marius@HIDDEN>
 ;;; Copyright © 2021 David Larsson <david.larsson@HIDDEN>
 ;;; Copyright © 2021 Aljosha Papsch <ep@HIDDEN>
+;;; Copyright © 2024 Giacomo Leidi <goodoldpaul@HIDDEN>
 ;;;
 ;;; 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>
   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>
   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 <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)))
+     (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 <postgresql-role-configuration>
-    (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 <postgresql-role-configuration>
-                            (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
Information forwarded to pelzflorian@HIDDEN, ludo@HIDDEN, maxim.cournoyer@HIDDEN, guix-patches@HIDDEN:
bug#73196; Package guix-patches. Full text available.

Message received at 73196 <at> debbugs.gnu.org:


Received: (at 73196) by debbugs.gnu.org; 12 Sep 2024 12:18:30 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Thu Sep 12 08:18:30 2024
Received: from localhost ([127.0.0.1]:40379 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1soimM-0006Mj-2x
	for submit <at> 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 <goodoldpaul@HIDDEN>) id 1soimI-0006MZ-Uw
 for 73196 <at> 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 <at> 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@HIDDEN) by localhost (Postfix) with
 ESMTPSA id 4X4GgF0qpzz117s
 for <73196 <at> debbugs.gnu.org>; Thu, 12 Sep 2024 12:18:17 +0000 (UTC)
Message-ID: <8fd9fb42-e6b6-5485-b437-23063dfd5627@HIDDEN>
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 <at> debbugs.gnu.org
Content-Language: en-US
From: paul <goodoldpaul@HIDDEN>
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 <at> debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: <debbugs-submit.debbugs.gnu.org>
List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe>
List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/>
List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org>
List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help>
List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe>
Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org
Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
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
Information forwarded to guix-patches@HIDDEN:
bug#73196; Package guix-patches. Full text available.

Message received at submit <at> debbugs.gnu.org:


Received: (at submit) by debbugs.gnu.org; 12 Sep 2024 11:25:27 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Thu Sep 12 07:25:27 2024
Received: from localhost ([127.0.0.1]:40286 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1sohx0-0003fA-Gq
	for submit <at> 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 <goodoldpaul@HIDDEN>) id 1sohwx-0003f1-Rc
 for submit <at> 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 <goodoldpaul@HIDDEN>)
 id 1sohwk-0007Lj-7t
 for guix-patches@HIDDEN; 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 <goodoldpaul@HIDDEN>)
 id 1sohwf-0000Jt-J9
 for guix-patches@HIDDEN; 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@HIDDEN) by localhost (Postfix) with
 ESMTPSA id 4X4FTk5Fsnz1195; Thu, 12 Sep 2024 11:24:58 +0000 (UTC)
From: Giacomo Leidi <goodoldpaul@HIDDEN>
To: guix-patches@HIDDEN
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@HIDDEN>
X-Mailer: git-send-email 2.46.0
MIME-Version: 1.0
X-Debbugs-Cc: Florian Pelz <pelzflorian@HIDDEN>, Ludovic Courtès <ludo@HIDDEN>, Maxim Cournoyer <maxim.cournoyer@HIDDEN>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Received-SPF: pass client-ip=2a11:7980:1::2:0;
 envelope-from=goodoldpaul@HIDDEN; 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 <goodoldpaul@HIDDEN>
X-BeenThere: debbugs-submit <at> debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: <debbugs-submit.debbugs.gnu.org>
List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe>
List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/>
List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org>
List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help>
List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe>
Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org
Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
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 <marius@HIDDEN>
 ;;; Copyright © 2021 David Larsson <david.larsson@HIDDEN>
 ;;; Copyright © 2021 Aljosha Papsch <ep@HIDDEN>
+;;; Copyright © 2024 Giacomo Leidi <goodoldpaul@HIDDEN>
 ;;;
 ;;; 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>
   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>
   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 <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)))
+     (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 <postgresql-role-configuration>
-    (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
Acknowledgement sent to Giacomo Leidi <goodoldpaul@HIDDEN>:
New bug report received and forwarded. Copy sent to pelzflorian@HIDDEN, ludo@HIDDEN, maxim.cournoyer@HIDDEN, guix-patches@HIDDEN. Full text available.
Report forwarded to pelzflorian@HIDDEN, ludo@HIDDEN, maxim.cournoyer@HIDDEN, guix-patches@HIDDEN:
bug#73196; Package guix-patches. Full text available.
Please note: This is a static page, with minimal formatting, updated once a day.
Click here to see this page with the latest information and nicer formatting.
Last modified: Sun, 12 Jan 2025 05:45:02 UTC

GNU bug tracking system
Copyright (C) 1999 Darren O. Benham, 1997 nCipher Corporation Ltd, 1994-97 Ian Jackson.