Re: 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(dot)klaver(at)aklaver(dot)com