Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite9d4001

Browse files
committed
Add tests of the CREATEROLE attribute
The current regression tests do not contain much testing of CREATEROLE.This patch, extracted from a larger patch set to modify how thatfeature works, remedies that omission.Author: Mark DilgerDiscussion:https://postgr.es/m/D9065DFB-56DB-4E89-A73E-DB8CC2C746C6@enterprisedb.com
1 parent6aa5186 commite9d4001

File tree

3 files changed

+284
-1
lines changed

3 files changed

+284
-1
lines changed
Lines changed: 145 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,145 @@
1+
-- ok, superuser can create users with any set of privileges
2+
CREATE ROLE regress_role_super SUPERUSER;
3+
CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS;
4+
-- fail, only superusers can create users with these privileges
5+
SET SESSION AUTHORIZATION regress_role_admin;
6+
CREATE ROLE regress_nosuch_superuser SUPERUSER;
7+
ERROR: must be superuser to create superusers
8+
CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS;
9+
ERROR: must be superuser to create replication users
10+
CREATE ROLE regress_nosuch_replication REPLICATION;
11+
ERROR: must be superuser to create replication users
12+
CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
13+
ERROR: must be superuser to create bypassrls users
14+
-- ok, having CREATEROLE is enough to create users with these privileges
15+
CREATE ROLE regress_createdb CREATEDB;
16+
CREATE ROLE regress_createrole CREATEROLE;
17+
CREATE ROLE regress_login LOGIN;
18+
CREATE ROLE regress_inherit INHERIT;
19+
CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
20+
CREATE ROLE regress_encrypted_password ENCRYPTED PASSWORD 'foo';
21+
CREATE ROLE regress_password_null PASSWORD NULL;
22+
-- ok, backwards compatible noise words should be ignored
23+
CREATE ROLE regress_noiseword SYSID 12345;
24+
NOTICE: SYSID can no longer be specified
25+
-- fail, cannot grant membership in superuser role
26+
CREATE ROLE regress_nosuch_super IN ROLE regress_role_super;
27+
ERROR: must be superuser to alter superusers
28+
-- fail, database owner cannot have members
29+
CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner;
30+
ERROR: role "pg_database_owner" cannot have explicit members
31+
-- ok, can grant other users into a role
32+
CREATE ROLE regress_inroles ROLE
33+
regress_role_super, regress_createdb, regress_createrole, regress_login,
34+
regress_inherit, regress_connection_limit, regress_encrypted_password, regress_password_null;
35+
-- fail, cannot grant a role into itself
36+
CREATE ROLE regress_nosuch_recursive ROLE regress_nosuch_recursive;
37+
ERROR: role "regress_nosuch_recursive" is a member of role "regress_nosuch_recursive"
38+
-- ok, can grant other users into a role with admin option
39+
CREATE ROLE regress_adminroles ADMIN
40+
regress_role_super, regress_createdb, regress_createrole, regress_login,
41+
regress_inherit, regress_connection_limit, regress_encrypted_password, regress_password_null;
42+
-- fail, cannot grant a role into itself with admin option
43+
CREATE ROLE regress_nosuch_admin_recursive ADMIN regress_nosuch_admin_recursive;
44+
ERROR: role "regress_nosuch_admin_recursive" is a member of role "regress_nosuch_admin_recursive"
45+
-- fail, regress_createrole does not have CREATEDB privilege
46+
SET SESSION AUTHORIZATION regress_createrole;
47+
CREATE DATABASE regress_nosuch_db;
48+
ERROR: permission denied to create database
49+
-- ok, regress_createrole can create new roles
50+
CREATE ROLE regress_plainrole;
51+
-- ok, roles with CREATEROLE can create new roles with it
52+
CREATE ROLE regress_rolecreator CREATEROLE;
53+
-- ok, roles with CREATEROLE can create new roles with privilege they lack
54+
CREATE ROLE regress_tenant CREATEDB CREATEROLE LOGIN INHERIT CONNECTION LIMIT 5;
55+
-- ok, regress_tenant can create objects within the database
56+
SET SESSION AUTHORIZATION regress_tenant;
57+
CREATE TABLE tenant_table (i integer);
58+
CREATE INDEX tenant_idx ON tenant_table(i);
59+
CREATE VIEW tenant_view AS SELECT * FROM pg_catalog.pg_class;
60+
REVOKE ALL PRIVILEGES ON tenant_table FROM PUBLIC;
61+
-- fail, these objects belonging to regress_tenant
62+
SET SESSION AUTHORIZATION regress_createrole;
63+
DROP INDEX tenant_idx;
64+
ERROR: must be owner of index tenant_idx
65+
ALTER TABLE tenant_table ADD COLUMN t text;
66+
ERROR: must be owner of table tenant_table
67+
DROP TABLE tenant_table;
68+
ERROR: must be owner of table tenant_table
69+
ALTER VIEW tenant_view OWNER TO regress_role_admin;
70+
ERROR: must be owner of view tenant_view
71+
DROP VIEW tenant_view;
72+
ERROR: must be owner of view tenant_view
73+
-- fail, cannot take ownership of these objects from regress_tenant
74+
REASSIGN OWNED BY regress_tenant TO regress_createrole;
75+
ERROR: permission denied to reassign objects
76+
-- ok, having CREATEROLE is enough to create roles in privileged roles
77+
CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
78+
CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
79+
CREATE ROLE regress_monitor IN ROLE pg_monitor;
80+
CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings;
81+
CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats;
82+
CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables;
83+
CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files;
84+
CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files;
85+
CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program;
86+
CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend;
87+
-- fail, creation of these roles failed above so they do not now exist
88+
SET SESSION AUTHORIZATION regress_role_admin;
89+
DROP ROLE regress_nosuch_superuser;
90+
ERROR: role "regress_nosuch_superuser" does not exist
91+
DROP ROLE regress_nosuch_replication_bypassrls;
92+
ERROR: role "regress_nosuch_replication_bypassrls" does not exist
93+
DROP ROLE regress_nosuch_replication;
94+
ERROR: role "regress_nosuch_replication" does not exist
95+
DROP ROLE regress_nosuch_bypassrls;
96+
ERROR: role "regress_nosuch_bypassrls" does not exist
97+
DROP ROLE regress_nosuch_super;
98+
ERROR: role "regress_nosuch_super" does not exist
99+
DROP ROLE regress_nosuch_dbowner;
100+
ERROR: role "regress_nosuch_dbowner" does not exist
101+
DROP ROLE regress_nosuch_recursive;
102+
ERROR: role "regress_nosuch_recursive" does not exist
103+
DROP ROLE regress_nosuch_admin_recursive;
104+
ERROR: role "regress_nosuch_admin_recursive" does not exist
105+
DROP ROLE regress_plainrole;
106+
-- ok, should be able to drop non-superuser roles we created
107+
DROP ROLE regress_createdb;
108+
DROP ROLE regress_createrole;
109+
DROP ROLE regress_login;
110+
DROP ROLE regress_inherit;
111+
DROP ROLE regress_connection_limit;
112+
DROP ROLE regress_encrypted_password;
113+
DROP ROLE regress_password_null;
114+
DROP ROLE regress_noiseword;
115+
DROP ROLE regress_inroles;
116+
DROP ROLE regress_adminroles;
117+
DROP ROLE regress_rolecreator;
118+
DROP ROLE regress_read_all_data;
119+
DROP ROLE regress_write_all_data;
120+
DROP ROLE regress_monitor;
121+
DROP ROLE regress_read_all_settings;
122+
DROP ROLE regress_read_all_stats;
123+
DROP ROLE regress_stat_scan_tables;
124+
DROP ROLE regress_read_server_files;
125+
DROP ROLE regress_write_server_files;
126+
DROP ROLE regress_execute_server_program;
127+
DROP ROLE regress_signal_backend;
128+
-- fail, role still owns database objects
129+
DROP ROLE regress_tenant;
130+
ERROR: role "regress_tenant" cannot be dropped because some objects depend on it
131+
DETAIL: owner of table tenant_table
132+
owner of view tenant_view
133+
-- fail, cannot drop ourself nor superusers
134+
DROP ROLE regress_role_super;
135+
ERROR: must be superuser to drop superusers
136+
DROP ROLE regress_role_admin;
137+
ERROR: current user cannot be dropped
138+
-- ok
139+
RESET SESSION AUTHORIZATION;
140+
DROP INDEX tenant_idx;
141+
DROP TABLE tenant_table;
142+
DROP VIEW tenant_view;
143+
DROP ROLE regress_tenant;
144+
DROP ROLE regress_role_admin;
145+
DROP ROLE regress_role_super;

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -89,7 +89,7 @@ test: brin_bloom brin_multi
8989
# ----------
9090
# Another group of parallel tests
9191
# ----------
92-
test: create_table_like alter_generic alter_operator misc async dbsize misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort
92+
test: create_table_like alter_generic alter_operator misc async dbsize misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort create_role
9393

9494
# rules cannot run concurrently with any test that creates
9595
# a view or rule in the public schema

‎src/test/regress/sql/create_role.sql

Lines changed: 138 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,138 @@
1+
-- ok, superuser can create users with any set of privileges
2+
CREATE ROLE regress_role_super SUPERUSER;
3+
CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS;
4+
5+
-- fail, only superusers can create users with these privileges
6+
SET SESSION AUTHORIZATION regress_role_admin;
7+
CREATE ROLE regress_nosuch_superuser SUPERUSER;
8+
CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS;
9+
CREATE ROLE regress_nosuch_replication REPLICATION;
10+
CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
11+
12+
-- ok, having CREATEROLE is enough to create users with these privileges
13+
CREATE ROLE regress_createdb CREATEDB;
14+
CREATE ROLE regress_createrole CREATEROLE;
15+
CREATE ROLE regress_login LOGIN;
16+
CREATE ROLE regress_inherit INHERIT;
17+
CREATE ROLE regress_connection_limit CONNECTIONLIMIT5;
18+
CREATE ROLE regress_encrypted_password ENCRYPTED PASSWORD'foo';
19+
CREATE ROLE regress_password_null PASSWORDNULL;
20+
21+
-- ok, backwards compatible noise words should be ignored
22+
CREATE ROLE regress_noiseword SYSID12345;
23+
24+
-- fail, cannot grant membership in superuser role
25+
CREATE ROLE regress_nosuch_superIN ROLE regress_role_super;
26+
27+
-- fail, database owner cannot have members
28+
CREATE ROLE regress_nosuch_dbownerIN ROLE pg_database_owner;
29+
30+
-- ok, can grant other users into a role
31+
CREATE ROLE regress_inroles ROLE
32+
regress_role_super, regress_createdb, regress_createrole, regress_login,
33+
regress_inherit, regress_connection_limit, regress_encrypted_password, regress_password_null;
34+
35+
-- fail, cannot grant a role into itself
36+
CREATE ROLE regress_nosuch_recursive ROLE regress_nosuch_recursive;
37+
38+
-- ok, can grant other users into a role with admin option
39+
CREATE ROLE regress_adminroles ADMIN
40+
regress_role_super, regress_createdb, regress_createrole, regress_login,
41+
regress_inherit, regress_connection_limit, regress_encrypted_password, regress_password_null;
42+
43+
-- fail, cannot grant a role into itself with admin option
44+
CREATE ROLE regress_nosuch_admin_recursive ADMIN regress_nosuch_admin_recursive;
45+
46+
-- fail, regress_createrole does not have CREATEDB privilege
47+
SET SESSION AUTHORIZATION regress_createrole;
48+
CREATEDATABASEregress_nosuch_db;
49+
50+
-- ok, regress_createrole can create new roles
51+
CREATE ROLE regress_plainrole;
52+
53+
-- ok, roles with CREATEROLE can create new roles with it
54+
CREATE ROLE regress_rolecreator CREATEROLE;
55+
56+
-- ok, roles with CREATEROLE can create new roles with privilege they lack
57+
CREATE ROLE regress_tenant CREATEDB CREATEROLE LOGIN INHERIT CONNECTIONLIMIT5;
58+
59+
-- ok, regress_tenant can create objects within the database
60+
SET SESSION AUTHORIZATION regress_tenant;
61+
CREATETABLEtenant_table (iinteger);
62+
CREATEINDEXtenant_idxON tenant_table(i);
63+
CREATEVIEWtenant_viewASSELECT*FROMpg_catalog.pg_class;
64+
REVOKE ALL PRIVILEGESON tenant_tableFROM PUBLIC;
65+
66+
-- fail, these objects belonging to regress_tenant
67+
SET SESSION AUTHORIZATION regress_createrole;
68+
DROPINDEX tenant_idx;
69+
ALTERTABLE tenant_table ADD COLUMN ttext;
70+
DROPTABLE tenant_table;
71+
ALTERVIEW tenant_view OWNER TO regress_role_admin;
72+
DROPVIEW tenant_view;
73+
74+
-- fail, cannot take ownership of these objects from regress_tenant
75+
REASSIGN OWNED BY regress_tenant TO regress_createrole;
76+
77+
-- ok, having CREATEROLE is enough to create roles in privileged roles
78+
CREATE ROLE regress_read_all_dataIN ROLE pg_read_all_data;
79+
CREATE ROLE regress_write_all_dataIN ROLE pg_write_all_data;
80+
CREATE ROLE regress_monitorIN ROLE pg_monitor;
81+
CREATE ROLE regress_read_all_settingsIN ROLE pg_read_all_settings;
82+
CREATE ROLE regress_read_all_statsIN ROLE pg_read_all_stats;
83+
CREATE ROLE regress_stat_scan_tablesIN ROLE pg_stat_scan_tables;
84+
CREATE ROLE regress_read_server_filesIN ROLE pg_read_server_files;
85+
CREATE ROLE regress_write_server_filesIN ROLE pg_write_server_files;
86+
CREATE ROLE regress_execute_server_programIN ROLE pg_execute_server_program;
87+
CREATE ROLE regress_signal_backendIN ROLE pg_signal_backend;
88+
89+
-- fail, creation of these roles failed above so they do not now exist
90+
SET SESSION AUTHORIZATION regress_role_admin;
91+
DROP ROLE regress_nosuch_superuser;
92+
DROP ROLE regress_nosuch_replication_bypassrls;
93+
DROP ROLE regress_nosuch_replication;
94+
DROP ROLE regress_nosuch_bypassrls;
95+
DROP ROLE regress_nosuch_super;
96+
DROP ROLE regress_nosuch_dbowner;
97+
DROP ROLE regress_nosuch_recursive;
98+
DROP ROLE regress_nosuch_admin_recursive;
99+
DROP ROLE regress_plainrole;
100+
101+
-- ok, should be able to drop non-superuser roles we created
102+
DROP ROLE regress_createdb;
103+
DROP ROLE regress_createrole;
104+
DROP ROLE regress_login;
105+
DROP ROLE regress_inherit;
106+
DROP ROLE regress_connection_limit;
107+
DROP ROLE regress_encrypted_password;
108+
DROP ROLE regress_password_null;
109+
DROP ROLE regress_noiseword;
110+
DROP ROLE regress_inroles;
111+
DROP ROLE regress_adminroles;
112+
DROP ROLE regress_rolecreator;
113+
DROP ROLE regress_read_all_data;
114+
DROP ROLE regress_write_all_data;
115+
DROP ROLE regress_monitor;
116+
DROP ROLE regress_read_all_settings;
117+
DROP ROLE regress_read_all_stats;
118+
DROP ROLE regress_stat_scan_tables;
119+
DROP ROLE regress_read_server_files;
120+
DROP ROLE regress_write_server_files;
121+
DROP ROLE regress_execute_server_program;
122+
DROP ROLE regress_signal_backend;
123+
124+
-- fail, role still owns database objects
125+
DROP ROLE regress_tenant;
126+
127+
-- fail, cannot drop ourself nor superusers
128+
DROP ROLE regress_role_super;
129+
DROP ROLE regress_role_admin;
130+
131+
-- ok
132+
RESET SESSION AUTHORIZATION;
133+
DROPINDEX tenant_idx;
134+
DROPTABLE tenant_table;
135+
DROPVIEW tenant_view;
136+
DROP ROLE regress_tenant;
137+
DROP ROLE regress_role_admin;
138+
DROP ROLE regress_role_super;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp