Update API to get details from postgres

[mzfr]

I have added the support for the API to work with Postgres. All the endpoints are working similarly to the way they were working with Redis. I haven't tested the tannerweb but I am assuming that if API is working then the web part would still be fine(I'll test tanner web and make sure nothing breaks in it).

Now the implementation might look sloppy because I used 4 different queries to extract the information rather than execute single query with multiple joins on the table. The reason I didn't use a single query with joins is that the data returned was weird[1].

[1] It returned a very large list(let's call it L) that had multiple lists in them and each small list was actually a row. Now the problem was that say if we have 5 cookies(key and value) so the first 5 lists of L have the same values except 1 different value(the cookie).

Sorry if that explanation was confusing :)

[afeena]

@mzfr Having 4 queries is ok, we just have to think how to do it in the most efficient way

I also think, maybe we can redesign our API according to our data model

[mzfr]

@afeena please test the changes that I pushed. I think there is still something wrong with the time.

[afeena]

@mzfr If you think something wrong with a time - test it with multiple cases. Please, test you code as much as you can before you push it.

[afeena]

@mzfr the bug still exists and I can't write all the paths into the postgres

{'path': '/%2F..%2F..%2F..%2F..%2F..%2F..%2F..%2F..%2F..%2F..%2Fetc%2Fpasswd', 'timestamp': 1593346931.2393837, 'response_status': 200}
Traceback (most recent call last):
  File "/usr/local/bin/tanner", line 4, in <module>
    __import__('pkg_resources').run_script('Tanner==0.6.0', 'tanner')
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 666, in run_script
    self.require(requires)[0].run_script(script_name, ns)
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 1453, in run_script
    exec(script_code, namespace, namespace)
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/EGG-INFO/scripts/tanner", line 35, in <module>
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/EGG-INFO/scripts/tanner", line 31, in main
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/server.py", line 157, in start
  File "/usr/local/lib64/python3.7/site-packages/aiohttp/web.py", line 124, in run_app
    loop.run_until_complete(runner.cleanup())
  File "uvloop/loop.pyx", line 1456, in uvloop.loop.Loop.run_until_complete
  File "/usr/local/lib64/python3.7/site-packages/aiohttp/web_runner.py", line 196, in cleanup
    await site.stop()
  File "/usr/local/lib64/python3.7/site-packages/aiohttp/web_runner.py", line 54, in stop
    await self._runner.shutdown()
  File "/usr/local/lib64/python3.7/site-packages/aiohttp/web_runner.py", line 269, in shutdown
    await self._app.shutdown()
  File "/usr/local/lib64/python3.7/site-packages/aiohttp/web_app.py", line 310, in shutdown
    await self.on_shutdown.send(self)
  File "/usr/local/lib64/python3.7/site-packages/aiohttp/signals.py", line 35, in send
    await receiver(*args, **kwargs)  # type: ignore
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/server.py", line 106, in on_shutdown
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/sessions/session_manager.py", line 83, in delete_sessions_on_shutdown
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/sessions/session_manager.py", line 98, in delete_session
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/sessions/session_analyzer.py", line 32, in analyze
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/sessions/session_analyzer.py", line 39, in save_session
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/dbutils.py", line 152, in add_analyzed_data
KeyError: 'attack_type'

[mzfr]

@afeena It's weird because I used nikto as well as zaproxy and I got equal number of accepted paths and counts in the paths table.

[afeena]

API return wrong number of sessions (it return the number of paths instead):

tanner=# select count(*) from sessions where sensor_id='776edef4-ec5f-4fe0-8485-02cf9ff1f64d';
 count
-------
    51
(1 row)

snare-stats/776edef4-ec5f-4fe0-8485-02cf9ff1f64d {"version": 1, "response": {"message": {"total_sessions": 27297, "total_duration": "3:24:26", "attack_frequency": {"cmd_exec": 29, "rfi": 3217, "sqli": 18, "lfi": 486, "unknown": 29, "index": 22797, "xss": 721}}}}

Filters doesn't seem to work

tanner=# select DISTINCT session_id from paths where attack_type=3;
              session_id
--------------------------------------
 ecc205a9-4922-43b8-a264-a3a53d34d31f
 8cc1de64-8cc3-4688-84ac-f26c6d46616a
 6d033415-ede3-40d3-8ece-172bf98629d2
 88cf8d13-a898-41cb-b858-050a45878740
 3f8010a0-8dd5-4e97-bf4f-bfc99624c595
(5 rows)

sessions?filters=attack_type:3
{"version": 1, "response": {"message": ["45e54cde-87c9-48a2-920e-d5cd9d36814b", "98f833a5-d640-49a0-98fb-d839dee4d24a", "37b62750-29e1-410d-9b30-85e08056abe7", "82000c56-4dd9-423d-b3cc-955637754ed3", "50a00106-a756-41cb-a82b-a9f7b5f8cb24", "ecc205a9-4922-43b8-a264-a3a53d34d31f", "3f8010a0-8dd5-4e97-bf4f-bfc99624c595", "1f2d3d91-a028-4fe1-9ac7-e1f39c3bdf57", "70badc0d-41ca-40dd-bd90-ce465d4fd02d", "afc81193-4b75-40a6-8d89-e3310b638c1c", "c0a202e3-ca8f-4836-ac90-e38a6ba572e0", "6ed53711-6e46-4e44-b3d8-07a7df1fc707", "a0ccf563-b3c3-4751-948c-a3aa1b612c41", "2aae8a4a-5f2a-46b6-a3a1-d4c10d37be38", "19269077-9508-4f94-ac44-4b8344d403f7", "22d7d7d4-80bf-46db-9d44-1b98d2985762", "6d033415-ede3-40d3-8ece-172bf98629d2", "78e9ed38-7b0a-4363-b0cf-0c72021c3740", "d3a37835-d178-452f-81ea-c84153d7dde2", "7b7830c3-38db-46d3-8275-49c336ee13d7", "ee289be7-b2c2-4e35-84e6-bef44b09e422", "c5f058c2-7e6a-4d9d-a0d6-dacca09151e9", "bf9d4149-5575-4ccb-bb50-7b808fb1754f", "1dae4145-fbc8-41e9-bd00-80be3031a0e2", "ff55c430-02d5-4c3f-aa1c-0aac7c43b83d", "7ac3629b-c87a-428c-be17-e459188b839f", "359a395e-cc7e-4189-97cc-7a3a188967ef", "1c858a7d-345e-4c6d-97c5-55029a247fd6", "88cf8d13-a898-41cb-b858-050a45878740", "2cdc519e-ff8d-4d7c-b861-87a5867620cd", "0cf99520-3f8f-4c3b-a6f8-850ec9da49b2", "25205507-604d-4b0a-84a0-0850f309f1ff", "437f3e77-5e89-450e-b21a-b847e26acaa0", "3bbb07f8-c0f5-4f74-89ef-c208d42a7bf8", "b8d66abb-0434-4475-a92d-148865e2d655", "8238c237-e331-4d2f-a5a0-637d5ba13df0", "df8d8a22-ddaf-481b-8f80-92a55181d5ee", "a1894717-bf39-4f67-8493-66debde31e0b", "f27f9d52-68e8-443d-9383-8d5a36b54aab", "45c14423-d5cf-47d4-9289-877642af2aa6", "8cd32c85-22bf-47ed-aec6-96db37dead8f", "027427fb-a5d6-4ae5-a884-6bc7c72b4efe", "8d777579-0b42-42d3-9561-08bd7a894af4", "2e3a2fba-c674-44f7-b910-c217e93f0e1b", "6fea642d-70dd-46bb-a671-a46230e8b4ac", "2837cd3a-d766-4cd2-a954-362ba2a57352", "d4ea1e03-0110-499f-a650-b9355cc05130", "2b1882b5-fe1c-4c4b-b951-f1216825c377", "9d7b1639-0d87-485f-bcbe-7af9b628715d", "8cc1de64-8cc3-4688-84ac-f26c6d46616a", "0b298a9d-d9f0-40a3-a94f-1da5f0f34030"]}}

Error 500 when using name of the attack

sessions?filters=attack_type:lfi

Traceback (most recent call last):
  File "/usr/local/lib64/python3.7/site-packages/aiohttp/web_protocol.py", line 390, in start
    resp = await self._request_handler(request)
  File "/usr/local/lib64/python3.7/site-packages/aiohttp/web_app.py", line 366, in _handle
    resp = await handler(request)
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/api/server.py", line 75, in handle_sessions
    sessions = await self.api.return_sessions(applied_filters)
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/api/api.py", line 196, in return_sessions
    query = await (await conn.execute(stmt)).fetchall()
  File "/usr/local/lib/python3.7/site-packages/aiopg/sa/connection.py", line 83, in _execute
    await cursor.execute(query, dp)
  File "/usr/local/lib/python3.7/site-packages/aiopg/cursor.py", line 113, in execute
    await self._conn._poll(waiter, timeout)
  File "/usr/local/lib/python3.7/site-packages/aiopg/connection.py", line 207, in _poll
    await asyncio.wait_for(self._waiter, timeout, loop=self._loop)
  File "/usr/lib64/python3.7/asyncio/tasks.py", line 442, in wait_for
    return fut.result()
  File "/usr/local/lib/python3.7/site-packages/aiopg/connection.py", line 106, in _ready
    state = self._conn.poll()
psycopg2.errors.UndefinedColumn: column "lfi" does not exist
LINE 1: ...'776edef4-ec5f-4fe0-8485-02cf9ff1f64d' AND P.attack_type=lfi

[afeena]

[root@ tanner]# tannerweb --config ../tanner.yaml
======== Running on http://0.0.0.0:8091 ========
(Press CTRL+C to quit)
Error handling request
Traceback (most recent call last):
  File "/usr/local/lib64/python3.7/site-packages/aiohttp/web_protocol.py", line 390, in start
    resp = await self._request_handler(request)
  File "/usr/local/lib64/python3.7/site-packages/aiohttp/web_app.py", line 366, in _handle
    resp = await handler(request)
  File "/usr/local/lib/python3.7/site-packages/aiohttp_jinja2/__init__.py", line 91, in wrapped
    context = await coro(*args)
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/web/server.py", line 21, in handle_index
    snares = await self.api.return_snares()
  File "/usr/local/lib/python3.7/site-packages/Tanner-0.6.0-py3.7.egg/tanner/api/api.py", line 41, in return_snares
    async with self.pg_client.acquire() as conn:
AttributeError: 'Redis' object has no attribute 'acquire

[afeena]

@mzfr Works good now :)