[server] Support loading/saving preset network rules

5 files changed, 188 insertions, 17 deletions

diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java
index d82e1279..df75e64c 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java
@@ -20,6 +20,7 @@ public class Updater {
 		addPredefinedFilters();
 		addPredefinedNetworkShares();
 		addPredefinedRunScripts();
+		addPredefinedNetworkRules();
 	}
 
 	private static void addLectureLocationMapTable() throws SQLException {
@@ -318,6 +319,45 @@ public class Updater {
 		}
 	}
 	
+	private static void addPredefinedNetworkRules() throws SQLException {
+		try (MysqlConnection connection = Database.getConnection()) {
+			if (!tableExists(connection, "presetnetworkrule")) {
+				MysqlStatement addStmt = connection.prepareStatement("CREATE TABLE `presetnetworkrule` ("
+						+ "  `ruleid` int(11) NOT NULL AUTO_INCREMENT,"
+						+ "  `rulename` varchar(100) NOT NULL,"
+						+ "  `ruledata` text NOT NULL,"
+						+ "  PRIMARY KEY (`ruleid`),"
+						+ "  KEY rulename (`rulename`)"
+						+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
+				addStmt.executeUpdate();
+				LOGGER.info("Updated database: Added preset network rules");
+			}
+			if (!tableExists(connection, "lecture_x_networkrule")) {
+				connection.prepareStatement("CREATE TABLE lecture_x_networkrule ("
+						+ "  lectureid char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,"
+						+ "  ruleid int(11) NOT NULL,"
+						+ "  PRIMARY KEY (lectureid,ruleid),"
+						+ "  KEY ruleid (ruleid)"
+						+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci")
+						.executeUpdate();
+				connection.prepareStatement(
+						"ALTER TABLE lecture_x_networkrule"
+								+ "  ADD CONSTRAINT lecture_x_networkrule_ibfk_1 FOREIGN KEY (lectureid)"
+								+ "    REFERENCES lecture (lectureid)"
+								+ "    ON DELETE CASCADE ON UPDATE CASCADE,"
+								+ "  ADD CONSTRAINT lecture_x_networkrule_ibfk_2 FOREIGN KEY (ruleid)"
+								+ "    REFERENCES presetnetworkrule (ruleid)"
+								+ "    ON DELETE CASCADE ON UPDATE CASCADE")
+								.executeUpdate();
+				LOGGER.info("Updated database: Created lecture_x_networkrule table + constraint");
+			}
+			connection.commit();
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in Updater.addPredefinedNetworkShares()", e);
+			throw e;
+		}
+	}
+	
 	/*
 	 * Helper methods
 	 */
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java
index a51a168a..9bae8009 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java
@@ -156,6 +156,9 @@ public class DbLecture {
 		if (lecture.isSetPresetScriptIds()) {
 			DbRunScript.writeLectureRunScripts(connection, lectureId, lecture.presetScriptIds);
 		}
+		if (lecture.isSetPresetNetworkExceptionIds()) {
+			DbLectureNetworkRules.writeLectureNetworkExceptions(connection, lectureId, lecture.presetNetworkExceptionIds);
+		}
 		stmt.executeUpdate();
 	}
 
@@ -337,6 +340,7 @@ public class DbLecture {
 			lecture.setRunscript(rs.getString("runscript"));
 			lecture.setNics(null); // TODO fill nics
 			lecture.setNetworkExceptions(decodeNetrules(rs.getString("netrules")));
+			lecture.setPresetNetworkExceptionIds(DbLectureNetworkRules.getForEdit(connection, lectureId));
 			lecture.setIsExam(rs.getBoolean("isexam"));
 			lecture.setHasInternetAccess(rs.getBoolean("hasinternetaccess"));
 			lecture.setHasUsbAccess(rs.getBoolean("hasusbaccess"));
@@ -609,6 +613,29 @@ public class DbLecture {
 		return retval;
 	}
 
+	public static boolean getFirewallRules(String lectureId, List<NetRule> list) throws SQLException, TNotFoundException {
+		try (MysqlConnection connection = Database.getConnection()) {
+			MysqlStatement stmt = connection.prepareStatement("SELECT netrules, hasinternetaccess FROM lecture"
+					+ " WHERE lectureid = :lectureid");
+			stmt.setString("lectureid", lectureId);
+			ResultSet rs = stmt.executeQuery();
+			if (!rs.next())
+				throw new TNotFoundException();
+			NetRule[] json = Json.deserialize(rs.getString("netrules"), NetRule[].class);
+			if (json != null && json.length != 0) {
+				list.addAll(Arrays.asList(json));
+			}
+			List<NetRule> others = DbLectureNetworkRules.getForStartup(connection, lectureId);
+			if (others != null) {
+				list.addAll(others);
+			}
+			return rs.getBoolean("hasinternetaccess");
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in getClientLaunchNetworkExceptions()", e);
+			throw e;
+		}
+	}
+
 	public static void deleteOld(int minAgeDays) throws SQLException {
 		try (MysqlConnection connection = Database.getConnection()) {
 			MysqlStatement stmt = connection.prepareStatement("DELETE FROM lecture WHERE endtime < :cutoff");
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLectureNetworkRules.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLectureNetworkRules.java
new file mode 100644
index 00000000..61b0c50d
--- /dev/null
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLectureNetworkRules.java
@@ -0,0 +1,98 @@
+package org.openslx.bwlp.sat.database.mappers;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+
+import org.apache.log4j.Logger;
+import org.openslx.bwlp.sat.database.Database;
+import org.openslx.bwlp.sat.database.MysqlConnection;
+import org.openslx.bwlp.sat.database.MysqlStatement;
+import org.openslx.bwlp.thrift.iface.NetRule;
+import org.openslx.bwlp.thrift.iface.PresetNetRule;
+import org.openslx.util.Json;
+
+public class DbLectureNetworkRules {
+	
+	private static final Logger LOGGER = Logger.getLogger(DbLectureNetworkRules.class);
+
+	public static void writeLectureNetworkExceptions(MysqlConnection connection, String lectureId,
+			List<Integer> ruleIds) throws SQLException {
+		if (lectureId == null || lectureId.isEmpty()) {
+			return;
+		}
+		MysqlStatement delStmt = connection
+				.prepareStatement("DELETE FROM lecture_x_networkrule WHERE lectureid = :lectureid");
+		delStmt.setString("lectureid", lectureId);
+		delStmt.executeUpdate();
+		if (ruleIds == null || ruleIds.isEmpty()) {
+			return;
+		}
+		MysqlStatement addStmt = connection
+				.prepareStatement("INSERT IGNORE INTO lecture_x_networkrule (lectureid, ruleid)"
+						+ " VALUES (:lectureid, :ruleid)");
+		addStmt.setString("lectureid", lectureId);
+		for (int ruleId : ruleIds) {
+			addStmt.setInt("ruleid", ruleId);
+			addStmt.executeUpdate();
+		}
+	}
+
+	static List<NetRule> getForStartup(MysqlConnection connection, String lectureId) throws SQLException {
+		MysqlStatement stmt = connection.prepareStatement("SELECT p.ruledata FROM presetnetworkrule p"
+				+ " INNER JOIN `lecture_x_networkrule` lxr"
+				+ " ON (lxr.lectureid = :lectureid AND lxr.ruleid = p.ruleid)");
+		stmt.setString("lectureid", lectureId);
+		ResultSet rs = stmt.executeQuery();
+		List<NetRule> result = new ArrayList<>();
+		while (rs.next()) {
+			NetRule[] lst = Json.deserialize(rs.getString("ruledata"), NetRule[].class);
+			if (lst == null)
+				continue;
+			for (NetRule r : lst) {
+				result.add(r);
+			}
+		}
+		return result;
+	}
+	
+	public static List<Integer> getForEdit(MysqlConnection connection, String lectureId) throws SQLException
+	{
+		MysqlStatement stmt = connection.prepareStatement("SELECT ruleid FROM lecture_x_networkrule"
+				+ " WHERE lectureid = :lectureid");
+		stmt.setString("lectureid", lectureId);
+		ResultSet rs = stmt.executeQuery();
+		List<Integer> result = new ArrayList<>();
+		while (rs.next()) {
+			result.add(rs.getInt("ruleid"));
+		}
+		return result;
+	}
+
+	public static List<PresetNetRule> getPredefined() throws SQLException {
+		try (MysqlConnection connection = Database.getConnection()) {
+			List<PresetNetRule> list = new ArrayList<>();
+			MysqlStatement stmt = connection
+					.prepareStatement("SELECT ruleid, rulename, ruledata"
+							+ " FROM presetnetworkrule");
+			ResultSet rs = stmt.executeQuery();
+			while (rs.next()) {
+				NetRule[] ruleArray = Json.deserialize(rs.getString("ruledata"), NetRule[].class);
+				List<NetRule> rules;
+				if (ruleArray == null || ruleArray.length == 0) {
+					rules = new ArrayList<>(0);
+				} else {
+					rules = Arrays.asList(ruleArray);
+				}
+				list.add(new PresetNetRule(rs.getInt("ruleid"), rs.getString("rulename"), rules));
+			}
+			return list;
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in getPredefinedNetshares()", e);
+			throw e;
+		}
+	}
+
+}
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/thrift/ServerHandler.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/thrift/ServerHandler.java
index a818a9ae..36bbea46 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/thrift/ServerHandler.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/thrift/ServerHandler.java
@@ -17,6 +17,7 @@ import org.openslx.bwlp.sat.database.mappers.DbImagePermissions;
 import org.openslx.bwlp.sat.database.mappers.DbLecture;
 import org.openslx.bwlp.sat.database.mappers.DbLectureFilter;
 import org.openslx.bwlp.sat.database.mappers.DbLectureNetshare;
+import org.openslx.bwlp.sat.database.mappers.DbLectureNetworkRules;
 import org.openslx.bwlp.sat.database.mappers.DbLecturePermissions;
 import org.openslx.bwlp.sat.database.mappers.DbLocation;
 import org.openslx.bwlp.sat.database.mappers.DbLog;
@@ -811,6 +812,12 @@ public class ServerHandler implements SatelliteServer.Iface {
 			throw new TInvocationException(InvocationError.INTERNAL_SERVER_ERROR,
 					"Database failure when querying predefined run scripts.");
 		}
+		try {
+			data.networkExceptions = DbLectureNetworkRules.getPredefined();
+		} catch (SQLException e) {
+			throw new TInvocationException(InvocationError.INTERNAL_SERVER_ERROR,
+					"Database failure when querying predefined network rules.");
+		}
 		return data;
 	}
 
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/web/WebServer.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/web/WebServer.java
index 8d10271d..3e91cfc5 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/web/WebServer.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/web/WebServer.java
@@ -6,6 +6,7 @@ import java.io.PipedInputStream;
 import java.io.PipedOutputStream;
 import java.nio.charset.StandardCharsets;
 import java.sql.SQLException;
+import java.util.ArrayList;
 import java.util.List;
 import java.util.Map;
 import java.util.concurrent.LinkedBlockingQueue;
@@ -23,7 +24,6 @@ import org.openslx.bwlp.sat.database.mappers.DbLecture;
 import org.openslx.bwlp.sat.database.mappers.DbLecture.LaunchData;
 import org.openslx.bwlp.sat.database.mappers.DbLecture.RunScript;
 import org.openslx.bwlp.sat.fileserv.FileServer;
-import org.openslx.bwlp.thrift.iface.LectureRead;
 import org.openslx.bwlp.thrift.iface.NetRule;
 import org.openslx.bwlp.thrift.iface.NetShare;
 import org.openslx.bwlp.thrift.iface.NetShareAuth;
@@ -208,28 +208,27 @@ public class WebServer extends NanoHTTPD {
 	}
 	
 	private Response serveLectureNetRules(String lectureId) {
-		LectureRead lecture;
+		List<NetRule> list = new ArrayList<>();
+		boolean defaultAllowed;
 		try {
-			lecture = DbLecture.getLectureDetails(null, lectureId);
-		} catch (TNotFoundException e) {
-			return notFound();
+			defaultAllowed = DbLecture.getFirewallRules(lectureId, list);
 		} catch (SQLException e) {
 			return internalServerError();
+		} catch (TNotFoundException e) {
+			return notFound();
 		}
 		StringBuilder sb = new StringBuilder();
-		if (lecture.networkExceptions != null) {
-			for (NetRule rule : lecture.networkExceptions) {
-				sb.append(rule.direction.name());
-				sb.append(' ');
-				sb.append(rule.host);
-				sb.append(' ');
-				sb.append(rule.port);
-				sb.append(' ');
-				sb.append(lecture.hasInternetAccess ? "REJECT" : "ACCEPT");
-				sb.append('\n');
-			}
+		for (NetRule rule : list) {
+			sb.append(rule.direction.name());
+			sb.append(' ');
+			sb.append(rule.host);
+			sb.append(' ');
+			sb.append(rule.port);
+			sb.append(' ');
+			sb.append(defaultAllowed ? "REJECT" : "ACCEPT");
+			sb.append('\n');
 		}
-		if (lecture.hasInternetAccess) {
+		if (defaultAllowed) {
 			sb.append("IN * 0 ACCEPT\n");
 			sb.append("OUT * 0 ACCEPT\n");
 		} else {