summaryrefslogtreecommitdiff
path: root/src/database
diff options
context:
space:
mode:
authorLoïc Blot <nerzhul@users.noreply.github.com>2020-04-27 06:54:48 +0200
committerGitHub <noreply@github.com>2020-04-27 06:54:48 +0200
commite564bf8eadb5aee7a90b2184b03316917c580aed (patch)
tree9d50066383ca4baf130d955f50845bf528936162 /src/database
parent2fe4641c1e00d72c1f651b83898b093a9454d43c (diff)
downloadminetest-e564bf8eadb5aee7a90b2184b03316917c580aed.tar.gz
minetest-e564bf8eadb5aee7a90b2184b03316917c580aed.tar.bz2
minetest-e564bf8eadb5aee7a90b2184b03316917c580aed.zip
Add PostgreSQL authentication backend (#9756)
* Add PostgreSQL authentication backend
Diffstat (limited to 'src/database')
-rw-r--r--src/database/database-postgresql.cpp175
-rw-r--r--src/database/database-postgresql.h24
2 files changed, 199 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);
+};