A database role can have a number of attributes that define its privileges and interact with the client authentication system.One such attribute is the CREATEROLE attribute, which is important to PostgreSQL database management of users and roles. In this blog post, we will discuss the improvement to this attribute that has been done for PostgreSQL 16.
In previous releases of PostgreSQL, a superuser can grant a non-superuser the CREATEROLE attribute. This is important because it gives the ability to someone other than the superuser to add, drop, and modify users to the database. Unfortunately in previous PostgreSQL versions, when a user is granted the CREATEROLE attribute, it gets the ability to drop users as well that it did not create.
With PostgreSQL 16, the CREATEROLE attribute still allows a non-superuser the ability to provision new users, however they can only drop users that they themselves created. The system will generate error codes if they try to delete users which they did not create.
In PostgreSQL 15 and older versions
1 2 3 4 5 6 7 8 9 10 11 12 13 | postgres=#CREATE ROLE Alice CREATEROLE LOGIN; CREATE ROLE postgres=# CREATE role bob; CREATE ROLE postgres=# du bob List of roles Role name | Attributes | Member of -----------+--------------+----------- bob | Cannot login | {} postgres=# c postgres alice You are now connected to database "postgres" as user "alice". postgres=drop role bob; DROP ROLE |
In PostgreSQL 16
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | postgres=# CREATE ROLE Alice CREATEROLE LOGIN; CREATE ROLE postgres=# CREATE role bob; CREATE ROLE postgres=# du bob List of roles Role name | Attributes -----------+-------------- bob | Cannot login postgres=#c postgres alice You are now connected to database "postgres" as user "alice". postgres=#drop role bob; ERROR: permission denied to drop role DETAIL: Only roles with the CREATEROLE attribute and the ADMIN option on role "bob" may drop this role. |
In previous releases, a user with the CREATEROLE attribute has access to all predefined system roles, including highly privileged roles like pg_execute_server_program and pg_write_server_files. CREATEROLE also allows them to grant privileges to themself or other users that they haven’t been granted themself.
When providing these roles pg_read_server_files, pg_write_server_files, and pg_execute_server_program to users, extreme caution should be taken because they have access to every file on the server file system, bypass all database-level permission checks when accessing files directly and might be exploited to obtain superuser-level access
So it seems we can expose our systems to risk by granting a user the CREATEROLE privilege. Now the concern is how to deal with the problem mentioned above.
In PostgreSQL 16, users with the CREATEROLE attribute do not have the ability to grant membership of a role to anyone; they can only grant membership to roles for which they have the ADMIN OPTION. 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.
Let me try to explain this with the help of an example.
In PostgreSQL 15 and older versions:
1 2 3 4 5 6 7 8 9 10 11 12 | postgres=#conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432". postgres=# CREATE ROLE dev_admin CREATEROLE LOGIN; CREATE ROLE postgres=#c postgres dev_admin You are now connected to database "postgres" as user "dev_admin". postgres=> CREATE ROLE QA_Admin LOGIN; CREATE ROLE postgres=grant pg_execute_server_program,pg_write_server_files,pg_monitor, pg_read_all_settings,pg_read_all_stats to QA_Admin; GRANT ROLE postgres=ALTER ROLE QA_Admin with encrypted password '***'; ALTER ROLE |
As per the above example, dev_admin can login from QA_admin whenever he wants and get almost all the privileges to view internal configurations as well as reading and writing the whole database which is not expected at all. This poses a security threat to the whole organisation.
In PostgreSQL 16:
1 2 3 4 5 6 7 8 9 10 11 | postgres=# conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432". postgres=# CREATE ROLE dev_admin CREATEROLE LOGIN; CREATE ROLE postgres=# c postgres dev_admin You are now connected to database "postgres" as user "dev_admin". postgres= CREATE ROLE QA_Admin LOGIN; CREATE ROLE postgres=grant pg_execute_server_program,pg_monitor to QA_Admin; ERROR: permission denied to grant role "pg_execute_server_program" DETAIL: Only roles with the ADMIN option on role "pg_execute_server_program" may grant this role. |
From above it seems that, if pg_execute_server_program, pg_monitor role is assigned to dev_admin user by a super user then only it would be possible to grant that role to QA_admin like below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | postgres=# conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432". postgres=> c postgres postgres You are now connected to database "postgres" as user "postgres". postgres=# GRANT pg_monitor to dev_admin with ADMIN OPTION; GRANT ROLE postgres=# GRANT pg_execute_server_program to dev_admin with ADMIN OPTION; GRANT ROLE postgres=# c postgres dev_admin You are now connected to database "postgres" as user "dev_admin". postgres=grant pg_monitor to QA_Admin; GRANT ROLE postgres=grant pg_execute_server_program to QA_Admin; GRANT ROLE |
The change in CREATEROLE attribute in PostgreSQL 16 is an important improvement to user management because it allows some users the ability to manage all aspects of their team, but not beyond the rights and not outside their own area of responsibility. This gives them just the right amount of control without allowing them to overstep what is required to execute their job.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
