Re: Pgagent is not reading pgpass file either in Windows or Linux.

On 05/29/2018 03:57 PM, nageswara Bandla wrote:

#1. Windows:

My guess because the LocalSystem user does not have permissions on your:
%APPDATA%/postgresql/pgpass.co <http://pgpass.co>nf
file. This seems to be confirmed by it working when you run pgagent as the login user.
Also:
passfile
     Specifies the name of the file used to store passwords (see Section 33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.co <http://pgpass.co>nf on Microsoft Windows. (No error is reported if this file does not exist.)


Does that mean we can not use pgagent (when installed)  as a  "LocalSystem" service account and define PGPASSFILE to a valid accessible location. ?

Not sure as I do not use Windows much and do not have a good handle on how it handles permissions. You might try raising an issue below to see if you can get some guidance:

https://github.com/postgres/pgagent/issues

Since I have tried copying pgpass.conf file to  C:\pgpass.conf and defined PGPASSFILE=C:\pgpass.conf
Still it was throwing no password supplied error.


#2. Linux
What happens if from the terminal as the postgres user you do?:
/path/to/pgagent 'connection string'

pgagent is throwing the same error from terminal as a postgres user as well. I have enabled debugging logging mode. It's not showing me which password file it's trying to access.

Remember there is a difference between the postgres system user and the postgres database user. So when you did the above where you in the postgres system user shell? That is where you set up the .pgpass file. Running as the postgres database user in another system account will not work unless you create a .pgpass file in that home directory as well.

It also seems that you have more the one instance of Postgres of installed and it looks like the 8.4.20 version is being found first. So I wonder if there is a library compatibility issue going on, given that 8.4 is well past EOL.

When I run psql from postgres user; It works fine.
*
*
*/usr/bin/pgagent_96 -f -l 2 hostaddr=127.0.0.1 dbname=linuxpostgresdb user=postgres port=5432*
DEBUG: Creating primary connection
DEBUG: Connection Information:
DEBUG:      user         : postgres
DEBUG:      port         : 5432
DEBUG:      host         : 127.0.0.1
DEBUG:      dbname       : linuxpostgresdb
DEBUG:      password     :
DEBUG:      conn timeout : 0
DEBUG: Connection Information:
DEBUG:      user         : postgres
DEBUG:      port         : 5432
DEBUG:      host         : 127.0.0.1
DEBUG:      dbname       : linuxpostgresdb
DEBUG:      password     :
DEBUG:      conn timeout : 0
DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=127.0.0.1 dbname=linuxpostgresdb
WARNING: Couldn't create the primary connection (attempt 1): fe_sendauth: no password supplied
DEBUG: Clearing all connections
DEBUG: Connection stats: total - 1, free - 0, deleted - 1

*# psql command is running fine:*
psql -h 127.0.0.1 -d linuxpostgresdb -U postgres

psql (8.4.20, server 9.6.6)
WARNING: psql version 8.4, server version 9.6.
          Some psql features might not work.
Type "help" for help.

linuxpostgresdb=#

--
Adrian Klaver
adrian.klaver@aklaver.com