'Permission denied' when using some psql commands

[Leonardo Fagundes Luz Serrano]

Brief Description
-------------

When using psql, command '\e' will give a permission error for /var/lib/postgresql/

The directory is owned by "postgres" user on centos and by "root" on debian.

Severity
-------------

Minor

Steps to Reproduce
-------------

In the controller, enter postgres cli with "sudo -u postgres psql" then try using the "\e" command.

Expected Behavior
-------------

No warning. File "/var/lib/postgresql/.selected_editor" created.

Actual Behavior
-------------

'Permission denied' error message when trying to create "/var/lib/postgresql/.selected_editor".

Reproducibility
-------------

Reproducible

System Configuration
-------------

AIO-SX IPv4

Load info (eg: 2022-03-10_20-00-07)
-------------

###

Wind River Cloud Platform
    Release 22.06
###
    Wind River Systems, Inc.
###
SW_VERSION="22.06"
BUILD_TARGET="Host Installer"
BUILD_TYPE="Formal"
BUILD_ID="2022-07-19_19-14-25"
SRC_BUILD_ID="236"

JOB="stx-common/parts/configure-build"
BUILD_BY="jenkins"
BUILD_NUMBER="236"
BUILD_HOST="yow-wrcp-lx.wrs.com"
BUILD_DATE="2022-07-19 19:17:26 -0400"

Last Pass
-------------

Unknown

Timestamp/Logs
-------------

On debian:

sysadmin@controller-0:~$ ls -la /var/lib | grep postgres
drwxr-xr-x 4 root root 4096 Jul 25 17:46 postgresql
sysadmin@controller-0:~$ sudo -u postgres psql
psql (13.5 (Debian 13.5-0+deb11u1))
Type "help" for help.

postgres=# \e
touch: cannot touch '/var/lib/postgresql/.selected_editor': Permission denied
Unable to create directory /var/lib/postgresql/.local/share/nano/: No such file or directory
It is required for saving/loading search history or cursor positions.
(output of command given to editor)

On centos:

controller-0:~$ ls -la /var/lib | grep postgres
drwx------. 4 postgres postgres 4096 Jul 25 13:57 pgsql
drwxr-xr-x 3 postgres postgres 4096 Jul 25 14:16 postgresql

controller-0:~# sudo -u postgres psql
could not change directory to "/root"
psql (9.2.24)
Type "help" for help.

postgres=# \e
(output of command given to editor)

Alarms
-------------

NA

Test Activity
-------------

NA

Workaround
-------------

Fix /var/lib/postgresql permission with:

sudo chown -R postgres:postgres /var/lib/postgresql

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/starlingx/stx-puppet/+/851254

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/starlingx/stx-puppet/+/851254
Committed: https://opendev.org/starlingx/stx-puppet/commit/1a030d0e9acde79a359055888dd5cd05d4f118b4
Submitter: "Zuul (22348)"
Branch: master

commit 1a030d0e9acde79a359055888dd5cd05d4f118b4
Author: Leonardo Fagundes Luz Serrano <email address hidden>
Date: Wed Jul 27 19:25:58 2022 +0000

    Debian: Fix psql 'permission denied' editor bug

    When using "\e" cmd on psql, postgres tries to make
    changes to /var/lib/postgres, but gets 'permission denied'

    This commit changes ownership of that directory
    from root to postgres.

    Test Plan:
    pass: bootstrap and unlock
    pass: no warning message

    Closes-Bug: 1982970

    Signed-off-by: Leonardo Fagundes Luz Serrano <email address hidden>
    Change-Id: I109e650ad8614295d8f6021037a30c76d58fee24