> On Jan 24, 2022, at 10:55 PM, Fujii Masao <masao.fu...@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'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
