pg_cron: cron.schedule_in_database is working without pg_hba.conf entry!?

From: "Zwettler Markus (OIZ)" <Markus(dot)Zwettler(at)zuerich(dot)ch>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: pg_cron: cron.schedule_in_database is working without pg_hba.conf entry!?
Date: 2023-03-20 16:56:20
Message-ID: GV0P278MB00990C7370E8E8F539E43AC08B809@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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)

Browse pgsql-admin by date

  From Date Subject
Next Message jian he 2023-03-21 06:15:19 Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
Previous Message Ron Johnson 2023-03-20 16:50:16 Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl