From debbugs-submit-bounces@debbugs.gnu.org Wed Jan 27 03:49:01 2021 Received: (at 45860) by debbugs.gnu.org; 27 Jan 2021 08:49:01 +0000 Received: from localhost ([127.0.0.1]:43734 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1l4gVY-0001NN-D1 for submit@debbugs.gnu.org; Wed, 27 Jan 2021 03:49:01 -0500 Received: from mira.cbaines.net ([212.71.252.8]:48664) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1l4gVW-0001NC-Mp for 45860@debbugs.gnu.org; Wed, 27 Jan 2021 03:48:59 -0500 Received: from localhost (82-132-234-1.dab.02.net [82.132.234.1]) by mira.cbaines.net (Postfix) with ESMTPSA id 4C62F27BC1A; Wed, 27 Jan 2021 08:48:57 +0000 (GMT) Received: from capella (localhost [127.0.0.1]) by localhost (OpenSMTPD) with ESMTP id 4a0dcf78; Wed, 27 Jan 2021 08:48:56 +0000 (UTC) References: <20210118101628.202607-1-othacehe@gnu.org> <20210118101628.202607-6-othacehe@gnu.org> User-agent: mu4e 1.4.14; emacs 27.1 From: Christopher Baines To: Mathieu Othacehe Subject: Re: [PATCH v2 5/5] services: postgresql: Add postgresql-role-service-type. In-reply-to: <20210118101628.202607-6-othacehe@gnu.org> Date: Wed, 27 Jan 2021 08:48:53 +0000 Message-ID: <87eei6hiiy.fsf@cbaines.net> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" X-Spam-Score: -0.0 (/) X-Debbugs-Envelope-To: 45860 Cc: 45860@debbugs.gnu.org X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) --=-=-= Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Mathieu Othacehe writes: > * gnu/services/databases.scm (postgresql-role, > postgresql-role?, postgresql-role-name, > postgresql-role-permissions, postgresql-role-create-database?, > postgresql-role-configuration, postgresql-role-configuration?, > postgresql-role-configuration-host, postgresql-role-configuration-roles, > postgresql-role-service-type): New procedures. > * gnu/tests/databases.scm: Test it. > * doc/guix.texi: Document it. > --- > doc/guix.texi | 61 ++++++++++++++++++++++++ > gnu/services/databases.scm | 95 ++++++++++++++++++++++++++++++++++++++ > gnu/tests/databases.scm | 44 +++++++++++++++++- > 3 files changed, 199 insertions(+), 1 deletion(-) > > diff --git a/doc/guix.texi b/doc/guix.texi > index 22674e2804..13d95b36d1 100644 > --- a/doc/guix.texi > +++ b/doc/guix.texi > @@ -19427,6 +19427,67 @@ here}. > @end table > @end deftp >=20=20 > +@deffn {Scheme Variable} postgresql-role-service-type > +This service allows to create PostgreSQL roles and databases after > +PostgreSQL service start. Here is an example of its use. > + > +@lisp > +(service postgresql-role-service-type > + (postgresql-role-configuration > + (roles > + (list (postgresql-role > + (name "test") > + (create-database? #t)))))) > +@end lisp > + > +This service can be extended with extra roles, as in this > +example: > + > +@lisp > +(service-extension postgresql-role-service-type > + (const (postgresql-role > + (name "alice") > + (create-database? #t)))) > +@end lisp > +@end deffn > + > +@deftp {Data Type} postgresql-role > +PostgreSQL manages database access permissions using the concept of > +roles. A role can be thought of as either a database user, or a group > +of database users, depending on how the role is set up. Roles can own > +database objects (for example, tables) and can assign privileges on > +those objects to other roles to control who has access to which objects. > + > +@table @asis > +@item @code{name} > +The role name. > + > +@item @code{permissions} (default: @code{'(createdb login)}) > +The role permissions list. Supported permissions are @code{createdb} > +and @code{login}. Why only support these two permissions/options? Accepting strings or symbols, and then just converting to an upper case string would allow all the permission options to be specified. > +@item @code{create-database?} (default: @code{#f}) > +Whether to create a database with the same name as the role. > + > +@end table > +@end deftp > + > +@deftp {Data Type} postgresql-role-configuration > +Data type representing the configuration of > +@var{postgresql-role-service-type}. > + > +@table @asis > +@item @code{host} (default: @code{"/var/run/postgresql"}) > +The PostgreSQL host to connect to. > + > +@item @code{log} (default: @code{"/var/log/postgresql_roles.log"}) > +File name of the log file. > + > +@item @code{roles} (default: @code{'()}) > +The initial PostgreSQL roles to create. > +@end table > +@end deftp > + > @subsubheading MariaDB/MySQL >=20=20 > @defvr {Scheme Variable} mysql-service-type > diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm > index 0d60616156..88e4b1813a 100644 > --- a/gnu/services/databases.scm > +++ b/gnu/services/databases.scm > @@ -58,6 +58,18 @@ > postgresql-service > postgresql-service-type >=20=20 > + postgresql-role > + postgresql-role? > + postgresql-role-name > + postgresql-role-permissions > + postgresql-role-create-database? > + postgresql-role-configuration > + postgresql-role-configuration? > + postgresql-role-configuration-host > + postgresql-role-configuration-roles > + > + postgresql-role-service-type > + > memcached-service-type > memcached-configuration > memcached-configuration? > @@ -343,6 +355,89 @@ and stores the database cluster in @var{data-directo= ry}." > (data-directory data-directory) > (extension-packages extension-packages)))) >=20=20 > +(define-record-type* > + postgresql-role make-postgresql-role > + postgresql-role? > + (name postgresql-role-name) ;string > + (permissions postgresql-role-permissions > + (default '(createdb login))) ;list > + (create-database? postgresql-role-create-database? ;boolean > + (default #f))) > + > +(define-record-type* > + 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 > + > +(define (postgresql-create-roles config) > + ;; See: https://www.postgresql.org/docs/current/sql-createrole.html fo= r the > + ;; complete permissions list. > + (define (format-permissions permissions) > + (let ((dict '((createdb . "CREATEDB") > + (login . "LOGIN")))) > + (string-join (map (lambda (permission) > + (assq-ref dict permission)) > + permissions) > + " "))) > + > + (define (roles->queries roles) > + (apply mixed-text-file "queries" > + (append-map (lambda (role) > + (match-record role > + (name permissions create-database?) > + `("CREATE ROLE " ,name > + " WITH " ,(format-permissions permissions) > + ";\n" > + ,@(if create-database? > + `("CREATE DATABASE " ,name > + " OWNER " ,name ";\n") > + '())))) > + roles))) > + > + (let ((host (postgresql-role-configuration-host config)) > + (roles (postgresql-role-configuration-roles config))) > + (program-file > + "postgresql-create-roles" > + #~(begin > + (let ((psql #$(file-append postgresql "/bin/psql"))) > + (execl psql psql "-a" > + "-h" #$host > + "-f" #$(roles->queries roles))))))) > + > +(define (postgresql-role-shepherd-service config) > + (match-record config > + (log) > + (list (shepherd-service > + (requirement '(postgres)) > + (provision '(postgres-roles)) > + (one-shot? #t) > + (start #~(make-forkexec-constructor > + (list #$(postgresql-create-roles config)) > + #:user "postgres" #:group "postgres" > + #:log-file #$log)) > + (documentation "Create PostgreSQL roles."))))) I'm guessing this service will fail if it's run twice, as the role/database will already exist? --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAmARKPZfFIAAAAAALgAo aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2Jh aW5lcy5uZXQACgkQXiijOwuE9XfAeA//cE5M76YBC2t0L8+Yzxq/9zqxVJZNaWr2 Xp5W1HxUuc5HKw/yxb4nsctOU9RByfHIJ4SpITYr/+v9Gb3pxRgXJODKWKegnqoU /PnTPBauhpW7x7QI2gLRtnhzywwqVjQ0DucGfWXO0uVMh/uxLdcI/x0Zi9Rp4FWZ nXIyj/upN9+wZE6GNSsoxwAqp0Br4bKTuYOw9NycHGmi6hNpBd9YgTGjdFI428qY 5RJ5N/tmvvvoFhyx9EUjrG75gYhKw9N6Rfv9hwWZcOJjCGsHF8cnLCvXm+543R3q 5+15pvLVJZA8EzNIKY0am3yOF/9Cz045oFw+4xs6PDk1PsEBF/5CTCX0h/aAw/4R pEqFAa1PT11JuoXn32iT4ofVKX+xxSIXGiinSvFhSM7VSE3QU3I9ulZ2VWilAcHZ pOnCK3Z0XGc2niy7qoQiLs1CxSdyBMUwdY5UdYjdZOEun96DLsDiB4oSGMUNTLlX mwvGu2Z9af/nUB2IfEhXBWpBMUp45bqcJwP8CSRn/yQh7L+JOLHfPAXyymwZZpxA bNLWdXWJztTEpy3Cx7ygWTAAoGG4daw5n5z/1GhJ2NDPV0fbNndTKvZB9aGQ7qJw gw0IbNbbDhltN+/eWhjKwUgtqzKh8cVxtIiu69k+kYtOGKGq3le/IbjJgCM33eWK BjJL9rQ/vxI= =TZDd -----END PGP SIGNATURE----- --=-=-=--