Add PostgreSQL authentication backend (#9756)

* Add PostgreSQL authentication backend

3 files changed, 207 insertions, 0 deletions

diff --git a/src/database/database-postgresql.cpp b/src/database/database-postgresql.cpp
index c1b81586d..ca750b466 100644
--- a/src/database/database-postgresql.cpp
+++ b/src/database/database-postgresql.cpp
@@ -166,6 +166,11 @@ void Database_PostgreSQL::endSave()
 	checkResults(PQexec(m_conn, "COMMIT;"));
 }
 
+void Database_PostgreSQL::rollback()
+{
+	checkResults(PQexec(m_conn, "ROLLBACK;"));
+}
+
 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
 	Database_PostgreSQL(connect_string),
 	MapDatabase()
@@ -637,4 +642,174 @@ void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
 	PQclear(results);
 }
 
+AuthDatabasePostgreSQL::AuthDatabasePostgreSQL(const std::string &connect_string) :
+		Database_PostgreSQL(connect_string), AuthDatabase()
+{
+	connectToDatabase();
+}
+
+void AuthDatabasePostgreSQL::createDatabase()
+{
+	createTableIfNotExists("auth",
+		"CREATE TABLE auth ("
+			"id SERIAL,"
+			"name TEXT UNIQUE,"
+			"password TEXT,"
+			"last_login INT NOT NULL DEFAULT 0,"
+			"PRIMARY KEY (id)"
+		");");
+
+	createTableIfNotExists("user_privileges",
+		"CREATE TABLE user_privileges ("
+			"id INT,"
+			"privilege TEXT,"
+			"PRIMARY KEY (id, privilege),"
+			"CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES auth (id) ON DELETE CASCADE"
+		");");
+}
+
+void AuthDatabasePostgreSQL::initStatements()
+{
+	prepareStatement("auth_read", "SELECT id, name, password, last_login FROM auth WHERE name = $1");
+	prepareStatement("auth_write", "UPDATE auth SET name = $1, password = $2, last_login = $3 WHERE id = $4");
+	prepareStatement("auth_create", "INSERT INTO auth (name, password, last_login) VALUES ($1, $2, $3) RETURNING id");
+	prepareStatement("auth_delete", "DELETE FROM auth WHERE name = $1");
+
+	prepareStatement("auth_list_names", "SELECT name FROM auth ORDER BY name DESC");
+
+	prepareStatement("auth_read_privs", "SELECT privilege FROM user_privileges WHERE id = $1");
+	prepareStatement("auth_write_privs", "INSERT INTO user_privileges (id, privilege) VALUES ($1, $2)");
+	prepareStatement("auth_delete_privs", "DELETE FROM user_privileges WHERE id = $1");
+}
+
+bool AuthDatabasePostgreSQL::getAuth(const std::string &name, AuthEntry &res)
+{
+	pingDatabase();
+
+	const char *values[] = { name.c_str() };
+	PGresult *result = execPrepared("auth_read", 1, values, false, false);
+	int numrows = PQntuples(result);
+	if (numrows == 0) {
+		PQclear(result);
+		return false;
+	}
+
+	res.id = pg_to_uint(result, 0, 0);
+	res.name = std::string(PQgetvalue(result, 0, 1), PQgetlength(result, 0, 1));
+	res.password = std::string(PQgetvalue(result, 0, 2), PQgetlength(result, 0, 2));
+	res.last_login = pg_to_int(result, 0, 3);
+
+	PQclear(result);
+
+	std::string playerIdStr = itos(res.id);
+	const char *privsValues[] = { playerIdStr.c_str() };
+	PGresult *results = execPrepared("auth_read_privs", 1, privsValues, false);
+
+	numrows = PQntuples(results);
+	for (int row = 0; row < numrows; row++)
+		res.privileges.emplace_back(PQgetvalue(results, row, 0));
+
+	PQclear(results);
+
+	return true;
+}
+
+bool AuthDatabasePostgreSQL::saveAuth(const AuthEntry &authEntry)
+{
+	pingDatabase();
+
+	beginSave();
+
+	std::string lastLoginStr = itos(authEntry.last_login);
+	std::string idStr = itos(authEntry.id);
+	const char *values[] = {
+		authEntry.name.c_str() ,
+		authEntry.password.c_str(),
+		lastLoginStr.c_str(),
+		idStr.c_str(),
+	};
+	execPrepared("auth_write", 4, values);
+
+	writePrivileges(authEntry);
+
+	endSave();
+	return true;
+}
+
+bool AuthDatabasePostgreSQL::createAuth(AuthEntry &authEntry)
+{
+	pingDatabase();
+
+	std::string lastLoginStr = itos(authEntry.last_login);
+	const char *values[] = {
+		authEntry.name.c_str() ,
+		authEntry.password.c_str(),
+		lastLoginStr.c_str()
+	};
+
+	beginSave();
+
+	PGresult *result = execPrepared("auth_create", 3, values, false, false);
+
+	int numrows = PQntuples(result);
+	if (numrows == 0) {
+		errorstream << "Strange behaviour on auth creation, no ID returned." << std::endl;
+		PQclear(result);
+		rollback();
+		return false;
+	}
+
+	authEntry.id = pg_to_uint(result, 0, 0);
+	PQclear(result);
+
+	writePrivileges(authEntry);
+
+	endSave();
+	return true;
+}
+
+bool AuthDatabasePostgreSQL::deleteAuth(const std::string &name)
+{
+	pingDatabase();
+
+	const char *values[] = { name.c_str() };
+	execPrepared("auth_delete", 1, values);
+
+	// privileges deleted by foreign key on delete cascade
+	return true;
+}
+
+void AuthDatabasePostgreSQL::listNames(std::vector<std::string> &res)
+{
+	pingDatabase();
+
+	PGresult *results = execPrepared("auth_list_names", 0,
+		NULL, NULL, NULL, false, false);
+
+	int numrows = PQntuples(results);
+
+	for (int row = 0; row < numrows; ++row)
+		res.emplace_back(PQgetvalue(results, row, 0));
+
+	PQclear(results);
+}
+
+void AuthDatabasePostgreSQL::reload()
+{
+	// noop for PgSQL
+}
+
+void AuthDatabasePostgreSQL::writePrivileges(const AuthEntry &authEntry)
+{
+	std::string authIdStr = itos(authEntry.id);
+	const char *values[] = { authIdStr.c_str() };
+	execPrepared("auth_delete_privs", 1, values);
+
+	for (const std::string &privilege : authEntry.privileges) {
+		const char *values[] = { authIdStr.c_str(), privilege.c_str() };
+		execPrepared("auth_write_privs", 2, values);
+	}
+}
+
+
 #endif // USE_POSTGRESQL
diff --git a/src/database/database-postgresql.h b/src/database/database-postgresql.h
index 5a8b89a51..340f0a7b8 100644
--- a/src/database/database-postgresql.h
+++ b/src/database/database-postgresql.h
@@ -36,6 +36,7 @@ public:
 
 	void beginSave();
 	void endSave();
+	void rollback();
 
 	bool initialized() const;
 
@@ -148,3 +149,26 @@ protected:
 private:
 	bool playerDataExists(const std::string &playername);
 };
+
+class AuthDatabasePostgreSQL : private Database_PostgreSQL, public AuthDatabase
+{
+public:
+	AuthDatabasePostgreSQL(const std::string &connect_string);
+	virtual ~AuthDatabasePostgreSQL() = default;
+
+	virtual void pingDatabase() { Database_PostgreSQL::pingDatabase(); }
+
+	virtual bool getAuth(const std::string &name, AuthEntry &res);
+	virtual bool saveAuth(const AuthEntry &authEntry);
+	virtual bool createAuth(AuthEntry &authEntry);
+	virtual bool deleteAuth(const std::string &name);
+	virtual void listNames(std::vector<std::string> &res);
+	virtual void reload();
+
+protected:
+	virtual void createDatabase();
+	virtual void initStatements();
+
+private:
+	virtual void writePrivileges(const AuthEntry &authEntry);
+};
diff --git a/src/serverenvironment.cpp b/src/serverenvironment.cpp
index 08d796118..27432e973 100644
--- a/src/serverenvironment.cpp
+++ b/src/serverenvironment.cpp
@@ -2187,6 +2187,14 @@ AuthDatabase *ServerEnvironment::openAuthDatabase(
 	if (name == "sqlite3")
 		return new AuthDatabaseSQLite3(savedir);
 
+#if USE_POSTGRESQL
+	if (name == "postgresql") {
+		std::string connect_string;
+		conf.getNoEx("pgsql_auth_connection", connect_string);
+		return new AuthDatabasePostgreSQL(connect_string);
+	}
+#endif
+
 	if (name == "files")
 		return new AuthDatabaseFiles(savedir);