Re: Pgagent is not reading pgpass file either in Windows or Linux. - Mailing list pgsql-general

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