Package: guix-patches;
Reported by: Giacomo Leidi <goodoldpaul <at> autistici.org>
Date: Thu, 12 Sep 2024 11:26:01 UTC
Severity: normal
Tags: patch
Done: Maxim Cournoyer <maxim.cournoyer <at> gmail.com>
Bug is archived. No further changes may be made.
View this message in rfc822 format
From: Giacomo Leidi <goodoldpaul <at> autistici.org> To: 73196 <at> debbugs.gnu.org Cc: Giacomo Leidi <goodoldpaul <at> autistici.org>, Ludovic Courtès <ludo <at> gnu.org>, Maxim Cournoyer <maxim.cournoyer <at> gmail.com> Subject: [bug#73196] [PATCH v7] services: postgresql-role: Add support for password files. Date: Sun, 27 Apr 2025 17:12:54 +0200
This commit adds a password-file to the postgresql-role field. It
allows users to provision Postgres roles with a set password.
* gnu/services/databases.scm (postgresql-role): Add password-file field.
(postgresql-role-configuration): Add requirement field.
(postgresql-create-roles): Add support for setting passwords from a
file without leaking passwords to the command line.
(postgresql-role-shepherd-service): Add support for customizable
requirements.
* gnu/tests/databases.scm: Test it.
* doc/guix.texi: Document the new field and fix the extension point example.
Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585
---
doc/guix.texi | 15 ++++++++---
gnu/services/databases.scm | 52 +++++++++++++++++++++++++++++++++-----
gnu/tests/databases.scm | 38 +++++++++++++++++++++++++---
3 files changed, 93 insertions(+), 12 deletions(-)
diff --git a/doc/guix.texi b/doc/guix.texi
index 7b418a4089..ca690c8ace 100644
--- a/doc/guix.texi
+++ b/doc/guix.texi
@@ -27741,9 +27741,10 @@ Database Services
@lisp
(service-extension postgresql-role-service-type
- (const (postgresql-role
- (name "alice")
- (create-database? #t))))
+ (const (list
+ (postgresql-role
+ (name "alice")
+ (create-database? #t)))))
@end lisp
@end defvar
@@ -27766,6 +27767,10 @@ Database Services
@item @code{create-database?} (default: @code{#f})
whether to create a database with the same name as the role.
+@item @code{password-file} (default: @code{#f})
+A string representing the path of a file that contains the password to be set
+for the role.
+
@item @code{encoding} (default: @code{"UTF8"})
The character set to use for storing text in the database.
@@ -27794,6 +27799,10 @@ Database Services
@item @code{log} (default: @code{"/var/log/postgresql_roles.log"})
File name of the log file.
+@item @code{requirement} (default: @code{'(user-processes postgres)}) (type: list-of-symbols)
+Set additional Shepherd services dependencies to the provisioned
+Shepherd service.
+
@item @code{roles} (default: @code{'()})
The initial PostgreSQL roles to create.
@end table
diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm
index edc3198ad5..86d4ecb739 100644
--- a/gnu/services/databases.scm
+++ b/gnu/services/databases.scm
@@ -9,6 +9,7 @@
;;; Copyright © 2020, 2022 Marius Bakke <marius <at> gnu.org>
;;; Copyright © 2021 David Larsson <david.larsson <at> selfhosted.xyz>
;;; Copyright © 2021 Aljosha Papsch <ep <at> stern-data.com>
+;;; Copyright © 2025 Giacomo Leidi <goodoldpaul <at> autistici.org>
;;;
;;; This file is part of GNU Guix.
;;;
@@ -32,6 +33,7 @@ (define-module (gnu services databases)
#:autoload (gnu system accounts) (default-shell)
#:use-module (gnu packages admin)
#:use-module (gnu packages base)
+ #:use-module (gnu packages bash)
#:use-module (gnu packages databases)
#:use-module (guix build-system trivial)
#:use-module (guix build union)
@@ -68,14 +70,18 @@ (define-module (gnu services databases)
postgresql-service
postgresql-service-type
+ %postgresql-role-shepherd-requirement
+
postgresql-role
postgresql-role?
postgresql-role-name
+ postgresql-role-password-file
postgresql-role-permissions
postgresql-role-create-database?
postgresql-role-configuration
postgresql-role-configuration?
postgresql-role-configuration-host
+ postgresql-role-configuration-requirement
postgresql-role-configuration-roles
postgresql-role-service-type
@@ -390,6 +396,8 @@ (define-record-type* <postgresql-role>
postgresql-role make-postgresql-role
postgresql-role?
(name postgresql-role-name) ;string
+ (password-file postgresql-role-password-file ;string
+ (default #f))
(permissions postgresql-role-permissions
(default '(createdb login))) ;list
(create-database? postgresql-role-create-database? ;boolean
@@ -403,11 +411,16 @@ (define-record-type* <postgresql-role>
(template postgresql-role-template ;string
(default "template1")))
+(define %postgresql-role-shepherd-requirement
+ '(user-processes postgres))
+
(define-record-type* <postgresql-role-configuration>
postgresql-role-configuration make-postgresql-role-configuration
postgresql-role-configuration?
(host postgresql-role-configuration-host ;string
(default "/var/run/postgresql"))
+ (requirement postgresql-role-configuration-requirement ;list-of-symbols
+ (default %postgresql-role-shepherd-requirement))
(log postgresql-role-configuration-log ;string
(default "/var/log/postgresql_roles.log"))
(roles postgresql-role-configuration-roles
@@ -425,19 +438,35 @@ (define (postgresql-create-roles config)
permissions)
" ")))
+ (define (password-value role)
+ (string-append "password_" (postgresql-role-name role)))
+
+ (define (role->password-variable role)
+ (let ((file-name (postgresql-role-password-file role)))
+ (if (string? file-name)
+ ;; This way passwords do not leak to the command line.
+ #~(string-append "-v \"" #$(password-value role)
+ "=$(" #$coreutils "/bin/cat " #$file-name ")\"")
+ "")))
+
(define (roles->queries roles)
(apply mixed-text-file "queries"
(append-map
(lambda (role)
(match-record role <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 "\""
@@ -452,20 +481,30 @@ (define (postgresql-create-roles config)
(let ((host (postgresql-role-configuration-host config))
(roles (postgresql-role-configuration-roles config)))
- #~(let ((psql #$(file-append postgresql "/bin/psql")))
- (list psql "-a" "-h" #$host "-f" #$(roles->queries roles)))))
+ (program-file "run-queries"
+ #~(let ((bash #$(file-append bash-minimal "/bin/bash"))
+ (psql #$(file-append postgresql "/bin/psql")))
+ (define command
+ (string-append
+ "set -e; exec " psql " -a -h " #$host " -f "
+ #$(roles->queries roles) " "
+ (string-join
+ (list
+ #$@(map role->password-variable roles))
+ " ")))
+ (execlp bash bash "-c" command)))))
(define (postgresql-role-shepherd-service config)
(match-record config <postgresql-role-configuration>
- (log)
+ (log requirement)
(list (shepherd-service
- (requirement '(user-processes postgres))
+ (requirement requirement)
(provision '(postgres-roles))
(one-shot? #t)
(start
#~(lambda args
(zero? (spawn-command
- #$(postgresql-create-roles config)
+ (list #$(postgresql-create-roles config))
#:user "postgres"
#:group "postgres"
;; XXX: As of Shepherd 1.0.2, #:log-file is not
@@ -484,6 +523,7 @@ (define postgresql-role-service-type
(match-record config <postgresql-role-configuration>
(host roles)
(postgresql-role-configuration
+ (inherit config)
(host host)
(roles (append roles extended-roles))))))
(default-value (postgresql-role-configuration))
diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm
index fd5041344b..c5da603565 100644
--- a/gnu/tests/databases.scm
+++ b/gnu/tests/databases.scm
@@ -142,6 +142,8 @@ (define %role-log-file
(define %postgresql-os
(simple-operating-system
+ (extra-special-file "/password"
+ (plain-file "password" "hello"))
(service postgresql-service-type
(postgresql-configuration
(postgresql postgresql)
@@ -158,6 +160,10 @@ (define %postgresql-os
(roles
(list (postgresql-role
(name "root")
+ (create-database? #t))
+ (postgresql-role
+ (name "alice")
+ (password-file "/password")
(create-database? #t))))))))
(define (run-postgresql-test)
@@ -230,14 +236,40 @@ (define (run-postgresql-test)
(marionette-eval
'(begin
(use-modules (gnu services herd)
+ (srfi srfi-1)
(ice-9 popen))
(current-output-port
(open-file "/dev/console" "w0"))
+ (every
+ (lambda (role)
+ (let* ((port (open-pipe*
+ OPEN_READ
+ #$(file-append postgresql "/bin/psql")
+ "-tA" "-c"
+ (string-append
+ "SELECT 1 FROM pg_database WHERE"
+ " datname='" role "'")))
+ (output (get-string-all port)))
+ (close-pipe port)
+ (string-contains output "1")))
+ '("root" "alice")))
+ marionette))
+
+ (test-assert "database passwords are set"
+ (marionette-eval
+ '(begin
+ (use-modules (gnu services herd)
+ (ice-9 match)
+ (ice-9 popen))
+ (current-output-port
+ (open-file "/dev/console" "w0"))
+ (setgid (passwd:gid (getpwnam "alice")))
+ (setuid (passwd:uid (getpw "alice")))
+ (setenv "PGPASSWORD" "hello")
(let* ((port (open-pipe*
OPEN_READ
- #$(file-append postgresql "/bin/psql")
- "-tA" "-c" "SELECT 1 FROM pg_database WHERE
- datname='root'"))
+ #$(file-append postgresql "/bin/psql") "-tA" "-c"
+ "SELECT 1 FROM pg_database WHERE datname='alice'"))
(output (get-string-all port)))
(close-pipe port)
(string-contains output "1")))
base-commit: 97ea59b846c5267098a019f36c84dcaa55fb123e
--
2.49.0
GNU bug tracking system
Copyright (C) 1999 Darren O. Benham,
1997,2003 nCipher Corporation Ltd,
1994-97 Ian Jackson.