Re: CREATEROLE and role ownership hierarchies

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

Yes, the patch restricts CREATEROLE privilege from granting any privilege they themselves lack. There is a regression test in the patch set which demonstrates this. See src/test/regress/expected/create_role.out. The diffs from v6-0004-Restrict-power-granted-via-CREATEROLE.patch are 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