MAIA User Registration

Instructions to read the MAIA User Registration requests from the MySQL database, register the users in Keycloak, create a namespace in the Kubernetes cluster, and store the user information in Vault.

!pip install pymysql pandas python-keycloak kubernetes sqlalchemy itables

Login to the MySQL database

The MySQL database contains the information about the users, the groups they belong to, and the namespaces they requested to be registered in. This Database is directly connected to the Sign-Up form in the MAIA website .

from sqlalchemy import create_engine

DB_URL = ""
DB_PORT = ""
DB_USERNAME = ""
DB_PASSWORD = ""

engine = create_engine(f"mysql+pymysql://{DB_USERNAME}:{DB_PASSWORD}@{DB_URL}:{DB_PORT}/mysql")
cnx = engine.raw_connection()

import pandas as pd

pd.read_sql_query("SHOW TABLES", con=cnx)

Read the tables

auth_user = pd.read_sql_query("SELECT * FROM auth_user", con=cnx)
auth_group = pd.read_sql_query("SELECT * FROM auth_group", con=cnx)
auth_user_groups = pd.read_sql_query("SELECT * FROM auth_user_groups", con=cnx)
authentication_maiauser = pd.read_sql_query("SELECT * FROM authentication_maiauser", con=cnx)

from itables import init_notebook_mode

init_notebook_mode(all_interactive=True)

from itables import show

show(auth_user,buttons=["copyHtml5", "csvHtml5", "excelHtml5"],
     layout={"top1": "searchPanes"},
    searchPanes={"layout": "columns-3", "cascadePanes": True},)

from keycloak import KeycloakAdmin
from keycloak import KeycloakOpenIDConnection


keycloak_connection = KeycloakOpenIDConnection(

                        server_url="",
                        server_url="",
                        username='',
                        password='',
                        realm_name="",
                        client_id="",
                        client_secret_key="",
                        verify=False)


keycloak_admin = KeycloakAdmin(connection=keycloak_connection)

users_to_register = {}


for user in auth_user.iterrows():
    uid = user[1]['id']
    username = user[1]['username']
    email = user[1]['email']
    user_groups = []
    for keycloack_user in keycloak_admin.get_users():

        if 'email' in keycloack_user:
            if keycloack_user['email'] == email:
                user_keycloack_groups = keycloak_admin.get_user_groups(user_id=keycloack_user['id'])
                for user_keycloack_group in user_keycloack_groups:
                    user_groups.append(user_keycloack_group['name'][len("MAIA:"):])

    if uid in authentication_maiauser['user_ptr_id'].values:
        requested_namespaces = authentication_maiauser[authentication_maiauser['user_ptr_id'] == uid][
            'namespace'].values
        print(
            f"User {username} requested to be registered in MAIA in {authentication_maiauser[authentication_maiauser['user_ptr_id'] == uid]['namespace'].values[0]} namespace")
        print(authentication_maiauser[authentication_maiauser['user_ptr_id'] == uid])
        for requested_namespace in requested_namespaces:
            if requested_namespace not in user_groups:
                print(f"User {username} is not in the group {requested_namespace}")
                #print(user_groups)
                if email not in users_to_register:
                    users_to_register[email] = [requested_namespace]
                else:
                    users_to_register[email].append(requested_namespace)

Register the users in Keycloak

users = keycloak_admin.get_users()
keycloak_admin.get_realms()
usernames = [user['username'] for user in users]

groups = keycloak_admin.get_groups()

users_to_register

ids_to_register = {}
for user in users_to_register:
    if user not in usernames:
        print(f"{user} is not registered in Keycloack!")
    else:
        for uid in users:
            if uid['username'] == user:
                ids_to_register[uid['id']]={}
                ids_to_register[uid['id']]['username'] = uid['username']
                ids_to_register[uid['id']]['groups'] = []

ids_to_register

existing_groups_to_register = ["MAIA:users"]

for group in groups:
    if group["name"] in existing_groups_to_register:
        for id_to_register in ids_to_register:
            ids_to_register[id_to_register]['groups'].append(group["id"])

for id_to_register in ids_to_register:
    namespaces = users_to_register[ids_to_register[id_to_register]['username']]
    for namespace in namespaces:
        payload = {
        "name": f"MAIA:{namespace}",
        "path": f"/MAIA:{namespace}",
        "attributes":

        { }
        ,
        "realmRoles": [],
        "clientRoles":

        {}
        ,
        "subGroups": [],
        "access":

        { "view": True, "manage": True, "manageMembership": True }
        }
        try:
            group_id = keycloak_admin.create_group(payload)
            ids_to_register[id_to_register]['groups'].append(group_id)
        except:
            for group in groups:
                if group["name"] == f"MAIA:{namespace}":
                    ids_to_register[id_to_register]['groups'].append(group["id"])

ids_to_register

Add the users to the groups

groups = keycloak_admin.get_groups()

for uid in ids_to_register:
    for user in users:

        if user["id"] == uid:
            print(f"Registering user {user['username']} with id {uid}")
            for gid in ids_to_register[uid]['groups']:
                print(gid)
                for group in groups:
                    if group["id"] == gid:
                        print(f"Adding user {user['username']} to group {group['name']}")

for uid in ids_to_register:
    for gid in ids_to_register[uid]['groups']:
        keycloak_admin.group_user_add(uid, gid)