powa-web does not use .pgpass for quals page

[aleszeleny]

I've got an error while I've tried to display a query predicates:

Jan 30 10:45:56 pgsql-powa-1.internal powa-web[998024]: [W 240130 10:45:56 web:1787] 501 GET /server/1/metrics/database/db_web/query/-7593617811000111983/quals/?from=2024-01-30+10%3A45%3A55%2B0100&to=2024-01-30+11%3A45%3A55%2B0100 (192.168.11.148): Could not connect to remote server: (psycopg2.OperationalError) connection to server at "192.168.16.106", port 5432 failed: fe_sendauth: no password supplied
Jan 30 10:45:56 pgsql-powa-1.internal powa-web[998024]:     
Jan 30 10:45:56 pgsql-powa-1.internal powa-web[998024]:     (Background on this error at: https://sqlalche.me/e/14/e3q8)
Jan 30 10:45:56 pgsql-powa-1.internal powa-web[998024]: [E 240130 10:45:56 web:2239] 501 GET /server/1/metrics/database/db_web/query/-7593617811000111983/quals/?from=2024-01-30+10%3A45%3A55%2B0100&to=2024-01-30+11%3A45%3A55%2B0100 (192.168.11.148) 61.27ms

Adding password to the powa_servers table fixed it, but other tabs works with .pgpasswell.

[rjuju]

Hi,

That's strange, it's supposed to work out of the box, and I think that I also tested this scenario in the past just to be sure.

Just to double check, are you modifying the .pgpass on the client side or on the powa-web server side?

[aleszeleny]

Hello, first of all, thanks for that fast response! I forgot to add the powa-web version: powa_15-web-4.1.4-1.rhel9.x86_64. on AlmaLinux9 (I have to use 4.1, because of some instances on CentOS7 where powa 4.2 packages are not available, and until we finish migration of al CentOS7 PG instances to AlmaLinux9, I have to stick on powa 4.1). I have powa collector powa-collector-1.2.0-1PGDG.rhel9.noarch installed on the same machine and it works with .pgpass as expected.

Both collector and Web UI run under Postgres (systems drop-in file is used):

powa-collector:

systemctl cat powa-collector.service 
# /usr/lib/systemd/system/powa-collector.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  It is recommended to use systemd
# "dropin" feature;  i.e. create file with suffix .conf under
# /etc/systemd/system/powa-collector.service.d directory overriding the
# unit's defaults. You can also use "systemctl edit powa-collector"
# Look at systemd.unit(5) manual page for more info.

[Unit]
Description=POWA Collector Daemon
Documentation=https://powa.readthedocs.io/en/latest/powa-collector/

After=syslog.target
After=network.target

[Service]
Type=idle

# Location of the configuration file
EnvironmentFile=/etc/powa-collector.conf

ExecStart=/usr/bin/python3 /usr/bin/powa-collector.py
KillMode=mixed
KillSignal=SIGTERM

Restart=on-failure

[Install]
WantedBy=multi-user.target

# /etc/systemd/system/powa-collector.service.d/override.conf
#
# Ansible managed
#
# see: Systemd Unit configuration logic -- http://man7.org/linux/man-pages/man5/systemd.unit.5.html for more details

[Service]
User=postgres
Group=postgres

And the powa-web:

systemctl cat powa-web-15.service 
# /usr/lib/systemd/system/powa-web-15.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/powa-15.service",
# containing
#       .include /lib/systemd/system/powa-15.service
#       ...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

[Unit]
Description=PoWA Web Interface Daemon
After=syslog.target
After=network.target

[Service]
Type=daemon

User=root
Group=root

# Location of powa conf file:
Environment=POWAWEBLOG=/var/log/powa-15.log

# Where to send early-startup messages from the server 
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
ExecStart=/usr/bin/powa-web > ${POWAWEBLOG} 2>&1
ExecStop=/usr/bin/kill -TERM $MAINPID
ExecReload=/usr/bin/kill -HUP $MAINPID

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target

And the password file:

[UAT][root@pgsql-powa-1 ~]# ls -l /var/lib/pgsql/.pgpass
-rw-------. 1 postgres postgres 1072 Jan 25 18:24 /var/lib/pgsql/.pgpass

Yes, the .pgpass is on the server running powa-web (and powa-collector).

The powa-collector service works with .pgpass like a charm.

Hope this helps. if there are some more details I should provide, I'll try to do so.

[rjuju]

Thanks for all the details! I think that the problem is because your powa-web is running as root (I'm assuming because you want to use a port less than 1000 or something like that), so powa-collector won't be looking for the postgres' pgpass in /var/lib/pgsql but in /root.

[aleszeleny]

Hello, my deep apologies, when downgrading form 4.2.2. (powa-web.service) to 4.1.4 (powa-web-15.service) I forgot to rename the systems drop-in file to let powa-web run under the postgres OS user so you were absolutely correct.

Just one more question - what mistake I did that the DB object parts of the report are vacant?

Kind regards and again, sorry for the confusion with using an unintended OS user...

Ales Zeleny

[rjuju]

Ah, mystery solved! No worries, I'm also quite aware that a fresh pair of eyes can be useful :)

For the DB objects tab you didn't do any mistake. This part was relying on a hack I did in the powa-archivist extension to be able to read the statistics for all databases without having to change the database. But this has been broken when upstream changed the statistics infrastructure to use some dynamic shared memory and not the old pgstat / temp files. Rather than trying to do the same hack on this new infrastructure I decided to add the possibility for the powa-collector daemon to connect to multiple databases, and collect much more information while at it. The feature itself is ready, but will only be available in the upcoming v5 which will bring a lot of other new features. So until then I'm really sorry but this tab will be unavailable :(

[aleszeleny]

Thanks for the explanation and yes, I like the approach to allow collector user to connect to the monitored databases!

Kind Regards Ales Zeleny