"connection failed" error for pg_cron extension

Question

Installed pg_cron and scheduled job, though there is connection error because job is not done and cron.job_run_details table shows connection failed error message.

As doc says:

Important: Internally, pg_cron uses libpq to open a new connection to the local database. It may be necessary to enable trust authentication for connections coming from localhost in pg_hba.conf for the user running the cron job. Alternatively, you can add the password to a .pgpass file, which libpq will use when opening a connection.

Created file .pgpass with needed parameters, but not sure how to "tell" to libpq that for this user, for this host and DB read this .pgpass file.

Any help appreciated.

Answer 1

I probably had .pgpass with wrong permissions or wrong place (as @PhilW suggested) but I found completely different way which worked for me (btw, I just removed .pgpass file).

Solution was simply changing cron.job.nodename to empty string:

UPDATE cron.job SET nodename = '';

Source: https://github.com/citusdata/pg_cron/issues/48

Answer 2

not sure how to "tell" to libpq that for this user, for this host and DB read this .pgpass file.

You don't need to.

The .pgpass file is always read when the account that "owns" it runs any postgres process that requires a password.
That ownership is determined by the file's location (i.e. in the account's home directory) and permissions (read/write to owner only).

The file contains host, database, username, etc. and the password to be used for each combination. Postgres will extract and use the password found when all the other "bits" match what's being requested.

From The Docs, the file contains:

hostname:port:database:username:password

So, for your HOST, DB and USER, you'd have something more like:

HOST:5432:DB:USER:password