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.kla...@aklaver.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@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.ban...@gmail.com >> <mailto:nag.ban...@gmail.com>> wrote: >> >> >> >> On Thu, May 31, 2018 at 5:16 PM, George Neuner <gneun...@comcast.net >> <mailto:gneun...@comcast.net>> wrote: >> >> On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla >> <nag.ban...@gmail.com <mailto:nag.ban...@gmail.com>> wrote: >> >> >On Thu, May 31, 2018 at 12:57 PM, George Neuner < >> gneun...@comcast.net <mailto: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 >> >> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >