GNU bug report logs - #41786
[PATCH] doc: Explain how to use psql with peer authentication.

Previous Next

To add a comment to this bug, you must first unarchive it, by sending
a message to control AT debbugs.gnu.org, with unarchive 41786 in the body.
You can then email your comments to 41786 AT debbugs.gnu.org in the normal way.

Toggle the display of automated, internal messages from the tracker.

Message #5 received at submit <at> debbugs.gnu.org (full text, mbox):

From: Pierre Neidhardt <mail <at> ambrevar.xyz>
To: guix-patches <at> gnu.org
Subject: [PATCH] doc: Explain how to use psql with peer authentication.
Date: Wed, 10 Jun 2020 11:31:47 +0200

* doc/guix.texi (Database Services): Add example of shell commands to use psql
as  system user.  Also add troubleshooting tip when service fails to start
because of incompatible cluster.
---
 doc/guix.texi | 18 ++++++++++++++++++
 1 file changed, 18 insertions(+)

diff --git a/doc/guix.texi b/doc/guix.texi
index 15e077a41c..1326be69e5 100644
--- a/doc/guix.texi
+++ b/doc/guix.texi
@@ -16787,6 +16787,24 @@ The PostgreSQL daemon loads its runtime configuration from @var{config-file},
 creates a database cluster with @var{locale} as the default
 locale, stored in @var{data-directory}.  It then listens on @var{port}.
 
+If the services fails to start, it may be due to an incompatible
+cluster already present in @var{data-directory}.  Adjust it (or, if you
+don't need the cluster anymore, delete @var{data-directory}), then
+restart the service.
+
+Peer authentication is used by default and the @code{postgres} user
+account has no shell, which prevents the direct execution of @code{psql}
+commands as this user.  To use @code{psql}, you can temporarily log in
+as @code{postgres} using a shell, create a PostgreSQL superuser with the
+same name as one of the system users and then create the associated
+database.
+
+@example
+sudo su postgres -s /bin/sh
+createuser --interface
+createdb $MY_USER_LOGIN      # Replace appropriately.
+@end example
+
 @cindex postgresql extension-packages
 Additional extensions are loaded from packages listed in
 @var{extension-packages}.  Extensions are available at runtime.  For instance,
-- 
2.26.2

Message #8 received at 41786 <at> debbugs.gnu.org (full text, mbox):

From: Oleg Pykhalov <go.wigust <at> gmail.com>
To: Pierre Neidhardt <mail <at> ambrevar.xyz>
Cc: 41786 <at> debbugs.gnu.org
Subject: Re: [bug#41786] [PATCH] doc: Explain how to use psql with peer
 authentication.
Date: Sat, 25 Jul 2020 22:11:12 +0300

[Message part 1 (text/plain, inline)]

Hi Pierre,

Pierre Neidhardt <mail <at> ambrevar.xyz> writes:

> * doc/guix.texi (Database Services): Add example of shell commands to use psql
> as  system user.  Also add troubleshooting tip when service fails to start
> because of incompatible cluster.
> ---
>  doc/guix.texi | 18 ++++++++++++++++++
>  1 file changed, 18 insertions(+)
>
> diff --git a/doc/guix.texi b/doc/guix.texi
> index 15e077a41c..1326be69e5 100644
> --- a/doc/guix.texi
> +++ b/doc/guix.texi
> @@ -16787,6 +16787,24 @@ The PostgreSQL daemon loads its runtime configuration from @var{config-file},
>  creates a database cluster with @var{locale} as the default
>  locale, stored in @var{data-directory}.  It then listens on @var{port}.
>  
> +If the services fails to start, it may be due to an incompatible
> +cluster already present in @var{data-directory}.  Adjust it (or, if you
> +don't need the cluster anymore, delete @var{data-directory}), then
> +restart the service.

OK, deleting data-directory probably will work for every issue,
e.g. data corruption for files inside :-).


Every ‘guix system reconfigure’ I see a message about
“/var/lib/postgresql/ already exists, skipping creating” if I remember
correctly.  Do you see this when you use a cluster?  

I don't have a strong opinion for this, but the message is enough, isn't
it?  Maybe we could say in the documentation to remove data directory in
any case when you don't need previous data?

> +Peer authentication is used by default and the @code{postgres} user
> +account has no shell, which prevents the direct execution of @code{psql}
> +commands as this user.  To use @code{psql}, you can temporarily log in
> +as @code{postgres} using a shell, create a PostgreSQL superuser with the
> +same name as one of the system users and then create the associated
> +database.
> +
> +@example
> +sudo su postgres -s /bin/sh
> +createuser --interface
> +createdb $MY_USER_LOGIN      # Replace appropriately.
> +@end example

I tried ‘sudo su postgres -s /bin/sh’, then:
--8<---------------cut here---------------start------------->8---
sh-5.0$ createuser --interface
sh: createuser: command not found
--8<---------------cut here---------------end--------------->8---

First of all we could use just ‘sudo’ to get a shell:

    sudo -u postgres -s /bin/sh


‘createuser’ and ‘createdb’ are in ‘/gnu/store/…-postgresql-10.13/bin’,
which is missing in my ‘postgres’ user's profile.

To fix this we could make ‘postgres’ available by default as in
https://git.savannah.gnu.org/cgit/guix.git/commit/?id=db65d7447c3adc46e2b24abfc07ae10c6c537df4

WDYT?


Regards,
Oleg.

[signature.asc (application/pgp-signature, inline)]

Message #11 received at 41786 <at> debbugs.gnu.org (full text, mbox):

From: Pierre Neidhardt <mail <at> ambrevar.xyz>
To: Oleg Pykhalov <go.wigust <at> gmail.com>
Cc: 41786 <at> debbugs.gnu.org
Subject: Re: [bug#41786] [PATCH] doc: Explain how to use psql with peer
 authentication.
Date: Sun, 26 Jul 2020 12:18:53 +0200

[Message part 1 (text/plain, inline)]

Oleg Pykhalov <go.wigust <at> gmail.com> writes:

> Every ‘guix system reconfigure’ I see a message about
> “/var/lib/postgresql/ already exists, skipping creating” if I remember
> correctly.  Do you see this when you use a cluster?  

Yes.

> I don't have a strong opinion for this, but the message is enough, isn't
> it?  Maybe we could say in the documentation to remove data directory in
> any case when you don't need previous data?

I don't find the message explicit enough, in particular it does not
explain that the existing data could cause Postgres to fail.

Finally, it's easy to miss the message in this wall of text.

> I tried ‘sudo su postgres -s /bin/sh’, then:
> --8<---------------cut here---------------start------------->8---
> sh-5.0$ createuser --interface
> sh: createuser: command not found
> --8<---------------cut here---------------end--------------->8---
>
> First of all we could use just ‘sudo’ to get a shell:
>
>     sudo -u postgres -s /bin/sh

I thought I had tried this and it didn't work for whatever reason.  I'll
test again.

> ‘createuser’ and ‘createdb’ are in ‘/gnu/store/…-postgresql-10.13/bin’,
> which is missing in my ‘postgres’ user's profile.

Right, I have postgresql installed in my user profile.

> To fix this we could make ‘postgres’ available by default as in
> https://git.savannah.gnu.org/cgit/guix.git/commit/?id=db65d7447c3adc46e2b24abfc07ae10c6c537df4

I think you meant the postgresql package.  Indeed, that would fix it.
However this means we are "polluting" all user profiles with executables
that are not necessarily wanted by everyone.  Is this desirable?

-- 
Pierre Neidhardt
https://ambrevar.xyz/

[signature.asc (application/pgp-signature, inline)]

Message #14 received at 41786 <at> debbugs.gnu.org (full text, mbox):

From: Oleg Pykhalov <go.wigust <at> gmail.com>
To: Pierre Neidhardt <mail <at> ambrevar.xyz>
Cc: 41786 <at> debbugs.gnu.org
Subject: Re: [bug#41786] [PATCH] doc: Explain how to use psql with peer
 authentication.
Date: Sun, 26 Jul 2020 14:26:17 +0300

[Message part 1 (text/plain, inline)]

Pierre Neidhardt <mail <at> ambrevar.xyz> writes:

[…]

>> ‘createuser’ and ‘createdb’ are in ‘/gnu/store/…-postgresql-10.13/bin’,
>> which is missing in my ‘postgres’ user's profile.
>
> Right, I have postgresql installed in my user profile.
>
>> To fix this we could make ‘postgres’ available by default as in
>> https://git.savannah.gnu.org/cgit/guix.git/commit/?id=db65d7447c3adc46e2b24abfc07ae10c6c537df4
>
> I think you meant the postgresql package.  Indeed, that would fix it.
> However this means we are "polluting" all user profiles with executables
> that are not necessarily wanted by everyone.  Is this desirable?

I see only one reason not to polluting - we probably would like to
‘maintain’ this decision by not removing postgresql package from all
user profiles later :-).

On the other hand:

- You have a running process because of service and postgresql client
  technically available to everyone via /gnu/store/…postgresql…/bin
- Adding postgresql to /run/current-system/profile/bin shouldn't break
  anyone.
- postgresql is useless without first time configuration by becoming a
  ‘postgres’ user to create databases and users.


If you don't like polluting, then please expand the documentation about
finding correct postgres client.  Personally I do this by ‘pgrep -fa
postgres’ as my user before becoming ‘postgres’ user, then copy the
‘/gnu/store/…-postgresql-10.13/bin/postgres’ to run ‘psql’ after ‘sudo’.

Thanks,
Oleg.

[signature.asc (application/pgp-signature, inline)]

Message #17 received at 41786 <at> debbugs.gnu.org (full text, mbox):

From: Pierre Neidhardt <mail <at> ambrevar.xyz>
To: 41786 <at> debbugs.gnu.org
Subject: [PATCH 1/2] services: postgresql: Provide postgresql commands.
Date: Tue, 28 Jul 2020 13:58:35 +0200

* gnu/services/databases.scm (postgresql-service-type): Extend
profile-service-type to provide postgresql commands.
---
 gnu/services/databases.scm | 4 +++-
 1 file changed, 3 insertions(+), 1 deletion(-)

diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm
index 473ece4e97..2bddf70f71 100644
--- a/gnu/services/databases.scm
+++ b/gnu/services/databases.scm
@@ -276,7 +276,9 @@ host	all	all	::1/128 	md5"))
                        (service-extension activation-service-type
                                           postgresql-activation)
                        (service-extension account-service-type
-                                          (const %postgresql-accounts))))
+                                          (const %postgresql-accounts))
+                       (service-extension profile-service-type
+                                          (compose list postgresql-configuration-postgresql))))
                 (default-value (postgresql-configuration))))
 
 (define* (postgresql-service #:key (postgresql postgresql)

base-commit: 64e8f2ec2dc6301019a5b93a82787ea6adaf8c76
-- 
2.27.0

Message #20 received at 41786 <at> debbugs.gnu.org (full text, mbox):

From: Pierre Neidhardt <mail <at> ambrevar.xyz>
To: 41786 <at> debbugs.gnu.org
Subject: [PATCH 2/2] doc: Explain how to use psql with peer authentication.
Date: Tue, 28 Jul 2020 13:58:36 +0200

* doc/guix.texi (Database Services): Add example of shell commands to use psql
as  system user.  Also add troubleshooting tip when service fails to start
because of incompatible cluster.
---
 doc/guix.texi | 18 ++++++++++++++++++
 1 file changed, 18 insertions(+)

diff --git a/doc/guix.texi b/doc/guix.texi
index 74c7417174..aba3f925b9 100644
--- a/doc/guix.texi
+++ b/doc/guix.texi
@@ -17260,6 +17260,24 @@ The PostgreSQL daemon loads its runtime configuration from @var{config-file},
 creates a database cluster with @var{locale} as the default
 locale, stored in @var{data-directory}.  It then listens on @var{port}.
 
+If the services fails to start, it may be due to an incompatible
+cluster already present in @var{data-directory}.  Adjust it (or, if you
+don't need the cluster anymore, delete @var{data-directory}), then
+restart the service.
+
+Peer authentication is used by default and the @code{postgres} user
+account has no shell, which prevents the direct execution of @code{psql}
+commands as this user.  To use @code{psql}, you can temporarily log in
+as @code{postgres} using a shell, create a PostgreSQL superuser with the
+same name as one of the system users and then create the associated
+database.
+
+@example
+sudo -u postgres -s /bin/sh
+createuser --interface
+createdb $MY_USER_LOGIN      # Replace appropriately.
+@end example
+
 @cindex postgresql extension-packages
 Additional extensions are loaded from packages listed in
 @var{extension-packages}.  Extensions are available at runtime.  For instance,
-- 
2.27.0

Message #23 received at 41786 <at> debbugs.gnu.org (full text, mbox):

From: Pierre Neidhardt <mail <at> ambrevar.xyz>
To: Oleg Pykhalov <go.wigust <at> gmail.com>
Cc: 41786 <at> debbugs.gnu.org
Subject: Re: [bug#41786] [PATCH] doc: Explain how to use psql with peer
 authentication.
Date: Tue, 28 Jul 2020 13:59:47 +0200

[Message part 1 (text/plain, inline)]

I've just sent an update which includes the postgresql commands in the
service and changes the sudo command.

Let me know if you are satisfied with these changes or if you think the
doc paragraph could still be improved.

Cheers!

-- 
Pierre Neidhardt
https://ambrevar.xyz/

[signature.asc (application/pgp-signature, inline)]

Message #26 received at 41786 <at> debbugs.gnu.org (full text, mbox):

From: Oleg Pykhalov <go.wigust <at> gmail.com>
To: Pierre Neidhardt <mail <at> ambrevar.xyz>
Cc: 41786 <at> debbugs.gnu.org
Subject: Re: [bug#41786] [PATCH 2/2] doc: Explain how to use psql with peer
 authentication.
Date: Tue, 28 Jul 2020 21:02:53 +0300

[Message part 1 (text/plain, inline)]

Hi,

Pierre Neidhardt <mail <at> ambrevar.xyz> writes:

[…]

> +@example
> +sudo -u postgres -s /bin/sh
> +createuser --interface
                ^^^^^^^^^
Rather “interactive”.

Otherwise LGTH.

Thanks,
Oleg.

[signature.asc (application/pgp-signature, inline)]

Message #29 received at 41786 <at> debbugs.gnu.org (full text, mbox):

From: Pierre Neidhardt <mail <at> ambrevar.xyz>
To: Oleg Pykhalov <go.wigust <at> gmail.com>
Cc: 41786 <at> debbugs.gnu.org
Subject: Re: [bug#41786] [PATCH 2/2] doc: Explain how to use psql with peer
 authentication.
Date: Sat, 01 Aug 2020 12:03:48 +0200

[Message part 1 (text/plain, inline)]

Merged in 1f0a41e95505c12af17c416651d70f18ea7c6a07, thanks!

[signature.asc (application/pgp-signature, inline)]

Previous Next

GNU bug tracking system
Copyright (C) 1999 Darren O. Benham, 1997,2003 nCipher Corporation Ltd, 1994-97 Ian Jackson.