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

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