def create_role(self, role_name, role_password, update=False, role_options="NOCREATEDB NOCREATEROLE LOGIN"): """ create a role object (PostgreSQL secific) """ # TODO: is any other dialect using the "role" concept? if self.dialect != "postgres": return False # TODO: do this only for SQLAlchemy if not self.__conn: raise osdbError("connection not available") if update: sqlcmd = "ALTER USER {} WITH PASSWORD '{}' {}".format( role_name, role_password, role_options) else: sqlcmd = "CREATE ROLE {} WITH {} PASSWORD '{}'".format( role_name, role_options, role_password) logger.info(sqlcmd) try: result = self.__conn.execute(sqlcmd) if result: logger.info("role '{}' with options '{}' created". format(role_name, role_options)) except Exception as e: logger.exception(e) logger.error("creation of new role '%s' with options '%s' failed", role_name, role_options) return False return result
def do_drop(self, params=None): db_url = cfg.read_param("database_url", "Please provide us the URL of the database") if db_url is None: print() logger.error("no URL specified: aborting!") return -1 if params and len(params) > 0: db_name = params[0] else: db_name = cfg.read_param("database_name", "Please provide the database to drop", DEFAULT_DB_NAME) db = self.getdb(db_url, db_name) if db is None: return -1 # check to see if the database has already been created if db.exists(): if cfg.read_param("database_force_drop", "Do you really want to drop the '{}' database". format(db_name), False, True): db.drop() else: logger.info("database '{}' not dropped!".format(db_name)) else: logger.warning("database '{}' does not exist!".format(db_name))
def ensure_user(self, db_url, db_name, admin_db): """ Ensures that the user/password in @db_url can connect to @db_name. It assumes @db_name has been created beforehand. If the user doesn't exist or has insufficient permissions, this will be fixed using the @admin_db connection. """ db_url = osdb.set_url_db(db_url, db_name) try: db = self.get_db(db_url, db_name, check_access=True) logger.info("connected to DB, '%s' user is already created", osdb.get_url_user(db_url)) except osdbAccessDeniedError: logger.info("creating access user for {} ...".format(db_name)) if not admin_db.ensure_user(db_url): logger.error("failed to create user on {} DB".format(db_name)) return -1 db = self.get_db(db_url, db_name, cfg_url_param='database_url') if db is None: return -1 db.destroy() return 0
def grant_db_options(self, role_name="opensips", role_options="ALL PRIVILEGES"): """ assign attibutes to a role object (PostgreSQL specific) """ # TODO: is any other dialect using the "role" concept? if self.dialect != "postgres": return False # TODO: do this only for SQLAlchemy if not self.__conn: raise osdbError("connection not available") return False logger.debug("Role '%s' will be granted with options '%s' on database '%s'", role_name, role_options, self.db_name) sqlcmd = "GRANT {} ON DATABASE {} TO {}".format(role_options, self.db_name, role_name) try: result = self.__conn.execute(sqlcmd) if result: logger.info("granted options '%s' to role '%s' on database '%s'", role_options, role_name, self.db_name) except: logger.error("granting options '%s' to role '%s' on database '%s' failed", role_options, role_name, self.db_name) return False return
def do_delete(self, params=None): if len(params) < 1: name = cfg.read_param( None, "Please provide the username you want to delete") if not name: logger.warning("no username to delete!") return -1 else: name = params[0] username, domain = self.user_get_domain(name) db = self.user_db_connect() if not db: return -1 delete_dict = {USER_NAME_COL: username, USER_DOMAIN_COL: domain} # check if the user already exists if not db.entry_exists(USER_TABLE, delete_dict): logger.error("User {}@{} does not exist".format(username, domain)) return -1 db.delete(USER_TABLE, delete_dict) logger.info("Successfully deleted {}@{}".format(username, domain)) db.destroy() return True
def parse(self, in_file): if not in_file: logger.info("no config file used!") elif os.path.isfile(in_file) and os.access(in_file, os.R_OK): self.config.read(in_file) else: logger.error("Either file is missing or is not readable.")
def create(self, db_name=None): """ create a database object """ if db_name is None: db_name = self.db_name # TODO: do this only for SQLAlchemy if not self.__conn: raise osdbError("connection not available") # all good - it's time to create the database if self.dialect == "postgres": logger.debug("Create Database '%s' for dialect: '%s'", self.db_name, self.dialect) self.__conn.connection.connection.set_isolation_level(0) try: self.__conn.execute( "CREATE DATABASE {} WITH TEMPLATE template1".format( self.db_name)) logger.info("Database '%s' for dialect '%s' created", self.db_name, self.dialect) self.__conn.connection.connection.set_isolation_level(1) except sqlalchemy.exc.OperationalError as se: logger.error("cannot create database: {}!".format(se)) else: self.__conn.execute("CREATE DATABASE {}".format(self.db_name)) return True
def ensure_user(self, db_url, db_name, admin_db): """ Ensures that the user/password in @db_url can connect to @db_name. It assumes @db_name has been created beforehand. If the user doesn't exist or has insufficient permissions, this will be fixed using the @admin_db connection. """ db_url = osdb.set_url_db(db_url, db_name) try: db = self.get_db(db_url, db_name, check_access=True) logger.info("access works, opensips user already exists") except osdbAccessDeniedError: logger.info("creating access user for {} ...".format(db_name)) if not admin_db.ensure_user(db_url): logger.error("failed to create user on {} DB".format(db_name)) return -1 try: db = self.get_db(db_url, db_name, check_access=True) except Exception as e: logger.exception(e) logger.error("failed to connect to {} " + "with non-admin user".format(db_name)) return -1 db.destroy() return 0
def alter_role(self, role_name, role_options=None, role_password=None): """ alter attributes of a role object """ # TODO: is any other dialect using the "role" concept? if self.dialect != "postgres": return False # TODO: do this only for SQLAlchemy if not self.__conn: raise osdbError("connection not available") return False if not role_options is None: sqlcmd = "ALTER ROLE {} WITH {}".format(role_name, role_options) msg = "Alter role '{}' with options '{}'". \ format(role_name, role_options, self.db_name) if not role_password is None: sqlcmd += " PASSWORD '{}'".format(role_password) msg += " and password '********'" msg += " on database '{}'".format(self.db_name) try: result = self.__conn.execute(sqlcmd) if result: logger.info( "{} was successfull".format(msg)) except: logger.error("%s failed", msg) return False return
def migrate(self, migrate_scripts, old_db, new_db, tables=[]): if self.dialect != "mysql": logger.error("Table data migration is only supported for MySQL!") return """ migrate from source to destination database using SQL schema files """ self.connect(old_db) try: ret = self.find('mysql.proc', "count(*)", { 'db': old_db, 'name': 'OSIPS_DB_MIGRATE_2_4_TO_3_0' }) if ret and ret.first()[0] != 0: self.__conn.execute( sqlalchemy.sql.text( "DROP PROCEDURE IF EXISTS OSIPS_DB_MIGRATE_2_4_TO_3_0" ).execution_options(autocommit=True)) ret = self.find('mysql.proc', "count(*)", { 'db': old_db, 'name': 'OSIPS_TB_COPY_2_4_TO_3_0' }) if ret and ret.first()[0] != 0: self.__conn.execute( sqlalchemy.sql.text( "DROP PROCEDURE IF EXISTS OSIPS_TB_COPY_2_4_TO_3_0"). execution_options(autocommit=True)) except Exception as e: logger.exception(e) logger.error("Failed to re-create migration stored procedures!") for ms in migrate_scripts: logger.debug("Importing {}...".format(ms)) self.exec_sql_file(ms) if tables: for tb in tables: logger.info("Migrating {} data... ".format(tb)) try: self.__conn.execute( sqlalchemy.sql.text( "CALL {}.OSIPS_TB_COPY_2_4_TO_3_0('{}', '{}', '{}')" .format(old_db, old_db, new_db, tb))) except Exception as e: logger.exception(e) logger.error( "Failed to migrate '{}' table data, ".format(tb) + "see above errors!") else: try: self.__conn.execute( sqlalchemy.sql.text( "CALL {}.OSIPS_DB_MIGRATE_2_4_TO_3_0('{}', '{}')". format(old_db, old_db, new_db))) except Exception as e: logger.exception(e) logger.error("Failed to migrate database!")
def do_trap(self, params): self.pids = [] self.gdb_outputs = {} self.process_info = "" trap_file = cfg.get("trap_file") logger.info("Trapping {} in {}".format(PROCESS_NAME, trap_file)) if params and len(params) > 0: self.pids = params else: thread = Thread(target=self.get_pids) thread.start() thread.join(timeout=1) if len(self.pids) == 0: logger.warning("could not get OpenSIPS pids through MI!") try: ps_pids = subprocess.check_output(["pidof",PROCESS_NAME]) self.pids = ps_pids.decode().split() except: logger.warning("could not find any OpenSIPS running!") self.pids = [] if len(self.pids) < 1: logger.error("could not find OpenSIPS' pids") return -1 logger.debug("Dumping PIDs: {}".format(", ".join(self.pids))) threads = [] for pid in self.pids: thread = Thread(target=self.get_gdb_output, args=(pid,)) thread.start() threads.append(thread) for thread in threads: thread.join() if len(self.gdb_outputs) == 0: logger.error("could not get output of gdb") return -1 with open(trap_file, "w") as tf: tf.write(self.process_info) for pid in self.pids: if pid not in self.gdb_outputs: logger.warning("No output from pid {}".format(pid)) continue try: procinfo = subprocess.check_output( ["ps", "--no-headers", "-ww", "-fp", pid]).decode()[:-1] except: procinfo = "UNKNOWN" tf.write("\n\n---start {} ({})\n{}". format(pid, procinfo, self.gdb_outputs[pid])) print("Trap file: {}".format(trap_file))
def do_migrate(self, params): if len(params) < 3: print( "Usage: database migrate <flavour> <old-database> <new-database>" ) return 0 flavour = params[0].lower() old_db = params[1] new_db = params[2] if flavour not in DB_MIGRATIONS: logger.error("unsupported migration flavour: {}".format(flavour)) return -1 admin_url = self.get_admin_db_url(new_db) if not admin_url: return -1 db = self.get_db(admin_url, new_db) if not db: return -1 if db.dialect != "mysql": logger.error("'migrate' is only available for MySQL right now! :(") return -1 if not db.exists(old_db): logger.error( "the source database ({}) does not exist!".format(old_db)) return -2 print("Creating database {}...".format(new_db)) if self.create_db(new_db, admin_url, db) < 0: return -1 if self.create_tables(new_db, admin_url, db) < 0: return -1 backend = osdb.get_url_driver(admin_url) # obtain the DB schema files for the in-use backend schema_path = self.get_schema_path(backend) if schema_path is None: return -1 migrate_scripts = self.get_migrate_scripts_path(backend) if migrate_scripts is None: logger.debug("migration scripts for %s not found", backend) return -1 else: logger.debug("found migration scripts for %s", backend) logger.info("Migrating all matching OpenSIPS tables...") db.migrate( flavour.replace('.', '_').upper(), migrate_scripts, old_db, new_db, DB_MIGRATIONS[flavour]) db.destroy() return True
def do_add(self, params): """ add a given table to the database (connection via URL) """ if len(params) < 1: logger.error("No module to add added") return -1 module = params[0] db_url = cfg.read_param("database_url", "Please provide us the URL of the database") if db_url is None: print() logger.error("no URL specified: aborting!") return -1 if len(params) < 2: db_name = cfg.read_param( "database_name", "Please provide the database to add the module to", DEFAULT_DB_NAME) else: db_name = params[1] # create an object store database instance db = self.get_db(db_url, db_name) if db is None: return -1 if not db.exists(): logger.warning("database '{}' does not exist!".format(db_name)) return -1 db_schema = db.dialect schema_path = self.get_schema_path(db_schema) if schema_path is None: return -1 module_file_path = os.path.join(schema_path, "{}-create.sql".format(module)) if not os.path.isfile(module_file_path): logger.warning( "cannot find OpenSIPS DB file: '{}'!".format(module_file_path)) return -1 db.connect(db_name) try: db.create_module(module_file_path) except osdbError as ex: logger.error("cannot import: {}".format(ex)) return -1 db.destroy() logger.info("Module {} has been successfully added!".format(module)) return True
def migrate(self, proc_suffix, migrate_scripts, old_db, new_db, tables=[]): """ migrate from source to destination database using SQL schema files @flavour: values should resemble: '2.4_to_3.0', '3.0_to_3.1' @sp_suffix: stored procedure name suffix, specific to each migration """ if self.dialect != "mysql": logger.error("Table data migration is only supported for MySQL!") return proc_db_migrate = 'OSIPS_DB_MIGRATE_{}'.format(proc_suffix) proc_tb_migrate = 'OSIPS_TB_COPY_{}'.format(proc_suffix) self.connect(old_db) # separately drop DB/table migration stored procedures if already # present, since there are issues with multiple statements in 1 import try: self.__conn.execute( sqlalchemy.sql.text("DROP PROCEDURE IF EXISTS {}".format( proc_db_migrate)).execution_options(autocommit=True)) self.__conn.execute( sqlalchemy.sql.text("DROP PROCEDURE IF EXISTS {}".format( proc_tb_migrate)).execution_options(autocommit=True)) except: logger.exception("Failed to drop migration stored procedures!") for ms in migrate_scripts: logger.debug("Importing {}...".format(ms)) self.exec_sql_file(ms) if tables: for tb in tables: logger.info("Migrating {} data... ".format(tb)) try: self.__conn.execute( sqlalchemy.sql.text( "CALL {}.{}('{}', '{}', '{}')".format( old_db, proc_tb_migrate, old_db, new_db, tb))) except Exception as e: logger.exception(e) logger.error( "Failed to migrate '{}' table data, ".format(tb) + "see above errors!") else: try: self.__conn.execute( sqlalchemy.sql.text("CALL {}.{}('{}', '{}')".format( old_db, proc_db_migrate, old_db, new_db))) except Exception as e: logger.exception(e) logger.error("Failed to migrate database!") print("Finished copying OpenSIPS table data " + "into database '{}'!".format(new_db))
def do_create_module(self, module_name): """ create database table for given module """ db_url = cfg.read_param( "database_url", "Please provide the URL to connect to the database") if db_url is None: print() logger.error("no URL specified: aborting!") return -1 db_name = cfg.read_param("database_name", "Please provide the database name", DEFAULT_DB_NAME) if db_name is None: logger.error("no URL specified: aborting!") return -1 # create an object store database instance db = self.get_db(db_url, db_name) if db is None: return -1 # connect to the database db.connect(db_name) # create table from schema-file for given module name module = ' '.join(module_name) logger.debug("module_name: '%s'", module) #re.sub('\ |\[|\'|\]', '', module) #module_name.strip('[']') db_schema = db_url.split(":")[0] schema_path = self.get_schema_path(db_schema) if schema_path is None: return -1 module_schema_file = os.path.join(schema_path, "{}-create.sql".format(module)) try: db.create_module(module_schema_file) logger.info( "database tables for module '%s' has been successfully created.", module_name) except osdbError as ex: logger.error("cannot import: {}".format(ex)) # terminate active database connection db.destroy() return True
def do_password(self, params=None): if len(params) < 1: name = cfg.read_param( None, "Please provide the username to change the password for") if not name: logger.error("empty username") return -1 else: name = params[0] username, domain = self.user_get_domain(name) db, osips_ver = self.user_db_connect() if not db: return -1 user_dict = {USER_NAME_COL: username, USER_DOMAIN_COL: domain} # check if the user already exists if not db.entry_exists(USER_TABLE, user_dict): logger.warning("User {}@{} does not exist".format( username, domain)) return -1 if len(params) > 1: password = params[1] else: password = self.user_get_password() if password is None: logger.error("Password not specified: " + "cannot change passowrd for user {}@{}".format( user, domain)) return -1 plain_text_pw = cfg.getBool("plain_text_passwords") update_dict = { USER_HA1_COL: self.user_get_ha1(username, domain, password), USER_PASS_COL: password if plain_text_pw else "" } if osips_ver < '3.2': update_dict[USER_HA1B_COL] = self.user_get_ha1b( username, domain, password) db.update(USER_TABLE, update_dict, user_dict) logger.info("Successfully changed password for {}@{}".format( username, domain)) db.destroy() return True
def do_add(self, params=None): if len(params) < 1: name = cfg.read_param( None, "Please provide the username you want to add") if not name: logger.warning("no username to add!") return -1 else: name = params[0] username, domain = self.user_get_domain(name) db, osips_ver = self.user_db_connect() if not db: return -1 insert_dict = {USER_NAME_COL: username, USER_DOMAIN_COL: domain} # check if the user already exists if db.entry_exists(USER_TABLE, insert_dict): logger.error("User {}@{} already exists".format(username, domain)) return -1 if len(params) > 1: password = params[1] else: password = self.user_get_password() if password is None: logger.error( "password not specified: cannot add user {}@{}".format( user, domain)) return -1 insert_dict[USER_HA1_COL] = \ self.user_get_ha1(username, domain, password) # only populate the 'ha1b' column on 3.1 or older OpenSIPS DBs if osips_ver < '3.2': insert_dict[USER_HA1B_COL] = \ self.user_get_ha1b(username, domain, password) insert_dict[USER_PASS_COL] = \ password if cfg.getBool("plain_text_passwords") else "" db.insert(USER_TABLE, insert_dict) logger.info("Successfully added {}@{}".format(username, domain)) db.destroy() return True
def do_drop_role(self, params=None): """ drop a given role (connection to given template via URL) """ db_url = cfg.read_param( "template_url", "Please provide the URL to connect as template") if db_url is None: print() logger.error("no URL specified: aborting!") return -1 db_template = cfg.read_param( "database_template", "Please provide the database template name", DEFAULT_DB_TEMPLATE) if db_template is None: logger.error("no URL specified: aborting!") return -1 # create an object store database instance db = self.get_db(db_url, db_template) if db is None: return -1 role_name = None if len(params) >= 1: role_name = ''.join(params[0]) if role_name is None: role_name = cfg.read_param("role_name", "Please provide the role name to drop", DEFAULT_ROLE_NAME) if db.exists_role(role_name=role_name) is True: if cfg.read_param( "rule_force_drop", "Do you really want to drop the role '{}'".format( role_name), False, True): db.drop_role(role_name) db.destroy() else: logger.info("role '{}' not dropped!".format(role_name)) else: logger.warning("role '{}' does not exist!".format(role_name))
def grant_table_options(self, role, table, privs="ALL PRIVILEGES"): if self.dialect != "postgres": return False if not self.__conn: raise osdbError("connection not available") sqlcmd = "GRANT {} ON TABLE {} TO {}".format(privs, table, role) logger.info(sqlcmd) try: result = self.__conn.execute(sqlcmd) except Exception as e: logger.exception(e) logger.error("failed to grant '%s' to '%s' on table '%s'", privs, role, table) return False return True
def do_drop(self, params=None): """ drop a given database object (connection via URL) """ db_url = self.ask_db_url() if db_url is None: return -1 if params and len(params) > 0: db_name = params[0] else: db_name = cfg.read_param("database_name", "Please provide the database to drop", DEFAULT_DB_NAME) # create an object store database instance db = self.get_db(db_url, db_name) if db is None: return -1 if db.dialect == "postgres": if params and len(params) > 1: role_name = params[1] else: role_name = cfg.read_param( "role_name", "Please provide the associated role name " + "to access the database", DEFAULT_ROLE_NAME) # check to see if the database has already been created if db.exists(): if cfg.read_param( "database_force_drop", "Do you really want to drop the '{}' database".format( db_name), False, True): if db.drop(): logger.info("database '%s' dropped!", db_name) else: logger.info("database '%s' not dropped!", db_name) if db.dialect == "postgres": if db.exists_role(role_name) is True: if cfg.read_param( "role_force_drop", "Do you really want to drop the '{}' role". format(role_name), False, True): # call function with parameter list params = [role_name] self.do_drop_role(params) else: logger.info("database '{}' not dropped!".format(db_name)) else: logger.warning("database '{}' does not exist!".format(db_name))
def do_drop(self, params=None): """ drop a given database object (connection via URL) For PostgreSQL, perform this operation using 'postgres' as role + DB """ if params and len(params) > 0: db_name = params[0] else: db_name = cfg.read_param("database_name", "Please provide the database to drop") admin_db_url = self.get_admin_db_url(db_name) if admin_db_url is None: return -1 if admin_db_url.lower().startswith("postgres"): admin_db_url = osdb.set_url_db(admin_db_url, 'postgres') # create an object store database instance db = self.get_db(admin_db_url, db_name) if db is None: return -1 # check to see if the database has already been created if db.exists(): if cfg.read_param( "database_force_drop", "Do you really want to drop the '{}' database".format( db_name), False, True, isbool=True): if db.drop(): logger.info("database '%s' dropped!", db_name) else: logger.info("database '%s' not dropped!", db_name) else: logger.info("database '{}' not dropped!".format(db_name)) else: logger.warning("database '{}' does not exist!".format(db_name)) db.destroy() return -1 db.destroy() return 0
def do_rootCA(self, params): global cfg logger.info("Preparing to generate CA cert + key...") # TODO # separate cli.cfg files for TLS are fully deprecated, this if block is # only kept for backwards-compatibility. Remove starting from v3.2! <3 if cfg.exists('tls_ca_config'): tls_cfg = cfg.get('tls_ca_config') cfg = OpenSIPSCLIConfig() cfg.parse(tls_cfg) ca_dir = cfg.read_param("tls_ca_dir", "Output directory", "/etc/opensips/tls/rootCA/") cert_file = cfg.read_param("tls_ca_cert_file", "Output cert file", "cacert.pem") key_file = cfg.read_param("tls_ca_key_file", "Output key file", "private/cakey.pem") c_f = join(ca_dir, cert_file) k_f = join(ca_dir, key_file) if (exists(c_f) or exists(k_f)) and not cfg.read_param( "tls_ca_overwrite", "CA certificate or key already exists, overwrite?", "yes", True): return # create a self-signed cert cert = crypto.X509() cert.get_subject().CN = cfg.read_param("tls_ca_common_name", "Website address (CN)", "opensips.org") cert.get_subject().C = cfg.read_param("tls_ca_country", "Country (C)", "RO") cert.get_subject().ST = cfg.read_param("tls_ca_state", "State (ST)", "Bucharest") cert.get_subject().L = cfg.read_param("tls_ca_locality", "Locality (L)", "Bucharest") cert.get_subject().O = cfg.read_param("tls_ca_organisation", "Organization (O)", "OpenSIPS") cert.get_subject().OU = cfg.read_param("tls_ca_organisational_unit", "Organisational Unit (OU)", "Project") cert.set_serial_number(randrange(100000)) cert.gmtime_adj_notBefore(0) notafter = int( cfg.read_param("tls_ca_notafter", "Certificate validity (seconds)", 315360000)) cert.gmtime_adj_notAfter(notafter) cert.set_issuer(cert.get_subject()) # create a key pair key = crypto.PKey() key_size = int( cfg.read_param("tls_ca_key_size", "RSA key size (bits)", 4096)) key.generate_key(crypto.TYPE_RSA, key_size) cert.set_pubkey(key) md = cfg.read_param("tls_ca_md", "Digest Algorithm", "SHA1") cert.sign(key, md) try: if not exists(dirname(c_f)): makedirs(dirname(c_f)) open(c_f, "wt").write( crypto.dump_certificate(crypto.FILETYPE_PEM, cert).decode('utf-8')) except Exception as e: logger.exception(e) logger.error("Failed to write to %s", c_f) return try: if not exists(dirname(k_f)): makedirs(dirname(k_f)) open(k_f, "wt").write( crypto.dump_privatekey(crypto.FILETYPE_PEM, key).decode('utf-8')) except Exception as e: logger.exception(e) logger.error("Failed to write to %s", k_f) return logger.info("CA certificate created in " + c_f) logger.info("CA private key created in " + k_f)
def ensure_user(self, db_url): url = make_url(db_url) if url.password is None: logger.error("database URL does not include a password") return False if url.drivername.lower() == "mysql": sqlcmd = """CREATE USER IF NOT EXISTS '{}'@'{}' IDENTIFIED BY '{}'""".format(url.username, url.host, url.password) try: result = self.__conn.execute(sqlcmd) if result: logger.info("created user '%s'@'%s'", url.username, url.host) except: logger.error("failed to create user '%s'@'%s'", url.username, url.host) return False sqlcmd = "SET PASSWORD FOR '{}'@'{}' = PASSWORD('{}')".format( url.username, url.host, url.password) try: result = self.__conn.execute(sqlcmd) if result: logger.info("set password for '%s'@'%s'", url.username, url.host) except: logger.error("failed to set password for '%s'@'%s'", url.username, url.host) return False sqlcmd = "GRANT ALL ON {}.* TO '{}'@'{}'".format( self.db_name, url.username, url.host) try: result = self.__conn.execute(sqlcmd) if result: logger.info("granted access to '%s'@'%s' on '%s'", url.username, url.host, self.db_name) except: logger.error("failed to grant access to '%s'@'%s'", url.username, url.host) return False sqlcmd = "FLUSH PRIVILEGES" try: result = self.__conn.execute(sqlcmd) logger.info("flushed privileges") except: logger.error("failed to flush privileges") return False elif url.drivername.lower() == "postgres": if not self.exists_role(url.username): logger.info("creating role %s", url.username) if not self.create_role(url.username, url.password): logger.error("failed to create role %s", url.username) self.create_role(url.username, url.password, update=True) sqlcmd = "GRANT ALL PRIVILEGES ON DATABASE {} TO {}".format( self.db_name, url.username) logger.info(sqlcmd) try: result = self.__conn.execute(sqlcmd) if result: logger.debug("... OK") except: logger.error("failed to grant ALL to '%s' on db '%s'", url.username, self.db_name) return False return True
def do_rootCA(self, params): tlscfg = cfg ca_cfg = cfg.get("tls_ca_config") if ca_cfg: tlscfg = OpenSIPSCLIConfig() tlscfg.parse(ca_cfg) cn = tlscfg.read_param( "tls_ca_common_name", "input the hostname of the website the certificate is for", "www.opensips.com") ca_dir = tlscfg.read_param("tls_ca_dir", "ca director", "/etc/opensips/tls/rootCA/") cert_file = tlscfg.read_param("tls_ca_cert_file", "cert_file", "cacert.pem") key_file = tlscfg.read_param("tls_ca_key_file", "key_file", "private/cakey.pem") c_f = join(ca_dir, cert_file) k_f = join(ca_dir, key_file) create_cert = False if not exists(c_f) or not exists(k_f): create_cert = True else: if tlscfg.read_param( "tls_ca_overwrite", "rootCA already exists, do you want to overwrite it?", "yes", True): create_cert = True if create_cert: # create a key pair key = crypto.PKey() key_size = int( tlscfg.read_param("tls_ca_key_size", "key_size", 4096)) key.generate_key(crypto.TYPE_RSA, key_size) # create a self-signed cert cert = crypto.X509() cert.get_subject().C = tlscfg.read_param("tls_ca_country", "country") cert.get_subject().ST = tlscfg.read_param("tls_ca_state", "state", "Ilfov") cert.get_subject().L = tlscfg.read_param("tls_ca_city", "city", "Bucharest") cert.get_subject().O = tlscfg.read_param("tls_ca_organisation", "organization", "opensips") cert.get_subject().OU = tlscfg.read_param( "tls_ca_organisational_unit", "organisational unit", "project") cert.get_subject().CN = cn cert.set_serial_number(1) cert.gmtime_adj_notBefore(0) notafter = int( tlscfg.read_param("tls_ca_notafter", "duration", 315360000)) cert.gmtime_adj_notAfter(notafter) cert.set_issuer(cert.get_subject()) cert.set_pubkey(key) md = tlscfg.read_param("tls_ca_md", "md", "sha1") cert.sign(key, md) if not exists(dirname(c_f)): makedirs(dirname(c_f)) try: open(c_f, "wt").write( crypto.dump_certificate(crypto.FILETYPE_PEM, cert).decode('utf-8')) except Exception as e: logger.error(e) if not exists(dirname(k_f)): makedirs(dirname(k_f)) try: open(k_f, "wt").write( crypto.dump_privatekey(crypto.FILETYPE_PEM, key).decode('utf-8')) except Exception as e: logger.error(e) logger.info("CA certificate created in " + c_f) logger.info("CA private key created in " + k_f)
from opensipscli.logger import logger from opensipscli.config import cfg import re try: import sqlalchemy import sqlalchemy_utils from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Date, Integer, String, Boolean from sqlalchemy.orm import sessionmaker, deferred from sqlalchemy.engine.url import make_url sqlalchemy_available = True logger.debug("SQLAlchemy version: ", sqlalchemy.__version__) except ImportError: logger.info("sqlalchemy and sqlalchemy_utils are not available!") sqlalchemy_available = False SUPPORTED_BACKENDS = [ "mysql", "postgres", "sqlite", "oracle", ] """ SQLAlchemy: Classes for ORM handling """ if sqlalchemy_available: Base = declarative_base()
try: import sqlalchemy from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Date, Integer, String, Boolean from sqlalchemy.orm import sessionmaker, deferred from sqlalchemy.engine.url import make_url sqlalchemy_available = True logger.debug("SQLAlchemy version: ", sqlalchemy.__version__) try: import sqlalchemy_utils except ImportError: logger.debug("using embedded implementation of SQLAlchemy_Utils") # copied from SQLAlchemy_utils repository from opensipscli.libs import sqlalchemy_utils except ImportError: logger.info("sqlalchemy not available!") sqlalchemy_available = False SUPPORTED_BACKENDS = [ "mysql", "postgres", "sqlite", "oracle", ] """ SQLAlchemy: Classes for ORM handling """ if sqlalchemy_available: Base = declarative_base() class Roles(Base):
def create_tables(self, db_name, db_url, admin_db, tables=[], create_std=True): """ create database tables """ db_url = osdb.set_url_db(db_url, db_name) # 2) prepare new object store database instance # use it to connect to the created database db = self.get_db(db_url, db_name) if db is None: return -1 if not db.exists(): logger.warning("database '{}' does not exist!".format(db_name)) return -1 schema_path = self.get_schema_path(db.dialect) if schema_path is None: return -1 if create_std: standard_file_path = os.path.join(schema_path, "standard-create.sql") if not os.path.isfile(standard_file_path): logger.error( "cannot find stardard OpenSIPS DB file: '{}'!".format( standard_file_path)) return -1 table_files = {'standard': standard_file_path} else: table_files = {} # check to see what tables we shall deploy if tables: pass elif cfg.exists("database_modules"): # we know exactly what modules we want to instsall tables_line = cfg.get("database_modules").strip().lower() if tables_line == "all": logger.debug("Creating all tables") tables = [ f.replace('-create.sql', '') \ for f in os.listdir(schema_path) \ if os.path.isfile(os.path.join(schema_path, f)) and \ f.endswith('-create.sql') ] else: logger.debug("Creating custom tables") tables = tables_line.split(" ") else: logger.debug("Creating standard tables") tables = STANDARD_DB_MODULES # check for corresponding SQL schemas files in system path logger.debug("checking tables: {}".format(" ".join(tables))) for table in tables: if table == "standard": # already checked for it continue table_file_path = os.path.join(schema_path, "{}-create.sql".format(table)) if not os.path.isfile(table_file_path): logger.warn("cannot find SQL file for module {}: {}".format( table, table_file_path)) else: table_files[table] = table_file_path username = osdb.get_url_user(db_url) admin_db.connect(db_name) # create tables from SQL schemas for module, table_file in table_files.items(): logger.info("Running {}...".format(os.path.basename(table_file))) try: db.create_module(table_file) if db.dialect == "postgres": self.pg_grant_table_access(table_file, username, admin_db) except osdbModuleAlreadyExistsError: logger.error("{} table(s) are already created!".format(module)) except osdbError as ex: logger.error("cannot import: {}".format(ex)) # terminate active database connection db.destroy() return 0
def ensure_user(self, db_url): url = make_url(db_url) if url.password is None: logger.error("database URL does not include a password") return False if url.drivername.lower() == "mysql": sqlcmd = "CREATE USER IF NOT EXISTS '{}' IDENTIFIED BY '{}'".format( url.username, url.password) try: result = self.__conn.execute(sqlcmd) if result: logger.info("created user '%s'", url.username) except: logger.error("failed to create user '%s'", url.username) return False if url.username == 'root': logger.debug("skipping password change for root user") else: """ Query compatibility facts when changing a MySQL user password: - SET PASSWORD syntax has diverged between MySQL and MariaDB - ALTER USER syntax is not supported in MariaDB < 10.2 """ # try MariaDB syntax first sqlcmd = "SET PASSWORD FOR '{}' = PASSWORD('{}')".format( url.username, url.password) try: result = self.__conn.execute(sqlcmd) if result: logger.info( "set password '%s%s%s' for '%s' (MariaDB)", url.password[0] if len(url.password) >= 1 else '', (len(url.password) - 2) * '*', url.password[-1] if len(url.password) >= 2 else '', url.username) except sqlalchemy.exc.ProgrammingError as se: try: if int(se.args[0].split(",")[0].split("(")[2]) == 1064: # syntax error! OK, now try Oracle MySQL syntax sqlcmd = "ALTER USER '{}' IDENTIFIED BY '{}'".format( url.username, url.password) result = self.__conn.execute(sqlcmd) if result: logger.info( "set password '%s%s%s' for '%s' (MySQL)", url.password[0] if len(url.password) >= 1 else '', (len(url.password) - 2) * '*', url.password[-1] if len(url.password) >= 2 else '', url.username) except: logger.exception("failed to set password for '%s'", url.username) return False except: logger.exception("failed to set password for '%s'", url.username) return False sqlcmd = "GRANT ALL ON {}.* TO '{}'".format( self.db_name, url.username) try: result = self.__conn.execute(sqlcmd) if result: logger.info("granted access to user '%s' on DB '%s'", url.username, self.db_name) except: logger.exception("failed to grant access to '%s' on DB '%s'", url.username, self.db_name) return False sqlcmd = "FLUSH PRIVILEGES" try: result = self.__conn.execute(sqlcmd) logger.info("flushed privileges") except: logger.exception("failed to flush privileges") return False elif url.drivername.lower() == "postgres": if not self.exists_role(url.username): logger.info("creating role %s", url.username) if not self.create_role(url.username, url.password): logger.error("failed to create role %s", url.username) self.create_role(url.username, url.password, update=True) sqlcmd = "GRANT ALL PRIVILEGES ON DATABASE {} TO {}".format( self.db_name, url.username) logger.info(sqlcmd) try: result = self.__conn.execute(sqlcmd) if result: logger.debug("... OK") except: logger.error("failed to grant ALL to '%s' on db '%s'", url.username, self.db_name) return False return True
def do_create(self, params=None): db_url = cfg.read_param("database_url", "Please provide us the URL of the database") if db_url is None: print() logger.error("no URL specified: aborting!") return -1 if params and len(params) > 0: db_name = params[0] else: db_name = cfg.read_param("database_name", "Please provide the database to create", DEFAULT_DB_NAME) db = osdb(db_url, db_name) # check to see if the database has already been created if db.exists(): logger.warn("database '{}' already exists!".format(db_name)) return -2 db_schema = db_url.split(":")[0] schema_path = self.get_schema_path(db_schema) if schema_path is None: return -1 standard_file_path = os.path.join(schema_path, "standard-create.sql") if not os.path.isfile(standard_file_path): logger.error("cannot find stardard OpenSIPS DB file: '{}'!".format( standard_file_path)) return -1 tables_files = [standard_file_path] # all good now - check to see what tables we shall deploy if cfg.exists("database_modules"): tables = cfg.get("database_modules").split(" ") else: tables = STANDARD_DB_MODULES logger.debug("deploying tables {}".format(" ".join(tables))) for table in tables: if table == "standard": # already checked for it continue table_file_path = os.path.join(schema_path, "{}-create.sql".format(table)) if not os.path.isfile(table_file_path): logger.warn("cannot find file to create {}: {}".format( table, table_file_path)) else: tables_files.append(table_file_path) db.create() db.use() for table_file in tables_files: try: db.create_module(table_file) except osdbError as ex: logger.error("cannot import: {}".format(ex)) db.destroy() logger.info("The database has been successfully created.") return 0
from opensipscli.module import Module from opensipscli.logger import logger from socket import gethostname from pprint import pprint from time import gmtime, mktime from os.path import exists, join, dirname from os import makedirs from opensipscli.config import cfg, OpenSIPSCLIConfig from random import randrange try: from OpenSSL import crypto, SSL openssl_available = True except ImportError: logger.info("OpenSSL library not available!") openssl_available = False class tls(Module): def do_rootCA(self, params): global cfg logger.info("Preparing to generate CA cert + key...") # TODO # separate cli.cfg files for TLS are fully deprecated, this if block is # only kept for backwards-compatibility. Remove starting from v3.2! <3 if cfg.exists('tls_ca_config'): tls_cfg = cfg.get('tls_ca_config') cfg = OpenSIPSCLIConfig() cfg.parse(tls_cfg)