X-Loop: help-debbugs@HIDDEN
Subject: [bug#73196] [PATCH] services: postgresql-role: Add support for password files.
Resent-From: Giacomo Leidi <goodoldpaul@HIDDEN>
Original-Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
Resent-CC: pelzflorian@HIDDEN, ludo@HIDDEN, maxim.cournoyer@HIDDEN, guix-patches@HIDDEN
Resent-Date: Thu, 12 Sep 2024 11:26:01 +0000
Resent-Message-ID: <handler.73196.B.172614032714088 <at> debbugs.gnu.org>
Resent-Sender: help-debbugs@HIDDEN
X-GNU-PR-Message: report 73196
X-GNU-PR-Package: guix-patches
X-GNU-PR-Keywords: patch
To: 73196 <at> debbugs.gnu.org
Cc: Giacomo Leidi <goodoldpaul@HIDDEN>, Florian Pelz <pelzflorian@HIDDEN>, Ludovic =?UTF-8?Q?Court=C3=A8s?= <ludo@HIDDEN>, Maxim Cournoyer <maxim.cournoyer@HIDDEN>
X-Debbugs-Original-To: guix-patches@HIDDEN
X-Debbugs-Original-Xcc: Florian Pelz <pelzflorian@HIDDEN>, Ludovic =?UTF-8?Q?Court=C3=A8s?= <ludo@HIDDEN>, Maxim Cournoyer <maxim.cournoyer@HIDDEN>
Received: via spool by submit <at> debbugs.gnu.org id=B.172614032714088
(code B ref -1); Thu, 12 Sep 2024 11:26:01 +0000
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 <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>
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
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-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
Content-Disposition: inline Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Mailer: MIME-tools 5.505 (Entity 5.505) Content-Type: text/plain; charset=utf-8 X-Loop: help-debbugs@HIDDEN From: help-debbugs@HIDDEN (GNU bug Tracking System) To: Giacomo Leidi <goodoldpaul@HIDDEN> Subject: bug#73196: Acknowledgement ([PATCH] services: postgresql-role: Add support for password files.) Message-ID: <handler.73196.B.172614032714088.ack <at> debbugs.gnu.org> References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@HIDDEN> X-Gnu-PR-Message: ack 73196 X-Gnu-PR-Package: guix-patches X-Gnu-PR-Keywords: patch Reply-To: 73196 <at> debbugs.gnu.org Date: Thu, 12 Sep 2024 11:26:02 +0000 Thank you for filing a new bug report with debbugs.gnu.org. This is an automatically generated reply to let you know your message has been received. Your message is being forwarded to the package maintainers and other interested parties for their attention; they will reply in due course. As you requested using X-Debbugs-CC, your message was also forwarded to Florian Pelz <pelzflorian@HIDDEN>, Ludovic Court=C3=A8s <ludo@gnu= .org>, Maxim Cournoyer <maxim.cournoyer@HIDDEN> (after having been given a bug report number, if it did not have one). Your message has been sent to the package maintainer(s): guix-patches@HIDDEN If you wish to submit further information on this problem, please send it to 73196 <at> debbugs.gnu.org. Please do not send mail to help-debbugs@HIDDEN unless you wish to report a problem with the Bug-tracking system. --=20 73196: https://debbugs.gnu.org/cgi/bugreport.cgi?bug=3D73196 GNU Bug Tracking System Contact help-debbugs@HIDDEN with problems
X-Loop: help-debbugs@HIDDEN
Subject: [bug#73196] [PATCH] services: postgresql-role: Add support for password files.
References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@HIDDEN>
In-Reply-To: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@HIDDEN>
Resent-From: paul <goodoldpaul@HIDDEN>
Original-Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
Resent-CC: guix-patches@HIDDEN
Resent-Date: Thu, 12 Sep 2024 12:19:01 +0000
Resent-Message-ID: <handler.73196.B73196.172614351024477 <at> debbugs.gnu.org>
Resent-Sender: help-debbugs@HIDDEN
X-GNU-PR-Message: followup 73196
X-GNU-PR-Package: guix-patches
X-GNU-PR-Keywords: patch
To: 73196 <at> debbugs.gnu.org
Received: via spool by 73196-submit <at> debbugs.gnu.org id=B73196.172614351024477
(code B ref 73196); Thu, 12 Sep 2024 12:19:01 +0000
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 <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
Content-Language: en-US
From: paul <goodoldpaul@HIDDEN>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Spam-Score: -0.7 (/)
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
X-Loop: help-debbugs@HIDDEN
Subject: [bug#73196] [PATCH v2] services: postgresql-role: Add support for password files.
References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@HIDDEN>
In-Reply-To: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@HIDDEN>
Resent-From: Giacomo Leidi <goodoldpaul@HIDDEN>
Original-Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
Resent-CC: pelzflorian@HIDDEN, ludo@HIDDEN, maxim.cournoyer@HIDDEN, guix-patches@HIDDEN
Resent-Date: Thu, 12 Sep 2024 12:19:02 +0000
Resent-Message-ID: <handler.73196.B73196.172614353424522 <at> debbugs.gnu.org>
Resent-Sender: help-debbugs@HIDDEN
X-GNU-PR-Message: followup 73196
X-GNU-PR-Package: guix-patches
X-GNU-PR-Keywords: patch
To: 73196 <at> debbugs.gnu.org
Cc: Giacomo Leidi <goodoldpaul@HIDDEN>, Florian Pelz <pelzflorian@HIDDEN>, Ludovic =?UTF-8?Q?Court=C3=A8s?= <ludo@HIDDEN>, Maxim Cournoyer <maxim.cournoyer@HIDDEN>
X-Debbugs-Original-Xcc: Florian Pelz <pelzflorian@HIDDEN>, Ludovic =?UTF-8?Q?Court=C3=A8s?= <ludo@HIDDEN>, Maxim Cournoyer <maxim.cournoyer@HIDDEN>
Received: via spool by 73196-submit <at> debbugs.gnu.org id=B73196.172614353424522
(code B ref 73196); Thu, 12 Sep 2024 12:19:02 +0000
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 <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>
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
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Spam-Score: -0.7 (/)
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
X-Loop: help-debbugs@HIDDEN
Subject: [bug#73196] [PATCH] services: postgresql-role: Add support for password files.
References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@HIDDEN>
In-Reply-To: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@HIDDEN>
Resent-From: paul <goodoldpaul@HIDDEN>
Original-Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
Resent-CC: guix-patches@HIDDEN
Resent-Date: Sun, 20 Oct 2024 23:01:01 +0000
Resent-Message-ID: <handler.73196.B73196.17294652139103 <at> debbugs.gnu.org>
Resent-Sender: help-debbugs@HIDDEN
X-GNU-PR-Message: followup 73196
X-GNU-PR-Package: guix-patches
X-GNU-PR-Keywords: patch
To: 73196 <at> debbugs.gnu.org
Received: via spool by 73196-submit <at> debbugs.gnu.org id=B73196.17294652139103
(code B ref 73196); Sun, 20 Oct 2024 23:01:01 +0000
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 <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
From: paul <goodoldpaul@HIDDEN>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Spam-Score: -0.7 (/)
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
X-Loop: help-debbugs@HIDDEN
Subject: [bug#73196] [PATCH v2] services: postgresql-role: Add support for password files.
References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@HIDDEN>
In-Reply-To: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@HIDDEN>
Resent-From: Giacomo Leidi <goodoldpaul@HIDDEN>
Original-Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
Resent-CC: ludo@HIDDEN, maxim.cournoyer@HIDDEN, guix-patches@HIDDEN
Resent-Date: Sun, 20 Oct 2024 23:01:02 +0000
Resent-Message-ID: <handler.73196.B73196.17294652329223 <at> debbugs.gnu.org>
Resent-Sender: help-debbugs@HIDDEN
X-GNU-PR-Message: followup 73196
X-GNU-PR-Package: guix-patches
X-GNU-PR-Keywords: patch
To: 73196 <at> debbugs.gnu.org
Cc: Giacomo Leidi <goodoldpaul@HIDDEN>, Ludovic =?UTF-8?Q?Court=C3=A8s?= <ludo@HIDDEN>, Maxim Cournoyer <maxim.cournoyer@HIDDEN>
X-Debbugs-Original-Xcc: Ludovic =?UTF-8?Q?Court=C3=A8s?= <ludo@HIDDEN>, Maxim Cournoyer <maxim.cournoyer@HIDDEN>
Received: via spool by 73196-submit <at> debbugs.gnu.org id=B73196.17294652329223
(code B ref 73196); Sun, 20 Oct 2024 23:01:02 +0000
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 <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>
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
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Spam-Score: -0.7 (/)
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
GNU bug tracking system
Copyright (C) 1999 Darren O. Benham,
1997 nCipher Corporation Ltd,
1994-97 Ian Jackson.