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 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 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 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: print("Migrating {} data... ".format(tb), end='') 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 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 migrate(self, migrate_scripts, old_db, new_db, tables=[]): """ migrate from source to destination database using SQL schema files """ self.connect(old_db) for ms in migrate_scripts: logger.debug("Importing {}...".format(ms)) self.exec_sql_file(ms) if tables: for tb in tables: print("Migrating {} data... ".format(tb), end='') try: self.__conn.execute( sqlalchemy.sql.text( "CALL {}.OSIPS_TB_COPY_2_4_TO_3_0('{}', '{}', '{}')" .format(old_db, old_db, new_db, tb)).execution_options(autocommit=True)) print("OK") except Exception as e: print("FAILED!") 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)).execution_options(autocommit=True)) except Exception as e: logger.exception(e) logger.error("Failed to migrate database!")
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.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_userCERT(self, params): global cfg logger.info("Preparing to generate user cert + key + CA list...") # 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_user_config'): tls_cfg = cfg.get('tls_user_config') cfg = OpenSIPSCLIConfig() cfg.parse(tls_cfg) user_dir = cfg.read_param("tls_user_dir", "Output directory", "/etc/opensips/tls/user/") cert_file = cfg.read_param("tls_user_cert_file", "Output cert file", "user-cert.pem") key_file = cfg.read_param("tls_user_key_file", "Output key file", "user-privkey.pem") calist_file = cfg.read_param("tls_user_calist_file", "Output CA list file", "user-calist.pem") c_f = join(user_dir, cert_file) k_f = join(user_dir, key_file) ca_f = join(user_dir, calist_file) if (exists(c_f) or exists(k_f) or exists(ca_f)) and not cfg.read_param("tls_user_overwrite", "User certificate, key or CA list file already exists, overwrite?", "yes", True): return cacert = cfg.read_param("tls_user_cacert", "CA cert file", "/etc/opensips/tls/rootCA/cacert.pem") cakey = cfg.read_param("tls_user_cakey", "CA key file", "/etc/opensips/tls/rootCA/private/cakey.pem") try: ca_cert = crypto.load_certificate(crypto.FILETYPE_PEM, open(cacert, 'rt').read()) except Exception as e: logger.exception(e) logger.error("Failed to load %s", cacert) return try: ca_key = crypto.load_privatekey(crypto.FILETYPE_PEM, open(cakey, 'rt').read()) except Exception as e: logger.exception(e) logger.error("Failed to load %s", cakey) return # create a self-signed cert cert = crypto.X509() cert.set_version(2) cert.get_subject().CN = cfg.read_param("tls_user_common_name", "Website address (CN)", "www.opensips.org") cert.get_subject().C = cfg.read_param("tls_user_country", "Country (C)", "RO") cert.get_subject().ST = cfg.read_param("tls_user_state", "State (ST)", "Bucharest") cert.get_subject().L = cfg.read_param("tls_user_locality", "Locality (L)", "Bucharest") cert.get_subject().O = cfg.read_param("tls_user_organisation", "Organization (O)", "OpenSIPS") cert.get_subject().OU = cfg.read_param("tls_user_organisational_unit", "Organisational Unit (OU)", "Project") cert.set_serial_number(randrange(100000)) cert.gmtime_adj_notBefore(0) notafter = int(cfg.read_param("tls_user_notafter", "Certificate validity (seconds)", 315360000)) cert.gmtime_adj_notAfter(notafter) cert.set_issuer(ca_cert.get_subject()) extensions = [ crypto.X509Extension(b'basicConstraints', False, b'CA:FALSE'), crypto.X509Extension(b'extendedKeyUsage', False, b'clientAuth,serverAuth') ] cert.add_extensions(extensions) # create a key pair key = crypto.PKey() key_size = int(cfg.read_param("tls_user_key_size", "RSA key size (bits)", 4096)) key.generate_key(crypto.TYPE_RSA, key_size) cert.set_pubkey(key) md = cfg.read_param("tls_user_md", "Digest Algorithm", "SHA256") cert.sign(ca_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 try: if not exists(dirname(ca_f)): makedirs(dirname(ca_f)) open(ca_f, "wt").write(crypto.dump_certificate(crypto.FILETYPE_PEM, ca_cert).decode('utf-8')) except Exception as e: logger.exception(e) logger.error("Failed to write to %s", ca_f) return logger.info("user certificate created in " + c_f) logger.info("user private key created in " + k_f) logger.info("user CA list (chain of trust) created in " + ca_f)