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

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.klaver@aklaver.com

#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 <adrian.klaver@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 <http://pgpass.co&gt;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
<http://pgpass.co&gt;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.klaver@aklaver.com

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

On Tue, May 29, 2018 at 6:15 PM, Adrian Klaver <adrian.klaver@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 <http://pgpass.co&gt;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 <
http://pgpass.co&gt;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.

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.

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.

* /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.

On Wed, May 30, 2018 at 5:11 AM, Stéphane Dunand <s.dunand@sirap.fr> 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.

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

On Tue, May 29, 2018 at 6:15 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@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 <http://pgpass.co&gt;
<http://pgpass.co&gt;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 <http://pgpass.co&gt;
<http://pgpass.co&gt;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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, 29 May 2018 13:32:46 -0700, Adrian Klaver
<adrian.klaver@aklaver.com> 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

On Wed, May 30, 2018 at 7:45 PM, George Neuner <gneuner2@comcast.net> wrote:

On Tue, 29 May 2018 13:32:46 -0700, Adrian Klaver
<adrian.klaver@aklaver.com> 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.

On Thu, 31 May 2018 11:24:18 -0500, nageswara Bandla
<nag.bandla@gmail.com> wrote:

On Wed, May 30, 2018 at 7:45 PM, George Neuner <gneuner2@comcast.net> 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

On Thu, May 31, 2018 at 12:57 PM, George Neuner <gneuner2@comcast.net>
wrote:

On Thu, 31 May 2018 11:24:18 -0500, nageswara Bandla
<nag.bandla@gmail.com> wrote:

On Wed, May 30, 2018 at 7:45 PM, George Neuner <gneuner2@comcast.net>

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.

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

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

George

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

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

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

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 <nag.bandla@gmail.com>
wrote:

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

--
Angular momentum makes the world go 'round.

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. <grin>

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

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

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@aklaver.com>
wrote:

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.klaver@aklaver.com