query vpn connections from database

2 files changed, 51 insertions, 13 deletions

diff --git a/src/hourly.py b/src/hourly.py
index 543ed20..c11f3f0 100755
--- a/src/hourly.py
+++ b/src/hourly.py
@@ -2,19 +2,56 @@
 
 from sys import argv
 import subprocess
+import os.path
+import yaml
+import psycopg2
 
 wrapper = '/var/lib/0tdns/vpn_wrapper.sh'
 perform_queries = '/var/lib/0tdns/perform_queries.py'
+db_config_path = '/etc/0tdns/db_connection_config.yml'
 
-def get_vpn_connections(hour):
-    # TODO query database for the necessary information,
-    # for now, return some sample though-up data
-    return (
-        # vpn_id | config_path
-        (14,       "./vpngate_178.254.251.12_udp_1195.ovpn"),
-        (13,       "./vpngate_public-vpn-229.opengw.net_tcp_443.ovpn")
-    )
+def sync_ovpn_config(cursor, vpn_id, config_path, config_hash):
+    cursor.execute('''
+    select ovpn_config
+    from vpn
+    where id = %s and ovpn_config_sha256 = %s
+    ''', (vpn_id, config_hash))
+    
+    (config_contents,) = cursor.fetchone()
+    
+    with open(config_path, "w") as config_file:
+        config_file.write(config_contents)
+
+def get_vpn_connections(cursor, hour):
+    # return (
+    #     # vpn_id | config_path
+    #     (14,       "./vpngate_178.254.251.12_udp_1195.ovpn"),
+    #     (13,       "./vpngate_public-vpn-229.opengw.net_tcp_443.ovpn")
+    # )
+    cursor.execute('''
+    select v.id, v.ovpn_config_sha256
+    from queries as q join vpn as v
+    on v.id = q.vpn_id;
+    ''')
+    return cursor.fetchall()
+
+config = yaml.safe_load(open(db_config_path, 'r'))
+connection = psycopg2.connect(user=config['user'], password=config['password'],
+                              host=config['host'], port=config['port'],
+                              database=config['database'])
+cursor = connection.cursor()
 
 hour = argv[1]
-for vpn_id, config_path in get_vpn_connections(hour):
-    subprocess.run([wrapper, config_path, perform_queries, hour])
+vpns = get_vpn_connections(cursor, hour)
+
+for vpn_id, config_hash in vpns:
+    config_path = "/var/lib/0tdns/{}.ovpn".format(config_hash)
+    if not os.path.isfile(config_path):
+        sync_ovpn_config(cursor, vpn_id, config_path, config_hash)
+
+cursor.close()
+connection.close()
+
+for vpn_id, config_hash in vpns:
+    config_path = "/var/lib/0tdns/{}.ovpn".format(config_hash)
+    subprocess.run([wrapper, config_path, perform_queries, hour, vpn_id])
diff --git a/src/perform_queries.py b/src/perform_queries.py
index 4762e14..e9c94ec 100755
--- a/src/perform_queries.py
+++ b/src/perform_queries.py
@@ -1,5 +1,6 @@
 #!/bin/python3
 
+from sys import argv
 import unbound
 
 def query_planned_queries(hour, vpn_id):
@@ -22,9 +23,9 @@ def resolve_call_back(mydata, status, result):
     if status==0 and result.havedata:
         print("Result:",result.data.address_list)
 
-#                                                       hour from argv    | vpn_id in database
 contexts = []
-for dns_addr, dns_id, services in query_planned_queries("1999-01-08 04:00", 11):
+#                                                       hour from argv    | vpn_id in database
+for dns_addr, dns_id, services in query_planned_queries(argv[1], argv[2]):
     ctx = unbound.ub_ctx()
     ctx.set_fwd(dns_addr)
     for service_id, service_name in services:
@@ -34,6 +35,6 @@ for dns_addr, dns_id, services in query_planned_queries("1999-01-08 04:00", 11):
                           resolve_call_back,
                           unbound.RR_TYPE_A, unbound.RR_CLASS_IN)
     contexts.append(ctx)
-    
+
 for ctx in contexts:
     ctx.wait()