From 919ab1dd863ffd6221827a9f6f9e93c19caef1fa Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Fri, 15 Jul 2022 15:22:03 +0200
Subject: progresssss

---
 html/maintenance.php | 54 ++++++++++++++++++++++++++++++++++++++++++++--------
 1 file changed, 46 insertions(+), 8 deletions(-)

diff --git a/html/maintenance.php b/html/maintenance.php
index 8b93f5f..3eaf640 100644
--- a/html/maintenance.php
+++ b/html/maintenance.php
@@ -6,25 +6,63 @@ if ($_SERVER['REMOTE_ADDR'] !== $_SERVER['SERVER_ADDR']) {
 
 $db = new SQLite3('/srv/http/vhosts/eckner.net/colocation.eckner.net/backend/sqlite.db');
 
-if (array_key_exists('new',$_GET)) {
+if (array_key_exists('drop',$_GET) && ($_GET['drop']=='DROP')) {
+  $db -> exec('DROP TABLE machines');
+  $db -> exec('DROP TABLE keys');
+  $db -> exec('DROP TABLE permissions');
+  $db -> exec('DROP TABLE granted_permissions');
+  $db -> exec('CREATE TABLE machines (id integer primary key AUTOINCREMENT, name varchar(32) not null unique, online int not null default 0, last_update TIMESTAMP default CURRENT_TIMESTAMP);');
+  $db -> exec('CREATE TABLE keys (id integer primary key AUTOINCREMENT, key varchar(128) not null unique);');
+  $db -> exec('CREATE TABLE permissions (id integer primary key AUTOINCREMENT, name varchar(32) not null unique)');
+  $db -> exec('CREATE TABLE granted_permissions (key_id integer not null, machine_id integer not null, permission_id integer not null)');
+  $stm = $db -> prepare('INSERT INTO permissions (name) VALUES (:permission)');
+  foreach (array('running', 'ping', 'power') as $permission) {
+    $stm -> bindValue('permission', $permission);
+    $stm -> execute();
+  }
+  echo 'ok' . "\n";
+  die();
+}
+
+if (array_key_exists('machine',$_GET)) {
   $stm = $db -> prepare('SELECT COUNT(1) FROM machines WHERE machines.name=:name');
-  $stm -> bindValue('name', $_GET['new']);
+  $stm -> bindValue('name', $_GET['machine']);
   $result = $stm -> execute();
   $result = $result -> fetchArray();
   $stm -> close();
 
   if ($result[0] == 0) {
-    $stm = $db -> prepare('INSERT INTO machines (name,key,online) VALUES (:name,:key,0)');
-    $stm -> bindValue('name', $_GET['new']);
+    $key = bin2hex(random_bytes(64));
+    $stm = $db -> prepare('INSERT INTO machines (name,online) VALUES (:machine,0)');
+    $stm -> bindValue('machine', $_GET['machine']);
+    $stm -> execute();
+    $machine_id = $db -> lastInsertRowID();
+    $stm -> close();
+
+    $stm = $db -> prepare('INSERT INTO keys (key) VALUES (:key)');
     $stm -> bindValue('key', bin2hex(random_bytes(64)));
     $stm -> execute();
+    $key_id = $db -> lastInsertRowID();
+    $stm -> close();
+
+    $stm = $db -> prepare(
+      'INSERT INTO granted_permissions (key_id, machine_id, permission_id) VALUES (:key,:machine,1)');
+    $stm -> bindValue('machine', $machine_id);
+    $stm -> bindValue('key', $key_id);
+    $stm -> execute();
     $stm -> close();
   }
 
-  $stm = $db -> prepare('SELECT machines.key FROM machines WHERE machines.name=:name');
-  $stm -> bindValue('name', $_GET['new']);
+  $stm = $db -> prepare(
+    'SELECT permissions.name, keys.key FROM machines' .
+    ' JOIN granted_permissions ON granted_permissions.machine_id = machines.id' .
+    ' JOIN permissions ON granted_permissions.permission_id = permissions.id' .
+    ' JOIN keys ON granted_permissions.key_id = keys.id' .
+    ' WHERE machines.name=:machine');
+  $stm -> bindValue('machine', $_GET['machine']);
   $result = $stm -> execute();
-  $result = $result -> fetchArray();
-  echo 'key = ' . $result['key'] . "\n";
+  while ($result = $result -> fetchArray()) {
+    echo 'permission = "' . $result['name'] . '", key = "' . $result['key'] . '"' . "\n";
+  }
   die();
 }
-- 
cgit v1.2.3-54-g00ecf