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.klaver@xxxxxxxxxxx> 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@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. On Fri, Jun 1, 2018 at 9:43 AM, nageswara Bandla <nag.bandla@xxxxxxxxx <mailto:nag.bandla@xxxxxxxxx>> wrote:
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 Thu, May 31, 2018 at 5:16 PM, George Neuner <gneuner2@xxxxxxxxxxx
<mailto:gneuner2@xxxxxxxxxxx>> wrote:
On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla
<nag.bandla@xxxxxxxxx <mailto:nag.bandla@xxxxxxxxx>> wrote:
>On Thu, May 31, 2018 at 12:57 PM, George Neuner <gneuner2@xxxxxxxxxxx <mailto:gneuner2@xxxxxxxxxxx>>
>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.klaver@xxxxxxxxxxx
