GNU bug report logs - #63926
[PATCH] services: postgresql: Add more role fields.

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

Received: (at 63926) by debbugs.gnu.org; 14 Jun 2023 10:38:49 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Wed Jun 14 06:38:49 2023
Received: from localhost ([127.0.0.1]:43811 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1q9Nto-0005WE-T0
	for submit <at> debbugs.gnu.org; Wed, 14 Jun 2023 06:38:49 -0400
Received: from eggs.gnu.org ([209.51.188.92]:34244)
 by debbugs.gnu.org with esmtp (Exim 4.84_2)
 (envelope-from <ludo@HIDDEN>) id 1q9Ntm-0005W2-NC
 for 63926 <at> debbugs.gnu.org; Wed, 14 Jun 2023 06:38:47 -0400
Received: from fencepost.gnu.org ([2001:470:142:3::e])
 by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
 (Exim 4.90_1) (envelope-from <ludo@HIDDEN>)
 id 1q9Ntf-0005XS-Go; Wed, 14 Jun 2023 06:38:39 -0400
DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=gnu.org;
 s=fencepost-gnu-org; h=MIME-Version:In-Reply-To:Date:References:Subject:To:
 From; bh=b6utA5TiVy2gyTr619GJ2ldZclNBxq9p/L3OQq4NH9M=; b=gY0k/O7A18Vxh4mT+fxt
 qUjsCGyXDrrJCROoUzbg+0KrPbsqKYCPYQfqXZFZ1Bipa65TqsMhw5I7fJf3InHkLHuDNLh5rcvSd
 YQ9RjnzdymROJuclueHyVci7F/GklCb/GkWJL78n2KRfst/awf8CwGW15TINv3siKR4iw8pwhu5Ci
 Uy8tZlmQ8itJVQfklvnm/+kbTb5ywQTHPiypvc8BDthWvufPsKzKYdAQjq6sgz5rKQ0ehglkt4Uir
 p47YCP2hzurw9NNikCGWiYHz5xeV+8nL2gbS/2Hj9jCRMm/dU0npx8NpoALmqp8BuuTSMSOLEwrMi
 jhsfaVrQOY5jpQ==;
Received: from 91-160-117-201.subs.proxad.net ([91.160.117.201] helo=ribbon)
 by fencepost.gnu.org with esmtpsa (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
 (Exim 4.90_1) (envelope-from <ludo@HIDDEN>)
 id 1q9Ntf-0005C1-3y; Wed, 14 Jun 2023 06:38:39 -0400
From: =?utf-8?Q?Ludovic_Court=C3=A8s?= <ludo@HIDDEN>
To: Miguel =?utf-8?Q?=C3=81ngel?= Moreno <me@HIDDEN>
Subject: Re: bug#63926: [PATCH] services: postgresql: Add more role fields.
References: <86legxos70.fsf@HIDDEN>
Date: Wed, 14 Jun 2023 12:38:36 +0200
In-Reply-To: <86legxos70.fsf@HIDDEN> ("Miguel =?utf-8?Q?=C3=81ngel?=
 Moreno"'s message of "Tue, 06 Jun 2023 11:28:35 +0200")
Message-ID: <87v8fqs503.fsf@HIDDEN>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/28.2 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
X-Spam-Score: -2.3 (--)
X-Debbugs-Envelope-To: 63926
Cc: Christopher Baines <mail@HIDDEN>, 63926 <at> debbugs.gnu.org
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: -3.3 (---)

Hi,

(Cc: Christopher Baines who knows pgsql better than I do.)

Miguel =C3=81ngel Moreno <me@HIDDEN> skribis:

> * gnu/services/databases.scm (postgresql-role): Add more role fields.
> (postgresql-create-roles): Honor it.
> * doc/guix.texi (Database Services): Document it.

That looks like an improvement!  Some comments:

>  @item @code{create-database?} (default: @code{#f})
> -Whether to create a database with the same name as the role.
> +whether to create a database with the same name as the role.

This change is unnecessary (leftover?).

> +@item @code{encoding} (default: @code{"UTF8"})
> +The character set to use for storing text in the database.

Or "UTF-8"?  It might be worth mentioning the naming convention for
encodings, especially if it differs from
<http://www.iana.org/assignments/character-sets>.

> +@item @code{collation} (default: @code{"en_US.utf8"})
> +The string sort order locale setting.
> +
> +@item @code{ctype} (default: @code{"en_US.utf8"})
> +The character classification locale setting.
> +
> +@item @code{template} (default: @code{"template1"})
> +The default template to copy the new database from when creating it.
> +Use @code{"template0"} for a pristine database with no system-local
> +modifications.

Are these defaults equivalent to those that were previously in effect?
If would be ideal.

Thanks, and apologies for the delay!

Ludo=E2=80=99.

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

Received: (at submit) by debbugs.gnu.org; 6 Jun 2023 13:49:53 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Tue Jun 06 09:49:53 2023
Received: from localhost ([127.0.0.1]:51231 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1q6X4K-0001Zm-AF
	for submit <at> debbugs.gnu.org; Tue, 06 Jun 2023 09:49:52 -0400
Received: from lists.gnu.org ([209.51.188.17]:47854)
 by debbugs.gnu.org with esmtp (Exim 4.84_2)
 (envelope-from <me@HIDDEN>) id 1q6SzZ-0006xt-8Q
 for submit <at> debbugs.gnu.org; Tue, 06 Jun 2023 05:28:42 -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 <me@HIDDEN>) id 1q6SzZ-0004kO-2E
 for guix-patches@HIDDEN; Tue, 06 Jun 2023 05:28:41 -0400
Received: from relay2-d.mail.gandi.net ([217.70.183.194])
 by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
 (Exim 4.90_1) (envelope-from <me@HIDDEN>) id 1q6SzX-0006Wh-9o
 for guix-patches@HIDDEN; Tue, 06 Jun 2023 05:28:40 -0400
X-GND-Sasl: me@HIDDEN
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mianmoreno.com;
 s=gm1; t=1686043717;
 h=from:from: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;
 bh=D5t8CXyoCjk7qAwTrY/4JaJY5oNibLod+NyBX27gmls=;
 b=bzeSQo3Gk187DT8TIQ1h/cEiPv81wO7ihavPUT1lJkb8ONiAgey5X/jwDfySAAZvtNjyg9
 Zc3827MYBNtl3iM2VFh6JJA18vNGOJjzyLMfrpG9Kcnr77PKcvRjSIOOryAsUc2brQnmho
 w0rGD1MWIPsngfeblqay3IRmN+LGxsDl06QKfsbayE1XP/44grFCOd8kUJIf5MvZHavzON
 bjH9x7VZTL54lZH5MmOJYcIHZE4mzlf4fRB6S4gE0y4FHVGTjSmIFDogHZLiTfZST0UU+S
 vbvEwg7N8h70VfwKN/XkcZKEV5OzkieXufViK3zT+dNuRH1zT/Dfl6hPIM1hhQ==
X-GND-Sasl: me@HIDDEN
Received: by mail.gandi.net (Postfix) with ESMTPSA id 7624E40015;
 Tue,  6 Jun 2023 09:28:35 +0000 (UTC)
From: Miguel =?utf-8?Q?=C3=81ngel?= Moreno <me@HIDDEN>
To: guix-patches@HIDDEN
Subject: [PATCH] services: postgresql: Add more role fields.
Date: Tue, 06 Jun 2023 11:28:35 +0200
Message-ID: <86legxos70.fsf@HIDDEN>
User-Agent: Gnus/5.13 (Gnus v5.13)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Received-SPF: pass client-ip=217.70.183.194; envelope-from=me@HIDDEN;
 helo=relay2-d.mail.gandi.net
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, RCVD_IN_MSPIKE_H3=0.001, RCVD_IN_MSPIKE_WL=0.001,
 SPF_HELO_NONE=0.001, SPF_PASS=-0.001,
 T_SCC_BODY_TEXT_LINE=-0.01 autolearn=ham autolearn_force=no
X-Spam_action: no action
X-Spam-Score: -1.6 (-)
X-Debbugs-Envelope-To: submit
X-Mailman-Approved-At: Tue, 06 Jun 2023 09:49:41 -0400
Cc: me@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.6 (--)

* gnu/services/databases.scm (postgresql-role): Add more role fields.
(postgresql-create-roles): Honor it.
* doc/guix.texi (Database Services): Document it.
---
 doc/guix.texi              | 16 +++++++++++++++-
 gnu/services/databases.scm | 19 ++++++++++++++++---
 2 files changed, 31 insertions(+), 4 deletions(-)

diff --git a/doc/guix.texi b/doc/guix.texi
index 60972f408d..e384a3a13d 100644
--- a/doc/guix.texi
+++ b/doc/guix.texi
@@ -24784,7 +24784,21 @@ Database Services
 @code{superuser}.
=20
 @item @code{create-database?} (default: @code{#f})
-Whether to create a database with the same name as the role.
+whether to create a database with the same name as the role.
+
+@item @code{encoding} (default: @code{"UTF8"})
+The character set to use for storing text in the database.
+
+@item @code{collation} (default: @code{"en_US.utf8"})
+The string sort order locale setting.
+
+@item @code{ctype} (default: @code{"en_US.utf8"})
+The character classification locale setting.
+
+@item @code{template} (default: @code{"template1"})
+The default template to copy the new database from when creating it.
+Use @code{"template0"} for a pristine database with no system-local
+modifications.
=20
 @end table
 @end deftp
diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm
index e8e42d3b7b..7148971c1d 100644
--- a/gnu/services/databases.scm
+++ b/gnu/services/databases.scm
@@ -363,7 +363,15 @@ (define-record-type* <postgresql-role>
   (permissions      postgresql-role-permissions
                     (default '(createdb login))) ;list
   (create-database? postgresql-role-create-database?  ;boolean
-                    (default #f)))
+                    (default #f))
+  (encoding postgresql-role-encoding ;string
+            (default "UTF8"))
+  (collation postgresql-role-collation ;string
+             (default "en_US.utf8"))
+  (ctype postgresql-role-ctype ;string
+         (default "en_US.utf8"))
+  (template postgresql-role-template ;string
+            (default "template1")))
=20
 (define-record-type* <postgresql-role-configuration>
   postgresql-role-configuration make-postgresql-role-configuration
@@ -392,7 +400,8 @@ (define (postgresql-create-roles config)
            (append-map
             (lambda (role)
               (match-record role <postgresql-role>
-                (name permissions create-database?)
+                (name permissions create-database? encoding collation ctype
+                      template)
                 `("SELECT NOT(EXISTS(SELECT 1 FROM pg_catalog.pg_roles WHE=
RE \
 rolname =3D '" ,name "')) as not_exists;\n"
 "\\gset\n"
@@ -402,7 +411,11 @@ (define (postgresql-create-roles config)
 ";\n"
 ,@(if create-database?
       `("CREATE DATABASE \"" ,name "\""
-        " OWNER \"" ,name "\";\n")
+        " OWNER \"" ,name "\"\n"
+        " ENCODING '" ,encoding "'\n"
+        " LC_COLLATE '" ,collation "'\n"
+        " LC_CTYPE '" ,ctype "'\n"
+        " TEMPLATE " ,template ";")
       '())
 "\\endif\n")))
             roles)))
--=20
2.40.1

--=20
Best regards,
Miguel =C3=81ngel Moreno