On Tue, May 29, 2018 at 6:15 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 05/29/2018 03:57 PM, nageswara Bandla wrote:
#1. Windows:
My guess because the LocalSystem user does not have permissions on your:
%APPDATA%/postgresql/pgpass.co <http://pgpass.co>nf
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 <http://pgpass.co>nf 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. ?
Not sure as I do not use Windows much and do not have a good handle on how it handles permissions. You might try raising an issue below to see if you can get some guidance:
Thank you Adrian, let me also try to raise an issue there and see.
https://github.com/postgres/pgagent/issues
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.
Remember there is a difference between the postgres system user and the postgres database user. So when you did the above where you in the postgres system user shell? That is where you set up the .pgpass file. Running as the postgres database user in another system account will not work unless you create a .pgpass file in that home directory as well.
It also seems that you have more the one instance of Postgres of installed and it looks like the 8.4.20 version is being found first. So I wonder if there is a library compatibility issue going on, given that 8.4 is well past EOL.
I am running pgagent command from postgres system user account where .pgpass is setup.
It’s definitely not a library issue. Since the same pgagent command that I gave earlier is working fine by appending “password=secret” at the end in pgagent command line.
/usr/bin/pgagent_96 -f -l 2 hostaddr=127.0.0.1 dbname=linuxpostgresdb user=postgres port=5432 password=secret
Anyway, I have tried your suggestion to clean up old postgres libraries and tried to keep only one version of postgres libraries exist in the entire system. It’s still same issue.
And I am trying in the same machine where the postgres is installed.
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=#
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
