]> git.lizzy.rs Git - minetest.git/blobdiff - src/database/database-postgresql.cpp
Add PostgreSQL authentication backend (#9756)
[minetest.git] / src / database / database-postgresql.cpp
index eec838ef01519d63c294bab8249077c3b9715d9b..ca750b466171ee313a52700b40976a3dd7df32bf 100644 (file)
@@ -36,8 +36,8 @@ with this program; if not, write to the Free Software Foundation, Inc.,
 #include "debug.h"
 #include "exceptions.h"
 #include "settings.h"
-#include "content_sao.h"
 #include "remoteplayer.h"
+#include "server/player_sao.h"
 
 Database_PostgreSQL::Database_PostgreSQL(const std::string &connect_string) :
        m_connect_string(connect_string)
@@ -90,13 +90,19 @@ void Database_PostgreSQL::connectToDatabase()
        initStatements();
 }
 
-void Database_PostgreSQL::verifyDatabase()
+void Database_PostgreSQL::pingDatabase()
 {
-       if (PQstatus(m_conn) == CONNECTION_OK)
-               return;
-
-       PQreset(m_conn);
-       ping();
+       // Verify DB connection with ping
+       try {
+               ping();
+       } catch (const DatabaseException &e) {
+               // If ping failed, show the error and try reconnect
+               PQreset(m_conn);
+
+               errorstream << e.what() << std::endl
+                       << "Reconnecting to database " << m_connect_string << std::endl;
+               connectToDatabase();
+       }
 }
 
 void Database_PostgreSQL::ping()
@@ -151,7 +157,7 @@ void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
 
 void Database_PostgreSQL::beginSave()
 {
-       verifyDatabase();
+       pingDatabase();
        checkResults(PQexec(m_conn, "BEGIN;"));
 }
 
@@ -160,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()
@@ -227,7 +238,7 @@ bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
                return false;
        }
 
-       verifyDatabase();
+       pingDatabase();
 
        s32 x, y, z;
        x = htonl(pos.X);
@@ -251,7 +262,7 @@ bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
 
 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
 {
-       verifyDatabase();
+       pingDatabase();
 
        s32 x, y, z;
        x = htonl(pos.X);
@@ -275,7 +286,7 @@ void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
 
 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
 {
-       verifyDatabase();
+       pingDatabase();
 
        s32 x, y, z;
        x = htonl(pos.X);
@@ -293,7 +304,7 @@ bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
 
 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
 {
-       verifyDatabase();
+       pingDatabase();
 
        PGresult *results = execPrepared("list_all_loadable_blocks", 0,
                NULL, NULL, NULL, false, false);
@@ -301,7 +312,7 @@ void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
        int numrows = PQntuples(results);
 
        for (int row = 0; row < numrows; ++row)
-               dst.push_back(pg_to_v3s16(results, 0, 0));
+               dst.push_back(pg_to_v3s16(results, row, 0));
 
        PQclear(results);
 }
@@ -435,7 +446,7 @@ void PlayerDatabasePostgreSQL::initStatements()
 
 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
 {
-       verifyDatabase();
+       pingDatabase();
 
        const char *values[] = { playername.c_str() };
        PGresult *results = execPrepared("load_player", 1, values, false);
@@ -451,7 +462,7 @@ void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
        if (!sao)
                return;
 
-       verifyDatabase();
+       pingDatabase();
 
        v3f pos = sao->getBasePosition();
        std::string pitch = ftos(sao->getLookPitch());
@@ -527,14 +538,15 @@ void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
                };
                execPrepared("save_player_metadata", 3, meta_values);
        }
-       sao->getMeta().setModified(false);
        endSave();
+
+       player->onSuccessfulSave();
 }
 
 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
 {
        sanity_check(sao);
-       verifyDatabase();
+       pingDatabase();
 
        const char *values[] = { player->getName() };
        PGresult *results = execPrepared("load_player", 1, values, false, false);
@@ -552,7 +564,7 @@ bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
                pg_to_float(results, 0, 3),
                pg_to_float(results, 0, 4))
        );
-       sao->setHPRaw((s16) pg_to_int(results, 0, 5));
+       sao->setHPRaw((u16) pg_to_int(results, 0, 5));
        sao->setBreath((u16) pg_to_int(results, 0, 6), false);
 
        PQclear(results);
@@ -609,7 +621,7 @@ bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
        if (!playerDataExists(name))
                return false;
 
-       verifyDatabase();
+       pingDatabase();
 
        const char *values[] = { name.c_str() };
        execPrepared("remove_player", 1, values);
@@ -619,7 +631,7 @@ bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
 
 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
 {
-       verifyDatabase();
+       pingDatabase();
 
        PGresult *results = execPrepared("load_player_list", 0, NULL, false);
 
@@ -630,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