Re: Pgagent is not reading pgpass file either in Windows or Linux.
Adrian, I think, it's not problem with pg_hba.conf. /etc/hosts is configured correctly. psql works fine and is inline with libpq, where as pgagent is not. pgagent still fails when we use hostaddr and host. usr/bin/pgagent_96 -f -l 2 hostaddr=127.0.0.1 host=localhost dbname=linuxpostgresdb user=postgres port=5432 On Mon, Jun 4, 2018 at 7:36 PM, Adrian Klaver wrote: > On 06/04/2018 03:31 PM, nageswara Bandla wrote: > >> I have figured out the issue with pgAgent both in Windows and Linux. >> >> PgAgent seems to ignore pgpass.conf/.pgpass whenever it has 127.0.0.1 >> (127.0.0.1:5432:*:postgres:postgres) throws an error: >> > > Could it be that hosts is not set up for?: > > 127.0.0.1 localhost > > See below also. > > >> *DEBUG: Creating DB connection: user=postgres port=5432 >> hostaddr=127.0.0.1 dbname=linuxpostgresdb* >> > > https://www.postgresql.org/docs/10/static/libpq-connect.html > #LIBPQ-PARAMKEYWORDS > > hostaddr > > "... > > If hostaddr is specified without host, the value for hostaddr gives the > server network address. The connection attempt will fail if the > authentication method requires a host name. > ... > " > > So in your pg_hba.conf are you using a host name or IP address? > > Looks like pgagent is using hostaddr w/o host and that will cause an issue > on psql also: > > .pgpass > 127.0.0.1:*:*:aklaver:some_pwd > > > psql "hostaddr=127.0.0.1 dbname=test user=aklaver" > Password: > Null display is "NULL". > psql (10.4) > SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, > bits: 256, compression: off) > Type "help" for help. > > Adding a host name works: > > psql "hostaddr=127.0.0.1 host=localhost dbname=test user=aklaver" > Null display is "NULL". > psql (10.4) > SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, > bits: 256, compression: off) > Type "help" for help. > > Using host alone works: > > aklaver@tito:~> psql "host=127.0.0.1 dbname=test user=aklaver" > Null display is "NULL". > psql (10.4) > SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, > bits: 256, compression: off) > Type "help" for help. > > > > >> *WARNING: Couldn't create the primary connection (attempt 1): >> fe_sendauth: no password supplied* >> >> * >> * >> >> The solution could be update .pgpass to have ( >> localhost:5432:*:postgres:postgres ) and then pgagent works fine without >> issues. >> >> >> I think, pgagent is not inline with libpq.dll while passing host address >> parameter. I have raised this concern with pgagent github where exactly >> they need to change >> >> the code in order for pgagent to be in line with psql program. >> >> >> https://github.com/postgres/pgagent/issues/14 >> >> >> On Fri, Jun 1, 2018 at 9:43 AM, nageswara Bandla > <mailto:nag.ban...@gmail.com>> wrote: >> >> >> >> On Thu, May 31, 2018 at 5:16 PM, George Neuner > <mailto:gneun...@comcast.net>> wrote: >> >> On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla >> mailto:nag.ban...@gmail.com>> wrote: >> >> >On Thu, May 31, 2018 at 12:57 PM, George Neuner < >> gneun...@comcast.net <mailto:gneun...@comcast.net>> >> >> >wrote: >> > >> >> It just occurred to me that you said PGPASSFILE was set to >> >> >> >> %APPDATA%/postgresql/pgpass.conf >> >> >> >> >> >> The problem may be that when LocalSystem expands %APPDATA%, it >> is >> >> finding its own directory, which might be any of: >> >> >> >>C:\Windows\ServiceProfiles\LocalService\appdata >> >>C:\Windows\System32\config\systemprofile\AppData >> >>C:\Windows\SysWOW64\config\systemprofile\AppData >> >> >> >> depending on your Windows version, policies (if any), and >> whether the >> >> executable is 32 or 64 bit. >> >> >> >> >> >> I wouldn't try messing with any of these directories. Instead >> try >> >> setting PGPASSFILE to the full path to your file. >> >> >> >> >> >I have tried all of them, pgagent is not recognizing any of the
Re: Pgagent is not reading pgpass file either in Windows or Linux.
I have figured out the issue with pgAgent both in Windows and Linux. PgAgent seems to ignore pgpass.conf/.pgpass whenever it has 127.0.0.1 (127.0.0.1:5432:*:postgres:postgres) throws an error: *DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=127.0.0.1 dbname=linuxpostgresdb* *WARNING: Couldn't create the primary connection (attempt 1): fe_sendauth: no password supplied* The solution could be update .pgpass to have ( localhost:5432:*:postgres:postgres ) and then pgagent works fine without issues. I think, pgagent is not inline with libpq.dll while passing host address parameter. I have raised this concern with pgagent github where exactly they need to change the code in order for pgagent to be in line with psql program. https://github.com/postgres/pgagent/issues/14 On Fri, Jun 1, 2018 at 9:43 AM, nageswara Bandla wrote: > > > On Thu, May 31, 2018 at 5:16 PM, George Neuner > wrote: > >> On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla >> wrote: >> >> >On Thu, May 31, 2018 at 12:57 PM, George Neuner >> >wrote: >> > >> >> It just occurred to me that you said PGPASSFILE was set to >> >> >> >> %APPDATA%/postgresql/pgpass.conf >> >> >> >> >> >> The problem may be that when LocalSystem expands %APPDATA%, it is >> >> finding its own directory, which might be any of: >> >> >> >>C:\Windows\ServiceProfiles\LocalService\appdata >> >>C:\Windows\System32\config\systemprofile\AppData >> >>C:\Windows\SysWOW64\config\systemprofile\AppData >> >> >> >> depending on your Windows version, policies (if any), and whether the >> >> executable is 32 or 64 bit. >> >> >> >> >> >> I wouldn't try messing with any of these directories. Instead try >> >> setting PGPASSFILE to the full path to your file. >> >> >> >> >> >I have tried all of them, pgagent is not recognizing any of the above >> >locations. In fact, I have tried both options >> > >> > #1. By defining PGPASSFILE to the above locations one after the other. >> > #2. By copying pgpass.conf to all the three locations by creating >> >Roaming/postgresql directories. >> > >> >And also I have defined PGPASSFILE=C:\pgpass.conf; I think, this should >> be >> >accessible to any system account. This also not working. >> >> >> One more stupid question and then I'm out of ideas ... >> >> >> Have you rebooted after changing the environment variable? >> >> Global environment changes normally don't take effect until the user >> logs out/in again. LocalSystem is not an interactive user - you have >> to restart the system to let it see environment changes. PITA. >> >> >> Yes, I did. But no luck..I guess, we have to live with this problem for > pgagent running as a Local System account. > We need to run pgagent service as "Logon user account" and provide user > logon credentials for running pgagent service. > > In Linux case, pgagent is not even reading .pgpass itself. The issue here > is that the logs (debug level log) are no help. It don't have much > information. > Which password file it is trying to read. > > > > > > > >> George >> >> >> >
Re: Pgagent is not reading pgpass file either in Windows or Linux.
On Thu, May 31, 2018 at 5:16 PM, George Neuner wrote: > On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla > wrote: > > >On Thu, May 31, 2018 at 12:57 PM, George Neuner > >wrote: > > > >> It just occurred to me that you said PGPASSFILE was set to > >> > >> %APPDATA%/postgresql/pgpass.conf > >> > >> > >> The problem may be that when LocalSystem expands %APPDATA%, it is > >> finding its own directory, which might be any of: > >> > >>C:\Windows\ServiceProfiles\LocalService\appdata > >>C:\Windows\System32\config\systemprofile\AppData > >>C:\Windows\SysWOW64\config\systemprofile\AppData > >> > >> depending on your Windows version, policies (if any), and whether the > >> executable is 32 or 64 bit. > >> > >> > >> I wouldn't try messing with any of these directories. Instead try > >> setting PGPASSFILE to the full path to your file. > >> > >> > >I have tried all of them, pgagent is not recognizing any of the above > >locations. In fact, I have tried both options > > > > #1. By defining PGPASSFILE to the above locations one after the other. > > #2. By copying pgpass.conf to all the three locations by creating > >Roaming/postgresql directories. > > > >And also I have defined PGPASSFILE=C:\pgpass.conf; I think, this should be > >accessible to any system account. This also not working. > > > One more stupid question and then I'm out of ideas ... > > > Have you rebooted after changing the environment variable? > > Global environment changes normally don't take effect until the user > logs out/in again. LocalSystem is not an interactive user - you have > to restart the system to let it see environment changes. PITA. > > > Yes, I did. But no luck..I guess, we have to live with this problem for pgagent running as a Local System account. We need to run pgagent service as "Logon user account" and provide user logon credentials for running pgagent service. In Linux case, pgagent is not even reading .pgpass itself. The issue here is that the logs (debug level log) are no help. It don't have much information. Which password file it is trying to read. > George > > >
Re: Pgagent is not reading pgpass file either in Windows or Linux.
On Thu, May 31, 2018 at 12:57 PM, George Neuner wrote: > On Thu, 31 May 2018 11:24:18 -0500, nageswara Bandla > wrote: > > >On Wed, May 30, 2018 at 7:45 PM, George Neuner > wrote: > > > >> LocalSystem has administrator permissions to virtually everything. > >> https://msdn.microsoft.com/en-us/library/windows/desktop/ > >> ms684190(v=vs.85).aspx > >> > >> It should be able to read files belonging to any user. > >> > >> But the LocalSystem account can see only *global* environment > >> variables ... it can't see any user specific ones. I would check if > >> the PGPASSFILE variable is set globally or only in the user account. > >> > >> > >> I don't know anything specifically about running pgagent on Windows, > >> so I can't say why it is giving an error if the docs say it should > >> not. > >> > > > > > >I am setting the PGPASSFILE in system environment variables. I am not > >setting it in user specific environmental variables. > > > It just occurred to me that you said PGPASSFILE was set to > > %APPDATA%/postgresql/pgpass.conf > > > The problem may be that when LocalSystem expands %APPDATA%, it is > finding its own directory, which might be any of: > >C:\Windows\ServiceProfiles\LocalService\appdata >C:\Windows\System32\config\systemprofile\AppData >C:\Windows\SysWOW64\config\systemprofile\AppData > > depending on your Windows version, policies (if any), and whether the > executable is 32 or 64 bit. > > > I wouldn't try messing with any of these directories. Instead try > setting PGPASSFILE to the full path to your file. > > I have tried all of them, pgagent is not recognizing any of the above locations. In fact, I have tried both options #1. By defining PGPASSFILE to the above locations one after the other. #2. By copying pgpass.conf to all the three locations by creating Roaming/postgresql directories. And also I have defined PGPASSFILE=C:\pgpass.conf; I think, this should be accessible to any system account. This also not working. Thank you. > > George > > >
Re: Pgagent is not reading pgpass file either in Windows or Linux.
On Wed, May 30, 2018 at 7:45 PM, George Neuner wrote: > On Tue, 29 May 2018 13:32:46 -0700, Adrian Klaver > wrote: > > >On 05/29/2018 12:14 PM, nageswara Bandla wrote: > > > >> As per the link- > >> (https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html) I set > >> PGPASSFILE environment variable to point to pgpass.conf location. Even > >> then, it’s throwing same above error message. I have found out that > >> pgagent is not reading pgpass.conf file when configured under > >> LocalSystem account. > >> > >> When I change the properties of the pgagent service to run under my > >> login user account. Then, it’s reading pgpass.conf file under > >> %APPDATA%/postgresql/pgpass.conf. > >> > >> I am clueless, why pgagent is not honoring PGPASSFILE env variable. > > > >My guess because the LocalSystem user does not have permissions on your: > > > >%APPDATA%/postgresql/pgpass.conf > > > >file. This seems to be confirmed by it working when you run pgagent as > >the login user. > > LocalSystem has administrator permissions to virtually everything. > https://msdn.microsoft.com/en-us/library/windows/desktop/ > ms684190(v=vs.85).aspx > > It should be able to read files belonging to any user. > > But the LocalSystem account can see only *global* environment > variables ... it can't see any user specific ones. I would check if > the PGPASSFILE variable is set globally or only in the user account. > > > I don't know anything specifically about running pgagent on Windows, > so I can't say why it is giving an error if the docs say it should > not. > I am setting the PGPASSFILE in system environment variables. I am not setting it in user specific environmental variables. > > George > > > >passfile > > > > Specifies the name of the file used to store passwords (see Section > >33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on > >Microsoft Windows. (No error is reported if this file does not exist.) > > ^^ > > >
Re: Pgagent is not reading pgpass file either in Windows or Linux.
On Wed, May 30, 2018 at 5:11 AM, Stéphane Dunand wrote: > > Le 29/05/2018 à 21:14, nageswara Bandla a écrit : > > > > The intention of this post is to find out ways to run pgagent without > passing password in its connection string. > > > > *Windows:* > > I have installed pgagent on windows and configured to run under Local > System account. > > > > Command:- > > C:\PostgreSQL\bigsql\pgagent\bin\pgagent.exe INSTALL pgagent -l 2 -u > LocalSystem hostaddr=127.0.0.1 dbname=postgresdb user=postgres > > > > I have logged into my windows account; where my profile has pgpass.conf in > %APPDATA%/postgresql/pgpass.conf file. > > > > 127.0.0.1:5432:*:postgres:postgres1 > > > > Pgagent is not started and throws error –“The pgagent service on Local > Computer started and then stopped. Some services stop automatically if they > are not in use by other services or programs”. > > Eventvwr log messages have these error messages - “Couldn't create the > primary connection (attempt 10): fe_sendauth: no password supplied” > > > > *PGPASSFILE env variable:* > > As per the link- (https://www.postgresql.org/docs/9.6/static/libpq-pgpass. > html) I set PGPASSFILE environment variable to point to pgpass.conf > location. Even then, it’s throwing same above error message. I have found > out that pgagent is not reading pgpass.conf file when configured under > LocalSystem account. > > > > When I change the properties of the pgagent service to run under my login > user account. Then, it’s reading pgpass.conf file under > %APPDATA%/postgresql/pgpass.conf. > > try without "-u LocalSystem", without PGPASSFILE env variable and with > your login account plus your %APPDATA%/postgresql/pgpass.conf file > Right, with login user account. It just works fine. I confirmed it above. But I was looking for alternatives not to request user for credentials to run the pgagent service. Instead run it from LocalSystem account. > > > > > I am clueless, why pgagent is not honoring PGPASSFILE env variable. > > > > > > *Linux:* > > In Linux, I have installed pgagent_96 using yum command. And tried to run > pgagent using command /etc/init.d/pgagent_96 from postgres user account. > > It fails with error message – “WARNING: Couldn't create the primary > connection (attempt 2): fe_sendauth: no password supplied” > > I made sure that I have .pgpass configuration file with 0600 permissions > in postgres user home directory. > > > > I have tried by defining PGPASSFILE env variable in postgres user account. > But it’s not working. > > > > Pgagent is starting only when we pass password in it’s connection string. > But which is not a good practice at all. > > in your /etc/init.d/pgagent_96 file, verify that the user starting the > command "pgagent ..." is postgres in your case. > Yes, I tries this, it was not working...that's why we are trying to see and run pgagent from the terminal and not in background by enabling -f option. > > > Stéphane Dunand > >
Re: Pgagent is not reading pgpass file either in Windows or Linux.
#1. Windows: My guess because the LocalSystem user does not have permissions on your: %APPDATA%/postgresql/pgpass.conf file. This seems to be confirmed by it working when you run pgagent as the login user. Also: passfile Specifies the name of the file used to store passwords (see Section 33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on Microsoft Windows. (No error is reported if this file does not exist.) Does that mean we can not use pgagent (when installed) as a "LocalSystem" service account and define PGPASSFILE to a valid accessible location. ? Since I have tried copying pgpass.conf file to C:\pgpass.conf and defined PGPASSFILE=C:\pgpass.conf Still it was throwing no password supplied error. #2. Linux What happens if from the terminal as the postgres user you do?: /path/to/pgagent 'connection string' pgagent is throwing the same error from terminal as a postgres user as well. I have enabled debugging logging mode. It's not showing me which password file it's trying to access. When I run psql from postgres user; It works fine. */usr/bin/pgagent_96 -f -l 2 hostaddr=127.0.0.1 dbname=linuxpostgresdb user=postgres port=5432* DEBUG: Creating primary connection DEBUG: Connection Information: DEBUG: user : postgres DEBUG: port : 5432 DEBUG: host : 127.0.0.1 DEBUG: dbname : linuxpostgresdb DEBUG: password : DEBUG: conn timeout : 0 DEBUG: Connection Information: DEBUG: user : postgres DEBUG: port : 5432 DEBUG: host : 127.0.0.1 DEBUG: dbname : linuxpostgresdb DEBUG: password : DEBUG: conn timeout : 0 DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=127.0.0.1 dbname=linuxpostgresdb WARNING: Couldn't create the primary connection (attempt 1): fe_sendauth: no password supplied DEBUG: Clearing all connections DEBUG: Connection stats: total - 1, free - 0, deleted - 1 *# psql command is running fine:* psql -h 127.0.0.1 -d linuxpostgresdb -U postgres psql (8.4.20, server 9.6.6) WARNING: psql version 8.4, server version 9.6. Some psql features might not work. Type "help" for help. linuxpostgresdb=# On Tue, May 29, 2018 at 3:32 PM, Adrian Klaver wrote: > On 05/29/2018 12:14 PM, nageswara Bandla wrote: > >> The intention of this post is to find out ways to run pgagent without >> passing password in its connection string. >> >> *Windows:* >> >> I have installed pgagent on windows and configured to run under Local >> System account. >> >> Command:- >> >> C:\PostgreSQL\bigsql\pgagent\bin\pgagent.exeINSTALL pgagent -l 2 -u >> LocalSystem hostaddr=127.0.0.1 dbname=postgresdb user=postgres >> >> I have logged into my windows account; where my profile has pgpass.conf >> in %APPDATA%/postgresql/pgpass.conf file. >> >> 127.0.0.1:5432:*:postgres:postgres1 >> >> Pgagent is not started and throws error –“The pgagent service on Local >> Computer started and then stopped. Some services stop automatically if they >> are not in use by other services or programs”. >> >> Eventvwr log messages have these error messages - “Couldn't create the >> primary connection (attempt 10): fe_sendauth: no password supplied” >> >> *PGPASSFILE env variable:* >> >> As per the link- (https://www.postgresql.org/do >> cs/9.6/static/libpq-pgpass.html) I set PGPASSFILE environment variable >> to point to pgpass.conf location. Even then, it’s throwing same above error >> message. I have found out that pgagent is not reading pgpass.conf file when >> configured under LocalSystem account. >> >> When I change the properties of the pgagent service to run under my login >> user account. Then, it’s reading pgpass.conf file under >> %APPDATA%/postgresql/pgpass.conf. >> >> I am clueless, why pgagent is not honoring PGPASSFILE env variable. >> > > My guess because the LocalSystem user does not have permissions on your: > > %APPDATA%/postgresql/pgpass.conf > > file. This seems to be confirmed by it working when you run pgagent as the > login user. > > Also: > > passfile > > Specifies the name of the file used to store passwords (see Section > 33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on > Microsoft Windows. (No error is reported if this file does not exist.) > ^^ > > > > >> *Linux:* >> >> In Linux, I have installed pgagent_96 using yum command. And tried to run >> pgagent using command /etc/init.d/pgagent_96 from postgres user account. >> > > This is going to depend on what pgagent_96 is doing? > > >> It fails with error me
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.
