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

* 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(+)

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.

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 <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.

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!