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.
Message #59 received at 73196 <at> debbugs.gnu.org (full text, mbox):
From: Giacomo Leidi <goodoldpaul <at> autistici.org> To: 73196 <at> debbugs.gnu.org Cc: Giacomo Leidi <goodoldpaul <at> autistici.org> Subject: [PATCH v8] services: postgresql-role: Add support for password files. Date: Tue, 29 Apr 2025 17:51:10 +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.
(postgresql-role-service-type): Pass on postgresql-role-configuration
fields values by default, this way user configured fields are not lost.
* gnu/tests/databases.scm: Test it.
* doc/guix.texi: Document the new field and fix the extension point example.
Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585
---
doc/guix.texi | 15 +++++++--
gnu/services/databases.scm | 64 +++++++++++++++++++++++++++++++-------
gnu/tests/databases.scm | 51 ++++++++++++++++++++++++++++--
3 files changed, 112 insertions(+), 18 deletions(-)
diff --git a/doc/guix.texi b/doc/guix.texi
index 7b418a40892..42073e52edb 100644
--- a/doc/guix.texi
+++ b/doc/guix.texi
@@ -27741,9 +27741,10 @@ Database Services
@lisp
(service-extension postgresql-role-service-type
- (const (postgresql-role
- (name "alice")
- (create-database? #t))))
+ (const (list
+ (postgresql-role
+ (name "alice")
+ (create-database? #t)))))
@end lisp
@end defvar
@@ -27766,6 +27767,10 @@ Database Services
@item @code{create-database?} (default: @code{#f})
whether to create a database with the same name as the role.
+@item @code{password-file} (default: @code{#f})
+A string representing the path of a file that contains the password to be set
+for the role.
+
@item @code{encoding} (default: @code{"UTF8"})
The character set to use for storing text in the database.
@@ -27794,6 +27799,10 @@ Database Services
@item @code{log} (default: @code{"/var/log/postgresql_roles.log"})
File name of the log file.
+@item @code{shepherd-requirement} (default: @code{'(user-processes postgres)}) (type: list-of-symbols)
+Set additional Shepherd services dependencies to the provisioned
+Shepherd service.
+
@item @code{roles} (default: @code{'()})
The initial PostgreSQL roles to create.
@end table
diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm
index edc3198ad50..882543ce5f4 100644
--- a/gnu/services/databases.scm
+++ b/gnu/services/databases.scm
@@ -9,6 +9,7 @@
;;; Copyright © 2020, 2022 Marius Bakke <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
+ %default-postgresql-role-shepherd-requirement
+
postgresql-role
postgresql-role?
postgresql-role-name
+ postgresql-role-password-file
postgresql-role-permissions
postgresql-role-create-database?
postgresql-role-configuration
postgresql-role-configuration?
postgresql-role-configuration-host
+ postgresql-role-configuration-shepherd-requirement
postgresql-role-configuration-roles
postgresql-role-service-type
@@ -390,6 +396,8 @@ (define-record-type* <postgresql-role>
postgresql-role make-postgresql-role
postgresql-role?
(name postgresql-role-name) ;string
+ (password-file postgresql-role-password-file ;string
+ (default #f))
(permissions postgresql-role-permissions
(default '(createdb login))) ;list
(create-database? postgresql-role-create-database? ;boolean
@@ -403,15 +411,20 @@ (define-record-type* <postgresql-role>
(template postgresql-role-template ;string
(default "template1")))
+(define %default-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"))
- (log postgresql-role-configuration-log ;string
- (default "/var/log/postgresql_roles.log"))
- (roles postgresql-role-configuration-roles
- (default '()))) ;list
+ (host postgresql-role-configuration-host ;string
+ (default "/var/run/postgresql"))
+ (shepherd-requirement postgresql-role-configuration-shepherd-requirement ;list-of-symbols
+ (default %default-postgresql-role-shepherd-requirement))
+ (log postgresql-role-configuration-log ;string
+ (default "/var/log/postgresql_roles.log"))
+ (roles postgresql-role-configuration-roles
+ (default '()))) ;list
(define (postgresql-create-roles config)
;; See: https://www.postgresql.org/docs/current/sql-createrole.html for the
@@ -425,19 +438,35 @@ (define (postgresql-create-roles config)
permissions)
" ")))
+ (define (password-value role)
+ (string-append "password_" (postgresql-role-name role)))
+
+ (define (role->password-variable role)
+ (let ((file-name (postgresql-role-password-file role)))
+ (if (string? file-name)
+ ;; This way passwords do not leak to the command line.
+ #~(string-append "-v \"" #$(password-value role)
+ "=$(" #$coreutils "/bin/cat " #$file-name ")\"")
+ "")))
+
(define (roles->queries roles)
(apply mixed-text-file "queries"
(append-map
(lambda (role)
(match-record role <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 shepherd-requirement)
(list (shepherd-service
- (requirement '(user-processes postgres))
+ (requirement shepherd-requirement)
(provision '(postgres-roles))
(one-shot? #t)
(start
#~(lambda args
(zero? (spawn-command
- #$(postgresql-create-roles config)
+ (list #$(postgresql-create-roles config))
#:user "postgres"
#:group "postgres"
;; XXX: As of Shepherd 1.0.2, #:log-file is not
@@ -484,6 +523,7 @@ (define postgresql-role-service-type
(match-record config <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 fd5041344b6..84ec2987d68 100644
--- a/gnu/tests/databases.scm
+++ b/gnu/tests/databases.scm
@@ -1,6 +1,7 @@
;;; GNU Guix --- Functional package management for GNU
;;; Copyright © 2017 Christopher Baines <mail <at> cbaines.net>
;;; Copyright © 2020, 2022 Marius Bakke <marius <at> gnu.org>
+;;; Copyright © 2025 Giacomo Leidi <goodoldpaul <at> autistici.org>
;;;
;;; This file is part of GNU Guix.
;;;
@@ -142,6 +143,8 @@ (define %role-log-file
(define %postgresql-os
(simple-operating-system
+ (extra-special-file "/password"
+ (plain-file "password" "hello"))
(service postgresql-service-type
(postgresql-configuration
(postgresql postgresql)
@@ -158,6 +161,10 @@ (define %postgresql-os
(roles
(list (postgresql-role
(name "root")
+ (create-database? #t))
+ (postgresql-role
+ (name "a_database")
+ (password-file "/password")
(create-database? #t))))))))
(define (run-postgresql-test)
@@ -230,17 +237,55 @@ (define (run-postgresql-test)
(marionette-eval
'(begin
(use-modules (gnu services herd)
+ (srfi srfi-1)
(ice-9 popen))
(current-output-port
(open-file "/dev/console" "w0"))
+ (every
+ (lambda (role)
+ (let* ((port (open-pipe*
+ OPEN_READ
+ #$(file-append postgresql "/bin/psql")
+ "-tA" "-c"
+ (string-append
+ "SELECT 1 FROM pg_database WHERE"
+ " datname='" role "'")))
+ (output (get-string-all port)))
+ (close-pipe port)
+ (string-contains output "1")))
+ '("root" "a_database")))
+ marionette))
+
+ (test-assert "database use fails without a password"
+ (marionette-eval
+ '(begin
+ (use-modules (gnu services herd)
+ (ice-9 popen))
+ (setgid (passwd:gid (getpwnam "alice")))
+ (setuid (passwd:uid (getpw "alice")))
+ (let ((output
+ (system* #$(file-append postgresql "/bin/psql") "-tA" "-h" "localhost" "-U" "a_database" "-c"
+ "SELECT 1 FROM pg_database WHERE datname='a_database'")))
+ (not (= output 0))))
+ marionette))
+
+ (test-assert "database passwords are set"
+ (marionette-eval
+ '(begin
+ (use-modules (gnu services herd)
+ (ice-9 popen))
+ (setgid (passwd:gid (getpwnam "alice")))
+ (setuid (passwd:uid (getpw "alice")))
+ (setenv "PGPASSWORD"
+ (call-with-input-file "/password" get-string-all))
(let* ((port (open-pipe*
OPEN_READ
#$(file-append postgresql "/bin/psql")
- "-tA" "-c" "SELECT 1 FROM pg_database WHERE
- datname='root'"))
+ "-U" "a_database" "-tA" "-h" "localhost" "-c"
+ "SELECT 1 FROM pg_database WHERE datname='a_database'"))
(output (get-string-all port)))
(close-pipe port)
- (string-contains output "1")))
+ (string=? output "1\n")))
marionette))
(test-end))))
base-commit: 1710c0941db517453ac2b88c0e854e8348172603
--
2.49.0
GNU bug tracking system
Copyright (C) 1999 Darren O. Benham,
1997,2003 nCipher Corporation Ltd,
1994-97 Ian Jackson.