When I use psql in the root directory, there is an error

[ChenJhua]

have error: could not change directory to "/root": Permission denied

centos8 ~ # sudo -u postgres /usr/pgsql-13/bin/psql 
could not change directory to "/root": Permission denied
psql (13.0)
Type "help" for help.

postgres=# 

When i use strace tracking

[pid 15479] getcwd("/root", 1024)       = 6
[pid 15479] stat("/usr/pgsql-13/bin/psql", {st_mode=S_IFREG|0755, st_size=644216, ...}) = 0
[pid 15479] access("/usr/pgsql-13/bin/psql", R_OK) = 0
[pid 15479] access("/usr/pgsql-13/bin/psql", X_OK) = 0
[pid 15479] getcwd("/root", 1024)       = 6
[pid 15479] chdir("/usr/pgsql-13/bin") = 0
[pid 15479] lstat("psql", {st_mode=S_IFREG|0755, st_size=644216, ...}) = 0
[pid 15479] getcwd("/usr/pgsql-13/bin", 1024) = 29
[pid 15479] chdir("/root")              = -1 EACCES (Permission denied)
[pid 15479] open("/usr/share/locale/locale.alias", O_RDONLY|O_CLOEXEC) = 3

I found that there is a directory switch that caused this error，because use chdir to bin and chdir to now dir

[maglub]

Hi,

This issue does not originate from this ansible role. It is more how the psql binary behaves together with sudo. If this would be a bug (which it isn't), you should redirect it to the postgresql project.

In any case, I think I know how you can work around it. On your system, the user postgres is not allowed to chdir to /root, which is not a bug in itself. The psql binary is trying to read some default files in the home directory. On your system, your sudo is probably set up in such way that the postgres user believes that its home directory is /root when you run the sudo command that way.

Have you tried to run it as this?

sudo -H -u postgres /usr/pgsql-13/bin/psql 

References

(venv) maglub@guran-vl002wue:~$ sudo --help
sudo - execute a command as another user

usage: sudo -h | -K | -k | -V
usage: sudo -v [-AknS] [-g group] [-h host] [-p prompt] [-u user]
usage: sudo -l [-AknS] [-g group] [-h host] [-p prompt] [-U user] [-u user] [command]
usage: sudo [-AbEHknPS] [-r role] [-t type] [-C num] [-g group] [-h host] [-p prompt] [-u user] [VAR=value] [-i|-s] [<command>]
usage: sudo -e [-AknS] [-r role] [-t type] [-C num] [-g group] [-h host] [-p prompt] [-u user] file ...

Options:
  -A, --askpass               use a helper program for password prompting
  -b, --background            run command in the background
  -C, --close-from=num        close all file descriptors >= num
  -E, --preserve-env          preserve user environment when running command
  -e, --edit                  edit files instead of running a command
  -g, --group=group           run command as the specified group name or ID


  -H, --set-home              set HOME variable to target user's home dir


  -h, --help                  display help message and exit
  -h, --host=host             run command on host (if supported by plugin)
  -i, --login                 run login shell as the target user; a command may also be specified
  -K, --remove-timestamp      remove timestamp file completely
  -k, --reset-timestamp       invalidate timestamp file
  -l, --list                  list user's privileges or check a specific command; use twice for longer format
  -n, --non-interactive       non-interactive mode, no prompts are used
  -P, --preserve-groups       preserve group vector instead of setting to target's
  -p, --prompt=prompt         use the specified password prompt
  -r, --role=role             create SELinux security context with specified role
  -S, --stdin                 read password from standard input
  -s, --shell                 run shell as the target user; a command may also be specified
  -t, --type=type             create SELinux security context with specified type
  -U, --other-user=user       in list mode, display privileges for user
  -u, --user=user             run command (or edit file) as specified user name or ID
  -V, --version               display version information and exit
  -v, --validate              update user's timestamp without running a command
  --                          stop processing command line arguments

[ChenJhua]

centos8 ~ # sudo -H -u postgres /usr/pgsql-13/bin/psql
could not change directory to "/root": Permission denied
psql.bin (10.15)
Type "help" for help.

postgres=# 

I have tried this before, but it didn’t work, strace chdir still exists
This has no effect, but I don’t like this error
Is there any other solution

[maglub]

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@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@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@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@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@cd3b256e5e0b:/# sudo -u postgres psql
psql (12.5 (Debian 12.5-1.pgdg100+1))
Type "help" for help.

postgres=# 

TL;DR

This is mostly cosmetic, but should rather be filed with the postgres project here: https://www.postgresql.org/account/login/?next=/account/submitbug/

[ChenJhua]

Ok, thank you
Because of security issues, the postgres user gave /sbin/nologin, so you cannot use su-postgres -c psql, you need to use sudo -u postgres psql

[gclough]

@ChenJhua , I will close this issue, but if you think it's not resolved then please reopen it.