0

I am trying to run a function periodically with pg_cron but every time it wants to execute it throws connection refused. the cron job is created using the postgres user.

pg_hba.conf

local   all             postgres                                md5
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
host    all             all             127.0.0.1/32            md5
host    postgres        postgres        localhost               trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I've tried adding a .pgpass file but, I don't know where to put it. this is my machine version: Debian GNU/Linux 10 (buster)

Improve this question
13
  • Is pg_cron run locally or remotely? Which database are you trying to connect to as which user?
    – tink
    Feb 18, 2021 at 17:08
  • locally, i am trying to connect to database postgres as user postgres
    – rafaelBackx
    Feb 18, 2021 at 17:11
  • And you're using the correct password? Typically .pgpass lives in the user's HOME ... which in ubuntu (I assume debian will be similar?) would be /var/lib/postgresql ...
    – tink
    Feb 18, 2021 at 17:17
  • i tried putting it in /var/lib/postgresql/.pgpass and /var/lib/postgresql/12/main/.pgpass both didn't work
    – rafaelBackx
    Feb 18, 2021 at 17:26
  • 1
    So pg_cron wasn't using local at all ... interesting. You still may want to narrow down the permitted users and/or find out what the password authentication with MD5 didn't work. Your solution leaves me with a stale taste in my mouth.
    – tink
    Feb 18, 2021 at 18:34

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.