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)
