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(dot)klaver(at)aklaver(dot)com