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

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