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)