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

[Adrian Klaver]

On 06/05/2018 07:53 AM, nageswara Bandla wrote:

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





Well so much for that idea. Will have to see what the answer to the 
filed issue is.



--
Adrian Klaver
adrian.kla...@aklaver.com

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

[nageswara Bandla]

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

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

[Adrian Klaver]

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 > wrote:




On Thu, May 31, 2018 at 5:16 PM, George Neuner mailto:gneun...@comcast.net>> wrote:

On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla
mailto:nag.ban...@gmail.com>> wrote:

>On Thu, May 31, 2018 at 12:57 PM, George Neuner 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

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

[George Neuner]

On 6/4/2018 6: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:


*DEBUG: Creating DB connection: user=postgres port=5432 
hostaddr=127.0.0.1 dbname=linuxpostgresdb*


*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


Wow !!!

I don't use pgpass much at all - usually I want the security of 
providing the password manually.  Since we started with the idea that 
the file wasn't being read properly, I was mainly trying to figure out 
how Windows could be screwing that up. 


There is a difference between localhost and 127.0.0.1:  localhost is DNS 
resolved - usually from the local hosts file - and thus works with 
either IPv4 or IPv6 addressing.  But it never would have occurred to me 
that using one vs the other, on an otherwise properly configured system, 
should cause an error.


Great work finding that.
George

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

[Ron]

I've noticed that .pgpass is case sensitive, so am not surprised that it 
also wouldn't note the difference between 127.0.0.1 and localhost.


On 06/04/2018 05: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:


*DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=127.0.0.1 
dbname=linuxpostgresdb*


*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 > wrote:




On Thu, May 31, 2018 at 5:16 PM, George Neuner mailto:gneun...@comcast.net>> wrote:

On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla
mailto:nag.ban...@gmail.com>> wrote:

>On Thu, May 31, 2018 at 12:57 PM, George Neuner
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






--
Angular momentum makes the world go 'round.

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

[nageswara Bandla]

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:

*DEBUG: Creating DB connection: user=postgres port=5432
hostaddr=127.0.0.1 dbname=linuxpostgresdb*

*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 
wrote:

>
>
> On Thu, May 31, 2018 at 5:16 PM, George Neuner 
> wrote:
>
>> On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla
>>  wrote:
>>
>> >On Thu, May 31, 2018 at 12:57 PM, George Neuner 
>> >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
>>
>>
>>
>

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

[nageswara Bandla]

On Thu, May 31, 2018 at 5:16 PM, George Neuner  wrote:

> On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla
>  wrote:
>
> >On Thu, May 31, 2018 at 12:57 PM, George Neuner 
> >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
>
>
>

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

[George Neuner]

On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla
 wrote:

>On Thu, May 31, 2018 at 12:57 PM, George Neuner 
>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.


George

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

[nageswara Bandla]

On Thu, May 31, 2018 at 12:57 PM, George Neuner 
wrote:

> On Thu, 31 May 2018 11:24:18 -0500, nageswara Bandla
>  wrote:
>
> >On Wed, May 30, 2018 at 7:45 PM, George Neuner 
> wrote:
> >
> >> LocalSystem has administrator permissions to virtually everything.
> >> https://msdn.microsoft.com/en-us/library/windows/desktop/
> >> ms684190(v=vs.85).aspx
> >>
> >> It should be able to read files belonging to any user.
> >>
> >> But the LocalSystem account can see only *global* environment
> >> variables ... it can't see any user specific ones.  I would check if
> >> the PGPASSFILE variable is set globally or only in the user account.
> >>
> >>
> >> I don't know anything specifically about running pgagent on Windows,
> >> so I can't say why it is giving an error if the docs say it should
> >> not.
> >>
> >
> >
> >I am setting the PGPASSFILE in system environment variables. I am not
> >setting it in user specific environmental variables.
>
>
> 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.


Thank you.





>
> George
>
>
>

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

[George Neuner]

On Thu, 31 May 2018 11:24:18 -0500, nageswara Bandla
 wrote:

>On Wed, May 30, 2018 at 7:45 PM, George Neuner  wrote:
>
>> LocalSystem has administrator permissions to virtually everything.
>> https://msdn.microsoft.com/en-us/library/windows/desktop/
>> ms684190(v=vs.85).aspx
>>
>> It should be able to read files belonging to any user.
>>
>> But the LocalSystem account can see only *global* environment
>> variables ... it can't see any user specific ones.  I would check if
>> the PGPASSFILE variable is set globally or only in the user account.
>>
>>
>> I don't know anything specifically about running pgagent on Windows,
>> so I can't say why it is giving an error if the docs say it should
>> not.
>>
>
>
>I am setting the PGPASSFILE in system environment variables. I am not
>setting it in user specific environmental variables.


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.


George

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

[nageswara Bandla]

On Wed, May 30, 2018 at 7:45 PM, George Neuner  wrote:

> On Tue, 29 May 2018 13:32:46 -0700, Adrian Klaver
>  wrote:
>
> >On 05/29/2018 12:14 PM, nageswara Bandla wrote:
> >
> >> As per the link-
> >> (https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html) I set
> >> PGPASSFILE environment variable to point to pgpass.conf location. Even
> >> then, it’s throwing same above error message. I have found out that
> >> pgagent is not reading pgpass.conf file when configured under
> >> LocalSystem account.
> >>
> >> When I change the properties of the pgagent service to run under my
> >> login user account. Then, it’s reading pgpass.conf file under
> >> %APPDATA%/postgresql/pgpass.conf.
> >>
> >> I am clueless, why pgagent is not honoring PGPASSFILE env variable.
> >
> >My guess because the LocalSystem user does not have permissions on your:
> >
> >%APPDATA%/postgresql/pgpass.conf
> >
> >file. This seems to be confirmed by it working when you run pgagent as
> >the login user.
>
> LocalSystem has administrator permissions to virtually everything.
> https://msdn.microsoft.com/en-us/library/windows/desktop/
> ms684190(v=vs.85).aspx
>
> It should be able to read files belonging to any user.
>
> But the LocalSystem account can see only *global* environment
> variables ... it can't see any user specific ones.  I would check if
> the PGPASSFILE variable is set globally or only in the user account.
>
>
> I don't know anything specifically about running pgagent on Windows,
> so I can't say why it is giving an error if the docs say it should
> not.
>


I am setting the PGPASSFILE in system environment variables. I am not
setting it in user specific environmental variables.





>
> George
>
>
> >passfile
> >
> > Specifies the name of the file used to store passwords (see Section
> >33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on
> >Microsoft Windows. (No error is reported if this file does not exist.)
> > ^^
>
>
>

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

[George Neuner]

On Tue, 29 May 2018 13:32:46 -0700, Adrian Klaver
 wrote:

>On 05/29/2018 12:14 PM, nageswara Bandla wrote:
>
>> As per the link- 
>> (https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html) I set 
>> PGPASSFILE environment variable to point to pgpass.conf location. Even 
>> then, it’s throwing same above error message. I have found out that 
>> pgagent is not reading pgpass.conf file when configured under 
>> LocalSystem account.
>> 
>> When I change the properties of the pgagent service to run under my 
>> login user account. Then, it’s reading pgpass.conf file under 
>> %APPDATA%/postgresql/pgpass.conf.
>> 
>> I am clueless, why pgagent is not honoring PGPASSFILE env variable.
>
>My guess because the LocalSystem user does not have permissions on your:
>
>%APPDATA%/postgresql/pgpass.conf
>
>file. This seems to be confirmed by it working when you run pgagent as 
>the login user.

LocalSystem has administrator permissions to virtually everything.
https://msdn.microsoft.com/en-us/library/windows/desktop/ms684190(v=vs.85).aspx

It should be able to read files belonging to any user.  

But the LocalSystem account can see only *global* environment
variables ... it can't see any user specific ones.  I would check if
the PGPASSFILE variable is set globally or only in the user account.


I don't know anything specifically about running pgagent on Windows,
so I can't say why it is giving an error if the docs say it should
not.

George


>passfile
>
> Specifies the name of the file used to store passwords (see Section 
>33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on 
>Microsoft Windows. (No error is reported if this file does not exist.)
> ^^

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

[Adrian Klaver]

On 05/30/2018 08:48 AM, nageswara Bandla wrote:



On Tue, May 29, 2018 at 6:15 PM, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 05/29/2018 03:57 PM, nageswara Bandla wrote:

#1. Windows:

My guess because the LocalSystem user does not have permissions
on your:
%APPDATA%/postgresql/pgpass.co 
nf
file. This seems to be confirmed by it working when you run
pgagent as the login user.
Also:
passfile
      Specifies the name of the file used to store passwords
(see Section 33.15). Defaults to ~/.pgpass, or
%APPDATA%\postgresql\pgpass.co 
nf on Microsoft Windows. (No error is reported
if this file does not exist.)


Does that mean we can not use pgagent (when installed)  as a 
"LocalSystem" service account and define PGPASSFILE to a valid

accessible location. ?


Not sure as I do not use Windows much and do not have a good handle
on how it handles permissions. You might try raising an issue below
to see if you can get some guidance:



Thank you Adrian, let me also try to raise an issue there and see.






I am running pgagent command from postgres system user account where 
.pgpass is setup.


It’s definitely not a library issue. Since the same pgagent command that 
I gave earlier is working fine by  appending  “password=secret” at the 
end in pgagent command line.


That in of itself does not eliminate library issues as supplying the 
password removes the need for libpq to search for a .pgpass or env 
variable specifying the file or the password itself and that is where 
pgagent seems to be having the problem. This is more of a comment for 
future troubleshooting then the present as reducing the Postgres 
versions to one eliminates library issues.




*/usr/bin/pgagent_96 -f -l 2 hostaddr=127.0.0.1 dbname=linuxpostgresdb 
user=postgres port=5432 password=secret*




*
*

Anyway, I have tried your suggestion to clean up old postgres libraries 
and tried to keep only one version of postgres libraries exist in the 
entire system. It’s still same issue.


And I am trying in the same machine where the postgres is installed.





When I run psql from postgres user; It works fine.
*
*
*/usr/bin/pgagent_96 -f -l 2 hostaddr=127.0.0.1
dbname=linuxpostgresdb user=postgres port=5432*
DEBUG: Creating primary connection
DEBUG: Connection Information:
DEBUG:      user         : postgres
DEBUG:      port         : 5432
DEBUG:      host         : 127.0.0.1
DEBUG:      dbname       : linuxpostgresdb
DEBUG:      password     :
DEBUG:      conn timeout : 0
DEBUG: Connection Information:
DEBUG:      user         : postgres
DEBUG:      port         : 5432
DEBUG:      host         : 127.0.0.1
DEBUG:      dbname       : linuxpostgresdb
DEBUG:      password     :
DEBUG:      conn timeout : 0
DEBUG: Creating DB connection: user=postgres port=5432
hostaddr=127.0.0.1 dbname=linuxpostgresdb
WARNING: Couldn't create the primary connection (attempt 1):
fe_sendauth: no password supplied
DEBUG: Clearing all connections
DEBUG: Connection stats: total - 1, free - 0, deleted - 1

*# psql command is running fine:*
psql -h 127.0.0.1 -d linuxpostgresdb -U postgres

psql (8.4.20, server 9.6.6)
WARNING: psql version 8.4, server version 9.6.
           Some psql features might not work.
Type "help" for help.

linuxpostgresdb=#






-- 
Adrian Klaver

adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com

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

[nageswara Bandla]

On Wed, May 30, 2018 at 5:11 AM, Stéphane Dunand  wrote:

>
> Le 29/05/2018 à 21:14, nageswara Bandla a écrit :
>
>
>
> The intention of this post is to find out ways to run pgagent without
> passing password in its connection string.
>
>
>
> *Windows:*
>
> I have installed pgagent on windows and configured to run under Local
> System account.
>
>
>
> Command:-
>
> C:\PostgreSQL\bigsql\pgagent\bin\pgagent.exe  INSTALL pgagent -l 2 -u
> LocalSystem hostaddr=127.0.0.1 dbname=postgresdb user=postgres
>
>
>
> I have logged into my windows account; where my profile has pgpass.conf in
> %APPDATA%/postgresql/pgpass.conf file.
>
>
>
> 127.0.0.1:5432:*:postgres:postgres1
>
>
>
> Pgagent is not started and throws error –“The pgagent service on Local
> Computer started and then stopped. Some services stop automatically if they
> are not in use by other services or programs”.
>
> Eventvwr log messages have these error messages - “Couldn't create the
> primary connection (attempt 10): fe_sendauth: no password supplied”
>
>
>
> *PGPASSFILE env variable:*
>
> As per the link- (https://www.postgresql.org/docs/9.6/static/libpq-pgpass.
> html)   I set PGPASSFILE environment variable to point to pgpass.conf
> location. Even then, it’s throwing same above error message. I have found
> out that pgagent is not reading pgpass.conf file when configured under
> LocalSystem account.
>
>
>
> When I change the properties of the pgagent service to run under my login
> user account. Then, it’s reading pgpass.conf file under
> %APPDATA%/postgresql/pgpass.conf.
>
> try without "-u LocalSystem", without PGPASSFILE env variable and with
> your login account plus your %APPDATA%/postgresql/pgpass.conf file
>

Right, with login user account. It just works fine. I confirmed it above.
But I was looking for alternatives not to request user for credentials to
run the pgagent service. Instead run it from LocalSystem account.


>
>
>
>
> I am clueless, why pgagent is not honoring PGPASSFILE env variable.
>
>
>
>
>
> *Linux:*
>
> In Linux, I have installed pgagent_96 using yum command. And tried to run
> pgagent using command /etc/init.d/pgagent_96 from postgres user account.
>
> It fails with error message – “WARNING: Couldn't create the primary
> connection (attempt 2): fe_sendauth: no password supplied”
>
> I made sure that I have .pgpass configuration file with 0600 permissions
> in postgres user home directory.
>
>
>
> I have tried by defining PGPASSFILE env variable in postgres user account.
> But it’s not working.
>
>
>
> Pgagent is starting only when we pass password in it’s connection string.
> But which is not a good practice at all.
>
> in your /etc/init.d/pgagent_96 file, verify that the user starting the
> command "pgagent ..." is postgres in your case.
>

 Yes, I tries this, it was not working...that's why we are trying to
see and run pgagent from the terminal and not in background by enabling -f
option.

>
>
> Stéphane Dunand
>
>

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

[Stéphane Dunand]

Le 29/05/2018 à 21:14, nageswara Bandla a écrit :


The intention of this post is to find out ways to run pgagent without 
passing password in its connection string.


*Windows:*

I have installed pgagent on windows and configured to run under Local 
System account.


Command:-

C:\PostgreSQL\bigsql\pgagent\bin\pgagent.exeINSTALL pgagent -l 2 -u 
LocalSystem hostaddr=127.0.0.1 dbname=postgresdb user=postgres


I have logged into my windows account; where my profile has 
pgpass.conf in %APPDATA%/postgresql/pgpass.conf file.


127.0.0.1:5432:*:postgres:postgres1

Pgagent is not started and throws error –“The pgagent service on Local 
Computer started and then stopped. Some services stop automatically if 
they are not in use by other services or programs”.


Eventvwr log messages have these error messages - “Couldn't create the 
primary connection (attempt 10): fe_sendauth: no password supplied”


*PGPASSFILE env variable:*

As per the link- 
(https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html) I set 
PGPASSFILE environment variable to point to pgpass.conf location. Even 
then, it’s throwing same above error message. I have found out that 
pgagent is not reading pgpass.conf file when configured under 
LocalSystem account.


When I change the properties of the pgagent service to run under my 
login user account. Then, it’s reading pgpass.conf file under 
%APPDATA%/postgresql/pgpass.conf.


try without "-u LocalSystem", without PGPASSFILE env variable and with 
your login account plus your %APPDATA%/postgresql/pgpass.conf file



I am clueless, why pgagent is not honoring PGPASSFILE env variable.

*Linux:*

In Linux, I have installed pgagent_96 using yum command. And tried to 
run pgagent using command /etc/init.d/pgagent_96 from postgres user 
account.


It fails with error message – “WARNING: Couldn't create the primary 
connection (attempt 2): fe_sendauth: no password supplied”


I made sure that I have .pgpass configuration file with 0600 
permissions in postgres user home directory.


I have tried by defining PGPASSFILE env variable in postgres user 
account. But it’s not working.


Pgagent is starting only when we pass password in it’s connection 
string. But which is not a good practice at all.



in your /etc/init.d/pgagent_96 file, verify that the user starting the 
command "pgagent ..." is postgres in your case.


Stéphane Dunand

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

[Adrian Klaver]

On 05/29/2018 03:57 PM, nageswara Bandla wrote:

#1. Windows:

My guess because the LocalSystem user does not have permissions on your:
%APPDATA%/postgresql/pgpass.co nf
file. This seems to be confirmed by it working when you run pgagent as 
the login user.

Also:
passfile
     Specifies the name of the file used to store passwords (see Section 
33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.co 
nf on Microsoft Windows. (No error is reported if this 
file does not exist.)



Does that mean we can not use pgagent (when installed)  as a  
"LocalSystem" service account and define PGPASSFILE to a valid 
accessible location. ?


Not sure as I do not use Windows much and do not have a good handle on 
how it handles permissions. You might try raising an issue below to see 
if you can get some guidance:


https://github.com/postgres/pgagent/issues

Since I have tried copying pgpass.conf file to  C:\pgpass.conf and 
defined PGPASSFILE=C:\pgpass.conf

Still it was throwing no password supplied error.


#2. Linux
What happens if from the terminal as the postgres user you do?:
/path/to/pgagent 'connection string'

pgagent is throwing the same error from terminal as a postgres user as 
well. I have enabled debugging logging mode. It's not showing me which 
password file it's trying to access.


Remember there is a difference between the postgres system user and the 
postgres database user. So when you did the above where you in the 
postgres system user shell? That is where you set up the .pgpass file. 
Running as the postgres database user in another system account will not 
work unless you create a .pgpass file in that home directory as well.


It also seems that you have more the one instance of Postgres of 
installed and it looks like the 8.4.20 version is being found first. So 
I wonder if there is a library compatibility issue going on, given that 
8.4 is well past EOL.




When I run psql from postgres user; It works fine.
*
*
*/usr/bin/pgagent_96 -f -l 2 hostaddr=127.0.0.1 dbname=linuxpostgresdb 
user=postgres port=5432*

DEBUG: Creating primary connection
DEBUG: Connection Information:
DEBUG:      user         : postgres
DEBUG:      port         : 5432
DEBUG:      host         : 127.0.0.1
DEBUG:      dbname       : linuxpostgresdb
DEBUG:      password     :
DEBUG:      conn timeout : 0
DEBUG: Connection Information:
DEBUG:      user         : postgres
DEBUG:      port         : 5432
DEBUG:      host         : 127.0.0.1
DEBUG:      dbname       : linuxpostgresdb
DEBUG:      password     :
DEBUG:      conn timeout : 0
DEBUG: Creating DB connection: user=postgres port=5432 
hostaddr=127.0.0.1 dbname=linuxpostgresdb
WARNING: Couldn't create the primary connection (attempt 1): 
fe_sendauth: no password supplied

DEBUG: Clearing all connections
DEBUG: Connection stats: total - 1, free - 0, deleted - 1

*# psql command is running fine:*
psql -h 127.0.0.1 -d linuxpostgresdb -U postgres

psql (8.4.20, server 9.6.6)
WARNING: psql version 8.4, server version 9.6.
          Some psql features might not work.
Type "help" for help.

linuxpostgresdb=#







--
Adrian Klaver
adrian.kla...@aklaver.com

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

[nageswara Bandla]

#1. Windows:

My guess because the LocalSystem user does not have permissions on your:
%APPDATA%/postgresql/pgpass.conf
file. This seems to be confirmed by it working when you run pgagent as the
login user.
Also:
passfile
Specifies the name of the file used to store passwords (see Section
33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on
Microsoft Windows. (No error is reported if this file does not exist.)


Does that mean we can not use pgagent (when installed)  as a  "LocalSystem"
service account and define PGPASSFILE to a valid accessible location. ?
Since I have tried copying pgpass.conf file to  C:\pgpass.conf and defined
PGPASSFILE=C:\pgpass.conf
Still it was throwing no password supplied error.


#2. Linux
What happens if from the terminal as the postgres user you do?:
/path/to/pgagent 'connection string'

pgagent is throwing the same error from terminal as a postgres user as
well. I have enabled debugging logging mode. It's not showing me which
password file it's trying to access.

When I run psql from postgres user; It works fine.

*/usr/bin/pgagent_96 -f -l 2 hostaddr=127.0.0.1 dbname=linuxpostgresdb
user=postgres port=5432*
DEBUG: Creating primary connection
DEBUG: Connection Information:
DEBUG:  user : postgres
DEBUG:  port : 5432
DEBUG:  host : 127.0.0.1
DEBUG:  dbname   : linuxpostgresdb
DEBUG:  password :
DEBUG:  conn timeout : 0
DEBUG: Connection Information:
DEBUG:  user : postgres
DEBUG:  port : 5432
DEBUG:  host : 127.0.0.1
DEBUG:  dbname   : linuxpostgresdb
DEBUG:  password :
DEBUG:  conn timeout : 0
DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=127.0.0.1
dbname=linuxpostgresdb
WARNING: Couldn't create the primary connection (attempt 1): fe_sendauth:
no password supplied
DEBUG: Clearing all connections
DEBUG: Connection stats: total - 1, free - 0, deleted - 1

*# psql command is running fine:*
psql -h 127.0.0.1 -d linuxpostgresdb -U postgres

psql (8.4.20, server 9.6.6)
WARNING: psql version 8.4, server version 9.6.
 Some psql features might not work.
Type "help" for help.

linuxpostgresdb=#




On Tue, May 29, 2018 at 3:32 PM, Adrian Klaver 
wrote:

> On 05/29/2018 12:14 PM, nageswara Bandla wrote:
>
>> The intention of this post is to find out ways to run pgagent without
>> passing password in its connection string.
>>
>> *Windows:*
>>
>> I have installed pgagent on windows and configured to run under Local
>> System account.
>>
>> Command:-
>>
>> C:\PostgreSQL\bigsql\pgagent\bin\pgagent.exeINSTALL pgagent -l 2 -u
>> LocalSystem hostaddr=127.0.0.1 dbname=postgresdb user=postgres
>>
>> I have logged into my windows account; where my profile has pgpass.conf
>> in %APPDATA%/postgresql/pgpass.conf file.
>>
>> 127.0.0.1:5432:*:postgres:postgres1
>>
>> Pgagent is not started and throws error –“The pgagent service on Local
>> Computer started and then stopped. Some services stop automatically if they
>> are not in use by other services or programs”.
>>
>> Eventvwr log messages have these error messages - “Couldn't create the
>> primary connection (attempt 10): fe_sendauth: no password supplied”
>>
>> *PGPASSFILE env variable:*
>>
>> As per the link- (https://www.postgresql.org/do
>> cs/9.6/static/libpq-pgpass.html) I set PGPASSFILE environment variable
>> to point to pgpass.conf location. Even then, it’s throwing same above error
>> message. I have found out that pgagent is not reading pgpass.conf file when
>> configured under LocalSystem account.
>>
>> When I change the properties of the pgagent service to run under my login
>> user account. Then, it’s reading pgpass.conf file under
>> %APPDATA%/postgresql/pgpass.conf.
>>
>> I am clueless, why pgagent is not honoring PGPASSFILE env variable.
>>
>
> My guess because the LocalSystem user does not have permissions on your:
>
> %APPDATA%/postgresql/pgpass.conf
>
> file. This seems to be confirmed by it working when you run pgagent as the
> login user.
>
> Also:
>
> passfile
>
> Specifies the name of the file used to store passwords (see Section
> 33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on
> Microsoft Windows. (No error is reported if this file does not exist.)
> ^^
>
>
>
>
>> *Linux:*
>>
>> In Linux, I have installed pgagent_96 using yum command. And tried to run
>> pgagent using command /etc/init.d/pgagent_96 from postgres user account.
>>
>
> This is going to depend on what pgagent_96 is doing?
>
>
>> It fails with error message – “WARNING: Couldn't create the primary
>> connection (attempt 2): fe_sendauth: no password supplied”
>>
>
> What happens if from the terminal as the postgres user you do?:
>
> /path/to/pgagent 'connection string'
>
>
>
>> I made sure that I have .pgpass configuration file with 0600 permissions
>> in postgres user home directory.
>>
>> I have tried by defining 

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

[Adrian Klaver]

On 05/29/2018 12:14 PM, nageswara Bandla wrote:
The intention of this post is to find out ways to run pgagent without 
passing password in its connection string.


*Windows:*

I have installed pgagent on windows and configured to run under Local 
System account.


Command:-

C:\PostgreSQL\bigsql\pgagent\bin\pgagent.exeINSTALL pgagent -l 2 -u 
LocalSystem hostaddr=127.0.0.1 dbname=postgresdb user=postgres


I have logged into my windows account; where my profile has pgpass.conf 
in %APPDATA%/postgresql/pgpass.conf file.


127.0.0.1:5432:*:postgres:postgres1

Pgagent is not started and throws error –“The pgagent service on Local 
Computer started and then stopped. Some services stop automatically if 
they are not in use by other services or programs”.


Eventvwr log messages have these error messages - “Couldn't create the 
primary connection (attempt 10): fe_sendauth: no password supplied”


*PGPASSFILE env variable:*

As per the link- 
(https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html) I set 
PGPASSFILE environment variable to point to pgpass.conf location. Even 
then, it’s throwing same above error message. I have found out that 
pgagent is not reading pgpass.conf file when configured under 
LocalSystem account.


When I change the properties of the pgagent service to run under my 
login user account. Then, it’s reading pgpass.conf file under 
%APPDATA%/postgresql/pgpass.conf.


I am clueless, why pgagent is not honoring PGPASSFILE env variable.


My guess because the LocalSystem user does not have permissions on your:

%APPDATA%/postgresql/pgpass.conf

file. This seems to be confirmed by it working when you run pgagent as 
the login user.


Also:

passfile

Specifies the name of the file used to store passwords (see Section 
33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on 
Microsoft Windows. (No error is reported if this file does not exist.)

^^





*Linux:*

In Linux, I have installed pgagent_96 using yum command. And tried to 
run pgagent using command /etc/init.d/pgagent_96 from postgres user account.


This is going to depend on what pgagent_96 is doing?



It fails with error message – “WARNING: Couldn't create the primary 
connection (attempt 2): fe_sendauth: no password supplied”


What happens if from the terminal as the postgres user you do?:

/path/to/pgagent 'connection string'



I made sure that I have .pgpass configuration file with 0600 permissions 
in postgres user home directory.


I have tried by defining PGPASSFILE env variable in postgres user 
account. But it’s not working.


Pgagent is starting only when we pass password in it’s connection 
string. But which is not a good practice at all.






--
Adrian Klaver
adrian.kla...@aklaver.com

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

[nageswara Bandla]

The intention of this post is to find out ways to run pgagent without
passing password in its connection string.



*Windows:*

I have installed pgagent on windows and configured to run under Local
System account.



Command:-

C:\PostgreSQL\bigsql\pgagent\bin\pgagent.exe  INSTALL pgagent -l 2 -u
LocalSystem hostaddr=127.0.0.1 dbname=postgresdb user=postgres



I have logged into my windows account; where my profile has pgpass.conf in
%APPDATA%/postgresql/pgpass.conf file.



127.0.0.1:5432:*:postgres:postgres1



Pgagent is not started and throws error –“The pgagent service on Local
Computer started and then stopped. Some services stop automatically if they
are not in use by other services or programs”.

Eventvwr log messages have these error messages - “Couldn't create the
primary connection (attempt 10): fe_sendauth: no password supplied”



*PGPASSFILE env variable:*

As per the link- (
https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html)   I set
PGPASSFILE environment variable to point to pgpass.conf location. Even
then, it’s throwing same above error message. I have found out that pgagent
is not reading pgpass.conf file when configured under LocalSystem account.



When I change the properties of the pgagent service to run under my login
user account. Then, it’s reading pgpass.conf file under
%APPDATA%/postgresql/pgpass.conf.



I am clueless, why pgagent is not honoring PGPASSFILE env variable.





*Linux:*

In Linux, I have installed pgagent_96 using yum command. And tried to run
pgagent using command /etc/init.d/pgagent_96 from postgres user account.

It fails with error message – “WARNING: Couldn't create the primary
connection (attempt 2): fe_sendauth: no password supplied”

I made sure that I have .pgpass configuration file with 0600 permissions in
postgres user home directory.



I have tried by defining PGPASSFILE env variable in postgres user account.
But it’s not working.



Pgagent is starting only when we pass password in it’s connection string.
But which is not a good practice at all.