# By: Riasat Ullah # This file contains all database queries that are related to an organization. from dbqueries import db_users from exceptions.user_exceptions import InvalidPassword, InvalidRequest, NotUniqueValue from modules import credentials from psycopg2 import errorcodes from utils import constants, errors, helpers, key_manager, permissions, roles, var_names from validations import organization_validator, string_validator import configuration as configs import datetime import json import psycopg2 def create_organization(conn, timestamp, organization_name, address, city, state, zip_code, country, sector, subdomain, billing_currency, owner_title, owner_email, owner_password, owner_first_name, owner_last_name, owner_iso_code, owner_phone, owner_preferred_username, owner_timezone, owner_language, owner_profile_picture, host_region, onb_flow_id): ''' Creates an organization including its admin and the first member (the admin). The admin is also registered as a verified user. :param conn: db connection :param timestamp: timestamp when the organization is being created :param organization_name: name of the organization :param address: address of the organization :param city: city the organization is located in :param state: state the organization is located in :param zip_code: zip code of the organization :param country: ISO country code of the organization's country :param sector: sector the organization is in :param subdomain: the organization's subdomain :param billing_currency: 3 letter currency :param owner_title: the job title of the admin :param owner_email: email of the admin :param owner_password: admin's user password :param owner_first_name: admin's first name :param owner_last_name: admin's last name :param owner_iso_code: 2 letter ISO code of the user's phone :param owner_phone: admin's phone number including country code (without +) :param owner_preferred_username: the preferred name of the admin :param owner_timezone: the timezone the admin is in :param owner_language: the language the admin's account should be set up in :param owner_profile_picture: url that can be used to retrieve the owner's profile picture :param host_region: data center region this organization's data will be hosted on :param onb_flow_id: unique ID that identifies the onboarding flow of the admin :errors: AssertionError, SqlInjection, InvalidUsername, InvalidPassword, NotUniqueValue, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) organization_validator.validate_organization_info(organization_name, address, city, state, zip_code, country, sector, owner_timezone, owner_language) assert billing_currency in configs.allowed_currencies assert host_region in configs.allowed_host_regions # Whether subdomain is unique or not gets tested in the create_organization function. # This is why we are not checking it here again. If the subdomain uniqueness check # is removed from the function, then it must be checked here. subdomain = subdomain.lower() string_validator.is_valid_subdomain(subdomain) if not string_validator.is_valid_password(owner_password): raise InvalidPassword(errors.err_password_invalid) if not string_validator.is_valid_preferred_username(owner_preferred_username): raise InvalidRequest(errors.err_preferred_username_invalid) owner_phone_code = helpers.get_phone_code(owner_iso_code) owner_email = owner_email.lower() assert db_users.email_is_unique(conn, owner_email, timestamp) organization_validator.validate_user_info(owner_first_name, owner_last_name, owner_iso_code, owner_phone_code, owner_phone, owner_timezone, owner_language) assert string_validator.is_web_url(owner_profile_picture) trial_end = (timestamp + datetime.timedelta(days=configs.trial_period)).date() trial_subscription_id = configs.digital_operations_subscription_id full_domain = helpers.construct_organization_full_domain(subdomain) org_perm = permissions.add_trial_add_ons(permissions.get_subscription_permission(trial_subscription_id)) owner_role_id = roles.owner_role_id user_perm = permissions.get_user_permission(org_perm, owner_role_id) owner_taskcall_email = helpers.construct_taskcall_email_address(owner_preferred_username, subdomain) hash_password, salt = credentials.convert_text_to_hash(owner_password) account_id = key_manager.generate_account_number() routine_key = key_manager.generate_reference_key() policy_key = key_manager.generate_reference_key() query = ''' select create_organization( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = ( timestamp, constants.end_timestamp, account_id, billing_currency, trial_subscription_id, trial_end, org_perm, organization_name, sector, address, city, state, zip_code, country, subdomain, full_domain, constants.email_accept, owner_email, owner_role_id, owner_title, owner_first_name, owner_last_name, owner_iso_code, owner_phone_code, owner_phone, owner_preferred_username, owner_taskcall_email, salt, hash_password, user_perm, routine_key, owner_timezone, owner_language, owner_profile_picture, policy_key, configs.standard_wait_minutes, constants.root_destination, host_region, onb_flow_id, ) try: conn.execute(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise NotUniqueValue(errors.err_subdomain_unavailable) else: raise except psycopg2.DatabaseError: raise def edit_organization_info(conn, timestamp, organization_id, organization_name, sector, address, city, state, zip_code, country, timezone, language): ''' Edits the basic information of an organization. :param conn: db connection :param timestamp: timestamp when the request is being made :param organization_id: organization id :param organization_name: name of the organization :param sector: sector the organization is in :param address: address of the organization :param city: city the organization is located in :param state: state the organization is located in :param zip_code: zip code of the organization :param country: ISO country code the organization is located in :param timezone: timezone the organization is in :param language: primary language of the organization :errors: AssertionError, DatabaseError, ValueError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) organization_validator.validate_organization_info(organization_name, address, city, state, zip_code, country, sector, timezone, language) query = ''' do $body$ begin update organizations set end_timestamp = %(timestamp)s where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s; insert into organizations ( select %(org_id)s, %(timestamp)s, %(end_time)s, account_id, %(org_name)s, %(sector)s, %(address)s, %(city)s, %(state)s, %(zip_code)s, %(iso_code)s, subdomain, billing_currency, %(tz)s, %(lang)s, host_region from organizations where start_timestamp <= %(timestamp)s and end_timestamp = %(timestamp)s and organization_id = %(org_id)s ); end; $body$ ''' query_params = {'org_id': organization_id, 'timestamp': timestamp, 'end_time': constants.end_timestamp, 'org_name': organization_name, 'sector': sector, 'address': address, 'city': city, 'state': state, 'zip_code': zip_code, 'iso_code': country, 'tz': timezone, 'lang': language} try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def update_billing_currency(conn, timestamp, organization_id, billing_currency): ''' Edits the basic information of an organization. :param conn: db connection :param timestamp: timestamp when the request is being made :param organization_id: organization id :param billing_currency: 3 letter currency :errors: AssertionError, DatabaseError, ValueError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert billing_currency in configs.allowed_currencies query = ''' do $body$ begin update organizations set end_timestamp = %(timestamp)s where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s; insert into organizations ( select organization_id, %(timestamp)s, %(end_time)s, account_id, organization_name, organization_sector, address, city, state, zip_code, iso_country_code, subdomain, %(bill_curr)s, organization_timezone, organization_language, host_region from organizations where start_timestamp <= %(timestamp)s and end_timestamp = %(timestamp)s and organization_id = %(org_id)s ); end; $body$ ''' query_params = {'org_id': organization_id, 'timestamp': timestamp, 'end_time': constants.end_timestamp, 'bill_curr': billing_currency} try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def create_new_member_request(conn, organization_id, org_perm, start_timestamp, end_timestamp, request_details): ''' Creates a new member request and the verification code that will be associated with the new member's email till the account is verified. Multiple new members can be added at the same time. :param conn: db connection :param organization_id: organization id of the organization who the new member is being added to :param org_perm: the permissions of the organization :param start_timestamp: timestamp when the request is being made :param end_timestamp: timestamp when the request should expire :param request_details: (list of dict) of new member details :errors: AssertionError, DatabaseError, NotUniqueValue ''' assert isinstance(organization_id, int) assert isinstance(start_timestamp, datetime.datetime) assert isinstance(end_timestamp, datetime.datetime) assert isinstance(request_details, list) query_params = [] for item in request_details: new_member_email = item[var_names.email].lower() new_member_role = item[var_names.user_role] new_member_title = item[var_names.job_title] code_hash, salt = item[var_names.verification_code] if not db_users.email_is_unique(conn, new_member_email, start_timestamp): raise NotUniqueValue(errors.err_requested_user_account_exists) assert string_validator.is_email_address(new_member_email) role_id = roles.get_role_id_from_name(new_member_role) if not permissions.is_user_role_allowed(org_perm, role_id): raise InvalidRequest(errors.err_user_role_invalid) # Do not allow a user to register as Owner. if role_id == roles.owner_role_id: raise InvalidRequest(errors.err_requested_user_owner_role) query_params.append((organization_id, start_timestamp, end_timestamp, new_member_email, role_id, new_member_title, constants.new_account_verification, salt, code_hash,)) query = "select create_new_member_request(%s, %s, %s, %s, %s::smallint, %s, %s, %s, %s);" try: conn.execute_batch(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise NotUniqueValue(errors.err_requested_user_account_exists) else: raise except psycopg2.DatabaseError: raise def resend_new_member_verification_code(conn, organization_id, start_timestamp, end_timestamp, new_member_email, new_salt, new_hash): ''' Saves the new verification code of an existing new member request. :param conn: db connection :param organization_id: organization id of the organization who the new member is being added to :param start_timestamp: timestamp when the request is being made :param end_timestamp: timestamp when the request should expire :param new_member_email: (str) email address of the new member :param new_salt: (str) the new salt for the verification code :param new_hash: (str) the new hash for the verification code :errors: AssertionError, DatabaseError, NotUniqueValue ''' assert isinstance(organization_id, int) assert isinstance(start_timestamp, datetime.datetime) assert isinstance(end_timestamp, datetime.datetime) assert string_validator.is_email_address(new_member_email) query = "select resend_new_member_verification_code(%s, %s, %s, %s, %s, %s, %s);" query_params = (organization_id, start_timestamp, end_timestamp, new_member_email, constants.new_account_verification, new_salt, new_hash,) try: conn.execute(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise LookupError(errors.err_unknown_resource) else: raise except psycopg2.DatabaseError: raise def cancel_new_member_request(conn, timestamp, organization_id, new_member_email): ''' Cancels a member addition request. :param conn: db connection :param timestamp: timestamp when the request is being made :param organization_id: organization id :param new_member_email: user_id of the member :errors: AssertionError, DatabaseError, LookupError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert string_validator.is_email_address(new_member_email) query = ''' do $body$ begin if new_member_request_exists(%(timestamp)s, %(org_id)s, %(email)s) then update organization_member_requests set end_timestamp = %(timestamp)s where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s and requested_email = %(email)s; update verification_codes set end_timestamp = %(timestamp)s where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and email = %(email)s and verification_type = %(verification_type)s; else raise check_violation; end if; end; $body$ ''' query_params = {'timestamp': timestamp, 'org_id': organization_id, 'email': new_member_email, 'verification_type': constants.new_account_verification} try: conn.execute(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise LookupError(errors.err_unknown_resource) else: raise except psycopg2.DatabaseError: raise def get_requested_user_account_info(conn, timestamp, requested_email): ''' Gets the role ID and the job title of a requested member account. :param conn: db connection :param timestamp: timestamp this request is being made on :param requested_email: email of the requested member :return: (tuple) -> (org_id, org_perm, role ID, role_name, job title) :errors: AssertionError, DatabaseError, LookupError ''' assert isinstance(timestamp, datetime.datetime) assert string_validator.is_email_address(requested_email) requested_email = requested_email.lower() query = ''' select mr.organization_id, opr.permissions, requested_role_id, urm.role_name, requested_job_title from organization_member_requests as mr join organization_permissions as opr using(organization_id) join user_role_map as urm on mr.requested_role_id = urm.role_id where mr.requested_email = %(req_email)s and mr.start_timestamp <= %(timestamp)s and mr.end_timestamp > %(timestamp)s and opr.start_timestamp <= %(timestamp)s and opr.end_timestamp > %(timestamp)s; ''' query_params = {'timestamp': timestamp, 'req_email': requested_email} try: result = conn.fetch(query, query_params) if len(result) == 1: return result[0] else: raise LookupError(errors.err_requested_user_not_found) except psycopg2.DatabaseError: raise def get_new_requested_accounts(conn, timestamp, organization_id): ''' Gets the list of new member accounts that have been requested for a given organization. :param conn: db connection :param timestamp: timestamp when the request is being made :param organization_id: organization id :return: (list of tuple) -> [ (email_1, role, job title), ...] :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' select requested_email, requested_role_id, requested_job_title from organization_member_requests where start_timestamp <= %s and end_timestamp > %s and organization_id = %s; ''' query_params = (timestamp, timestamp, organization_id,) try: result = conn.fetch(query, query_params) requested_accounts = [] for email_, role_id, job_title in result: role_name = roles.user_role_maps[role_id] requested_accounts.append((email_, role_name, job_title)) return requested_accounts except psycopg2.DatabaseError: raise def get_organization_account_auto_provision_info(conn, timestamp, subdomain): ''' Gets the organization ID, permissions and default role ID to set up a new auto provisioned account with. :param conn: db connection :param timestamp: timestamp this request is being made on :param subdomain: subdomain of the organization :return: (tuple) -> (org_id, org_perm, role ID) :errors: AssertionError, DatabaseError, LookupError ''' assert isinstance(timestamp, datetime.datetime) assert string_validator.is_valid_subdomain(subdomain) query = ''' select org.organization_id, opr.permissions, oss.default_role_id from organizations as org join organization_permissions as opr using(organization_id) join organization_sso as oss using(organization_id) where org.subdomain = %(sub_dom)s and org.start_timestamp <= %(timestamp)s and org.end_timestamp > %(timestamp)s and opr.start_timestamp <= %(timestamp)s and opr.end_timestamp > %(timestamp)s and oss.start_timestamp <= %(timestamp)s and oss.end_timestamp > %(timestamp)s and oss.allow_auto_provision = true and oss.default_role_id is not null; ''' query_params = {'timestamp': timestamp, 'sub_dom': subdomain} try: result = conn.fetch(query, query_params) if len(result) == 1: return result[0] else: raise LookupError(errors.err_unknown_resource) except psycopg2.DatabaseError: raise def get_organization_details(conn, timestamp, with_organization_id): ''' Gets the details of an organization. :param conn: db connection :param timestamp: timestamp when the request is being made :param with_organization_id: organization id :return: (dict) containing the details ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(with_organization_id, int) query = ''' select account_id, organization_name, organization_sector, address, city, state, zip_code, iso_country_code, cc.country, subdomain, billing_currency, organization_timezone, organization_language from organizations join country_codes as cc using (iso_country_code) where start_timestamp <= %s and end_timestamp > %s and organization_id = %s; ''' query_params = (timestamp, timestamp, with_organization_id,) try: result = conn.fetch(query, query_params) if len(result) == 1: for acc_id, name_, sector, address, city, state, zip_code, iso_code, country, subdomain,\ bill_curr, tz, lang in result: data = {var_names.account_id: acc_id, var_names.organization_name: name_, var_names.sector: sector, var_names.address: address, var_names.city: city, var_names.state: state, var_names.zip_code: zip_code, var_names.country: iso_code, var_names.country_name: country, var_names.subdomain: subdomain, var_names.billing_currency: bill_curr, var_names.timezone: tz, var_names.language: lang} return data elif len(result) == 0: raise LookupError(errors.err_unknown_resource) else: raise SystemError(errors.err_internal_multiple_entries_found + ' Organization ID ' + str(with_organization_id)) except psycopg2.DatabaseError: raise def get_organization_subdomain(conn, timestamp, organization_id): ''' Gets the subdomain that is associated with an organization. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: organization id :return: (str) subdomain :errors: AssertionError, DatabaseError, ValueError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' select subdomain from organizations where start_timestamp <= %s and end_timestamp > %s and organization_id = %s; ''' query_params = (timestamp, timestamp, organization_id,) try: result = conn.fetch(query, query_params) if len(result) == 1: return result[0][0] elif len(result) == 0: raise ValueError(errors.err_unknown_resource) else: raise SystemError(errors.err_internal_multiple_entries_found + ' Organization ID - ' + str(organization_id)) except psycopg2.DatabaseError: raise def update_subdomain(conn, timestamp, organization_id, subdomain, user_values, service_values): ''' Updates the subdomain of an organization and concurrently updates the taskcall email address of all the members of the organization.This should be used at the time the subdomain is being bought. :param conn: db connection :param timestamp: timestamp when the request is made :param organization_id: organization id :param subdomain: the subdomain :param user_values: (list of dict) -> [ {user_id: , policy_id: , taskcall_email: }, ... ] :param service_values: (list of dict) -> [ {service_id: , integration_id: , integration_email: , policy_id: }, ... ] :errors: AssertionError, DatabaseError, ValueError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert string_validator.is_valid_subdomain(subdomain) current_subdomain = get_organization_subdomain(conn, timestamp, organization_id) if current_subdomain != subdomain: subdomain = subdomain.lower() current_full_domain = helpers.construct_organization_full_domain(current_subdomain) new_full_domain = helpers.construct_organization_full_domain(subdomain) try: query = ''' select update_subdomain( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (timestamp, constants.end_timestamp, organization_id, subdomain, current_full_domain, new_full_domain, constants.email_accept, constants.root_destination, json.dumps(user_values), json.dumps(service_values),) conn.execute(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise NotUniqueValue(errors.err_subdomain_unavailable) else: raise except psycopg2.DatabaseError: raise def subdomain_is_available(conn, timestamp, subdomain): ''' Checks if a subdomain is available at a given time or not. :param conn: db connection :param timestamp: timestamp to check on :param subdomain: subdomain to check for :return: (boolean) True if it is; False otherwise ''' assert isinstance(timestamp, datetime.datetime) assert string_validator.is_valid_subdomain(subdomain) subdomain = subdomain.lower() query = " select is_subdomain_available(%s, %s); " query_params = (timestamp, subdomain,) try: result = conn.fetch(query, query_params) return result[0][0] except psycopg2.DatabaseError: raise def get_organization_permissions(conn, timestamp, organization_ids=None): ''' Get the permissions of a organizations at a given point in time. :param conn: db connection :param timestamp: timestamp when the request was made :param organization_ids: (int or list) of organization IDs :return: (dict) -> {org id: perm, ...} ''' assert isinstance(timestamp, datetime.datetime) query_params = {'timestamp': timestamp} cond = '' if organization_ids is not None: cond = " and organization_id = any(%(org_id)s) " query_params['org_id'] = helpers.get_int_list(organization_ids) query = ''' select organization_id, permissions from organization_permissions where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s {0} '''.format(cond) try: result = conn.fetch(query, query_params) data = dict() for org_, perm_ in result: data[org_] = perm_ return data except psycopg2.DatabaseError: raise def org_trial_status_and_user_counts(conn, timestamp, organization_id): ''' Checks if an organization has a limit on the number of users it can have given its subscription. :param conn: db connection :param timestamp: timestamp this request is being made on :param organization_id: ID of the organization :return: (tuple) (is trial, max user limit, standard user count) :errors: AssertionError, DatabaseError, LookupError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' with t1 as ( select organization_id, count(role_id) as standard_user_count from organization_members where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s and role_id in ( select role_id from user_role_map where is_stakeholder is null or is_stakeholder = false ) group by organization_id ) select subscription_id, is_trial, standard_user_count from organization_subscriptions join t1 using(organization_id) where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id} try: result = conn.fetch(query, query_params) if len(result) == 0: raise LookupError(errors.err_unknown_resource) sub_id, is_trial, standard_user_count = result[0] max_limit = None if sub_id == configs.free_subscription_id or is_trial: max_limit = configs.free_subscription_max_users return is_trial, max_limit, standard_user_count except psycopg2.DatabaseError: raise def get_email_assignees(conn, timestamp, emails=None, organization_id=None, service_id=None): ''' Get allowed email assignees and their details. :param conn: db connection :param timestamp: timestamp this request is being made on :param emails: (str or list of str) email address(es) to filter by :param organization_id: organization ID to filter by :param service_id: service ID to filter by :return: (dict) -> {email: [org ID, pol ID, serv ID]} ''' assert isinstance(timestamp, datetime.datetime) query_params = {'timestamp': timestamp} conditions = [] if emails is not None: conditions.append(" taskcall_email = any(%(emails)s) ") query_params['emails'] = helpers.get_string_list(emails, check_for_email=True) if organization_id is not None: assert isinstance(organization_id, int) conditions.append(" organization_id = %(org_id)s ") query_params['org_id'] = organization_id if service_id is not None: assert isinstance(service_id, int) conditions.append(" for_serviceid is not null and for_serviceid = %(serv_id)s ") query_params['serv_id'] = service_id query = ''' select taskcall_email, organization_id, for_policyid, for_serviceid from email_virtual where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s {0}; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) data = dict() for mail_, org_id_, pol_id_, serv_id_ in result: data[mail_] = [org_id_, pol_id_, serv_id_] return data except psycopg2.DatabaseError: raise def get_cacheable_keys_of_organization(conn, timestamp, organization_id): ''' Get all the identifying keys of items of an organization that could have been cached. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :return: (tuple of lists) -> ( api_keys, tc emails, integration keys, policy IDs, service IDs, instance IDs ) :errors: AssertionError, DatabaseError, LookupError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' with t1 as ( select organization_id, array_agg(api_key) as org_api_keys from api_keys where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s group by organization_id ) , t2 as ( select organization_id, array_agg(taskcall_email) as org_tc_emails from email_virtual where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s group by organization_id ) , t3 as ( select organization_id, array_agg(integration_key) as org_integrations from service_integrations where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s group by organization_id ) , t4 as( select organization_id, array_agg(policyid) as org_policies from policies where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s group by organization_id ) , t5 as( select organization_id, array_agg(serviceid) as org_services from services where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s group by organization_id ) , t6 as( select organization_id, array_agg(instanceid) as org_instances from task_instances where organization_id = %(org_id)s and is_open = true group by organization_id ) select org.organization_id, t1.org_api_keys, t2.org_tc_emails, t3.org_integrations, t4.org_policies, t5.org_services, t6.org_instances from organizations as org left join t1 using(organization_id) left join t2 using(organization_id) left join t3 using(organization_id) left join t4 using(organization_id) left join t5 using(organization_id) left join t6 using(organization_id) where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and org.organization_id = %(org_id)s; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id} try: result = conn.fetch(query, query_params) if len(result) == 0: raise LookupError(errors.err_unknown_resource) else: for org_id, api_keys, tc_emails, integration_keys, policy_ids, service_ids, instance_ids in result: return api_keys, tc_emails, integration_keys, policy_ids, service_ids, instance_ids except psycopg2.DatabaseError: raise def blacklist_event(conn, timestamp, organization_id, reason, instance_id): ''' Store a potentially malicious or ill-intended events caused by an organization. :param conn: db connection :param timestamp: timestamp of when the event happened :param organization_id: ID of the organization :param reason: the reason why this was detected as a threat :param instance_id: ID of the instance that triggered the blacklisting :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(reason, str) assert isinstance(instance_id, int) query = "insert into organization_blacklisted_events values (%s, %s, %s, %s);" query_params = (organization_id, timestamp, reason, instance_id,) try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def get_blacklisted_events(conn, min_timestamp, organization_id=None): ''' Get blacklisted events. :param conn: db connection :param min_timestamp: the minimum timestamp when to look for :param organization_id: (int) ID of the organization to filter by :return: (dict of list) -> {org ID: [{timestamp: .., reason: ...}, ...], ...} :errors: AssertionError, DatabaseError ''' assert isinstance(min_timestamp, datetime.datetime) query_params = {'min_tmsp': min_timestamp} condition = '' if organization_id is not None: assert isinstance(organization_id, int) condition = ' and organization_id = %(org_id)s ' query_params['org_id'] = organization_id query = ''' select organization_id, utc_timestamp, reason, instanceid from organization_blacklisted_events where to_ignore is not true and utc_timestamp >= %(min_tmsp)s {0}; '''.format(condition) try: result = conn.fetch(query, query_params) data = dict() for org_id, tmsp, reason, inst_id in result: if org_id not in data: data[org_id] = [] data[org_id].append({var_names.timestamp: tmsp, var_names.reason: reason, var_names.instance_id: inst_id}) return data except psycopg2.DatabaseError: raise def set_blacklisted_events_to_ignore(conn, organization_id): ''' Set the blacklisted events of an organization to be ignored. :param conn: db connection :param organization_id: ID of the organization :errors: AssertionError, DatabaseError ''' assert isinstance(organization_id, int) query = ''' begin; update organization_blacklisted_events set to_ignore = true where organization_id = %s and to_ignore is null; end; ''' query_params = (organization_id,) try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def close_organization(conn, timestamp, org_id): ''' Close an organization account. :param conn: db connection :param timestamp: timestamp when this request is being made :param org_id: the organization ID :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(org_id, int) query = " select close_organization(%s, %s); " query_params = (org_id, timestamp,) try: conn.execute(query, query_params) except psycopg2.DatabaseError: conn.conn.rollback() raise def get_organization_sales_channel_details(conn, timestamp, organization_id): ''' Get the details of a sales channel that an organization is connected to. :param conn: db connection :param timestamp: timestamp when this requets is being made :param organization_id: ID of the organization :return: (dict) of details ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' select channel_id, support_email from organization_sales_channel as org_sc join sales_channels as sc using(channel_id) where sc.start_date <= %(timestamp)s and sc.end_date > %(timestamp)s and org_sc.start_timestamp <= %(timestamp)s and org_sc.end_timestamp > %(timestamp)s and org_sc.organization_id = %(org_id)s; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id} try: result = conn.fetch(query, query_params) data = dict() for ch_id, sup_email in result: data = { var_names.channel_id: ch_id, var_names.email: sup_email } return data except psycopg2.DatabaseError: raise def get_organization_sso_settings(conn, timestamp, subdomain=None, organization_id=None): ''' Gets the SSO details of an organization. :param conn: db connection :param timestamp: timestamp when the request is being made :param subdomain: the subdomain of the organization at TaskCall :param organization_id: ID of the organization :return: (dict) of SSO details | None if no SSO settings are present ''' assert isinstance(timestamp, datetime.datetime) query_params = {'timestamp': timestamp} conditions = [] if subdomain is not None: string_validator.is_valid_subdomain(subdomain) conditions.append(' org.subdomain = %(sub_dom)s ') query_params['sub_dom'] = subdomain if organization_id is not None: assert isinstance(organization_id, int) conditions.append(' org.organization_id = %(org_id)s ') query_params['org_id'] = organization_id query = ''' select org.organization_id, intt.integration_type, allow_direct_login, allow_auto_provision, default_role_id, saml_certificate, saml_key, login_url, logout_url, metadata_url, entity_id, vendor_id, vendor_subdomain, org_sso.additional_info from organizations as org join organization_sso as org_sso using(organization_id) join integration_types as intt using(integration_type_id) where org.start_timestamp <= %(timestamp)s and org.end_timestamp > %(timestamp)s and org_sso.start_timestamp <= %(timestamp)s and org_sso.end_timestamp > %(timestamp)s and intt.start_date <= %(timestamp)s and intt.end_date > %(timestamp)s {0}; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) if len(result) > 0: for org_id, intt_type, dir_log, auto_prov, role_id, saml_cert, saml_key, in_url, out_url, meta_url,\ ent_id, vnd_id, vnd_sub, add_info in result: data = { var_names.organization_id: org_id, var_names.integration_type: intt_type, var_names.direct_login: dir_log, var_names.auto_provision: auto_prov, var_names.user_role: roles.user_role_maps[role_id] if role_id is not None else None, var_names.saml_certificate: saml_cert, var_names.saml_key: saml_key, var_names.login_url: in_url, var_names.logout_url: out_url, var_names.metadata_url: meta_url, var_names.entity_id: ent_id, var_names.vendor_id: vnd_id, var_names.vendor_subdomain: vnd_sub, var_names.additional_info: add_info } return data else: return None except psycopg2.DatabaseError: raise def update_organization_sso_settings( conn, timestamp, organization_id, integration_type, direct_login=False, auto_provision=False, role_id=None, saml_certificate=None, saml_key=None, login_url=None, logout_url=None, metadata_url=None, entity_id=None, vendor_id=None, vendor_subdomain=None, additional_info=None ): ''' Update the SSO settings of an organization. :param conn: db connection :param timestamp: timestamp when the request is being made :param organization_id: ID of the organization :param integration_type: the type of integration :param direct_login: (boolean) whether direct login with TaskCall will be allowed or not :param auto_provision: (boolean) whether users can be auto registered upon first SSO sign in :param role_id: default role id that will be associated to the new auto provisioned user :param saml_certificate: SAML certificate :param saml_key: SAML key :param login_url: login url to redirect to :param logout_url: logout url to redirect to :param metadata_url: SAML metadata url :param entity_id: SAML entity ID :param vendor_id: ID of the SSO vendor :param vendor_subdomain: the subdomain of the organization with the SSO vendor :param additional_info: any additional information that might be relevant :return: ''' assert isinstance(timestamp, datetime.datetime) organization_validator.validate_organization_sso_settings( organization_id, integration_type, direct_login, auto_provision, role_id, saml_certificate, saml_key, login_url, logout_url, metadata_url, entity_id, vendor_id, vendor_subdomain, additional_info ) if additional_info is not None: additional_info = json.dumps(additional_info) query = ''' select update_organization_sso_settings( %s, %s, %s, %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (organization_id, timestamp, constants.end_timestamp, integration_type, direct_login, auto_provision, role_id, saml_certificate, saml_key, login_url, logout_url, metadata_url, entity_id, vendor_id, vendor_subdomain, additional_info,) try: conn.execute(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise InvalidRequest(errors.err_invalid_request) except psycopg2.DatabaseError: raise def delete_organization_sso_settings(conn, timestamp, organization_id): ''' Delete current organization SSO settings. :param conn: db connection :param timestamp: timestamp when the request is being made :param organization_id: ID of the organization :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' update organization_sso set end_timestamp = %(timestamp)s where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id} try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise