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)

14
  • Is pg_cron run locally or remotely? Which database are you trying to connect to as which user?
    – tink
    Feb 18 at 17:08
  • locally, i am trying to connect to database postgres as user postgres
    – rafaelBackx
    Feb 18 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 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 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 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy