Thread: pg_cron: cron.schedule_in_database is working without pg_hba.conf entry!?
pg_cron: cron.schedule_in_database is working without pg_hba.conf entry!?
pg_cron: cron.schedule_in_database is working without pg_hba.conf entry for my user "monitor" (superuser)?
See below.
I always thought there must be an pg_hba.conf entry for the user?
sh-4.4$ cat pg_hba.conf
# Do not edit this file manually!
# It will be overwritten by Patroni!
local all "postgres" peer
hostssl replication "_crunchyrepl" all cert
hostssl "postgres" "_crunchyrepl" all cert
host all "_crunchyrepl" all reject
host all "ccp_monitoring" "127.0.0.0/8" scram-sha-256
host all "ccp_monitoring" "::1/128" scram-sha-256
host all "ccp_monitoring" all reject
hostssl all all all md5
sh-4.4$ fgrep socket postgresql.conf
unix_socket_directories = '/tmp/postgres'
sh-4.4$ fgrep cron.host postgresql.conf
cron.host = '/tmp/postgres'
sh-4.4$ psql -U monitor cmppgdb001
psql: error: connection to server on socket "/tmp/postgres/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "monitor", database "cmppgdb001", no encryption
sh-4.4$ psql
psql (14.3)
Type "help" for help.
postgres=# \c cmppgdb001 monitor
connection to server on socket "/tmp/postgres/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "monitor", database "cmppgdb001", no encryption
Previous connection kept
postgres=# \c cmppgdb001
You are now connected to database "cmppgdb001" as user "postgres".
cmppgdb001=# set session authorization monitor;
SET
cmppgdb001=# create table t1 (i int);
CREATE TABLE
cmppgdb001=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | spatial_ref_sys | table | postgres
public | t1 | table | monitor
(2 rows)
cmppgdb001=# reset session authorization;
RESET
cmppgdb001=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+----------+---------+----------+----------+----------+----------+--------+---------
(0 rows)
postgres=# select cron.schedule_in_database ('my-test','* * * * *','update t1 set i = i + 1','cmppgdb001','monitor',true);
schedule_in_database
----------------------
3
(1 row)
postgres=# select * from cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+-----------+-------------------------+---------------+----------+------------+----------+--------+---------
3 | * * * * * | update t1 set i = i + 1 | /tmp/postgres | 5432 | cmppgdb001 | monitor | t | my-test
(1 row)
postgres=# select * from cron.job_run_details;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+------------+----------+-------------------------+-----------+----------------+-------------------------------+-------------------------------
3 | 34 | 270145 | cmppgdb001 | monitor | update t1 set i = i + 1 | succeeded | UPDATE 0 | 2023-03-20 17:39:01.016343+01 | 2023-03-20 17:39:02.023517+01
(1 row)