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

Adrian,

I think, it's not problem with pg_hba.conf. /etc/hosts is configured
correctly. psql works fine and is inline with libpq, where as pgagent is
not.

pgagent still fails when we use hostaddr and host.
usr/bin/pgagent_96 -f -l 2 hostaddr=127.0.0.1 host=localhost
dbname=linuxpostgresdb user=postgres port=5432

On Mon, Jun 4, 2018 at 7:36 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 06/04/2018 03:31 PM, nageswara Bandla wrote:
>
>> 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:
>>
>
> Could it be that hosts is not set up for?:
>
> 127.0.0.1 localhost
>
> See below also.
>
>
>> *DEBUG: Creating DB connection: user=postgres port=5432
>> hostaddr=127.0.0.1 dbname=linuxpostgresdb*
>>
>
> https://www.postgresql.org/docs/10/static/libpq-connect.html
> #LIBPQ-PARAMKEYWORDS
>
> hostaddr
>
> "...
>
> If hostaddr is specified without host, the value for hostaddr gives the
> server network address. The connection attempt will fail if the
> authentication method requires a host name.
> ...
> "
>
> So in your pg_hba.conf are you using a host name or IP address?
>
> Looks like pgagent is using hostaddr w/o host and that will cause an issue
> on psql also:
>
> .pgpass
> 127.0.0.1:*:*:aklaver:some_pwd
>
>
> psql "hostaddr=127.0.0.1 dbname=test user=aklaver"
> Password:
> Null display is "NULL".
> psql (10.4)
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> bits: 256, compression: off)
> Type "help" for help.
>
> Adding a host name works:
>
> psql "hostaddr=127.0.0.1 host=localhost dbname=test user=aklaver"
> Null display is "NULL".
> psql (10.4)
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> bits: 256, compression: off)
> Type "help" for help.
>
> Using host alone works:
>
> aklaver(at)tito:~> psql "host=127.0.0.1 dbname=test user=aklaver"
> Null display is "NULL".
> psql (10.4)
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> bits: 256, compression: off)
> Type "help" for help.
>
>
>
>
>> *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(dot)bandla(at)gmail(dot)com
>> <mailto:nag(dot)bandla(at)gmail(dot)com>> wrote:
>>
>>
>>
>> On Thu, May 31, 2018 at 5:16 PM, George Neuner <gneuner2(at)comcast(dot)net
>> <mailto:gneuner2(at)comcast(dot)net>> wrote:
>>
>> On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla
>> <nag(dot)bandla(at)gmail(dot)com <mailto:nag(dot)bandla(at)gmail(dot)com>> wrote:
>>
>> >On Thu, May 31, 2018 at 12:57 PM, George Neuner <
>> gneuner2(at)comcast(dot)net <mailto:gneuner2(at)comcast(dot)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
>>
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>