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

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

[Pierre Neidhardt]

* 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

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

[Oleg Pykhalov]

[-- Attachment #1: Type: text/plain, Size: 2703 bytes --]

Hi Pierre,

Pierre Neidhardt <mail@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.

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 832 bytes --]

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

[Pierre Neidhardt]

[-- Attachment #1: Type: text/plain, Size: 1741 bytes --]

Oleg Pykhalov <go.wigust@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/

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 487 bytes --]

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

[Oleg Pykhalov]

[-- Attachment #1: Type: text/plain, Size: 1537 bytes --]

Pierre Neidhardt <mail@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.

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 832 bytes --]

[bug#41786] [PATCH 1/2] services: postgresql: Provide postgresql commands.

[Pierre Neidhardt]

* 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

[bug#41786] [PATCH 2/2] doc: Explain how to use psql with peer authentication.

[Pierre Neidhardt]

* 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

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

[Pierre Neidhardt]

[-- Attachment #1: Type: text/plain, Size: 285 bytes --]

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/

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 487 bytes --]

[bug#41786] [PATCH 2/2] doc: Explain how to use psql with peer authentication.

[Oleg Pykhalov]

[-- Attachment #1: Type: text/plain, Size: 227 bytes --]

Hi,

Pierre Neidhardt <mail@ambrevar.xyz> writes:

[…]

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

Otherwise LGTH.

Thanks,
Oleg.

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 832 bytes --]

[bug#41786] [PATCH 2/2] doc: Explain how to use psql with peer authentication.

[Pierre Neidhardt]

[-- Attachment #1: Type: text/plain, Size: 60 bytes --]

Merged in 1f0a41e95505c12af17c416651d70f18ea7c6a07, thanks!

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 487 bytes --]