Subject: bug#38198: closed (Re: bug#38198: missing shell for postgresql system user)

From: [email-masked] (GNU bug Tracking System)
Date: Thu, 14 Nov 2019 10:03:02 +0000

Your bug report

#38198: missing shell for postgresql system user

which was filed against the guix package, has been closed.

The explanation is attached below, along with your original report.
If you require more details, please reply to [email-masked].

-- 
38198: http://debbugs.gnu.org/cgi/bugreport.cgi?bug=38198
GNU Bug Tracking System
Contact [email-masked] with problems

Subject: Re: bug#38198: missing shell for postgresql system user
From: Giovanni Biscuolo <[email-masked]>
To: Gábor Boskovits <[email-masked]>
Cc: [email-masked]
Date: Thu, 14 Nov 2019 11:02:15 +0100
List-Id: <debbugs-submit.debbugs.gnu.org>

Hello Gábor,

Gábor Boskovits <[email-masked]> writes:

[...]

>> To access a database server on localhost for the first time as the user
>> postgres (the superuser) a person should use:
>>
>> --8<---------------cut here---------------start------------->8---
>> sudo su postgres -c 'psql'
>> --8<---------------cut here---------------end--------------->8---

[...]

> I would not mind this change, I think it is ok. However it is easy to
> work around this with su -s.
> I usually do that.

Oh, that's the real solution: thanks! (I should study more...)

  sudo su postgres -s psql

Since giving a shell to the postgres user is **not** the solution, I'm
closing this bug.

I'll try to send a patch to the relevant documentation in the manual,
since now that we changed "trust" to "peer" a regular user cannot
connect to a newly initialized database using `psql -U postgres` (and is
good for security reasons); the user must be part of the "wheel" group
(or be authorized via sudoers in other ways) to be able to use `sudo su
postgres -s psql`

[...]

Thanks! Gio'

-- 
Giovanni Biscuolo

Xelera IT Infrastructures

Subject: missing shell for postgresql system user
From: Giovanni Biscuolo <[email-masked]>
To: [email-masked]
Date: Wed, 13 Nov 2019 18:36:52 +0100
List-Id: <debbugs-submit.debbugs.gnu.org>

Hello Guix!

Current postgresql access rules (pg_hba.conf) defaults to (see
[bug#36191] for details on that patch):

--8<---------------cut here---------------start------------->8---
local	all	all			peer
host	all	all	127.0.0.1/32 	md5
host	all	all	::1/128 	md5
--8<---------------cut here---------------end--------------->8---

Peer authentication works by obtaining the (local) client's operating
system user name from the kernel and using it as the allowed database
user name, and is better than "trust" authentication

To access a database server on localhost for the first time as the user
postgres (the superuser) a person should use:

--8<---------------cut here---------------start------------->8---
sudo su postgres -c 'psql'
--8<---------------cut here---------------end--------------->8---

AFAIK this is the only method available after database initialization,
with peer authentication

Since the postgres user currently have a nologin shell (from
gnu/services/databases.scm):

--8<---------------cut here---------------start------------->8---
(define %postgresql-accounts
  (list (user-group (name "postgres") (system? #t))
        (user-account
         (name "postgres")
         (group "postgres")
         (system? #t)
         (comment "PostgreSQL server user")
         (home-directory "/var/empty")
         (shell (file-append shadow "/sbin/nologin")))))
--8<---------------cut here---------------end--------------->8---

the above command does not work

As a workaround I changed the postgres user shell to <store>/bin/bash
and I was able to connect

I do not see any security issue giving a shell to postgres, since it's
password is disabled in /etc/shadow so the only way to access as
postgres is via `sudo su postgres`

Thougts?

Thanks, Gio'

-- 
Giovanni Biscuolo

Xelera IT Infrastructures