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

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

>
>
> Stéphane Dunand
>
>