From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp2.migadu.com ([2001:41d0:403:58f0::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms13.migadu.com with LMTPS id AGhNE1Pc4ma9LAAAe85BDQ:P1 (envelope-from ) for ; Thu, 12 Sep 2024 12:19:31 +0000 Received: from aspmx1.migadu.com ([2001:41d0:403:58f0::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp2.migadu.com with LMTPS id AGhNE1Pc4ma9LAAAe85BDQ (envelope-from ) for ; Thu, 12 Sep 2024 14:19:31 +0200 X-Envelope-To: larch@yhetil.org Authentication-Results: aspmx1.migadu.com; dkim=fail ("headers rsa verify failed") header.d=debbugs.gnu.org header.s=debbugs-gnu-org header.b=hNaenu9B; dkim=fail ("headers rsa verify failed") header.d=autistici.org header.s=stigmate header.b=brKGBIgF; spf=pass (aspmx1.migadu.com: domain of "guix-patches-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="guix-patches-bounces+larch=yhetil.org@gnu.org"; dmarc=pass (policy=none) header.from=gnu.org ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1726143571; h=from:from:sender:sender:reply-to:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:cc:mime-version:mime-version: content-type:content-type: content-transfer-encoding:content-transfer-encoding:resent-cc: resent-from:resent-sender:resent-message-id:in-reply-to:in-reply-to: references:references:list-id:list-help:list-unsubscribe: list-subscribe:list-post:dkim-signature; bh=/+BZJGkpZte5PEs5Q8crEzeVPdPbRSsPqnz/YiFs0jw=; b=D5rO/L83y4uhkpfUAjKXe9m0KlrGnd5argujSpud/pvsxd/9OTmgNVA60cuEwwNyItk/iG dxy3HL5KVks+0GtmvolFK9eMr4RKdzPfpCsi0BC7XWHBKm0/XYClHMu/RHE1YmoQQz4NcE XwKyuIJAnep26W2UWsmUP7HUS0XSd/VQ950/UMbQJWjjhDZjT0s2pRCYif2+aH0UTMmKvj Ks9REkidb7VGREag+LhvoUl4MI8nQFLYAanKVrVkHAPW+aGPfq50fdrNhsOe4mukvfOutL aQx5zEjDV5CpBa1rUubYO9OVWFBt2WRsl3nGEHsDjc7PXLF5tiYpyruOMPcPlg== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=fail ("headers rsa verify failed") header.d=debbugs.gnu.org header.s=debbugs-gnu-org header.b=hNaenu9B; dkim=fail ("headers rsa verify failed") header.d=autistici.org header.s=stigmate header.b=brKGBIgF; spf=pass (aspmx1.migadu.com: domain of "guix-patches-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="guix-patches-bounces+larch=yhetil.org@gnu.org"; dmarc=pass (policy=none) header.from=gnu.org ARC-Seal: i=1; s=key1; d=yhetil.org; t=1726143571; a=rsa-sha256; cv=none; b=RGIfIq73v4eJRlI3h9fTZRPKwgPr9r3iF4KCuHHHPNVqZcgBTG7cBywcCAmi27zEKaxlKl Oe0e+cDVdleSKsp5MlFLEc0lca0TCJQxBjYJNG+4Rl/Zp3fsVtn1twkyTqnqTFF3GqYinJ 3z9LRCi7qrXcu6LPILgojMbusBXfp1PfEocDN6XJNyishnHnsWKwZm5SY6NMucUEIh1svH S7EB2s+mQlGmdXHiA5K6e2iI7kpG0iZ+Fuo4tXgoYzcqmeoBo/tzaiKSIBH4iI+n76GI0Y 9HMGlezoJBba9daQKPaQgRJqVNUA6oS0Nx7e4yJWTo/kFUePa0j9cGzxobTNUA== Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id E342E6CBC2 for ; Thu, 12 Sep 2024 14:19:30 +0200 (CEST) Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1soimq-0001vG-0b; Thu, 12 Sep 2024 08:19:00 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1soimn-0001uz-TP for guix-patches@gnu.org; Thu, 12 Sep 2024 08:18:57 -0400 Received: from debbugs.gnu.org ([2001:470:142:5::43]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1soimm-0006gP-8q; Thu, 12 Sep 2024 08:18:56 -0400 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=debbugs.gnu.org; s=debbugs-gnu-org; h=MIME-Version:Date:From:To:In-Reply-To:References:Subject; bh=/+BZJGkpZte5PEs5Q8crEzeVPdPbRSsPqnz/YiFs0jw=; b=hNaenu9BJPKENYXpaLuG7+LQlXeCUEcS+0eoGLzZcpbmmHk1jaFOfpXXQNrxIFe2WTsCgmgZGCfhL7p/u7K9rIRGb5heydTRG+4YL7bXk+9WwTgXQaGm93HEqKuFv7QvMojo47pPHVa7gsLTHVF+uEyPF3tckFSF4PQ8i3LMzyLT9BOefuzKuyRs6HZdL1MnKMviNC+lCJrwlfob2FP/nWZbjINwKNFSc7AS7StuqOQ8C6lokAEnlOZBNPNLJpKicTP/tn7HmKJ6+LjgZbAhsO/4MTUnHRbbXAKv38ntIRwecpD7sj3ZXrRTgKf9BaFRsS1ZLPsj2PsRDLtHjYy3+A==; Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1soims-0006Nz-8t; Thu, 12 Sep 2024 08:19:02 -0400 X-Loop: help-debbugs@gnu.org Subject: [bug#73196] [PATCH v2] services: postgresql-role: Add support for password files. References: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@autistici.org> In-Reply-To: <98e22806482f6ce257decf5e1d3097fc08925212.1726140263.git.goodoldpaul@autistici.org> Resent-From: Giacomo Leidi Original-Sender: "Debbugs-submit" Resent-CC: pelzflorian@pelzflorian.de, ludo@gnu.org, maxim.cournoyer@gmail.com, guix-patches@gnu.org Resent-Date: Thu, 12 Sep 2024 12:19:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 73196 X-GNU-PR-Package: guix-patches X-GNU-PR-Keywords: patch To: 73196@debbugs.gnu.org Cc: Giacomo Leidi , Florian Pelz , Ludovic =?UTF-8?Q?Court=C3=A8s?= , Maxim Cournoyer X-Debbugs-Original-Xcc: Florian Pelz , Ludovic =?UTF-8?Q?Court=C3=A8s?= , Maxim Cournoyer Received: via spool by 73196-submit@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 ) id 1soimj-0006NR-CA for submit@debbugs.gnu.org; Thu, 12 Sep 2024 08:18:54 -0400 Received: from confino.investici.org ([93.190.126.19]:24121) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1soimh-0006NJ-PK for 73196@debbugs.gnu.org; Thu, 12 Sep 2024 08:18:52 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=autistici.org; s=stigmate; t=1726143523; bh=/+BZJGkpZte5PEs5Q8crEzeVPdPbRSsPqnz/YiFs0jw=; h=From:To:Cc:Subject:Date:From; b=brKGBIgFKTDr8Kx25Z2VQXlahNb9eGg/jUs5DdCSiAYWVDTirjk9jSfUfcABadK3l VKqAsC/e61dtrboA3SibxIy4qi9W7bckLxJGUx5JUDAqRNfulmn3WZTiL53YN1rKYa c883Lo+hFgiOGeUI2s1I9iCaIEzKvN1Qh6s3WGl4= Received: from mx1.investici.org (unknown [127.0.0.1]) by confino.investici.org (Postfix) with ESMTP id 4X4Ggl3xlKz11B2; Thu, 12 Sep 2024 12:18:43 +0000 (UTC) Received: from [93.190.126.19] (mx1.investici.org [93.190.126.19]) (Authenticated sender: goodoldpaul@autistici.org) by localhost (Postfix) with ESMTPSA id 4X4Ggl3Bs3z117s; Thu, 12 Sep 2024 12:18:43 +0000 (UTC) Date: Thu, 12 Sep 2024 14:18:28 +0200 Message-ID: X-Mailer: git-send-email 2.46.0 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list X-BeenThere: guix-patches@gnu.org List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Reply-to: Giacomo Leidi X-ACL-Warn: , Giacomo Leidi via Guix-patches From: Giacomo Leidi via Guix-patches via Errors-To: guix-patches-bounces+larch=yhetil.org@gnu.org Sender: guix-patches-bounces+larch=yhetil.org@gnu.org X-Migadu-Flow: FLOW_IN X-Migadu-Country: US X-Migadu-Queue-Id: E342E6CBC2 X-Migadu-Scanner: mx11.migadu.com X-Spam-Score: -5.59 X-Migadu-Spam-Score: -5.59 X-TUID: X5GH3cCSnjym This commit adds a password-file to the postgresql-role field. It allows users to provision Postgres roles with a set password. * gnu/services/databases.scm (postgresql-role): Add password-file field; (postgresql-role-configuration): add requirement field; (postgresql-create-roles): add support for setting passwords from a file without leaking passwords to the command line; (postgresql-role-shepherd-service): add support for customizable requirements. * gnu/tests/databases.scm: Test it. * doc/guix.texi: Document it. Change-Id: I3aabaa10b0c5e826c5aa874e5649e25a3508a585 --- doc/guix.texi | 15 ++++++++--- gnu/services/databases.scm | 51 ++++++++++++++++++++++++++++++++------ gnu/tests/databases.scm | 38 +++++++++++++++++++++++++--- 3 files changed, 90 insertions(+), 14 deletions(-) diff --git a/doc/guix.texi b/doc/guix.texi index 981ffb8c58..8e6f1b8b2a 100644 --- a/doc/guix.texi +++ b/doc/guix.texi @@ -26294,9 +26294,10 @@ Database Services @lisp (service-extension postgresql-role-service-type - (const (postgresql-role - (name "alice") - (create-database? #t)))) + (const (list + (postgresql-role + (name "alice") + (create-database? #t))))) @end lisp @end defvar @@ -26319,6 +26320,10 @@ Database Services @item @code{create-database?} (default: @code{#f}) whether to create a database with the same name as the role. +@item @code{password-file} (default: @code{#f}) +A string representing the path of a file that contains the password to be set +for the role. + @item @code{encoding} (default: @code{"UTF8"}) The character set to use for storing text in the database. @@ -26347,6 +26352,10 @@ Database Services @item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) File name of the log file. +@item @code{requirement} (default: @code{'()}) (type: list-of-symbols) +Set additional Shepherd services dependencies to the provisioned +Shepherd service. + @item @code{roles} (default: @code{'()}) The initial PostgreSQL roles to create. @end table diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index fa332d7978..d77988d8c5 100644 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -9,6 +9,7 @@ ;;; Copyright © 2020, 2022 Marius Bakke ;;; Copyright © 2021 David Larsson ;;; Copyright © 2021 Aljosha Papsch +;;; Copyright © 2024 Giacomo Leidi ;;; ;;; This file is part of GNU Guix. ;;; @@ -31,6 +32,7 @@ (define-module (gnu services databases) #:use-module (gnu system shadow) #:use-module (gnu packages admin) #:use-module (gnu packages base) + #:use-module (gnu packages bash) #:use-module (gnu packages databases) #:use-module (guix build-system trivial) #:use-module (guix build union) @@ -65,11 +67,13 @@ (define-module (gnu services databases) postgresql-role postgresql-role? postgresql-role-name + postgresql-role-password-file postgresql-role-permissions postgresql-role-create-database? postgresql-role-configuration postgresql-role-configuration? postgresql-role-configuration-host + postgresql-role-configuration-requirement postgresql-role-configuration-roles postgresql-role-service-type @@ -372,6 +376,8 @@ (define-record-type* postgresql-role make-postgresql-role postgresql-role? (name postgresql-role-name) ;string + (password-file postgresql-role-password-file ;string + (default #f)) (permissions postgresql-role-permissions (default '(createdb login))) ;list (create-database? postgresql-role-create-database? ;boolean @@ -390,6 +396,8 @@ (define-record-type* postgresql-role-configuration? (host postgresql-role-configuration-host ;string (default "/var/run/postgresql")) + (requirement postgresql-role-configuration-requirement ;list-of-symbols + (default '())) (log postgresql-role-configuration-log ;string (default "/var/log/postgresql_roles.log")) (roles postgresql-role-configuration-roles @@ -407,19 +415,36 @@ (define (postgresql-create-roles config) permissions) " "))) + (define (password-value role) + (string-append "password_" (postgresql-role-name role))) + + (define (role->password-variable role) + (define file-name + (postgresql-role-password-file role)) + (if (string? file-name) + ;; This way passwords do not leak to the command line + (string-append "-v \"" (password-value role) + "=$(cat " file-name ")\"") + "")) + (define (roles->queries roles) (apply mixed-text-file "queries" (append-map (lambda (role) (match-record role (name permissions create-database? encoding collation ctype - template) + template password-file) `("SELECT NOT(EXISTS(SELECT 1 FROM pg_catalog.pg_roles WHERE \ rolname = '" ,name "')) as not_exists;\n" "\\gset\n" "\\if :not_exists\n" "CREATE ROLE \"" ,name "\"" " WITH " ,(format-permissions permissions) +,(if (and (string? password-file) + (not (string-null? password-file))) + (string-append + "\nPASSWORD :'" (password-value role) "'") + "") ";\n" ,@(if create-database? `("CREATE DATABASE \"" ,name "\"" @@ -434,20 +459,30 @@ (define (postgresql-create-roles config) (let ((host (postgresql-role-configuration-host config)) (roles (postgresql-role-configuration-roles config))) - #~(let ((psql #$(file-append postgresql "/bin/psql"))) - (list psql "-a" "-h" #$host "-f" #$(roles->queries roles))))) + (program-file "run-queries" + #~(let ((bash #$(file-append bash-minimal "/bin/bash")) + (psql #$(file-append postgresql "/bin/psql"))) + (define command + (string-append + "set -e; exec " psql " -c -a -h " #$host " -f " + #$(roles->queries roles) " " + (string-join + (list + #$@(map role->password-variable roles)) + " "))) + (execlp bash bash "-c" command))))) (define (postgresql-role-shepherd-service config) (match-record config - (log) + (log requirement) (list (shepherd-service - (requirement '(postgres)) + (requirement `(postgres ,@requirement)) (provision '(postgres-roles)) (one-shot? #t) (start #~(lambda args (let ((pid (fork+exec-command - #$(postgresql-create-roles config) + (list #$(postgresql-create-roles config)) #:user "postgres" #:group "postgres" #:log-file #$log))) @@ -462,9 +497,9 @@ (define postgresql-role-service-type (compose concatenate) (extend (lambda (config extended-roles) (match-record config - (host roles) + (roles) (postgresql-role-configuration - (host host) + (inherit config) (roles (append roles extended-roles)))))) (default-value (postgresql-role-configuration)) (description "Ensure the specified PostgreSQL roles are diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index 7c8b87942f..81484b2954 100644 --- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -142,6 +142,8 @@ (define %role-log-file (define %postgresql-os (simple-operating-system + (extra-special-file "/password" + (plain-file "password" "hello")) (service postgresql-service-type (postgresql-configuration (postgresql postgresql) @@ -158,6 +160,10 @@ (define %postgresql-os (roles (list (postgresql-role (name "root") + (create-database? #t)) + (postgresql-role + (name "alice") + (password-file "/password") (create-database? #t)))))))) (define (run-postgresql-test) @@ -230,14 +236,40 @@ (define (run-postgresql-test) (marionette-eval '(begin (use-modules (gnu services herd) + (srfi srfi-1) (ice-9 popen)) (current-output-port (open-file "/dev/console" "w0")) + (every + (lambda (role) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tA" "-c" + (string-append + "SELECT 1 FROM pg_database WHERE" + " datname='" role "'"))) + (output (get-string-all port))) + (close-pipe port) + (string-contains output "1"))) + '("root" "alice"))) + marionette)) + + (test-assert "database passwords are set" + (marionette-eval + '(begin + (use-modules (gnu services herd) + (ice-9 match) + (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (setgid (passwd:gid (getpwnam "alice"))) + (setuid (passwd:uid (getpw "alice"))) + (setenv "PGPASSWORD" "hello") (let* ((port (open-pipe* OPEN_READ - #$(file-append postgresql "/bin/psql") - "-tA" "-c" "SELECT 1 FROM pg_database WHERE - datname='root'")) + #$(file-append postgresql "/bin/psql") "-tA" "-c" + "SELECT 1 FROM pg_database WHERE datname='alice'")) (output (get-string-all port))) (close-pipe port) (string-contains output "1"))) base-commit: 590904cca15922e6474fbd3a71af9b3a45b268af -- 2.46.0