diff options
| -rw-r--r-- | README.md | 2 | ||||
| -rw-r--r-- | config.cfg.dist | 4 | ||||
| -rw-r--r-- | postgres/README.txt | 142 | ||||
| -rw-r--r-- | postgres/initial-data.sql (renamed from postgres/init.sql) | 0 | ||||
| -rw-r--r-- | sqlite/patches/.gitignore | 2 | ||||
| -rw-r--r-- | sqlite/z-001-migrate-v01-to-v02.sql | 30 | ||||
| -rw-r--r-- | sqlite/z-999-initial-data.sql (renamed from sqlite/patches/9999-initial-data.sql) | 6 | ||||
| -rw-r--r-- | src/querymanager.cc | 4 |
8 files changed, 175 insertions, 15 deletions
@@ -16,7 +16,7 @@ make clean # remove `build` directory ``` ## Running (SQLite) -The query manager becomes the database, automatically initializing and maintaining the schema, based on the files in `sqlite/` (see `sqlite/README.txt`). The default schema file won't automatically insert any initial data (see `sqlite/init.sql`), although you could put some insertions at the end to avoid having to manually run something like `sqlite/init.sql`. There are a few configuration options, and in particular `SQLite.*` options that can be adjusted in `config.cfg` but the defaults should work for most use cases. +The query manager becomes the database, automatically initializing and maintaining the schema, based on the files in `sqlite/` (see `sqlite/README.txt`). The default schema file won't automatically insert any initial data but that can be changed by using a patch (again, see `sqlite/README.txt`). There are a few configuration options, and in particular `SQLite.*` options that can be adjusted in `config.cfg` but the defaults should work for most use cases. ## Running (PostgreSQL) The query manager becomes a relay to the actual database. And with PostgreSQL being a distributed database system, it makes no sense to have individual clients managing the schema, since there could be multiple, each with their own assumptions. For that reason there is a `SchemaInfo` table with a `VERSION` row that will be queried at startup and compared against `POSTGRESQL_SCHEMA_VERSION`, defined in `src/database_postgres.cc`, to make sure there is an agreement on the schema version. It is hardcoded because schema changes will usually result in query changes. diff --git a/config.cfg.dist b/config.cfg.dist index dcf7212..26f7a21 100644 --- a/config.cfg.dist +++ b/config.cfg.dist @@ -11,8 +11,8 @@ SQLite.MaxCachedStatements = 100 # Empty values are ignored, meaning their defaults will be used instead. # For more information see: # https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS -PostgreSQL.Host = "localhost" -PostgreSQL.Port = "5432" +PostgreSQL.Host = "" +PostgreSQL.Port = "" PostgreSQL.DBName = "tibia" PostgreSQL.User = "tibia" PostgreSQL.Password = "" diff --git a/postgres/README.txt b/postgres/README.txt new file mode 100644 index 0000000..a96995b --- /dev/null +++ b/postgres/README.txt @@ -0,0 +1,142 @@ +WARNING: This is not meant to be a complete guide on PostgreSQL, but rather a +"first steps" kind of guide. It'll only cover things on the surface level. For +a deep dive into how the database operates, how to properly configure it, and +ultimately properly administrate it, you MUST refer to the PostgreSQL manual +for your version. The most current version of the manual will describe the most +recent features, but not all features are present in all versions. + If anything, you should absolutely consult the section that regards server +administration "III. Server Administration". + + MANUAL https://www.postgresql.org/docs/current/index.html + +Installation +------------ + PostgreSQL is available in most Linux distributions as a package which is +the preferred way to get it installed. Some will automatically setup a service, +create service users, initialize the database cluster, etc... If not, you might +need to do one or more steps manually. If you're having trouble, most systems +will have specific instructions on how to set everything up. Just as an example +here are a few links for common systems: + DEBIAN https://www.postgresql.org/download/linux/debian/ + REDHAT https://www.postgresql.org/download/linux/redhat/ + SUSE https://www.postgresql.org/download/linux/suse/ + UBUNTU https://www.postgresql.org/download/linux/ubuntu/ + ARCH https://wiki.archlinux.org/title/PostgreSQL + +Configuration +------------- + By default, configuration files will be in the `data` directory which can +change locations depending on how the server was installed but is usually in +`/var/lib/postgres/data`. + All files in the `data` directory are OWNED by the *postgres* SYSTEM user, +meaning you'll only be able to modify them if you're logged in as *postgres*, +by using *sudo* privileges, or both with `sudo su postgres`. + The bulk of the configuration is inside `postgresql.conf` which has multiple +options, but of particular interest are the "CONNECTIONS AND AUTHENTICATION" +options. I won't go over specifics here but if you're planning on accepting +remote connections, you MUST properly configure SSL communication. + Access to the database is controlled with `pg_hba.conf`. This is different +from MySQL where you'd specify users as 'user'@'host' with SQL to restrict +them to certain hosts. Instead you need to specify how certain users/roles +may connect to the database in this file. Properly configuring it is probably +the most important step in securing the database, aside from configuring SSL +communication. + The last file is `pg_ident.conf` which declares mappings from system users +to database users. These mappings alone don't do anything. They must be +explicitly referenced as `map=MAPNAME` in `pg_hba.conf` for supported +authentication methods. + + Here is an example of a `pg_hba.conf` + `pg_ident.conf` local access config. +It'll allow *systemuser* to connect as *postgres* to any database using the +*peer* method which checks the system user name. It'll also allow the *tibia* +user to connect to the *tibia* database using the *scram-sha-256* password +authentication scheme. Local connections will use UNIX-domain sockets and for +that matter you'd leave `PostgreSQL.Host` empty. + +``` +# pg_hba.conf +# TYPE DATABASE USER ADDRESS METHOD +local all postgres peer map=super +local tibia tibia scram-sha-256 + +# pg_ident.conf +# MAPNAME SYSTEM-USERNAME PG-USERNAME +super systemuser postgres +``` + + MANUAL https://www.postgresql.org/docs/current/runtime-config.html + MANUAL https://www.postgresql.org/docs/current/client-authentication.html + +Database Setup +-------------- + It is highly advised to not use a SUPERUSER when connecting to the database +from the query manager, or any other service for that matter. This warrants the +creation of a secondary user that has access, but not administrative privileges. + I figured it would be simpler to have a sequence of *PSQL* commands with their +descriptions. Having a database minimaly ready for the query manager should be +a matter of following this sequence. + + Unless a database is specified, *PSQL* will connect to one with the same name +as the specified user. If the user is not explicitly specified, the system user +name will be used. Running `psql -U postgres` will connect to *postgres* as the +user *postgres*. Note that you can't connect without a database, so you'd connect +to *postgres* in order to create new databases. + +1 - Create and connect to a new database. Note that the `OWNER = postgres` clause +is redundant here but it's just to show that having the database owned by the +super user is intended. +``` +psql -U postgres -c "CREATE DATABASE tibia OWNER = postgres;" +psql -U postgres tibia +``` + +2 - Set default privileges. Newly created databases may have some default PUBLIC +privileges that we'll want to revoke to make sure the set of users that are able +to connect is tighly controlled. Then, for users that are able to connect, we +want to give default access privileges to tables. +``` +REVOKE ALL ON DATABASE tibia FROM PUBLIC; +ALTER DEFAULT PRIVILEGES IN SCHEMA public + GRANT SELECT, INSERT, UPDATE, DELETE + ON TABLES TO PUBLIC; +``` + +3 - Initialize schema. This is done by executing commands from `postgres/schema.sql`, +and optionally `postgres/initial-data.sql`. Note that since we set default privileges +before creating any tables, they should already have the approppriate privileges. +If done the other way around, we'd need to manually update table privileges. +``` +\i postgres/schema.sql +\i postgres/initial-data.sql +``` + +4 - Create secondary user. This is straighforward. Create a user with *LOGIN* +privileges and a *PASSWORD*. Then grant *CONNECT* privileges to the database. +``` +CREATE ROLE tibia WITH LOGIN PASSWORD '********'; +GRANT CONNECT ON DATABASE tibia TO tibia; +``` + + This is just one way. There are probably other, more optimal setups, but +for a small testing bench, it will do. And don't take my word on anything. +You should always check the manual for a complete description on how things +work. + + To wrap, here is a list of helpful commands available in *PSQL*. They'll +show up along with a lot of other commands when running `\?`. +``` +\q # quit +\l # list databases (will show database privileges) +\du # list users (will show user privileges) +\dO # list collations +\dt # list tables +\dv # list views +\ds # list sequences +\di # list indexes +\d NAME # describe table/view/sequence/index +\dp # list privileges +\ddp # list default privileges +``` + + MANUAL https://www.postgresql.org/docs/current/sql-commands.html + diff --git a/postgres/init.sql b/postgres/initial-data.sql index fbe40c7..fbe40c7 100644 --- a/postgres/init.sql +++ b/postgres/initial-data.sql diff --git a/sqlite/patches/.gitignore b/sqlite/patches/.gitignore new file mode 100644 index 0000000..d6b7ef3 --- /dev/null +++ b/sqlite/patches/.gitignore @@ -0,0 +1,2 @@ +* +!.gitignore diff --git a/sqlite/z-001-migrate-v01-to-v02.sql b/sqlite/z-001-migrate-v01-to-v02.sql index b7df053..f471f53 100644 --- a/sqlite/z-001-migrate-v01-to-v02.sql +++ b/sqlite/z-001-migrate-v01-to-v02.sql @@ -1,14 +1,30 @@ -- NOTE(fusion): This file contains the migration script from v0.1 to v0.2. It --- can be placed into `patches/` to upgrade an existing database. Note that these --- changes are already present in the latest `schema.sql`, so trying to patch a --- newly created database will probably result in errors. See `sqlite/README.txt` --- for more details. +-- must be manually executed as `sqlite3 -bail -echo tibia.db < migration.sql` +-- because the original schema didn't have a `Patches` table which is necessary +-- with the new automatic patching system. Future migration scripts can be placed +-- in `patches/` for automatic execution but not this one unfortunately. +-- These changes are already present in the latest `schema.sql`, so trying to +-- apply it to a newly created database will result in errors. For more details +-- see `sqlite/README.txt`. --============================================================================== -ALTER TABLE Worlds RENAME COLUMN OnlineRecord TO OnlinePeak; -ALTER TABLE Worlds RENAME COLUMN OnlineRecordTimestamp TO OnlinePeakTimestamp; -ALTER TABLE CharacterRights RENAME COLUMN Right TO Name; +BEGIN; + +PRAGMA application_id = 0x54694442; + +PRAGMA user_version = 1; +CREATE TABLE Patches ( + FileName TEXT NOT NULL COLLATE NOCASE, + Timestamp INTEGER NOT NULL, + UNIQUE (FileName) +); + +ALTER TABLE Worlds RENAME COLUMN OnlineRecord TO OnlinePeak; +ALTER TABLE Worlds RENAME COLUMN OnlineRecordTimestamp TO OnlinePeakTimestamp; ALTER TABLE Worlds ADD COLUMN LastStartup INTEGER NOT NULL DEFAULT 0; ALTER TABLE Worlds ADD COLUMN LastShutdown INTEGER NOT NULL DEFAULT 0; +ALTER TABLE CharacterRights RENAME COLUMN Right TO Name; + +COMMIT; diff --git a/sqlite/patches/9999-initial-data.sql b/sqlite/z-999-initial-data.sql index 53d92f2..d20fee0 100644 --- a/sqlite/patches/9999-initial-data.sql +++ b/sqlite/z-999-initial-data.sql @@ -1,6 +1,6 @@ --- NOTE(fusion): This file contains sample initial data and will be executed --- automatically as a patch by the query manager. See `sqlite/README.txt` for --- more details. +-- NOTE(fusion): This file contains sample initial data and can be executed +-- automatically as a patch by the query manager if placed at `sqlite/patches`. +-- See `sqlite/README.txt` for more details. --============================================================================== INSERT INTO Worlds (WorldID, Name, Type, RebootTime, Host, Port, MaxPlayers, diff --git a/src/querymanager.cc b/src/querymanager.cc index 56df1f8..bac3c74 100644 --- a/src/querymanager.cc +++ b/src/querymanager.cc @@ -780,8 +780,8 @@ int main(int argc, const char **argv){ g_Config.SQLite.MaxCachedStatements = 100; // PostgreSQL Config - StringBufCopy(g_Config.PostgreSQL.Host, "localhost"); - StringBufCopy(g_Config.PostgreSQL.Port, "5432"); + StringBufCopy(g_Config.PostgreSQL.Host, ""); + StringBufCopy(g_Config.PostgreSQL.Port, ""); StringBufCopy(g_Config.PostgreSQL.DBName, "tibia"); StringBufCopy(g_Config.PostgreSQL.User, "tibia"); StringBufCopy(g_Config.PostgreSQL.Password, ""); |