def pg_register_bignewinserts():
inserts = ['response']
for table in inserts:
create_function_on_insert = "DROP FUNCTION IF EXISTS notify_new" + table + "() cascade;" + \
"CREATE FUNCTION notify_new" + table + \
"() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_notify('new" + table + \
"', NEW.id::text); RETURN NULL; END; $$;"
create_trigger_on_insert = "CREATE TRIGGER new" + table + \
"_trigger AFTER INSERT ON " + table + " FOR EACH ROW EXECUTE PROCEDURE notify_new" + \
table + "();"
try:
apfell_db.execute_sql(create_function_on_insert)
apfell_db.execute_sql(create_trigger_on_insert)
except Exception as e:
print(e)
def pg_register_deletes():
updates = ['command', 'commandparameters', 'commandtransform']
for table in updates:
create_function_on_deletes = "DROP FUNCTION IF EXISTS notify_deleted" + table + "() cascade;" + \
"CREATE FUNCTION notify_deleted" + table + \
"() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_notify('deleted" + \
table + "', row_to_json(OLD)::text); RETURN NULL; END; $$;"
create_trigger_on_deletes = "CREATE TRIGGER deleted" + table + \
"_trigger AFTER DELETE ON " + table + \
" FOR EACH ROW EXECUTE PROCEDURE notify_deleted" + table + "();"
try:
apfell_db.execute_sql(create_function_on_deletes)
apfell_db.execute_sql(create_trigger_on_deletes)
except Exception as e:
print(e)
def pg_register_updatedcallback():
create_function_on_callback_changes = """
DROP FUNCTION IF EXISTS notify_updatedcallback() cascade;
CREATE FUNCTION notify_updatedcallback() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_notify('updatedcallback', row_to_json(NEW)::text); RETURN NULL; END; $$;
"""
create_trigger_on_callback_changes = """
CREATE TRIGGER updatedcallback_trigger AFTER UPDATE ON callback FOR EACH ROW EXECUTE PROCEDURE notify_updatedcallback();
"""
try:
apfell_db.execute_sql(create_function_on_callback_changes)
except Exception as e:
print(e)
try:
apfell_db.execute_sql(create_trigger_on_callback_changes)
except Exception as e:
print(e)
def pg_register_updates():
updates = ['callback', 'task', 'response', 'payload', 'c2profile', 'operator', 'operation', 'payloadtype',
'command', 'operatoroperation', 'payloadtypec2profile']
for table in updates:
create_function_on_changes = "DROP FUNCTION IF EXISTS notify_updated" + table + "() cascade;" + \
"CREATE FUNCTION notify_updated" + table + \
"() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_notify('updated" + \
table + "', row_to_json(NEW)::text); RETURN NULL; END; $$;"
create_trigger_on_changes = "CREATE TRIGGER updated" + table + \
"_trigger AFTER UPDATE ON " + table + \
" FOR EACH ROW EXECUTE PROCEDURE notify_updated" + table + "();"
try:
apfell_db.execute_sql(create_function_on_changes)
apfell_db.execute_sql(create_trigger_on_changes)
except Exception as e:
print(e)
def pg_register_newinserts():
inserts = ['callback', 'task', 'payload', 'c2profile', 'operator', 'operation', 'payloadtype',
'command', 'operatoroperation', 'payloadtypec2profile', 'filemeta']
for table in inserts:
create_function_on_insert = "DROP FUNCTION IF EXISTS notify_new" + table + "() cascade;" + \
"CREATE FUNCTION notify_new" + table + \
"() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_notify('new" + table + \
"', row_to_json(NEW)::text); RETURN NULL; END; $$;"
create_trigger_on_insert = "CREATE TRIGGER new" + table + \
"_trigger AFTER INSERT ON " + table + " FOR EACH ROW EXECUTE PROCEDURE notify_new" + \
table + "();"
try:
apfell_db.execute_sql(create_function_on_insert)
apfell_db.execute_sql(create_trigger_on_insert)
except Exception as e:
print(e)
def pg_register_newtask():
create_function_on_task_changes = """
DROP FUNCTION IF EXISTS notify_newtask() cascade;
CREATE FUNCTION notify_newtask() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_notify('newtask', row_to_json(NEW)::text);RETURN NULL;END;$$;
"""
create_trigger_on_task_changes = """
CREATE TRIGGER newtask_trigger AFTER INSERT ON task FOR EACH ROW EXECUTE PROCEDURE notify_newtask();
"""
# await db_objects.execute(notify_on_callback_changes)
try:
apfell_db.execute_sql(create_function_on_task_changes)
except Exception as e:
print(e)
try:
apfell_db.execute_sql(create_trigger_on_task_changes)
except Exception as e:
print(e)
def pg_register_updates():
updates = ['callback', 'task', 'response', 'payload', 'c2profile', 'operator', 'operation', 'payloadtype',
'command', 'operatoroperation', 'payloadtypec2profile', 'filemeta', 'payloadcommand',
'attack', 'credential', 'keylog', 'commandparameters', 'transform', 'loadedcommands',
'commandtransform', 'attackcommand', 'attacktask', 'artifact', 'artifacttemplate', 'taskartifact']
for table in updates:
create_function_on_changes = "DROP FUNCTION IF EXISTS notify_updated" + table + "() cascade;" + \
"CREATE FUNCTION notify_updated" + table + \
"() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_notify('updated" + \
table + "', NEW.id::text); RETURN NULL; END; $$;"
create_trigger_on_changes = "CREATE TRIGGER updated" + table + \
"_trigger AFTER UPDATE ON " + table + \
" FOR EACH ROW EXECUTE PROCEDURE notify_updated" + table + "();"
try:
apfell_db.execute_sql(create_function_on_changes)
apfell_db.execute_sql(create_trigger_on_changes)
except Exception as e:
print(e)
def pg_register_newresponse():
# https://stackoverflow.com/questions/25435669/fire-trigger-on-update-of-columna-or-columnb-or-columnc
create_function_on_response_changes = """
DROP FUNCTION IF EXISTS notify_newresponse() cascade;
CREATE FUNCTION notify_newresponse() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_notify('newresponse', row_to_json(NEW)::text);RETURN NULL;END;$$;
"""
create_trigger_on_response_changes = """
CREATE TRIGGER newresponse_trigger AFTER INSERT ON response FOR EACH ROW EXECUTE PROCEDURE notify_newresponse();
"""
# await db_objects.execute(notify_on_callback_changes)
try:
apfell_db.execute_sql(create_function_on_response_changes)
except Exception as e:
print(e)
try:
apfell_db.execute_sql(create_trigger_on_response_changes)
except Exception as e:
print(e)
def setup():
current_time = str(datetime.datetime.now())
try:
# Create default apfell_admin
create_apfell_admin = "INSERT INTO operator (username, password, admin, last_login, creation_time, active)" + \
" VALUES ('apfell_admin', " + \
"'E3D5B5899BA81F553666C851A66BEF6F88FC9713F82939A52BC8D0C095EBA68E604B788347D489CC93A61599C6A37D0BE51EE706F405AF5D862947EF8C36A201', " + \
"True, DEFAULT, '" + current_time + "',True) ON CONFLICT (username) DO NOTHING;"
apfell_db.execute_sql(create_apfell_admin)
# Create 'default' operation
create_default_operation = "INSERT INTO operation (name, admin_id) VALUES ('default', " + \
"(SELECT id FROM operator WHERE username='******')) ON CONFLICT (name) DO NOTHING"
apfell_db.execute_sql(create_default_operation)
# Create default C2 profile
create_default_c2profile = "INSERT INTO c2profile (name, description, operator_id, " + \
"creation_time, running, operation_id) VALUES ('default', 'default RESTful C2 channel', " + \
"(SELECT id FROM operator WHERE username='******'), " + \
"'" + current_time + "',True," + \
"(SELECT id FROM operation WHERE name='default')) ON CONFLICT (name) DO NOTHING;"
apfell_db.execute_sql(create_default_c2profile)
# Create default payload types, only one supported by default right now
default_payload_types = ['apfell-jxa', 'apfell-app']
for ptype in default_payload_types:
create_payload_type = "INSERT INTO payloadtype (ptype, operator_id, creation_time) VALUES ('" + ptype + \
"', (SELECT id FROM operator WHERE username='******'), '" + current_time + \
"') ON CONFLICT (ptype) DO NOTHING"
apfell_db.execute_sql(create_payload_type)
# Add apfell_admin to the default operation
create_default_assignment = "INSERT INTO operatoroperation (operator_id, operation_id) VALUES (" + \
"(SELECT id FROM operator WHERE username='******')," + \
"(SELECT id FROM operation WHERE name='default')) ON CONFLICT (operator_id, operation_id) DO NOTHING"
apfell_db.execute_sql(create_default_assignment)
# Add default commands to default profiles
# one manual example for now, but need an easier way to automate this
# Add default payload_type and c2_profile mapping
for ptype in default_payload_types:
create_ptype_c2_mappings = "INSERT INTO payloadtypec2profile (payload_type_id, c2_profile_id) VALUES (" + \
"(SELECT id FROM payloadtype WHERE ptype='" + ptype + "')," + \
"(SELECT id FROM c2profile WHERE name='default')) ON CONFLICT (payload_type_id, c2_profile_id) DO NOTHING"
apfell_db.execute_sql(create_ptype_c2_mappings)
# Create default commands that are associated with payloadtypes
file = open('./app/templates/default_commands.json', 'r')
command_file = json.load(file)
for cmd_group in command_file['payload_types']:
for cmd in cmd_group['commands']:
create_cmd = "INSERT INTO command (cmd, needs_admin, description, help_cmd, payload_type_id, operator_id, creation_time) " + \
"VALUES ('" + cmd['cmd'] + "', " + cmd['needs_admin'] + ", '" + cmd['description'].replace("'", "''") + "', '" + \
cmd['help'] + "', (SELECT id FROM payloadtype WHERE ptype='" + cmd_group['name'] + "')," + \
"(SELECT id FROM operator WHERE username='******'), '" + current_time + "') ON CONFLICT " + \
"(cmd, payload_type_id) DO NOTHING"
apfell_db.execute_sql(create_cmd)
file.close()
except Exception as e:
print(e)
