Role propagation error due to interdependent roles while adding new nodes

[alperkocatas]

On the cluster, we had a role set like the following:

select * from pg_roles;

rolname: read_only_role
rolsuper = f       
rolinherit = t          
rolcreaterole = f
rolcreatedb = f
rolcanlogin = t
rolreplication = f
rolbypassrls = f
rolconnlimit = -1 
rolvaliduntil = ''

SELECT
  parent.rolname  AS role,
  member.rolname  AS member,
  am.admin_option
FROM pg_auth_members am
JOIN pg_roles parent ON parent.oid = am.roleid
JOIN pg_roles member ON member.oid = am.member
ORDER BY role, member;

            role             |     member     | admin_option
-----------------------------+----------------+--------------
...
...
read_only_role              | role1     | t
read_only_role              | role2     | f
role2                       | role1     | t

On the Pg logs for the newly created node:

... ERROR: permission denied to grant priviledges as role "role1"
... DETAIL:  The grantor must have the ADMIN option on the role "read_only_role"
STATEMENT: SELECT woker_create_or_alter_role("role2", 'CREATE ROLE role2 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT -1 PASSWORD ''SCRAM SHA *** '); GRANT read_only_role to role2 WITH INHERIT TRUE GRANTED BY role1;

In the newly created node, citus creates the objects in the following order:

- CREATE ROLE read_only_role 
- CREATE ROLE role2
- GRANT read_only_role to role2 WITH INHERIT TRUE GRANTED BY role1;
      -  ... but at this time, since role1 does not have the ADMIN option, above query cannot succeed. 
      - The fix is to make sure to create the "read_only_role | role1 | t" entry before above

To sum up, citus creates roles that have interdependencies in an incorrect order, and this failure causes add node operation to fail. The new node cannot be registered until we login to the new node and execute:

CREATE ROLE read_only_role WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT -1
GRANT read_only_role TO  espdevadmin  WITH ADMIN OPTION;

so that node registration can succeed.