Thread: Pgagent is not reading pgpass file either in Windows or Linux.
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.
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.co
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
%APPDATA%/postgresql/pgpass.co
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
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'
/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 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.htm l) 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
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>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>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 :
try without "-u LocalSystem", without PGPASSFILE env variable and with your login account plus your %APPDATA%/postgresql/pgpass.conf file
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.
in your /etc/init.d/pgagent_96 file, verify that the user starting the command "pgagent ..." is postgres in your case.
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.
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>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>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.
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
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 :try without "-u LocalSystem", without PGPASSFILE env variable and with your login account plus your %APPDATA%/postgresql/pgpass.
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. 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.
in your /etc/init.d/pgagent_96 file, verify that the user starting the command "pgagent ..." is postgres in your case.
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.
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
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> > <http://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 <http://pgpass.co> > <http://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.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 >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.) > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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.
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.)
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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.
George
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.
George
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.
On Fri, Jun 1, 2018 at 9:43 AM, nageswara Bandla <nag.bandla@gmail.com> wrote:
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.George
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.
On Fri, Jun 1, 2018 at 9:43 AM, nageswara Bandla <nag.bandla@gmail.com> wrote: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.George
--
Angular momentum makes the world go 'round.
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.
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/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
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