Re: CREATEROLE and role ownership hierarchies

> On Jan 24, 2022, at 10:55 PM, Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
>
> +1
>
> One of "mischiefs" I'm thinking problematic is that users with CREATEROLE can give any predefined role that they
don'thave, to other users including themselves. For example, users with CREATEROLE can give pg_execute_server_program
tothemselves and run any OS commands by COPY PROGRAM. This would be an issue when providing something like PostgreSQL
cloudservice that wants to prevent end users from running OS commands but allow them to create/drop roles. Does the
proposedpatch fix also this issue? 

Yes, the patch restricts CREATEROLE privilege from granting any privilege they themselves lack.  There is a regression
testin the patch set which demonstrates this.  See src/test/regress/expected/create_role.out.  The diffs from
v6-0004-Restrict-power-granted-via-CREATEROLE.patchare quoted here for ease of viewing: 

--- ok, having CREATEROLE is enough to create roles in privileged roles
+-- fail, having CREATEROLE is not enough to create roles in privileged roles
 CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
+ERROR:  must have admin option on role "pg_read_all_data"
 CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
+ERROR:  must have admin option on role "pg_write_all_data"
 CREATE ROLE regress_monitor IN ROLE pg_monitor;
+ERROR:  must have admin option on role "pg_monitor"
 CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings;
+ERROR:  must have admin option on role "pg_read_all_settings"
 CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats;
+ERROR:  must have admin option on role "pg_read_all_stats"
 CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables;
+ERROR:  must have admin option on role "pg_stat_scan_tables"
 CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files;
+ERROR:  must have admin option on role "pg_read_server_files"
 CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files;
+ERROR:  must have admin option on role "pg_write_server_files"
 CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program;
+ERROR:  must have admin option on role "pg_execute_server_program"
 CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend;
+ERROR:  must have admin option on role "pg_signal_backend"

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company