I have figured out the issue with pgAgent both in Windows and Linux.
PgAgent seems to ignore pgpass.conf/.pgpass whenever it has 127.0.0.1
(127.0.0.1:5432:*:postgres:postgres) throws an error:
*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*
The solution could be update .pgpass to have (
localhost:5432:*:postgres:postgres
) and then pgagent works fine without issues.
I think, pgagent is not inline with libpq.dll while passing host address
parameter. I have raised this concern with pgagent github where exactly
they need to change
the code in order for pgagent to be in line with psql program.
https://github.com/postgres/pgagent/issues/14
On Fri, Jun 1, 2018 at 9:43 AM, nageswara Bandla <nag.ban...@gmail.com>
wrote:
>
>
> On Thu, May 31, 2018 at 5:16 PM, George Neuner <gneun...@comcast.net>
> wrote:
>
>> On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla
>> <nag.ban...@gmail.com> wrote:
>>
>> >On Thu, May 31, 2018 at 12:57 PM, George Neuner <gneun...@comcast.net>
>> >wrote:
>> >
>> >> It just occurred to me that you said PGPASSFILE was set to
>> >>
>> >> %APPDATA%/postgresql/pgpass.conf
>> >>
>> >>
>> >> The problem may be that when LocalSystem expands %APPDATA%, it is
>> >> finding its own directory, which might be any of:
>> >>
>> >> C:\Windows\ServiceProfiles\LocalService\appdata
>> >> C:\Windows\System32\config\systemprofile\AppData
>> >> C:\Windows\SysWOW64\config\systemprofile\AppData
>> >>
>> >> depending on your Windows version, policies (if any), and whether the
>> >> executable is 32 or 64 bit.
>> >>
>> >>
>> >> I wouldn't try messing with any of these directories. Instead try
>> >> setting PGPASSFILE to the full path to your file.
>> >>
>> >>
>> >I have tried all of them, pgagent is not recognizing any of the above
>> >locations. In fact, I have tried both options
>> >
>> > #1. By defining PGPASSFILE to the above locations one after the other.
>> > #2. By copying pgpass.conf to all the three locations by creating
>> >Roaming/postgresql directories.
>> >
>> >And also I have defined PGPASSFILE=C:\pgpass.conf; I think, this should
>> be
>> >accessible to any system account. This also not working.
>>
>>
>> One more stupid question and then I'm out of ideas ...
>>
>>
>> Have you rebooted after changing the environment variable?
>>
>> Global environment changes normally don't take effect until the user
>> logs out/in again. LocalSystem is not an interactive user - you have
>> to restart the system to let it see environment changes. PITA.
>>
>>
>> Yes, I did. But no luck..I guess, we have to live with this problem for
> pgagent running as a Local System account.
> We need to run pgagent service as "Logon user account" and provide user
> logon credentials for running pgagent service.
>
> In Linux case, pgagent is not even reading .pgpass itself. The issue here
> is that the logs (debug level log) are no help. It don't have much
> information.
> Which password file it is trying to read.
>
>
>
>
>
>
>
>> George
>>
>>
>>
>
