Agent connection not working due to database error

[lospoelos]

Hi team, I upgraded my Faraday server from 4.3.1 to 4.3.3. After the upgrade, I am not able to add new agents. The following error shows in faraday-server.log:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column agent_schedule.vuln_tag does not exist
LINE 1: ...olve_hostname AS agent_schedule_resolve_hostname, agent_sche...

I ran faraday-manage migrate after upgrading the server but this did not help. The agent reports that it's connecting fine:

faraday-dispatcher run --token 680250
2023-03-08 10:03:12,220 - faraday_agent_dispatcher - INFO {MainThread} [dispatcher.py:200 - register()]  Registered successfully
2023-03-08 10:03:12,230 - faraday_agent_dispatcher - INFO {MainThread} [dispatcher.py:242 - connect()]  Connection to Faraday server succeeded

[ezk06eer]

Hi @lospoelos,

could you do?

faraday-manage migrate --downgrade

faraday-manage migrate

and share the output to us?

Example for the connection on 4.3.3:

faraday server log:

faraday_app    | 2023-03-08T14:38:29+0000 - faraday.server.app - INFO {PoolThread-twisted.internet.reactor-0} [pid:104] [app.py:305 - user_logged_in_successful()]  User [faraday] logged in from IP [172.18.0.1] at [2023-03-08 14:38:29.192959]
faraday_app    | 2023-03-08T14:38:35+0000 - faraday.server.api.base - INFO {PoolThread-twisted.internet.reactor-0} [pid:104] [base.py:1226 - _perform_create()]  <Workspace 1> created
faraday_app    | 2023-03-08T14:47:20+0000 - faraday.server.api.base - INFO {PoolThread-twisted.internet.reactor-1} [pid:104] [base.py:1226 - _perform_create()]  Agent agent-new created
faraday_app    | 2023-03-08T14:47:20+0000 - faraday.server.websocket_factories - INFO {MainThread} [pid:104] [websocket_factories.py:232 - join_agent()]  Agent agent-new id 1 joined!

faraday dispatcher log:

(faraday-dispatcher) ➜  downloads cp dispatcher.yaml $HOME/.faraday/config/
(faraday-dispatcher) ➜  downloads faraday-dispatcher run --token=258544
2023-03-08 11:47:20,036 - faraday_agent_dispatcher - INFO {MainThread} [dispatcher.py:160 - register()]  token_registration_url: http://localhost:5985/_api/v3/agents
2023-03-08 11:47:20,118 - faraday_agent_dispatcher - INFO {MainThread} [dispatcher.py:200 - register()]  Registered successfully
2023-03-08 11:47:20,140 - faraday_agent_dispatcher - INFO {MainThread} [dispatcher.py:242 - connect()]  Connection to Faraday server succeeded

[lospoelos]

Hi @ezk06eer , It does not seem to like the downgrade option:

faraday-manage --downgrade
Usage: faraday-manage [OPTIONS] COMMAND [ARGS]...
Try 'faraday-manage -h' for help.

Error: No such option: --downgrade

[ezk06eer]

@lospoelos missed the option faraday-manage migrate --downgrade

[lospoelos]

@ezk06eer The faraday-manage commands now ran successfully.

# faraday-manage migrate --downgrade
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 1e95dde5b9c8 -> f20aa8756612, cascade on KB
# faraday-manage migrate
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade f20aa8756612 -> 1e95dde5b9c8, cascade on KB

However, it did not fix the issue. The agent connects but after refreshing the agent browser windows, it fails:

2023-03-09T09:20:20+0000 - faraday.server.websocket_factories - INFO {MainThread} [pid:202107] [websocket_factories.py:232 - join_agent()]  Agent ichabod id 6 joined!
2023-03-09T09:20:27+0000 - faraday.server.app - ERROR {PoolThread-twisted.internet.reactor-0} [pid:202107] [app.py:1891 - log_exception()]  Exception on /v3/agents [GET]
Traceback (most recent call last):
  File "/nix/store/6zam6rp8fhadbfig79l9vrxgd2w7lzzw-python3.8-SQLAlchemy-1.3.19/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/nix/store/6zam6rp8fhadbfig79l9vrxgd2w7lzzw-python3.8-SQLAlchemy-1.3.19/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column agent_schedule.vuln_tag does not exist
LINE 1: ...olve_hostname AS agent_schedule_resolve_hostname, agent_sche...
                                                             ^

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column agent_schedule.vuln_tag does not exist
LINE 1: ...olve_hostname AS agent_schedule_resolve_hostname, agent_sche...
                                                             ^

[SQL: SELECT agent_schedule.create_date AS agent_schedule_create_date, agent_schedule.update_date AS agent_schedule_update_date, agent_schedule.id AS agent_schedule_id, agent_schedule.description AS agent_schedule_description, agent_schedule.crontab AS agent_schedule_crontab, agent_schedule.timezone AS agent_schedule_timezone, agent_schedule.active AS agent_schedule_active, agent_schedule.last_run AS agent_schedule_last_run, agent_schedule.executor_id AS agent_schedule_executor_id, agent_schedule.ignore_info AS agent_schedule_ignore_info, agent_schedule.resolve_hostname AS agent_schedule_resolve_hostname, agent_schedule.vuln_tag AS agent_schedule_vuln_tag, agent_schedule.service_tag AS agent_schedule_service_tag, agent_schedule.host_tag AS agent_schedule_host_tag, agent_schedule.parameters AS agent_schedule_parameters, agent_schedule.creator_id AS agent_schedule_creator_id, agent_schedule.update_user_id AS agent_schedule_update_user_id
FROM agent_schedule
WHERE %(param_1)s = agent_schedule.executor_id]
[parameters: {'param_1': 10}]
(Background on this error at: http://sqlalche.me/e/13/f405)

[ezk06eer]

@lospoelos There is a way but it might cause lose of information, if you dont have anything you will miss from your instance, you can do a clean install of the db. Or you can go 10 downgrades and the apply the migrate command.

So, to start from scratch go over the user that has privilege on your db and run:

This will create a new db of faraday you will lose all the info in faraday!!!

sudo su - postgres && dropdb faraday && dropuser faraday_postgresql
faraday-manage init-db

if you want to fix this issue in concrete but you will lose some cwe and cve information + some tag info and relationships in some vulns with referecences.

Command to fix the migration jumped:

faraday-manage migrate --downgrade -10
faraday-manage migrate

Cheers!

[lospoelos]

Thanks @ezk06eer . Nuking the database and reinitialising did the trick. All good now. Thank you for you help.