Skip to content

Commit 63f7e91

Browse files
michaelpqmichaelpq
committed
Fix crashes with CREATE SCHEMA AUTHORIZATION and schema elements
CREATE SCHEMA AUTHORIZATION with appended schema elements can lead to crashes when comparing the schema name of the query with the schemas used in the qualification of some clauses in the elements' queries. The origin of the problem is that the transformation routine for the elements listed in a CREATE SCHEMA query uses as new, expected, schema name the one listed in CreateSchemaStmt itself. However, depending on the query, CreateSchemaStmt.schemaname may be NULL, being computed instead from the role specification of the query given by the AUTHORIZATION clause, that could be either: - A user name string, with the new schema name being set to the same value as the role given. - Guessed from CURRENT_ROLE, SESSION_ROLE or CURRENT_ROLE, with a new schema name computed from the security context where CREATE SCHEMA is running. Regression tests are added for CREATE SCHEMA with some appended elements (some of them with schema qualifications), covering also some role specification patterns. While on it, this simplifies the context structure used during the transformation of the elements listed in a CREATE SCHEMA query by removing the fields for the role specification and the role type. They were not used, and for the role specification this could be confusing as the schema name may by extracted from that at the beginning of CreateSchemaCommand(). This issue exists for a long time, so backpatch down to all the versions supported. Reported-by: Song Hongyu Author: Michael Paquier Reviewed-by: Richard Guo Discussion: https://postgr.es/m/17909-f65c12dfc5f0451d@postgresql.org Backpatch-through: 11
1 parent ce96625 commit 63f7e91

File tree

7 files changed

+193
-22
lines changed

7 files changed

+193
-22
lines changed

‎src/backend/commands/schemacmds.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -179,7 +179,8 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString,
179179
* we cannot, in general, run parse analysis on one statement until we
180180
* have actually executed the prior ones.
181181
*/
182-
parsetree_list = transformCreateSchemaStmt(stmt);
182+
parsetree_list = transformCreateSchemaStmtElements(stmt->schemaElts,
183+
schemaName);
183184

184185
/*
185186
* Execute each command contained in the CREATE SCHEMA. Since the grammar

‎src/backend/parser/parse_utilcmd.c

Lines changed: 19 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -98,12 +98,10 @@ typedef struct
9898
bool ofType; /* true if statement contains OF typename */
9999
} CreateStmtContext;
100100

101-
/* State shared by transformCreateSchemaStmt and its subroutines */
101+
/* State shared by transformCreateSchemaStmtElements and its subroutines */
102102
typedef struct
103103
{
104-
const char *stmtType; /* "CREATE SCHEMA" or "ALTER SCHEMA" */
105-
char *schemaname; /* name of schema */
106-
RoleSpec *authrole; /* owner of schema */
104+
const char *schemaname; /* name of schema */
107105
List *sequences; /* CREATE SEQUENCE items */
108106
List *tables; /* CREATE TABLE items */
109107
List *views; /* CREATE VIEW items */
@@ -137,7 +135,7 @@ static void transformCheckConstraints(CreateStmtContext *cxt,
137135
static void transformConstraintAttrs(CreateStmtContext *cxt,
138136
List *constraintList);
139137
static void transformColumnType(CreateStmtContext *cxt, ColumnDef *column);
140-
static void setSchemaName(char *context_schema, char **stmt_schema_name);
138+
static void setSchemaName(const char *context_schema, char **stmt_schema_name);
141139
static void transformPartitionCmd(CreateStmtContext *cxt, PartitionCmd *cmd);
142140
static List *transformPartitionRangeBounds(ParseState *pstate, List *blist,
143141
Relation parent);
@@ -3678,14 +3676,18 @@ transformColumnType(CreateStmtContext *cxt, ColumnDef *column)
36783676

36793677

36803678
/*
3681-
* transformCreateSchemaStmt -
3682-
* analyzes the CREATE SCHEMA statement
3679+
* transformCreateSchemaStmtElements -
3680+
* analyzes the elements of a CREATE SCHEMA statement
36833681
*
3684-
* Split the schema element list into individual commands and place
3685-
* them in the result list in an order such that there are no forward
3686-
* references (e.g. GRANT to a table created later in the list). Note
3687-
* that the logic we use for determining forward references is
3688-
* presently quite incomplete.
3682+
* Split the schema element list from a CREATE SCHEMA statement into
3683+
* individual commands and place them in the result list in an order
3684+
* such that there are no forward references (e.g. GRANT to a table
3685+
* created later in the list). Note that the logic we use for determining
3686+
* forward references is presently quite incomplete.
3687+
*
3688+
* "schemaName" is the name of the schema that will be used for the creation
3689+
* of the objects listed, that may be compiled from the schema name defined
3690+
* in the statement or a role specification.
36893691
*
36903692
* SQL also allows constraints to make forward references, so thumb through
36913693
* the table columns and move forward references to a posterior alter-table
@@ -3701,15 +3703,13 @@ transformColumnType(CreateStmtContext *cxt, ColumnDef *column)
37013703
* extent.
37023704
*/
37033705
List *
3704-
transformCreateSchemaStmt(CreateSchemaStmt *stmt)
3706+
transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
37053707
{
37063708
CreateSchemaStmtContext cxt;
37073709
List *result;
37083710
ListCell *elements;
37093711

3710-
cxt.stmtType = "CREATE SCHEMA";
3711-
cxt.schemaname = stmt->schemaname;
3712-
cxt.authrole = (RoleSpec *) stmt->authrole;
3712+
cxt.schemaname = schemaName;
37133713
cxt.sequences = NIL;
37143714
cxt.tables = NIL;
37153715
cxt.views = NIL;
@@ -3721,7 +3721,7 @@ transformCreateSchemaStmt(CreateSchemaStmt *stmt)
37213721
* Run through each schema element in the schema element list. Separate
37223722
* statements by type, and do preliminary analysis.
37233723
*/
3724-
foreach(elements, stmt->schemaElts)
3724+
foreach(elements, schemaElts)
37253725
{
37263726
Node *element = lfirst(elements);
37273727

@@ -3806,10 +3806,10 @@ transformCreateSchemaStmt(CreateSchemaStmt *stmt)
38063806
* Set or check schema name in an element of a CREATE SCHEMA command
38073807
*/
38083808
static void
3809-
setSchemaName(char *context_schema, char **stmt_schema_name)
3809+
setSchemaName(const char *context_schema, char **stmt_schema_name)
38103810
{
38113811
if (*stmt_schema_name == NULL)
3812-
*stmt_schema_name = context_schema;
3812+
*stmt_schema_name = unconstify(char *, context_schema);
38133813
else if (strcmp(context_schema, *stmt_schema_name) != 0)
38143814
ereport(ERROR,
38153815
(errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),

‎src/include/parser/parse_utilcmd.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,8 @@ extern IndexStmt *transformIndexStmt(Oid relid, IndexStmt *stmt,
2424
const char *queryString);
2525
extern void transformRuleStmt(RuleStmt *stmt, const char *queryString,
2626
List **actions, Node **whereClause);
27-
extern List *transformCreateSchemaStmt(CreateSchemaStmt *stmt);
27+
extern List *transformCreateSchemaStmtElements(List *schemaElts,
28+
const char *schemaName);
2829
extern PartitionBoundSpec *transformPartitionBound(ParseState *pstate, Relation parent,
2930
PartitionBoundSpec *spec);
3031
extern List *expandTableLikeClause(RangeVar *heapRel,

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

Lines changed: 98 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,98 @@
1+
--
2+
-- CREATE_SCHEMA
3+
--
4+
-- Schema creation with elements.
5+
CREATE ROLE regress_create_schema_role SUPERUSER;
6+
-- Cases where schema creation fails as objects are qualified with a schema
7+
-- that does not match with what's expected.
8+
-- This checks all the object types that include schema qualifications.
9+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
10+
CREATE SEQUENCE schema_not_existing.seq;
11+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role)
12+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
13+
CREATE TABLE schema_not_existing.tab (id int);
14+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role)
15+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
16+
CREATE VIEW schema_not_existing.view AS SELECT 1;
17+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role)
18+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
19+
CREATE INDEX ON schema_not_existing.tab (id);
20+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role)
21+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
22+
CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab
23+
EXECUTE FUNCTION schema_trig.no_func();
24+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role)
25+
-- Again, with a role specification and no schema names.
26+
SET ROLE regress_create_schema_role;
27+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
28+
CREATE SEQUENCE schema_not_existing.seq;
29+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role)
30+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
31+
CREATE TABLE schema_not_existing.tab (id int);
32+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role)
33+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
34+
CREATE VIEW schema_not_existing.view AS SELECT 1;
35+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role)
36+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
37+
CREATE INDEX ON schema_not_existing.tab (id);
38+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role)
39+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
40+
CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab
41+
EXECUTE FUNCTION schema_trig.no_func();
42+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role)
43+
-- Again, with a schema name and a role specification.
44+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
45+
CREATE SEQUENCE schema_not_existing.seq;
46+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
47+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
48+
CREATE TABLE schema_not_existing.tab (id int);
49+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
50+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
51+
CREATE VIEW schema_not_existing.view AS SELECT 1;
52+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
53+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
54+
CREATE INDEX ON schema_not_existing.tab (id);
55+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
56+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
57+
CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab
58+
EXECUTE FUNCTION schema_trig.no_func();
59+
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
60+
RESET ROLE;
61+
-- Cases where the schema creation succeeds.
62+
-- The schema created matches the role name.
63+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
64+
CREATE TABLE regress_create_schema_role.tab (id int);
65+
\d regress_create_schema_role.tab
66+
Table "regress_create_schema_role.tab"
67+
Column | Type | Collation | Nullable | Default
68+
--------+---------+-----------+----------+---------
69+
id | integer | | |
70+
71+
DROP SCHEMA regress_create_schema_role CASCADE;
72+
NOTICE: drop cascades to table regress_create_schema_role.tab
73+
-- Again, with a different role specification and no schema names.
74+
SET ROLE regress_create_schema_role;
75+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
76+
CREATE TABLE regress_create_schema_role.tab (id int);
77+
\d regress_create_schema_role.tab
78+
Table "regress_create_schema_role.tab"
79+
Column | Type | Collation | Nullable | Default
80+
--------+---------+-----------+----------+---------
81+
id | integer | | |
82+
83+
DROP SCHEMA regress_create_schema_role CASCADE;
84+
NOTICE: drop cascades to table tab
85+
-- Again, with a schema name and a role specification.
86+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
87+
CREATE TABLE regress_schema_1.tab (id int);
88+
\d regress_schema_1.tab
89+
Table "regress_schema_1.tab"
90+
Column | Type | Collation | Nullable | Default
91+
--------+---------+-----------+----------+---------
92+
id | integer | | |
93+
94+
DROP SCHEMA regress_schema_1 CASCADE;
95+
NOTICE: drop cascades to table regress_schema_1.tab
96+
RESET ROLE;
97+
-- Clean up
98+
DROP ROLE regress_create_schema_role;

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,7 @@ test: copy copyselect copydml insert insert_conflict
4848
# ----------
4949
# More groups of parallel tests
5050
# ----------
51-
test: create_misc create_operator create_procedure
51+
test: create_misc create_operator create_procedure create_schema
5252
# These depend on create_misc and create_operator
5353
test: create_index create_index_spgist create_view index_including index_including_gist
5454

‎src/test/regress/serial_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,7 @@ test: insert_conflict
6161
test: create_misc
6262
test: create_operator
6363
test: create_procedure
64+
test: create_schema
6465
test: create_index
6566
test: create_index_spgist
6667
test: create_view

‎src/test/regress/sql/create_schema.sql

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,70 @@
1+
--
2+
-- CREATE_SCHEMA
3+
--
4+
5+
-- Schema creation with elements.
6+
7+
CREATE ROLE regress_create_schema_role SUPERUSER;
8+
9+
-- Cases where schema creation fails as objects are qualified with a schema
10+
-- that does not match with what's expected.
11+
-- This checks all the object types that include schema qualifications.
12+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
13+
CREATE SEQUENCE schema_not_existing.seq;
14+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
15+
CREATE TABLE schema_not_existing.tab (id int);
16+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
17+
CREATE VIEW schema_not_existing.view AS SELECT 1;
18+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
19+
CREATE INDEX ON schema_not_existing.tab (id);
20+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
21+
CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab
22+
EXECUTE FUNCTION schema_trig.no_func();
23+
-- Again, with a role specification and no schema names.
24+
SET ROLE regress_create_schema_role;
25+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
26+
CREATE SEQUENCE schema_not_existing.seq;
27+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
28+
CREATE TABLE schema_not_existing.tab (id int);
29+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
30+
CREATE VIEW schema_not_existing.view AS SELECT 1;
31+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
32+
CREATE INDEX ON schema_not_existing.tab (id);
33+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
34+
CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab
35+
EXECUTE FUNCTION schema_trig.no_func();
36+
-- Again, with a schema name and a role specification.
37+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
38+
CREATE SEQUENCE schema_not_existing.seq;
39+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
40+
CREATE TABLE schema_not_existing.tab (id int);
41+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
42+
CREATE VIEW schema_not_existing.view AS SELECT 1;
43+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
44+
CREATE INDEX ON schema_not_existing.tab (id);
45+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
46+
CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab
47+
EXECUTE FUNCTION schema_trig.no_func();
48+
RESET ROLE;
49+
50+
-- Cases where the schema creation succeeds.
51+
-- The schema created matches the role name.
52+
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
53+
CREATE TABLE regress_create_schema_role.tab (id int);
54+
\d regress_create_schema_role.tab
55+
DROP SCHEMA regress_create_schema_role CASCADE;
56+
-- Again, with a different role specification and no schema names.
57+
SET ROLE regress_create_schema_role;
58+
CREATE SCHEMA AUTHORIZATION CURRENT_USER
59+
CREATE TABLE regress_create_schema_role.tab (id int);
60+
\d regress_create_schema_role.tab
61+
DROP SCHEMA regress_create_schema_role CASCADE;
62+
-- Again, with a schema name and a role specification.
63+
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_USER
64+
CREATE TABLE regress_schema_1.tab (id int);
65+
\d regress_schema_1.tab
66+
DROP SCHEMA regress_schema_1 CASCADE;
67+
RESET ROLE;
68+
69+
-- Clean up
70+
DROP ROLE regress_create_schema_role;

0 commit comments

Comments
 (0)