Re: Pgagent is not reading pgpass file either in Windows or Linux. - Mailing list pgsql-general
| From | nageswara Bandla |
|---|---|
| Subject | Re: Pgagent is not reading pgpass file either in Windows or Linux. |
| Date | |
| Msg-id | CADJadRDZLZyRuD20dDy-LkV9hrZhKkzObQYHHWDTKt1iZEDtHQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Pgagent is not reading pgpass file either in Windows or Linux. (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Pgagent is not reading pgpass file either in Windows or Linux.
|
| List | pgsql-general |
#1. Windows:
My guess because the LocalSystem user does not have permissions on your:
%APPDATA%/postgresql/pgpass.co
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.co
%APPDATA%/postgresql/pgpass.co
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.co
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'
/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.klaver@aklaver.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/docs/9.6/static/libpq-pgpass.htm l) 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
pgsql-general by date: