def import_database(target_file, clear=True):
"""
Import the contents of a serialized database from an archive previously
created with the :py:func:`.export_database` function. The current
:py:data:`~king_phisher.server.database.models.SCHEMA_VERSION` must be the
same as the exported archive.
.. warning::
This will by default delete the contents of the current database in
accordance with the *clear* parameter. If *clear* is not
specified and objects in the database and import share an ID, they will
be merged.
:param str target_file: The database archive file to import from.
:param bool clear: Whether or not to delete the contents of the
existing database before importing the new data.
"""
kpdb = archive.ArchiveFile(target_file, 'r')
schema_version = kpdb.metadata['database-schema']
if schema_version != models.SCHEMA_VERSION:
raise errors.KingPhisherDatabaseError(
"incompatible database schema versions ({0} vs {1})".format(
schema_version, models.SCHEMA_VERSION))
if clear:
clear_database()
session = Session()
for table in models.metadata.sorted_tables:
table_data = kpdb.get_data('tables/' + table.name)
for row in sqlalchemy.ext.serializer.loads(table_data):
session.merge(row)
session.commit()
kpdb.close()
def _popen_psql(sql):
if os.getuid():
raise RuntimeError('_popen_psql can only be used as root due to su requirement')
proc_h = _popen(['su', 'postgres', '-c', "psql -At -c \"{0}\"".format(sql)])
if proc_h.wait():
raise errors.KingPhisherDatabaseError("failed to execute postgresql query '{0}' via su and psql".format(sql))
output = proc_h.stdout.read()
output = output.decode('utf-8')
output = output.strip()
return output.split('\n')
def init_alembic(engine, schema_version):
"""
Creates the alembic_version table and sets the value of the table according
to the specified schema version.
:param engine: The engine used to connect to the database.
:type engine: :py:class:`sqlalchemy.engine.Engine`
:param int schema_version: The MetaData schema_version to set the alembic version to.
"""
pattern = re.compile(r'[a-f0-9]{10,16}_schema_v\d+\.py')
alembic_revision = None
alembic_directory = find.data_directory('alembic')
if not alembic_directory:
raise errors.KingPhisherDatabaseError(
'cannot find the alembic data directory')
alembic_versions_files = os.listdir(
os.path.join(alembic_directory, 'versions'))
for file in alembic_versions_files:
if not pattern.match(file):
continue
if not file.endswith('_schema_v' + str(schema_version) + '.py'):
continue
alembic_revision = file.split('_', 1)[0]
break
if not alembic_revision:
raise errors.KingPhisherDatabaseError(
"cannot find current alembic version for schema version {0}".
format(schema_version))
alembic_metadata = sqlalchemy.MetaData(engine)
alembic_table = sqlalchemy.Table(
'alembic_version', alembic_metadata,
sqlalchemy.Column('version_num',
sqlalchemy.String,
primary_key=True,
nullable=False))
alembic_metadata.create_all()
alembic_version_entry = alembic_table.insert().values(
version_num=alembic_revision)
engine.connect().execute(alembic_version_entry)
logger.info(
"alembic_version table initialized to {0}".format(alembic_revision))
def _popen_psql(sql):
if os.getuid():
raise RuntimeError(
'_popen_psql can only be used as root due to su requirement')
results = startup.run_process(
['su', 'postgres', '-c', "psql -At -c \"{0}\"".format(sql)])
if results.status != os.EX_OK:
raise errors.KingPhisherDatabaseError(
"failed to execute postgresql query '{0}' via su and psql".format(
sql))
return results.stdout.split('\n')
def init_database_postgresql(connection_url):
"""
Perform additional initialization checks and operations for a PostgreSQL
database. If the database is hosted locally this will ensure that the
service is currently running and start it if it is not. Additionally if the
specified database or user do not exist, they will be created.
:param connection_url: The url for the PostgreSQL database connection.
:type connection_url: :py:class:`sqlalchemy.engine.url.URL`
:return: The initialized database engine.
"""
if not ipaddress.is_loopback(connection_url.host):
return
is_sanitary = lambda s: re.match(r'^[a-zA-Z0-9_]+$', s) is not None
systemctl_bin = smoke_zephyr.utilities.which('systemctl')
if systemctl_bin is None:
logger.info(
'postgresql service status check failed (could not find systemctl)'
)
else:
postgresql_setup = smoke_zephyr.utilities.which('postgresql-setup')
if postgresql_setup is None:
logger.debug('postgresql-setup was not found')
else:
logger.debug(
'using postgresql-setup to ensure that the database is initialized'
)
proc_h = _popen([postgresql_setup, '--initdb'])
proc_h.wait()
proc_h = _popen([systemctl_bin, 'status', 'postgresql.service'])
# wait for the process to return and check if it's running (status 0)
if proc_h.wait() == 0:
logger.debug('postgresql service is already running via systemctl')
else:
logger.info(
'postgresql service is not running, starting it now via systemctl'
)
proc_h = _popen([systemctl_bin, 'start', 'postgresql'])
if proc_h.wait() != 0:
logger.error(
'failed to start the postgresql service via systemctl')
raise errors.KingPhisherDatabaseError(
'postgresql service failed to start via systemctl')
logger.debug(
'postgresql service successfully started via systemctl')
rows = _popen_psql('SELECT usename FROM pg_user')
if connection_url.username not in rows:
logger.info(
'the specified postgresql user does not exist, adding it now')
if not is_sanitary(connection_url.username):
raise errors.KingPhisherInputValidationError(
'will not create the postgresql user (username contains bad characters)'
)
if not is_sanitary(connection_url.password):
raise errors.KingPhisherInputValidationError(
'will not create the postgresql user (password contains bad characters)'
)
rows = _popen_psql(
"CREATE USER {url.username} WITH PASSWORD '{url.password}'".format(
url=connection_url))
if rows != ['CREATE ROLE']:
logger.error('failed to create the postgresql user')
raise errors.KingPhisherDatabaseError(
'failed to create the postgresql user')
logger.debug('the specified postgresql user was successfully created')
rows = _popen_psql('SELECT datname FROM pg_database')
if connection_url.database not in rows:
logger.info(
'the specified postgresql database does not exist, adding it now')
if not is_sanitary(connection_url.database):
raise errors.KingPhisherInputValidationError(
'will not create the postgresql database (name contains bad characters)'
)
rows = _popen_psql(
"CREATE DATABASE {url.database} OWNER {url.username}".format(
url=connection_url))
if rows != ['CREATE DATABASE']:
logger.error('failed to create the postgresql database')
raise errors.KingPhisherDatabaseError(
'failed to create the postgresql database')
logger.debug(
'the specified postgresql database was successfully created')
def init_database(connection_url, extra_init=False):
"""
Create and initialize the database engine. This must be done before the
session object can be used. This will also attempt to perform any updates to
the database schema if the backend supports such operations.
:param str connection_url: The url for the database connection.
:param bool extra_init: Run optional extra dbms-specific initialization logic.
:return: The initialized database engine.
"""
connection_url = normalize_connection_url(connection_url)
connection_url = sqlalchemy.engine.url.make_url(connection_url)
logger.info("initializing database connection with driver {0}".format(
connection_url.drivername))
if connection_url.drivername == 'sqlite':
engine = sqlalchemy.create_engine(
connection_url,
connect_args={'check_same_thread': False},
poolclass=sqlalchemy.pool.StaticPool)
sqlalchemy.event.listens_for(
engine, 'begin')(lambda conn: conn.execute('BEGIN'))
elif connection_url.drivername == 'postgresql':
if extra_init:
init_database_postgresql(connection_url)
engine = sqlalchemy.create_engine(
connection_url, connect_args={'client_encoding': 'utf8'})
else:
raise errors.KingPhisherDatabaseError(
'only sqlite and postgresql database drivers are supported')
Session.remove()
Session.configure(bind=engine)
inspector = sqlalchemy.inspect(engine)
if 'campaigns' not in inspector.get_table_names():
logger.debug('campaigns table not found, creating all new tables')
try:
models.Base.metadata.create_all(engine)
except sqlalchemy.exc.SQLAlchemyError as error:
error_lines = (line.strip() for line in error.message.split('\n'))
raise errors.KingPhisherDatabaseError(
'SQLAlchemyError: ' + ' '.join(error_lines).strip())
schema_version = get_schema_version(engine)
logger.debug("current database schema version: {0} ({1})".format(
schema_version,
('latest' if schema_version == models.SCHEMA_VERSION else 'obsolete')))
if 'alembic_version' not in inspector.get_table_names():
logger.debug(
'alembic version table not found, attempting to create and set version'
)
init_alembic(engine, schema_version)
if schema_version > models.SCHEMA_VERSION:
raise errors.KingPhisherDatabaseError(
'the database schema is for a newer version, automatic downgrades are not supported'
)
elif schema_version < models.SCHEMA_VERSION:
alembic_config_file = find.data_file('alembic.ini')
if not alembic_config_file:
raise errors.KingPhisherDatabaseError(
'cannot find the alembic.ini configuration file')
alembic_directory = find.data_directory('alembic')
if not alembic_directory:
raise errors.KingPhisherDatabaseError(
'cannot find the alembic data directory')
config = alembic.config.Config(alembic_config_file)
config.config_file_name = alembic_config_file
config.set_main_option('script_location', alembic_directory)
config.set_main_option('skip_logger_config', 'True')
config.set_main_option('sqlalchemy.url', str(connection_url))
logger.warning(
"automatically updating the database schema from version {0} to {1}"
.format(schema_version, models.SCHEMA_VERSION))
try:
alembic.command.upgrade(config, 'head')
except Exception as error:
logger.critical(
"database schema upgrade failed with exception: {0}.{1} {2}".
format(error.__class__.__module__, error.__class__.__name__,
getattr(error, 'message', '')).rstrip(),
exc_info=True)
raise errors.KingPhisherDatabaseError(
'failed to upgrade to the latest database schema')
logger.info(
"successfully updated the database schema from version {0} to {1}".
format(schema_version, models.SCHEMA_VERSION))
# reset it because it may have been altered by alembic
Session.remove()
Session.configure(bind=engine)
set_metadata('database_driver', connection_url.drivername)
set_metadata('last_started', datetime.datetime.utcnow())
set_metadata('schema_version', models.SCHEMA_VERSION)
logger.debug("connected to {0} database: {1}".format(
connection_url.drivername, connection_url.database))
signals.db_initialized.send(connection_url)
return engine
def init_database(connection_url):
"""
Create and initialize the database engine. This must be done before the
session object can be used. This will also attempt to perform any updates to
the database schema if the backend support such operations.
:param str connection_url: The url for the database connection.
:return: The initialized database engine.
"""
connection_url = normalize_connection_url(connection_url)
connection_url = sqlalchemy.engine.url.make_url(connection_url)
logger.info("initializing database connection with driver {0}".format(
connection_url.drivername))
if connection_url.drivername == 'sqlite':
engine = sqlalchemy.create_engine(
connection_url,
connect_args={'check_same_thread': False},
poolclass=sqlalchemy.pool.StaticPool)
sqlalchemy.event.listens_for(
engine, 'begin')(lambda conn: conn.execute('BEGIN'))
elif connection_url.drivername == 'postgresql':
engine = sqlalchemy.create_engine(connection_url)
else:
raise errors.KingPhisherDatabaseError(
'only sqlite and postgresql database drivers are supported')
Session.remove()
Session.configure(bind=engine)
try:
models.Base.metadata.create_all(engine)
except sqlalchemy.exc.SQLAlchemyError as error:
error_lines = map(lambda line: line.strip(), error.message.split('\n'))
raise errors.KingPhisherDatabaseError('SQLAlchemyError: ' +
' '.join(error_lines).strip())
session = Session()
set_meta_data('database_driver',
connection_url.drivername,
session=session)
schema_version = (get_meta_data('schema_version', session=session)
or models.SCHEMA_VERSION)
session.commit()
session.close()
logger.debug("current database schema version: {0} ({1}current)".format(
schema_version,
('' if schema_version == models.SCHEMA_VERSION else 'not ')))
if schema_version > models.SCHEMA_VERSION:
raise errors.KingPhisherDatabaseError(
'the database schema is for a newer version, automatic downgrades are not supported'
)
elif schema_version < models.SCHEMA_VERSION:
alembic_config_file = find.find_data_file('alembic.ini')
if not alembic_config_file:
raise errors.KingPhisherDatabaseError(
'cannot find the alembic.ini configuration file')
alembic_directory = find.find_data_directory('alembic')
if not alembic_directory:
raise errors.KingPhisherDatabaseError(
'cannot find the alembic data directory')
config = alembic.config.Config(alembic_config_file)
config.config_file_name = alembic_config_file
config.set_main_option('script_location', alembic_directory)
config.set_main_option('skip_logger_config', 'True')
config.set_main_option('sqlalchemy.url', str(connection_url))
logger.warning(
"automatically updating the database schema to version {0}".format(
models.SCHEMA_VERSION))
try:
alembic.command.upgrade(config, 'head')
except Exception as error:
logger.critical(
"database schema upgrade failed with exception: {0}.{1} {2}".
format(error.__class__.__module__, error.__class__.__name__,
getattr(error, 'message', '')).rstrip())
raise errors.KingPhisherDatabaseError(
'failed to upgrade to the latest database schema')
set_meta_data('schema_version', models.SCHEMA_VERSION)
logger.debug("connected to {0} database: {1}".format(
connection_url.drivername, connection_url.database))
return engine
