BUG #16915: use psql have error "could not change directory to "/root": Permission denied"

The following bug has been logged on the website:

Bug reference: 16915
Logged by: ChenJianHua
Email address: 370619197(at)qq(dot)com
PostgreSQL version: 13.0
Operating system: CENTOS 7
Description:

Just so that you have the correct expectation, the issue you have has
nothing to do with this ansible repository. I am looking into this as I find
it interesting. It is an issue that came with Postgres 13, as it does not
seem to be present in Postgres 12.

(As a hint, it helps to show your full strace command when you ask us for
help, so that we can reproduce and analyze your issue).

To reproduce your issue, I did the following:

#--- the postgres docker container is debian based, though
docker run -d --name=postgres --rm -e POSTGRES_PASSWORD=password postgres
docker exec -it postgres bash sudo

root(at)c376851a8443:~# sudo -u postgres psql
could not change directory to "/root": Permission denied
psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.

postgres=#

apt update
apt -y install strace procps vim

root(at)c376851a8443:~# strace -f sudo -u postgres psql 2>&1 | grep -E
"chdir|getcwd"
getcwd("/root", 4096) = 6
getcwd(0x7fff854af7e0, 4096) = 6
[pid 584] getcwd("/root", 4096) = 6
[pid 583] getcwd("/root", 1024) = 6
[pid 583] getcwd("/root", 1024) = 6
[pid 583] chdir("/usr/lib/postgresql/13/bin") = 0
[pid 583] getcwd("/usr/lib/postgresql/13/bin", 1024) = 27

[pid 583] chdir("/root") = -1 EACCES (Permission denied)

[pid 583] getcwd("/usr/lib/postgresql/13/bin", 1024) = 27
[pid 583] getcwd("/usr/lib/postgresql/13/bin", 1024) = 27
[pid 583] chdir("/usr/lib/postgresql/13/bin") = 0
[pid 583] getcwd("/usr/lib/postgresql/13/bin", 1024) = 27
[pid 583] chdir("/usr/lib/postgresql/13/bin") = 0
So, your problem is that you run sudo as the root user, and the root home
directory is more protected than other directories.

If you, for example run sudo -u postgres psql as another user, you will not
have the same (and only cosmetic) issue:

# create user
useradd -m arne

# give the user sudo rights
cat<<EOT >> /etc/sudoers
arne ALL=(ALL:ALL) NOPASSWD: ALL
EOT

root(at)c376851a8443:~# su - arne
$ sudo -u postgres psql
psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.

postgres=#
Or, you just run psql as postgres without using sudo when you are root.

root(at)c376851a8443:~# su - postgres -c psql
psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.

postgres=#
Or, you just ignore the error message and just run sudo -u postgres psql.

In a Postgres 12 container:

docker run -d --rm --name=postgres_12 -e POSTGRES_PASSWORD=password
postgres:12
docker exec -it postgres_12 bash

root(at)cd3b256e5e0b:/# sudo -u postgres psql
psql (12.5 (Debian 12.5-1.pgdg100+1))
Type "help" for help.

postgres=#