Skip to content

Commit c9a0dc3

Browse files
tvondratvondra
committed
Disallow CREATE STATISTICS on system catalogs
Add a check that CREATE STATISTICS does not add extended statistics on system catalogs, similarly to indexes etc. It can be overriden using the allow_system_table_mods GUC. This bug exists since 7b504eb, adding the extended statistics, so backpatch all the way back to PostgreSQL 10. Author: Tomas Vondra Reported-by: Dean Rasheed Backpatch-through: 10 Discussion: https://postgr.es/m/CAEZATCXAPrrOKwEsyZKQ4uzzJQWBCt6QAvOcgqRGdWwT1zb%2BrQ%40mail.gmail.com
1 parent 4823621 commit c9a0dc3

File tree

3 files changed

+21
-10
lines changed

3 files changed

+21
-10
lines changed

‎src/backend/commands/statscmds.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -135,6 +135,13 @@ CreateStatistics(CreateStatsStmt *stmt)
135135
if (!pg_class_ownercheck(RelationGetRelid(rel), stxowner))
136136
aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(rel->rd_rel->relkind),
137137
RelationGetRelationName(rel));
138+
139+
/* Creating statistics on system catalogs is not allowed */
140+
if (!allowSystemTableMods && IsSystemRelation(rel))
141+
ereport(ERROR,
142+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
143+
errmsg("permission denied: \"%s\" is a system catalog",
144+
RelationGetRelationName(rel))));
138145
}
139146

140147
Assert(rel);

‎src/test/regress/expected/stats_ext.out

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@ begin
2525
end;
2626
$$;
2727
-- Verify failures
28+
CREATE TABLE ext_stats_test (x int, y int, z int);
2829
CREATE STATISTICS tst;
2930
ERROR: syntax error at or near ";"
3031
LINE 1: CREATE STATISTICS tst;
@@ -39,16 +40,17 @@ LINE 1: CREATE STATISTICS tst FROM sometab;
3940
^
4041
CREATE STATISTICS tst ON a, b FROM nonexistent;
4142
ERROR: relation "nonexistent" does not exist
42-
CREATE STATISTICS tst ON a, b FROM pg_class;
43+
CREATE STATISTICS tst ON a, b FROM ext_stats_test;
4344
ERROR: column "a" does not exist
44-
CREATE STATISTICS tst ON relname, relname, relnatts FROM pg_class;
45+
CREATE STATISTICS tst ON x, x, y FROM ext_stats_test;
4546
ERROR: duplicate column name in statistics definition
46-
CREATE STATISTICS tst ON relnatts + relpages FROM pg_class;
47+
CREATE STATISTICS tst ON x + y FROM ext_stats_test;
4748
ERROR: only simple column references are allowed in CREATE STATISTICS
48-
CREATE STATISTICS tst ON (relpages, reltuples) FROM pg_class;
49+
CREATE STATISTICS tst ON (x, y) FROM ext_stats_test;
4950
ERROR: only simple column references are allowed in CREATE STATISTICS
50-
CREATE STATISTICS tst (unrecognized) ON relname, relnatts FROM pg_class;
51+
CREATE STATISTICS tst (unrecognized) ON x, y FROM ext_stats_test;
5152
ERROR: unrecognized statistics kind "unrecognized"
53+
DROP TABLE ext_stats_test;
5254
-- Ensure stats are dropped sanely, and test IF NOT EXISTS while at it
5355
CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
5456
CREATE STATISTICS IF NOT EXISTS ab1_a_b_stats ON a, b FROM ab1;

‎src/test/regress/sql/stats_ext.sql

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -28,15 +28,17 @@ end;
2828
$$;
2929

3030
-- Verify failures
31+
CREATE TABLE ext_stats_test (x int, y int, z int);
3132
CREATE STATISTICS tst;
3233
CREATE STATISTICS tst ON a, b;
3334
CREATE STATISTICS tst FROM sometab;
3435
CREATE STATISTICS tst ON a, b FROM nonexistent;
35-
CREATE STATISTICS tst ON a, b FROM pg_class;
36-
CREATE STATISTICS tst ON relname, relname, relnatts FROM pg_class;
37-
CREATE STATISTICS tst ON relnatts + relpages FROM pg_class;
38-
CREATE STATISTICS tst ON (relpages, reltuples) FROM pg_class;
39-
CREATE STATISTICS tst (unrecognized) ON relname, relnatts FROM pg_class;
36+
CREATE STATISTICS tst ON a, b FROM ext_stats_test;
37+
CREATE STATISTICS tst ON x, x, y FROM ext_stats_test;
38+
CREATE STATISTICS tst ON x + y FROM ext_stats_test;
39+
CREATE STATISTICS tst ON (x, y) FROM ext_stats_test;
40+
CREATE STATISTICS tst (unrecognized) ON x, y FROM ext_stats_test;
41+
DROP TABLE ext_stats_test;
4042

4143
-- Ensure stats are dropped sanely, and test IF NOT EXISTS while at it
4244
CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);

0 commit comments

Comments
 (0)