Skip to content

Commit 6a3a85b

Browse files
gbartolinileonardocemnenciaNiccoloFei
authored
fix: schema-qualify catalog references in monitoring queries (#10576)
Unqualified references to `pg_catalog` functions and views are resolved via `search_path`, which can be manipulated by a database user to shadow built-in objects. Use explicit `pg_catalog`. qualification throughout the shipped default-monitoring config and documentation samples. Closes #10575 Assisted-by: Claude Signed-off-by: Gabriele Bartolini <[email protected]> Signed-off-by: Leonardo Cecchi <[email protected]> Signed-off-by: Marco Nenciarini <[email protected]> Signed-off-by: Niccolò Fei <[email protected]> Co-authored-by: Leonardo Cecchi <[email protected]> Co-authored-by: Marco Nenciarini <[email protected]> Co-authored-by: Niccolò Fei <[email protected]>
1 parent 770e95c commit 6a3a85b

5 files changed

Lines changed: 42 additions & 42 deletions

File tree

‎config/manager/default-monitoring.yaml‎

Lines changed: 18 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -27,11 +27,11 @@ data:
2727
, state
2828
, usename
2929
, COALESCE(application_name, '') AS application_name
30-
, COUNT(*)
31-
, COALESCE(EXTRACT (EPOCH FROM (max(now() - xact_start))), 0) AS max_tx_secs
30+
, pg_catalog.count(*)
31+
, COALESCE(EXTRACT (EPOCH FROM (pg_catalog.max(pg_catalog.now() OPERATOR(pg_catalog.-) xact_start))), 0) AS max_tx_secs
3232
FROM pg_catalog.pg_stat_activity
3333
GROUP BY datname, state, usename, application_name
34-
) sa ON states.state = sa.state
34+
) sa ON states.state OPERATOR(pg_catalog.=) sa.state
3535
WHERE sa.usename IS NOT NULL
3636
metrics:
3737
- datname:
@@ -55,10 +55,10 @@ data:
5555
5656
backends_waiting:
5757
query: |
58-
SELECT count(*) AS total
58+
SELECT pg_catalog.count(*) AS total
5959
FROM pg_catalog.pg_locks blocked_locks
6060
JOIN pg_catalog.pg_locks blocking_locks
61-
ON blocking_locks.locktype = blocked_locks.locktype
61+
ON blocking_locks.locktype OPERATOR(pg_catalog.=) blocked_locks.locktype
6262
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
6363
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
6464
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
@@ -68,8 +68,8 @@ data:
6868
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
6969
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
7070
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
71-
AND blocking_locks.pid != blocked_locks.pid
72-
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
71+
AND blocking_locks.pid OPERATOR(pg_catalog.<>) blocked_locks.pid
72+
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid OPERATOR(pg_catalog.=) blocking_locks.pid
7373
WHERE NOT blocked_locks.granted
7474
metrics:
7575
- total:
@@ -110,14 +110,14 @@ data:
110110
pg_replication:
111111
query: "SELECT CASE WHEN (
112112
NOT pg_catalog.pg_is_in_recovery()
113-
OR pg_catalog.pg_last_wal_receive_lsn() = pg_catalog.pg_last_wal_replay_lsn())
113+
OR pg_catalog.pg_last_wal_receive_lsn() OPERATOR(pg_catalog.=) pg_catalog.pg_last_wal_replay_lsn())
114114
THEN 0
115115
ELSE GREATEST (0,
116-
EXTRACT(EPOCH FROM (now() - pg_catalog.pg_last_xact_replay_timestamp())))
116+
EXTRACT(EPOCH FROM (pg_catalog.now() OPERATOR(pg_catalog.-) pg_catalog.pg_last_xact_replay_timestamp())))
117117
END AS lag,
118118
pg_catalog.pg_is_in_recovery() AS in_recovery,
119-
EXISTS (TABLE pg_stat_wal_receiver) AS is_wal_receiver_up,
120-
(SELECT count(*) FROM pg_catalog.pg_stat_replication) AS streaming_replicas"
119+
EXISTS (TABLE pg_catalog.pg_stat_wal_receiver) AS is_wal_receiver_up,
120+
(SELECT pg_catalog.count(*) FROM pg_catalog.pg_stat_replication) AS streaming_replicas"
121121
metrics:
122122
- lag:
123123
usage: "GAUGE"
@@ -165,17 +165,17 @@ data:
165165
query: |
166166
SELECT archived_count
167167
, failed_count
168-
, COALESCE(EXTRACT(EPOCH FROM (now() - last_archived_time)), -1) AS seconds_since_last_archival
169-
, COALESCE(EXTRACT(EPOCH FROM (now() - last_failed_time)), -1) AS seconds_since_last_failure
168+
, COALESCE(EXTRACT(EPOCH FROM (pg_catalog.now() OPERATOR(pg_catalog.-) last_archived_time)), -1) AS seconds_since_last_archival
169+
, COALESCE(EXTRACT(EPOCH FROM (pg_catalog.now() OPERATOR(pg_catalog.-) last_failed_time)), -1) AS seconds_since_last_failure
170170
, COALESCE(EXTRACT(EPOCH FROM last_archived_time), -1) AS last_archived_time
171171
, COALESCE(EXTRACT(EPOCH FROM last_failed_time), -1) AS last_failed_time
172-
, COALESCE(CAST(CAST('x'||pg_catalog.right(pg_catalog.split_part(last_archived_wal, '.', 1), 16) AS pg_catalog.bit(64)) AS pg_catalog.int8), -1) AS last_archived_wal_start_lsn
173-
, COALESCE(CAST(CAST('x'||pg_catalog.right(pg_catalog.split_part(last_failed_wal, '.', 1), 16) AS pg_catalog.bit(64)) AS pg_catalog.int8), -1) AS last_failed_wal_start_lsn
172+
, COALESCE(CAST(CAST('x' OPERATOR(pg_catalog.||) pg_catalog.right(pg_catalog.split_part(last_archived_wal, '.', 1), 16) AS pg_catalog.bit(64)) AS pg_catalog.int8), -1) AS last_archived_wal_start_lsn
173+
, COALESCE(CAST(CAST('x' OPERATOR(pg_catalog.||) pg_catalog.right(pg_catalog.split_part(last_failed_wal, '.', 1), 16) AS pg_catalog.bit(64)) AS pg_catalog.int8), -1) AS last_failed_wal_start_lsn
174174
, EXTRACT(EPOCH FROM stats_reset) AS stats_reset_time
175175
FROM pg_catalog.pg_stat_archiver
176176
predicate_query: |
177177
SELECT NOT pg_catalog.pg_is_in_recovery()
178-
OR pg_catalog.current_setting('archive_mode') = 'always'
178+
OR pg_catalog.current_setting('archive_mode') OPERATOR(pg_catalog.=) 'always'
179179
metrics:
180180
- archived_count:
181181
usage: "COUNTER"
@@ -461,12 +461,12 @@ data:
461461
pg_extensions:
462462
query: |
463463
SELECT
464-
current_database() as datname,
464+
pg_catalog.current_database() as datname,
465465
name as extname,
466466
default_version,
467467
installed_version,
468468
CASE
469-
WHEN default_version = installed_version THEN 0
469+
WHEN default_version OPERATOR(pg_catalog.=) installed_version THEN 0
470470
ELSE 1
471471
END AS update_available
472472
FROM pg_catalog.pg_available_extensions

‎docs/src/monitoring.md‎

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -510,14 +510,14 @@ metadata:
510510
data:
511511
custom-queries: |
512512
pg_replication:
513-
query: "SELECT CASE WHEN NOT pg_is_in_recovery()
513+
query: "SELECT CASE WHEN NOT pg_catalog.pg_is_in_recovery()
514514
THEN 0
515515
ELSE GREATEST (0,
516-
EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())))
516+
EXTRACT(EPOCH FROM (pg_catalog.now() OPERATOR(pg_catalog.-) pg_catalog.pg_last_xact_replay_timestamp())))
517517
END AS lag,
518-
pg_is_in_recovery() AS in_recovery,
519-
EXISTS (TABLE pg_stat_wal_receiver) AS is_wal_receiver_up,
520-
(SELECT count(*) FROM pg_stat_replication) AS streaming_replicas"
518+
pg_catalog.pg_is_in_recovery() AS in_recovery,
519+
EXISTS (TABLE pg_catalog.pg_stat_wal_receiver) AS is_wal_receiver_up,
520+
(SELECT pg_catalog.count(*) FROM pg_catalog.pg_stat_replication) AS streaming_replicas"
521521
522522
metrics:
523523
- lag:
@@ -553,7 +553,7 @@ some_query: |
553553
FROM some_table
554554
query: |
555555
SELECT
556-
count(*) as rows
556+
pg_catalog.count(*) as rows
557557
FROM some_table
558558
metrics:
559559
- rows:
@@ -586,8 +586,8 @@ as in the following example:
586586
some_query: |
587587
query: |
588588
SELECT
589-
current_database() as datname,
590-
count(*) as rows
589+
pg_catalog.current_database() as datname,
590+
pg_catalog.count(*) as rows
591591
FROM some_table
592592
metrics:
593593
- datname:
@@ -618,8 +618,8 @@ aforementioned query):
618618
some_query: |
619619
query: |
620620
SELECT
621-
current_database() as datname,
622-
count(*) as rows
621+
pg_catalog.current_database() as datname,
622+
pg_catalog.count(*) as rows
623623
FROM some_table
624624
metrics:
625625
- datname:

‎docs/src/samples/cluster-example-monitoring.yaml‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -40,7 +40,7 @@ data:
4040
- "*"
4141
query: |
4242
SELECT
43-
current_database() datname,
43+
pg_catalog.current_database() datname,
4444
schemaname,
4545
relname,
4646
seq_scan,
@@ -63,7 +63,7 @@ data:
6363
analyze_count,
6464
autoanalyze_count
6565
FROM
66-
pg_stat_user_tables
66+
pg_catalog.pg_stat_user_tables
6767
metrics:
6868
- datname:
6969
usage: "LABEL"
@@ -133,7 +133,7 @@ data:
133133
description: "Number of times this table has been analyzed by the autovacuum daemon"
134134
135135
pg_statio_user_tables:
136-
query: "SELECT current_database() datname, schemaname, relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit FROM pg_statio_user_tables"
136+
query: "SELECT pg_catalog.current_database() datname, schemaname, relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit FROM pg_catalog.pg_statio_user_tables"
137137
metrics:
138138
- datname:
139139
usage: "LABEL"
@@ -177,7 +177,7 @@ data:
177177
application_name,
178178
SUM(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change))::bigint)::float AS process_idle_seconds_sum,
179179
COUNT(*) AS process_idle_seconds_count
180-
FROM pg_stat_activity
180+
FROM pg_catalog.pg_stat_activity
181181
WHERE state = 'idle'
182182
GROUP BY application_name
183183
),
@@ -192,7 +192,7 @@ data:
192192
END
193193
)::bigint AS bucket
194194
FROM
195-
pg_stat_activity,
195+
pg_catalog.pg_stat_activity,
196196
UNNEST(ARRAY[1, 2, 5, 15, 30, 60, 90, 120, 300]) AS le
197197
GROUP BY application_name, le
198198
ORDER BY application_name, le
@@ -222,7 +222,7 @@ metadata:
222222
stringData:
223223
pg-database: |
224224
pg_database:
225-
query: "SELECT pg_database.datname, pg_database_size(pg_database.datname) as size_bytes FROM pg_database"
225+
query: "SELECT pg_database.datname, pg_catalog.pg_database_size(pg_database.datname) as size_bytes FROM pg_catalog.pg_database"
226226
primary: true
227227
cache_seconds: 30
228228
metrics:

‎tests/e2e/fixtures/metrics/custom-queries-with-target-databases.yaml‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@ data:
99
some_query:
1010
query: |
1111
SELECT
12-
current_database() as datname,
12+
pg_catalog.current_database() as datname,
1313
count(*) as rows
1414
FROM test_table
1515
metrics:
@@ -35,7 +35,7 @@ stringData:
3535
some_query_test:
3636
query: |
3737
SELECT
38-
current_database() as datname,
38+
pg_catalog.current_database() as datname,
3939
count(*) as rows
4040
metrics:
4141
- datname:

‎tests/e2e/fixtures/metrics/custom-queries.yaml‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -7,15 +7,15 @@ metadata:
77
data:
88
queries.yaml: |
99
pg_postmaster: # wokeignore:rule=master
10-
query: "SELECT EXTRACT(EPOCH FROM (now() - pg_postmaster_start_time())) AS start_time_seconds" # wokeignore:rule=master
10+
query: "SELECT EXTRACT(EPOCH FROM (pg_catalog.now() - pg_catalog.pg_postmaster_start_time())) AS start_time_seconds" # wokeignore:rule=master
1111
primary: false
1212
metrics:
1313
- start_time_seconds:
1414
usage: "COUNTER"
1515
description: "Seconds since the postgres server start"
1616
additional-queries: |
1717
pg_wal_files:
18-
query: "SELECT COALESCE(sum(1), 0) AS total FROM pg_ls_waldir() AS d (file) WHERE file ~ '^[0-9A-F]{8}[0-9A-F]{8}[0-9A-F]{8}$'"
18+
query: "SELECT COALESCE(pg_catalog.sum(1), 0) AS total FROM pg_catalog.pg_ls_waldir() AS d (file) WHERE file ~ '^[0-9A-F]{8}[0-9A-F]{8}[0-9A-F]{8}$'"
1919
primary: false
2020
metrics:
2121
- total:
@@ -47,7 +47,7 @@ metadata:
4747
data:
4848
queries.yaml: |
4949
pg_database:
50-
query: "SELECT pg_database.datname, pg_database_size(pg_database.datname) as size_bytes FROM pg_database"
50+
query: "SELECT pg_database.datname, pg_catalog.pg_database_size(pg_database.datname) as size_bytes FROM pg_catalog.pg_database"
5151
primary: false
5252
metrics:
5353
- datname:
@@ -59,7 +59,7 @@ data:
5959
6060
additional-queries: |
6161
e2e_tests_replication_slots_status:
62-
query: "SELECT count(*) AS inactive FROM pg_replication_slots WHERE NOT active"
62+
query: "SELECT pg_catalog.count(*) AS inactive FROM pg_catalog.pg_replication_slots WHERE NOT active"
6363
primary: false
6464
metrics:
6565
- inactive:
@@ -75,7 +75,7 @@ metadata:
7575
stringData:
7676
queries.yaml: |
7777
pg_stat_archiver:
78-
query: "SELECT archived_count, failed_count FROM pg_stat_archiver"
78+
query: "SELECT archived_count, failed_count FROM pg_catalog.pg_stat_archiver"
7979
primary: false
8080
metrics:
8181
- archived_count:
@@ -88,7 +88,7 @@ stringData:
8888
additional-queries: |
8989
pg_locks:
9090
query: |
91-
SELECT count(*) as blocked_queries
91+
SELECT pg_catalog.count(*) as blocked_queries
9292
FROM pg_catalog.pg_locks blocked_locks
9393
JOIN pg_catalog.pg_locks blocking_locks
9494
ON blocking_locks.locktype = blocked_locks.locktype

0 commit comments

Comments
 (0)