1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
|
/*
* fix-CVE-2024-4317.sql
*
* Copyright (c) 2024, PostgreSQL Global Development Group
*
* src/backend/catalog/fix-CVE-2024-4317.sql
*
* This file should be run in every database in the cluster to address
* CVE-2024-4317.
*/
SET search_path = pg_catalog;
CREATE OR REPLACE VIEW pg_stats_ext WITH (security_barrier) AS
SELECT cn.nspname AS schemaname,
c.relname AS tablename,
sn.nspname AS statistics_schemaname,
s.stxname AS statistics_name,
pg_get_userbyid(s.stxowner) AS statistics_owner,
( SELECT array_agg(a.attname ORDER BY a.attnum)
FROM unnest(s.stxkeys) k
JOIN pg_attribute a
ON (a.attrelid = s.stxrelid AND a.attnum = k)
) AS attnames,
pg_get_statisticsobjdef_expressions(s.oid) as exprs,
s.stxkind AS kinds,
sd.stxdinherit AS inherited,
sd.stxdndistinct AS n_distinct,
sd.stxddependencies AS dependencies,
m.most_common_vals,
m.most_common_val_nulls,
m.most_common_freqs,
m.most_common_base_freqs
FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid)
JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid)
LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace)
LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace)
LEFT JOIN LATERAL
( SELECT array_agg(values) AS most_common_vals,
array_agg(nulls) AS most_common_val_nulls,
array_agg(frequency) AS most_common_freqs,
array_agg(base_frequency) AS most_common_base_freqs
FROM pg_mcv_list_items(sd.stxdmcv)
) m ON sd.stxdmcv IS NOT NULL
WHERE pg_has_role(c.relowner, 'USAGE')
AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
CREATE OR REPLACE VIEW pg_stats_ext_exprs WITH (security_barrier) AS
SELECT cn.nspname AS schemaname,
c.relname AS tablename,
sn.nspname AS statistics_schemaname,
s.stxname AS statistics_name,
pg_get_userbyid(s.stxowner) AS statistics_owner,
stat.expr,
sd.stxdinherit AS inherited,
(stat.a).stanullfrac AS null_frac,
(stat.a).stawidth AS avg_width,
(stat.a).stadistinct AS n_distinct,
(CASE
WHEN (stat.a).stakind1 = 1 THEN (stat.a).stavalues1
WHEN (stat.a).stakind2 = 1 THEN (stat.a).stavalues2
WHEN (stat.a).stakind3 = 1 THEN (stat.a).stavalues3
WHEN (stat.a).stakind4 = 1 THEN (stat.a).stavalues4
WHEN (stat.a).stakind5 = 1 THEN (stat.a).stavalues5
END) AS most_common_vals,
(CASE
WHEN (stat.a).stakind1 = 1 THEN (stat.a).stanumbers1
WHEN (stat.a).stakind2 = 1 THEN (stat.a).stanumbers2
WHEN (stat.a).stakind3 = 1 THEN (stat.a).stanumbers3
WHEN (stat.a).stakind4 = 1 THEN (stat.a).stanumbers4
WHEN (stat.a).stakind5 = 1 THEN (stat.a).stanumbers5
END) AS most_common_freqs,
(CASE
WHEN (stat.a).stakind1 = 2 THEN (stat.a).stavalues1
WHEN (stat.a).stakind2 = 2 THEN (stat.a).stavalues2
WHEN (stat.a).stakind3 = 2 THEN (stat.a).stavalues3
WHEN (stat.a).stakind4 = 2 THEN (stat.a).stavalues4
WHEN (stat.a).stakind5 = 2 THEN (stat.a).stavalues5
END) AS histogram_bounds,
(CASE
WHEN (stat.a).stakind1 = 3 THEN (stat.a).stanumbers1[1]
WHEN (stat.a).stakind2 = 3 THEN (stat.a).stanumbers2[1]
WHEN (stat.a).stakind3 = 3 THEN (stat.a).stanumbers3[1]
WHEN (stat.a).stakind4 = 3 THEN (stat.a).stanumbers4[1]
WHEN (stat.a).stakind5 = 3 THEN (stat.a).stanumbers5[1]
END) correlation,
(CASE
WHEN (stat.a).stakind1 = 4 THEN (stat.a).stavalues1
WHEN (stat.a).stakind2 = 4 THEN (stat.a).stavalues2
WHEN (stat.a).stakind3 = 4 THEN (stat.a).stavalues3
WHEN (stat.a).stakind4 = 4 THEN (stat.a).stavalues4
WHEN (stat.a).stakind5 = 4 THEN (stat.a).stavalues5
END) AS most_common_elems,
(CASE
WHEN (stat.a).stakind1 = 4 THEN (stat.a).stanumbers1
WHEN (stat.a).stakind2 = 4 THEN (stat.a).stanumbers2
WHEN (stat.a).stakind3 = 4 THEN (stat.a).stanumbers3
WHEN (stat.a).stakind4 = 4 THEN (stat.a).stanumbers4
WHEN (stat.a).stakind5 = 4 THEN (stat.a).stanumbers5
END) AS most_common_elem_freqs,
(CASE
WHEN (stat.a).stakind1 = 5 THEN (stat.a).stanumbers1
WHEN (stat.a).stakind2 = 5 THEN (stat.a).stanumbers2
WHEN (stat.a).stakind3 = 5 THEN (stat.a).stanumbers3
WHEN (stat.a).stakind4 = 5 THEN (stat.a).stanumbers4
WHEN (stat.a).stakind5 = 5 THEN (stat.a).stanumbers5
END) AS elem_count_histogram
FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid)
LEFT JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid)
LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace)
LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace)
JOIN LATERAL (
SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,
unnest(sd.stxdexpr)::pg_statistic AS a
) stat ON (stat.expr IS NOT NULL)
WHERE pg_has_role(c.relowner, 'USAGE')
AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
|
