Creating a Superset user programmatically

Question

How to go about creating users programmatically in Superset with a defined role?

Say I have an email sign in page. User signs up. After sign up I would like to programmatically create the user with the gamma role into a Superset instance.

Answer 1

I've used a reply from superset git and created the following classes to add users with roles. You need to have a json file with the users info and the name of the role(s) that you want to apply. Like this:

[
    {
        "first_name": "a", 
        "last_name": "b", 
        "username": "abc", 
        "email": "[email protected]",
        "password": "123",
        "roles": ["generalViewer","databaseXView"]
    }, 
    {
        "first_name": "b", 
        "last_name": "c", 
        "username": "bcd", 
        "email": "[email protected]", 
        "password": "123",
        "roles": ["generalViewer","databaseXView"]
    }
]

Because we are using the role name (we can get them from the superset through normal navigation) is needed to setup the database credentials to get the role_id. Also need to setup the login of any user with permissions to create another one.

import requests
import json
from bs4 import BeautifulSoup as bs
from bs4 import Comment
from time import sleep
import psycopg2
from typing import List, Tuple
import pandas as pd

class SupersetApi:
    def __init__(self, username=None, password=None):
        self.s = requests.session()
        self.base_url = "http://123.45.67.890:8088/" #superset server ip
        self._csrf = self._getCSRF(self.url('login/'))
        self.headers = {'X-CSRFToken': self._csrf, 'Referer': self.url('login/')}
        # note: does not use headers because of flask_wtf.csrf.validate_csrf
        # if data is dict it is used as form and ends up empty but flask_wtf checks if data ...
        payload = {'username': username, 'password': password, 'csrf_token': self._csrf}
        self.post('login/', payload, None)
        #self.s.post(self.url(), data=payload, headers = {})

    def url(self, url_path):
        return self.base_url + url_path

    def get(self, url_path):
        return self.s.get(self.url(url_path), headers=self.headers)

    def post(self, url_path, data=None, json_data=None, **kwargs):
        kwargs.update({'url': self.url(url_path), 'headers': self.headers})
        if data:
            data['csrf_token'] = self._csrf
            kwargs['data'] = data
        if json_data:
            kwargs['json'] = json_data
        try:
            response = self.s.post(**kwargs)
            statusCode = response.status_code
            if statusCode < 399:
                return True, statusCode
            else:
                print('POST response: {}'.format(statusCode))
                return False, statusCode
        except Exception as e:
            print(e)
            return False, e

    def _getCSRF(self, url_path):
        try:
            response = self.s.get(url_path)
            response.raise_for_status()
        except Exception as e:
            print(e)
            exit()
        soup = bs(response.content, "html.parser")
        for tag in soup.find_all('input', id='csrf_token'):
            csrf_token = tag['value']
        return  csrf_token


class PostgresDB:
    def __init__(self, db_name: str, db_user: str, db_host: str, db_password: str) -> None:
        self.db_name = db_name
        self.db_user = db_user
        self.db_host = db_host
        self.db_password = db_password
        self.conn = None
        self.cur = None

    def connect(self) -> None:
        try: 
            self.conn = psycopg2.connect('dbname={} user={} host={} password={}'.format(
                self.db_name, self.db_user, self.db_host, self.db_password))
            self.cur = self.conn.cursor()
        except Exception as e:
            raise Exception('Couldn\'t connect to the database. Error: {}'.format(e))

    def commit(self) -> None:
        if self.conn is not None:
            self.conn.commit()
        else:
            raise Exception('Connection not opened to commit')

    def close_connection(self) -> None:
        if self.cur is not None or self.conn is not None:
            try:
                self.cur.close()
            except:
                pass
            try:
                self.conn.close()
            except:
                pass
        else:
            print('Connection and Cursor not opened to be closed')

    def get_from_ab_role(self, columns: List, filters: str) -> List:
        roles_table = 'public.ab_role'
        sql_columns = ','.join(columns)
        sql = "SELECT {} FROM {} WHERE {}".format(sql_columns, roles_table, filters)
        self.cur.execute(sql)
        return self.cur.fetchall()


class SupersetUser:
    def __init__(self, user_dict: dict, db: 'PostgresDB'):
        self.first_name = user_dict.get('first_name')
        self.last_name = user_dict.get('last_name')
        self.username = user_dict.get('username')
        self.email = user_dict.get('email')
        self.active = True
        self.password = user_dict.get('password')
        self.roles = self.get_role_id(db, user_dict.get('roles'))

    def get_role_id(self, db: 'PostgresDB', roles: List):
        filter = 'name = '
        filter = filter + "'{}' ".format(roles[0])
        if len(roles) > 1:
            for role in roles[1:]:
                filter = filter + "OR name = '{}' ".format(role)
        ids = []
        for id in db.get_from_ab_role(['id'], filter):
            ids.append(str(id[0]))
        return ids

    def create_user(self, superset_api: SupersetApi) -> Tuple:
        Error_friendly_message = None
        Error_not = True

        url_path = 'users/api/create'
        payload = {'first_name': self.first_name,
                    'last_name': self.last_name,
                    'username': self.username,
                    'email': self.email,
                    'active': self.active,
                    'conf_password': self.password,
                    'password': self.password,
                    'roles': self.roles
                    }

        Error_not, http_response_code = superset_api.post(url_path=url_path, json=payload)
        if Error_not:
            print('User {} created. Corresponding e-mail: {}'.format(self.username, self.email))
            return Error_not, Error_friendly_message, http_response_code
        elif http_response_code == 500:
            Error_not = False
            Error_friendly_message = ('Ops! Something went wrong. Probably already exist an ' 
                                    'user with the same e-mail: {}, or an error with the json variables... '
                                    'All of them must be strings or a list of strings'.format(self.email))
            return Error_not, Error_friendly_message, http_response_code
        else:
            Error_not = False
            Error_friendly_message = 'Ops! Something went wrong. Try again.'
            return Error_not, Error_friendly_message, http_response_code



#file that contains the users to be created
FILE_NAME = 'users.json'
#need credentials from user with admin role to create new user
ADMIN_USR = 'admin'
ADMIN_PSWD = 'adminpassword'
DB_NAME = 'superset_database'
DB_USER = 'superset_user'
DB_HOST = '123.45.67.890'
DB_PSWD = 'superset_password'

superset = SupersetApi(ADMIN_USR, ADMIN_PSWD)
portgre_db = PostgresDB(DB_NAME, DB_USER, DB_HOST, DB_PSWD)
portgre_db.connect()

try:
    with open(FILE_NAME, 'r') as f:
        users = json.load(f)
        print('File successfully read')
except FileNotFoundError as e:
    print(e)

for index, user in enumerate(users):
    userRoles = []
    superset_user = SupersetUser(user, portgre_db)
    Error_not, Error_friendly_message, http_response_code = superset_user.create_user(superset)
    if not Error_not:
        print('Could\'t create user {}.'.format(superset_user.username))
        print(Error_friendly_message)
        print('HTTP Response Code: {}'.format(http_response_code))

portgre_db.close_connection()

Answer 2

use this command for superset < 4

superset fab create-admin \
                --username admin \
                --firstname Superset \
                --lastname Admin \
                --email [email protected] \
                --password admin \
                || true