diff options
| -rw-r--r-- | src/database/database-postgresql.cpp | 175 | ||||
| -rw-r--r-- | src/database/database-postgresql.h | 24 | ||||
| -rw-r--r-- | src/serverenvironment.cpp | 8 |
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); |