What is CREATEROLE Privilege?
1: A role with the CREATEROLE privilege has the ability to add, remove, and modify other roles.
2: It is unable to create or modify superuser. User will experience the subsequent error:
create user john superuser; ERROR: must be superuser to alter superusers
3: It does not allow the authority to grant or revoke the REPLICATION permission, create REPLICATION users, edit the role properties of such users, or modify the REPLICATION privilege. The error message is as follows:
create user john replication;
ERROR: must be superuser to create replication users
4: A user with the CREATEROLE privilege has access to all predefined system roles, including highly privileged roles like pg_execute_server_program and pg_write_server_files, and can grant or revoke membership even in roles to which it does not (yet) have any access.
The purpose of the pg_read_server_files, pg_write_server_files, and pg_execute_server_program roles is to give administrators access to trusted, non-superuser roles that can access files and run programs on the database server as the user the database is running as. When providing these roles to users, extreme caution should be taken because they have access to every file on the server file system, circumvent all database-level permission checks when accessing files directly, and might be exploited to obtain superuser-level access. One can find these privileges inside pg_roles table.
Select rolnames from pg_roles;
rolname
-----------------------------
postgres
pg_database_owner
pg_read_all_data
pg_write_all_data
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_read_server_files
pg_write_server_files
pg_execute_server_program
pg_signal_backend
pg_checkpoint
pg_maintain
pg_use_reserved_connections
pg_create_subscription
alice
bob
This suggests that we can expose our systems to risk by granting a user the CREATEROLE privilege. The key concern right now is how to deal with the problem mentioned in point number 4.
In PostgreSQL 16, users with the CREATEROLE permission no longer have the option to grant membership of a role to anyone; instead, like other users, they can only grant membership to roles for which they have the ADMIN OPTION. Robert Haas has developed this functionality.
Let’s understand what WITH ADMIN OPTION means.
The WITH ADMIN OPTION clause gives the user the authority to grant membership of a role to other users, to revoke membership of the role from other members of the role, and to comment on a role – but not to drop the role.
The WITH ADMIN OPTION permission does not give the user the right to grant or revoke WITH ADMIN OPTION for a role to another user.
Let me try and explain this with the help of an example that compares the behavior in the older version with the behavior in PostgreSQL 16.
PostgreSQL 15 and older versions
A superuser creates the role, Alice, and grants the new role to user BOB.
CREATE ROLE Alice CREATEROLE LOGIN;
Alice logins inside database, creates a new user bob, grants some important privileges(check pg_roles table) to bob user and then, alters bob password:
CREATE ROLE bob LOGIN;
grant pg_monitor, pg_read_all_settings, pg_read_all_stats,pg_stat_scan_tables... to BOB;
ALTER ROLE bob with encrypted password 'dev';
Now Alice can login from bob user whenever she wants and it has almost all the privileges to view internal configuration file as well reading and writing whole database. Alice can also alter those roles which are created by other users.
PostgreSQL 16
A superuser creates the role, Alice, with CREATEROLE privilege:
CREATE ROLE Alice CREATEROLE LOGIN;
Now ROLE Alice logins inside database, creates a new user bob and tries to grant some important privileges (check pg_roles table) to bob user but gets error.
CREATE ROLE bob LOGIN;
grant pg_monitor to BOB;
ERROR: permission denied to grant role "pg_monitor"
DETAIL: Only roles with the ADMIN option on role "pg_monitor" may grant this role.
This means if pg_monitor role is assigned to Alice by a super like this:
GRANT pg_monitor to alice with ADMIN OPTION;
Then Alice can grant this role to other users. Along with this, if Alice tries to change the password of another user (which is not created or managed by Alice), it will get the following error:
Alter user john with encrypted password 'dev';
ERROR: permission denied to alter role
DETAIL: To change another role's password, the current user must have the CREATEROLE attribute and
the ADMIN option on the role.
Conclusion
The CREATEROLE permission is crucial for managing users and roles. Database administrators can assign the job of generating new roles while still maintaining control over access control methods by assigning this privilege to specified users or roles. When granting the CREATEROLE privilege, care must be taken to make sure that only dependable people or roles are possess it. PostgreSQL administrators can create an effective and secure database environment that is tailored to their organization’s needs by having a thorough understanding of the CREATEROLE privilege. Make sure to read the mentioned links for better understanding.
