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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
-- ok, superuser can create users with any set of privileges
CREATE ROLE regress_role_super SUPERUSER;
CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS;
-- fail, only superusers can create users with these privileges
SET SESSION AUTHORIZATION regress_role_admin;
CREATE ROLE regress_nosuch_superuser SUPERUSER;
ERROR: must be superuser to create superusers
CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS;
ERROR: must be superuser to create replication users
CREATE ROLE regress_nosuch_replication REPLICATION;
ERROR: must be superuser to create replication users
CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
ERROR: must be superuser to create bypassrls users
-- ok, having CREATEROLE is enough to create users with these privileges
CREATE ROLE regress_createdb CREATEDB;
CREATE ROLE regress_createrole CREATEROLE;
CREATE ROLE regress_login LOGIN;
CREATE ROLE regress_inherit INHERIT;
CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
CREATE ROLE regress_encrypted_password ENCRYPTED PASSWORD 'foo';
CREATE ROLE regress_password_null PASSWORD NULL;
-- ok, backwards compatible noise words should be ignored
CREATE ROLE regress_noiseword SYSID 12345;
NOTICE: SYSID can no longer be specified
-- fail, cannot grant membership in superuser role
CREATE ROLE regress_nosuch_super IN ROLE regress_role_super;
ERROR: must be superuser to alter superusers
-- fail, database owner cannot have members
CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner;
ERROR: role "pg_database_owner" cannot have explicit members
-- ok, can grant other users into a role
CREATE ROLE regress_inroles ROLE
regress_role_super, regress_createdb, regress_createrole, regress_login,
regress_inherit, regress_connection_limit, regress_encrypted_password, regress_password_null;
-- fail, cannot grant a role into itself
CREATE ROLE regress_nosuch_recursive ROLE regress_nosuch_recursive;
ERROR: role "regress_nosuch_recursive" is a member of role "regress_nosuch_recursive"
-- ok, can grant other users into a role with admin option
CREATE ROLE regress_adminroles ADMIN
regress_role_super, regress_createdb, regress_createrole, regress_login,
regress_inherit, regress_connection_limit, regress_encrypted_password, regress_password_null;
-- fail, cannot grant a role into itself with admin option
CREATE ROLE regress_nosuch_admin_recursive ADMIN regress_nosuch_admin_recursive;
ERROR: role "regress_nosuch_admin_recursive" is a member of role "regress_nosuch_admin_recursive"
-- fail, regress_createrole does not have CREATEDB privilege
SET SESSION AUTHORIZATION regress_createrole;
CREATE DATABASE regress_nosuch_db;
ERROR: permission denied to create database
-- ok, regress_createrole can create new roles
CREATE ROLE regress_plainrole;
-- ok, roles with CREATEROLE can create new roles with it
CREATE ROLE regress_rolecreator CREATEROLE;
-- ok, roles with CREATEROLE can create new roles with privilege they lack
CREATE ROLE regress_tenant CREATEDB CREATEROLE LOGIN INHERIT CONNECTION LIMIT 5;
-- ok, regress_tenant can create objects within the database
SET SESSION AUTHORIZATION regress_tenant;
CREATE TABLE tenant_table (i integer);
CREATE INDEX tenant_idx ON tenant_table(i);
CREATE VIEW tenant_view AS SELECT * FROM pg_catalog.pg_class;
REVOKE ALL PRIVILEGES ON tenant_table FROM PUBLIC;
-- fail, these objects belonging to regress_tenant
SET SESSION AUTHORIZATION regress_createrole;
DROP INDEX tenant_idx;
ERROR: must be owner of index tenant_idx
ALTER TABLE tenant_table ADD COLUMN t text;
ERROR: must be owner of table tenant_table
DROP TABLE tenant_table;
ERROR: must be owner of table tenant_table
ALTER VIEW tenant_view OWNER TO regress_role_admin;
ERROR: must be owner of view tenant_view
DROP VIEW tenant_view;
ERROR: must be owner of view tenant_view
-- fail, cannot take ownership of these objects from regress_tenant
REASSIGN OWNED BY regress_tenant TO regress_createrole;
ERROR: permission denied to reassign objects
-- ok, having CREATEROLE is enough to create roles in privileged roles
CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
CREATE ROLE regress_monitor IN ROLE pg_monitor;
CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings;
CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats;
CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables;
CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files;
CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files;
CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program;
CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend;
-- fail, creation of these roles failed above so they do not now exist
SET SESSION AUTHORIZATION regress_role_admin;
DROP ROLE regress_nosuch_superuser;
ERROR: role "regress_nosuch_superuser" does not exist
DROP ROLE regress_nosuch_replication_bypassrls;
ERROR: role "regress_nosuch_replication_bypassrls" does not exist
DROP ROLE regress_nosuch_replication;
ERROR: role "regress_nosuch_replication" does not exist
DROP ROLE regress_nosuch_bypassrls;
ERROR: role "regress_nosuch_bypassrls" does not exist
DROP ROLE regress_nosuch_super;
ERROR: role "regress_nosuch_super" does not exist
DROP ROLE regress_nosuch_dbowner;
ERROR: role "regress_nosuch_dbowner" does not exist
DROP ROLE regress_nosuch_recursive;
ERROR: role "regress_nosuch_recursive" does not exist
DROP ROLE regress_nosuch_admin_recursive;
ERROR: role "regress_nosuch_admin_recursive" does not exist
DROP ROLE regress_plainrole;
-- ok, should be able to drop non-superuser roles we created
DROP ROLE regress_createdb;
DROP ROLE regress_createrole;
DROP ROLE regress_login;
DROP ROLE regress_inherit;
DROP ROLE regress_connection_limit;
DROP ROLE regress_encrypted_password;
DROP ROLE regress_password_null;
DROP ROLE regress_noiseword;
DROP ROLE regress_inroles;
DROP ROLE regress_adminroles;
DROP ROLE regress_rolecreator;
DROP ROLE regress_read_all_data;
DROP ROLE regress_write_all_data;
DROP ROLE regress_monitor;
DROP ROLE regress_read_all_settings;
DROP ROLE regress_read_all_stats;
DROP ROLE regress_stat_scan_tables;
DROP ROLE regress_read_server_files;
DROP ROLE regress_write_server_files;
DROP ROLE regress_execute_server_program;
DROP ROLE regress_signal_backend;
-- fail, role still owns database objects
DROP ROLE regress_tenant;
ERROR: role "regress_tenant" cannot be dropped because some objects depend on it
DETAIL: owner of table tenant_table
owner of view tenant_view
-- fail, cannot drop ourself nor superusers
DROP ROLE regress_role_super;
ERROR: must be superuser to drop superusers
DROP ROLE regress_role_admin;
ERROR: current user cannot be dropped
-- ok
RESET SESSION AUTHORIZATION;
DROP INDEX tenant_idx;
DROP TABLE tenant_table;
DROP VIEW tenant_view;
DROP ROLE regress_tenant;
DROP ROLE regress_role_admin;
DROP ROLE regress_role_super;
|
