pgAgent fails to connect database :'fe_sendauth: no password supplied' (in Windows7)

arayo picture arayo · Apr 18, 2015 · Viewed 8.2k times · Source

Help me please.. I have some questions about pgAgent.

[Environment]

DB : PostgreSQL 9.3

OS : Window 7

OS login ID : [email protected]

pgAdmin login ID : postgres

pgAgent user : postgres 

pgAgent schema belongs to: postgres DB

DB that I'll treat : testDB  (In Step configuration, I specified 'testDB' as Database.
                   Connection type is 'local'. Connection string is empty.)

DB server : local machine (my laptop computer)

I scheduled pgAgent job, but it fails to connect database.

Output message ( pgAdmin>Statistics )

[Warning] Couldn't get a connection to the database (pgAgent)

(What database? There are 2 DBs. 'postgres' and 'testDB'. And I'll treat 'testDB'. Then couldn't get a connection to which database?)

windows event viewer

Failed to create new connection to database 'testDB':'fe_sendauth: no password supplied'

My pgpass.conf

localhost:5432:postgres:postgres:[pgAdmin password for postgres]
localhost:5432:testDB:postgres:[pgAdmin password for postgres]

My pg_hba.conf

@remove-line-for-nolocal@# "local" is for Unix domain socket connections only

@remove-line-for-nolocal@local   all             all                                     @authmethodlocal@
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 @authmethodhost@
# Allow replication connections from localhost, by a user with the
# replication privilege.
@remove-line-for-nolocal@#local   replication     @default_username@                                @authmethodlocal@
#host    replication     @default_username@        127.0.0.1/32            @authmethodhost@
#host    replication     @default_username@        ::1/128                 @authmethodhost@

My pgAgent command option ( Service>pgAgent>Property>General tab )

C:\Program Files (x86)\pgAgent\bin\pgagent.exe RUN pgAgent host=localhost port=5432 user=postgres dbname=postgres

pgAgent service Logon ID ( Service>pgAgent>Property>Logon tab )

id : [email protected]

pwd : [ [email protected]'s password ]

[Conclusion]

Password setting in pgpass.conf seems wrong for anything I know. Or pg_hba.conf Or logon ID in Services>pgAgent>Logon Actually.. I really don't understand what's wrong.^^

Thank you for your help in advance ^^

Answer

arayo picture arayo · May 1, 2015

Beautiful day... I solved it ^^

[Solution]

I made other OS login ID. (NOT need to be 'postgres' !!)
And put pgpass.conf in that ID's AppData\Roaming\postgresql

[Reason]

pgAgent could not find pgpass.conf in login ID's directory.

I said my OS login ID is [email protected].

But [email protected]'s real name(folder name in C:\Users) was 'SOME NAME TEST'.

That's DIFFERENT !!

When I checked logon ID in services, pgAgent's logon ID was '.\[email protected]'.

I guess pgAgent tried to find pgpass.conf in 'C:\Users\[email protected]\AppData\Roaming\postgresql'.

But there was no such directory.!!

My pgpass.conf was in 'C:\Users\SOME NAME TEST\AppData\Roaming\postgresql'

I made a other OS ID 'anyname' in casual way. (control panel>account management.)

login ID : anyname

real name : anyname ( C:\Users\anyname )

That's SAME !! ^^

And placed pgpass.conf in 'C:\Users\anyname\AppData\Roaming\postgresql'

Then changed the pgAgent's logon ID to '.\anyname' in services.

Now it works well ^^