Re: CREATEROLE and role ownership hierarchies - Mailing list pgsql-hackers

> On Oct 27, 2021, at 7:32 PM, Shinya Kato <Shinya11.Kato@oss.nttdata.com> wrote:
>
> I was able to add the membership of a role with a different owner.
> In brief, "a" was able to change the membership of owner "shinya".
> Is this the correct behavior?

I believe it is required for backwards compatibility.  In a green field, we might consider doing things differently.

The only intentional backward compatibility break in this patch set is the the behavior of CREATEROLE.  The general
hopeis that such a compatibility break will help far more than it hurts, as CREATEROLE does not appear to be a well
adoptedfeature.  I would expect that breaking the behavior of the WITH ADMIN OPTION feature would cause a lot more
pain.


Trying your example on both the unpatched and the patched sources, things appear to work as they should:


UNPATCHED
------------------
mark.dilger=# CREATE ROLE a LOGIN;
CREATE ROLE
mark.dilger=# GRANT pg_execute_server_program TO a WITH ADMIN OPTION;
GRANT ROLE
mark.dilger=# CREATE ROLE b;
CREATE ROLE
mark.dilger=# \du+ a
                           List of roles
 Role name | Attributes |          Member of          | Description
-----------+------------+-----------------------------+-------------
 a         |            | {pg_execute_server_program} |

mark.dilger=# \du+ b
                   List of roles
 Role name |  Attributes  | Member of | Description
-----------+--------------+-----------+-------------
 b         | Cannot login | {}        |

mark.dilger=# \c - a
You are now connected to database "mark.dilger" as user "a".
mark.dilger=> GRANT pg_execute_server_program TO b;
GRANT ROLE
mark.dilger=> \du+ b
                            List of roles
 Role name |  Attributes  |          Member of          | Description
-----------+--------------+-----------------------------+-------------
 b         | Cannot login | {pg_execute_server_program} |

mark.dilger=> \du+ "mark.dilger"
                                           List of roles
  Role name  |                         Attributes                         | Member of | Description
-------------+------------------------------------------------------------+-----------+-------------
 mark.dilger | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |


PATCHED:
---------------
mark.dilger=# CREATE ROLE a LOGIN;
CREATE ROLE
mark.dilger=# GRANT pg_execute_server_program TO a WITH ADMIN OPTION;
GRANT ROLE
mark.dilger=# CREATE ROLE b;
CREATE ROLE
mark.dilger=# \du+ a
                                  List of roles
 Role name |    Owner    | Attributes |          Member of          | Description
-----------+-------------+------------+-----------------------------+-------------
 a         | mark.dilger |            | {pg_execute_server_program} |

mark.dilger=# \du+ b
                          List of roles
 Role name |    Owner    |  Attributes  | Member of | Description
-----------+-------------+--------------+-----------+-------------
 b         | mark.dilger | Cannot login | {}        |

mark.dilger=# \c - a
You are now connected to database "mark.dilger" as user "a".
mark.dilger=> GRANT pg_execute_server_program TO b;
GRANT ROLE
mark.dilger=> \du+ b
                                   List of roles
 Role name |    Owner    |  Attributes  |          Member of          | Description
-----------+-------------+--------------+-----------------------------+-------------
 b         | mark.dilger | Cannot login | {pg_execute_server_program} |

mark.dilger=> \du+ "mark.dilger"
                                                  List of roles
  Role name  |    Owner    |                         Attributes                         | Member of | Description
-------------+-------------+------------------------------------------------------------+-----------+-------------
 mark.dilger | mark.dilger | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |



You should notice that the owner of role "b" is the superuser "mark.dilger", and that owner's attributes are unchanged.
But your point that role "a" can change the attributes of role "mark.dilger" is correct, as shown here: 

mark.dilger=> GRANT pg_execute_server_program TO "mark.dilger";
GRANT ROLE
mark.dilger=> \du+ "mark.dilger"
                                                           List of roles
  Role name  |    Owner    |                         Attributes                         |          Member of          |
Description 

-------------+-------------+------------------------------------------------------------+-----------------------------+-------------
 mark.dilger | mark.dilger | Superuser, Create role, Create DB, Replication, Bypass RLS | {pg_execute_server_program} |



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