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