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

nageswara Bandla <nag.bandla@xxxxxxxxx> · Wed, 30 May 2018 10:51:51 -0500

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