From 5094bd68c734f836cc437b66af4dbdfbfa5d033e Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Wed, 27 Feb 2019 16:17:00 +0100 Subject: [server] Support loading/saving preset network rules --- .../org/openslx/bwlp/sat/database/Updater.java | 40 +++++++++ .../bwlp/sat/database/mappers/DbLecture.java | 27 ++++++ .../database/mappers/DbLectureNetworkRules.java | 98 ++++++++++++++++++++++ .../org/openslx/bwlp/sat/thrift/ServerHandler.java | 7 ++ .../java/org/openslx/bwlp/sat/web/WebServer.java | 33 ++++---- 5 files changed, 188 insertions(+), 17 deletions(-) create mode 100644 dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLectureNetworkRules.java 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 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 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 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 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 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 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 result = new ArrayList<>(); + while (rs.next()) { + result.add(rs.getInt("ruleid")); + } + return result; + } + + public static List getPredefined() throws SQLException { + try (MysqlConnection connection = Database.getConnection()) { + List 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 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 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 { -- cgit v1.2.3-55-g7522