Thread: How to make PostreSQL utilities honor home directories?

Hi Everyone,

We are having a heck of a time getting PostreSQL utilities to honor
home directories. For example, when I execute this script:

    sudo -H -u postgres PGPASSWORD=${password} \
        psql -h "${hostname}" -U "${username}" -d "${database}" \
        --command="..."

It produces failures:

    could not change directory to "/home/jwalton/godojo": Permission denied

/home/jwalton is my home directory. The postgres user does not have
access to my stuff. The postgres user's home directory is
/var/lib/pgsql .

Reading through the `psql --help` options and searching on the web is
not turning up any hits.

We are also observing the errors when using pg_isready .

How to make PostreSQL utilities honor home directories?

Thanks in advance.

Jeff

On 9/1/22 3:01 PM, Jeffrey Walton wrote:
> Hi Everyone,
> 
> We are having a heck of a time getting PostreSQL utilities to honor
> home directories. For example, when I execute this script:
> 
>      sudo -H -u postgres PGPASSWORD=${password} \
>          psql -h "${hostname}" -U "${username}" -d "${database}" \
>          --command="..."

Why are you doing this in the first place?

Just do:

psql -h "${hostname}" -U "${username}" -d "${database}" \
         --command="..."

and use .pgpass:

https://www.postgresql.org/docs/14/libpq-pgpass.html

or set PGPASSWORD in your home env.

> 
> It produces failures:
> 
>      could not change directory to "/home/jwalton/godojo": Permission denied
> 
> /home/jwalton is my home directory. The postgres user does not have
> access to my stuff. The postgres user's home directory is
> /var/lib/pgsql .
> 
> Reading through the `psql --help` options and searching on the web is
> not turning up any hits.
> 
> We are also observing the errors when using pg_isready .
> 
> How to make PostreSQL utilities honor home directories?
> 
> Thanks in advance.
> 
> Jeff
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Jeffrey Walton <noloader@gmail.com> writes:
> We are having a heck of a time getting PostreSQL utilities to honor
> home directories. For example, when I execute this script:

>     sudo -H -u postgres PGPASSWORD=${password} \
>         psql -h "${hostname}" -U "${username}" -d "${database}" \
>         --command="..."

> It produces failures:

>     could not change directory to "/home/jwalton/godojo": Permission denied

You've left out quite a lot of information here ... like what
connection that directory has to do with anything.  Is it your
current directory when you invoke this command?

If so, a plausible explanation is that psql is trying to chase a
symlink to somewhere, which involves some chdir's so it can resolve
the symlink correctly, and afterwards it has to change back to
where it started --- which would fail if it can't look up that
directory.

Why it's trying to resolve a symlink isn't apparent though.
Is the "psql" you're invoking a symlink to somewhere?

            regards, tom lane

On 9/1/22 3:01 PM, Jeffrey Walton wrote:
> Hi Everyone,
> 
> We are having a heck of a time getting PostreSQL utilities to honor
> home directories. For example, when I execute this script:
> 
>      sudo -H -u postgres PGPASSWORD=${password} \
>          psql -h "${hostname}" -U "${username}" -d "${database}" \
>          --command="..."
> 
> It produces failures:
> 
>      could not change directory to "/home/jwalton/godojo": Permission denied

What if you add -i?:

sudo -H -u postgres -i ...

> 
> /home/jwalton is my home directory. The postgres user does not have
> access to my stuff. The postgres user's home directory is
> /var/lib/pgsql .
> 
> Reading through the `psql --help` options and searching on the web is
> not turning up any hits.
> 
> We are also observing the errors when using pg_isready .
> 
> How to make PostreSQL utilities honor home directories?
> 
> Thanks in advance.
> 
> Jeff
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Well, like others mentioned before, it is not getting fully clear
what You are trying to achieve. But, in any case, if this is Your
problem ....

On Thu, Sep 01, 2022 at 06:01:02PM -0400, Jeffrey Walton wrote:
! Hi Everyone,
! 
! We are having a heck of a time getting PostreSQL utilities to honor
! home directories. For example, when I execute this script:
! 
!     sudo -H -u postgres PGPASSWORD=${password} \
!         psql -h "${hostname}" -U "${username}" -d "${database}" \
!         --command="..."
! 
! It produces failures:
! 
!     could not change directory to "/home/jwalton/godojo": Permission denied

 ... this appears to me as rather a sudo issue. Because certainly
psql cannot execute /as user postgres/ in a directory where user
postgres is not allowed to enter. So sudo should fix that, and in
my sudo installation I find either a "-D directory" option for
sudo (that should change the directory accordingly) or a "--login"
option (that would run a full login shell for the user postgres,
which, alongside going to the postgres homedir, does a lot of
other things which may or may not be desireable in your installation).

On 2022-09-01 18:16:14 -0400, Tom Lane wrote:
> Jeffrey Walton <noloader@gmail.com> writes:
> > We are having a heck of a time getting PostreSQL utilities to honor
> > home directories. For example, when I execute this script:
>
> >     sudo -H -u postgres PGPASSWORD=${password} \
> >         psql -h "${hostname}" -U "${username}" -d "${database}" \
> >         --command="..."
>
> > It produces failures:
>
> >     could not change directory to "/home/jwalton/godojo": Permission denied
>
> You've left out quite a lot of information here ... like what
> connection that directory has to do with anything.  Is it your
> current directory when you invoke this command?

Probably. See below.


> If so, a plausible explanation is that psql is trying to chase a
> symlink to somewhere, which involves some chdir's so it can resolve
> the symlink correctly, and afterwards it has to change back to
> where it started --- which would fail if it can't look up that
> directory.
>
> Why it's trying to resolve a symlink isn't apparent though.
> Is the "psql" you're invoking a symlink to somewhere?

It is on Debian/Ubuntu:

% ls -l =psql
lrwxrwxrwx 1 root root 37 Aug 11 11:25 /bin/psql -> ../share/postgresql-common/pg_wrapper*

(this is the pgdg package)

I do get the same message, but psql seems to start normally:

% sudo -u postgres -H psql
could not change directory to "/home/hjp/tmp/t": Permission denied
Null display is "(∅)".
Line style is unicode.
Border style is 2.
Unicode border line style is "double".
Timing is on.
Expanded display is used automatically.
psql (13.8 (Ubuntu 13.8-1.pgdg20.04+1), server 11.17 (Ubuntu 11.17-1.pgdg20.04+1))
Type "help" for help.

postgres=#

However, when I start a shell, I see that the directory has been
changed:

postgres=# \!
postgres@trintignant:/usr/lib/postgresql/13/bin$


However, the symlink doesn't seem to be the culprit. If I run

% sudo -u postgres -H /usr/lib/postgresql/13/bin/psql

(which is not a symlink)

I get the same behaviour. So it seems that psql changes to its basedir
and then can't change back again.

And sure enough, strace shows:

chdir("/usr/lib/postgresql/13/bin")     = 0
chdir("/home/hjp/tmp/t")                = 0
chdir("/usr/lib/postgresql/13/bin")     = 0
chdir("/home/hjp/tmp/t")                = 0

(this is without sudo, because I can't strace that)

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On Thu, Sep 1, 2022 at 8:23 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2022-09-01 18:16:14 -0400, Tom Lane wrote:
> > Jeffrey Walton <noloader@gmail.com> writes:
> > > We are having a heck of a time getting PostreSQL utilities to honor
> > > home directories. For example, when I execute this script:
> >
> > >     sudo -H -u postgres PGPASSWORD=${password} \
> > >         psql -h "${hostname}" -U "${username}" -d "${database}" \
> > >         --command="..."
> >
> > > It produces failures:
> >
> > >     could not change directory to "/home/jwalton/godojo": Permission denied
> >
> > You've left out quite a lot of information here ... like what
> > connection that directory has to do with anything.  Is it your
> > current directory when you invoke this command?
>
> Probably. See below.
>
>
> > If so, a plausible explanation is that psql is trying to chase a
> > symlink to somewhere, which involves some chdir's so it can resolve
> > the symlink correctly, and afterwards it has to change back to
> > where it started --- which would fail if it can't look up that
> > directory.
> >
> > Why it's trying to resolve a symlink isn't apparent though.
> > Is the "psql" you're invoking a symlink to somewhere?
>
> It is on Debian/Ubuntu:
>
> % ls -l =psql
> lrwxrwxrwx 1 root root 37 Aug 11 11:25 /bin/psql -> ../share/postgresql-common/pg_wrapper*
>
> (this is the pgdg package)
>
> I do get the same message, but psql seems to start normally:
>
> % sudo -u postgres -H psql
> could not change directory to "/home/hjp/tmp/t": Permission denied
> Null display is "(∅)".
> Line style is unicode.
> Border style is 2.
> Unicode border line style is "double".
> Timing is on.
> Expanded display is used automatically.
> psql (13.8 (Ubuntu 13.8-1.pgdg20.04+1), server 11.17 (Ubuntu 11.17-1.pgdg20.04+1))
> Type "help" for help.
>
> postgres=#
>
> However, when I start a shell, I see that the directory has been
> changed:
>
> postgres=# \!
> postgres@trintignant:/usr/lib/postgresql/13/bin$
>
>
> However, the symlink doesn't seem to be the culprit. If I run
>
> % sudo -u postgres -H /usr/lib/postgresql/13/bin/psql
>
> (which is not a symlink)
>
> I get the same behaviour. So it seems that psql changes to its basedir
> and then can't change back again.
>
> And sure enough, strace shows:
>
> chdir("/usr/lib/postgresql/13/bin")     = 0
> chdir("/home/hjp/tmp/t")                = 0
> chdir("/usr/lib/postgresql/13/bin")     = 0
> chdir("/home/hjp/tmp/t")                = 0
>
> (this is without sudo, because I can't strace that)

Thanks Peter.

I guess there is no way to avoid the problem.

And if interested, others have trouble, too. I found this when
searching for a resolution:
https://github.com/ANXS/postgresql/issues/499 .

Jeff

"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> However, the symlink doesn't seem to be the culprit. If I run
> % sudo -u postgres -H /usr/lib/postgresql/13/bin/psql
> (which is not a symlink)
> I get the same behaviour. So it seems that psql changes to its basedir
> and then can't change back again.

Ah --- looking closer at that code, it will chdir *before* checking
whether the target file is a symlink, which is probably unnecessarily
stupid.  I'm wondering whether we could drop that logic altogether [1],
but that won't help you today.

I concur with the other person asking why you want to sudo to postgres
at all, though.  It's generally safest if the client side isn't running
as the same user as the server.

            regards, tom lane

[1] https://www.postgresql.org/message-id/797232.1662075573%40sss.pgh.pa.us

On Thu, Sep 1, 2022 at 8:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> > However, the symlink doesn't seem to be the culprit. If I run
> > % sudo -u postgres -H /usr/lib/postgresql/13/bin/psql
> > (which is not a symlink)
> > I get the same behaviour. So it seems that psql changes to its basedir
> > and then can't change back again.
>
> Ah --- looking closer at that code, it will chdir *before* checking
> whether the target file is a symlink, which is probably unnecessarily
> stupid.  I'm wondering whether we could drop that logic altogether [1],
> but that won't help you today.
>
> I concur with the other person asking why you want to sudo to postgres
> at all, though.  It's generally safest if the client side isn't running
> as the same user as the server.

The use case is an install of DefectDojo [2]. I _think_ they are
taking advantage of the fact that as root, you don't need to
authenticate because of postgresql's use of domain sockets. (Hat tip
for that, by the way). The installer code will install packages, setup
the database, install the DefectDojo programs, etc.

Jeff

> [1] https://www.postgresql.org/message-id/797232.1662075573%40sss.pgh.pa.us
[2] https://github.com/DefectDojo/godojo

On 9/1/22 6:31 PM, Jeffrey Walton wrote:
> On Thu, Sep 1, 2022 at 8:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> "Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
>>> However, the symlink doesn't seem to be the culprit. If I run
>>> % sudo -u postgres -H /usr/lib/postgresql/13/bin/psql
>>> (which is not a symlink)
>>> I get the same behaviour. So it seems that psql changes to its basedir
>>> and then can't change back again.
>>
>> Ah --- looking closer at that code, it will chdir *before* checking
>> whether the target file is a symlink, which is probably unnecessarily
>> stupid.  I'm wondering whether we could drop that logic altogether [1],
>> but that won't help you today.
>>
>> I concur with the other person asking why you want to sudo to postgres
>> at all, though.  It's generally safest if the client side isn't running
>> as the same user as the server.
> 
> The use case is an install of DefectDojo [2]. I _think_ they are
> taking advantage of the fact that as root, you don't need to
> authenticate because of postgresql's use of domain sockets. (Hat tip
> for that, by the way). The installer code will install packages, setup
> the database, install the DefectDojo programs, etc.

You are not running as root(Postgres won't allow you do this anyway) you 
are running as postgres system user and the authentication is handled by 
pg_hba.conf. I'm also betting that if you look at pg_hba.conf it is set 
up to do peer authentication and hence the need to be postgres system 
user. You can obtain a similar result in pg_hba.conf with:

TYPE  DATABASE        USER            ADDRESS                 METHOD
local  all            postgres                                trust

Then you could eliminate the whole sudo dance.

Or set up:

TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres      all             <some_host>             scram-
sha-256

if you want password protection.

> 
> Jeff
> 
>> [1] https://www.postgresql.org/message-id/797232.1662075573%40sss.pgh.pa.us
> [2] https://github.com/DefectDojo/godojo
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2022-09-01 21:10:44 -0700, Adrian Klaver wrote:
> On 9/1/22 6:31 PM, Jeffrey Walton wrote:
> > On Thu, Sep 1, 2022 at 8:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > I concur with the other person asking why you want to sudo to postgres
> > > at all, though.  It's generally safest if the client side isn't running
> > > as the same user as the server.
> >
> > The use case is an install of DefectDojo [2]. I _think_ they are
> > taking advantage of the fact that as root, you don't need to
> > authenticate because of postgresql's use of domain sockets. (Hat tip
> > for that, by the way). The installer code will install packages, setup
> > the database, install the DefectDojo programs, etc.
>
> You are not running as root(Postgres won't allow you do this anyway) you are
> running as postgres system user and the authentication is handled by
> pg_hba.conf. I'm also betting that if you look at pg_hba.conf it is set up
> to do peer authentication and hence the need to be postgres system user. You
> can obtain a similar result in pg_hba.conf with:
>
> TYPE  DATABASE        USER            ADDRESS                 METHOD
> local  all            postgres                                trust
>
> Then you could eliminate the whole sudo dance.

OTOH it allows any user on the machine to connect as postgres, which may
or may not be a problem.

I like to use ident for local connections:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     ident map=localusers

and in pg_ident.conf:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
localusers      root                    postgres

Then root can invoke `psql -U postgres ...`, but other users can't.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 2022-09-01 20:49:56 -0400, Jeffrey Walton wrote:
> On Thu, Sep 1, 2022 at 8:23 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> >
> > On 2022-09-01 18:16:14 -0400, Tom Lane wrote:
> > > Jeffrey Walton <noloader@gmail.com> writes:
> > > > We are having a heck of a time getting PostreSQL utilities to honor
> > > > home directories. For example, when I execute this script:
> > >
> > > >     sudo -H -u postgres PGPASSWORD=${password} \
> > > >         psql -h "${hostname}" -U "${username}" -d "${database}" \
> > > >         --command="..."
> > >
> > > > It produces failures:
> > >
> > > >     could not change directory to "/home/jwalton/godojo": Permission denied

> > I get the same behaviour. So it seems that psql changes to its basedir
> > and then can't change back again.
>
> I guess there is no way to avoid the problem.

Changing to /tmp (or some other directory accessible by posgres) before
running the script would avoid it.
As would (temporarily) changing the permissions of the diretor(y/ies).
Or not using sudo at all (see other messages).

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On Fri, Sep 2, 2022 at 7:34 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2022-09-01 20:49:56 -0400, Jeffrey Walton wrote:
> > On Thu, Sep 1, 2022 at 8:23 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> > >
> > > On 2022-09-01 18:16:14 -0400, Tom Lane wrote:
> > > > Jeffrey Walton <noloader@gmail.com> writes:
> > > > > We are having a heck of a time getting PostreSQL utilities to honor
> > > > > home directories. For example, when I execute this script:
> > > >
> > > > >     sudo -H -u postgres PGPASSWORD=${password} \
> > > > >         psql -h "${hostname}" -U "${username}" -d "${database}" \
> > > > >         --command="..."
> > > >
> > > > > It produces failures:
> > > >
> > > > >     could not change directory to "/home/jwalton/godojo": Permission denied
>
> > > I get the same behaviour. So it seems that psql changes to its basedir
> > > and then can't change back again.
> >
> > I guess there is no way to avoid the problem.
>
> Changing to /tmp (or some other directory accessible by posgres) before
> running the script would avoid it.
> As would (temporarily) changing the permissions of the diretor(y/ies).
> Or not using sudo at all (see other messages).

Ok, thanks everyone.