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

On Tue, May 29, 2018 at 6:15 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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:
>
>

Thank you Adrian, let me also try to raise an issue there and see.

> 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.
>

I am running pgagent command from postgres system user account where
.pgpass is setup.

It’s definitely not a library issue. Since the same pgagent command that I
gave earlier is working fine by appending “password=secret” at the end in
pgagent command line.

* /usr/bin/pgagent_96 -f -l 2 hostaddr=127.0.0.1 dbname=linuxpostgresdb
user=postgres port=5432 password=secret*

Anyway, I have tried your suggestion to clean up old postgres libraries and
tried to keep only one version of postgres libraries exist in the entire
system. It’s still same issue.

And I am trying in the same machine where the postgres is installed.

>
>
>> 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
>