From eba55f8361fc36179e36bf8a3cea5067b5341e37 Mon Sep 17 00:00:00 2001 From: fusion32 Date: Sat, 18 Oct 2025 18:21:31 -0300 Subject: update documentation + wrap the few SQLite scripts --- postgres/README.txt | 142 ++++++++++++++++++++++++++++++++++++++++++++++ postgres/init.sql | 117 -------------------------------------- postgres/initial-data.sql | 117 ++++++++++++++++++++++++++++++++++++++ 3 files changed, 259 insertions(+), 117 deletions(-) create mode 100644 postgres/README.txt delete mode 100644 postgres/init.sql create mode 100644 postgres/initial-data.sql (limited to 'postgres') 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/init.sql deleted file mode 100644 index fbe40c7..0000000 --- a/postgres/init.sql +++ /dev/null @@ -1,117 +0,0 @@ --- NOTE(fusion): This file contains sample initial data. It's wrapped into a --- transaction to avoid errors from leaving the database in some partial state. -BEGIN; - --- 111111/tibia -INSERT INTO Accounts (AccountID, Email, Auth) - VALUES (111111, '@tibia', '\x206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); - --- NOTE(fusion): This would generally be a sequence of insertions but the way --- PostgreSQL handles unique auto increment columns makes so manually inserted --- values end up colliding with generated values at some point. --- On a real scenario, we'd never have manually assigned ids unless we're --- restoring some database dump, in which case it would also have saved --- sequences. -WITH - WorldIns AS ( - INSERT INTO Worlds (Name, Type, RebootTime, Host, Port, MaxPlayers, - PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) - VALUES ('Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100) - RETURNING WorldID, Name - ), - CharacterIns AS ( - INSERT INTO Characters (WorldID, AccountID, Name, Sex) - SELECT W.WorldID, C.AccountID, C.Name, C.Sex - FROM WorldIns AS W, - LATERAL (VALUES - (111111, 'Gamemaster on ' || W.Name, 1), - (111111, 'Player on ' || W.Name, 1) - ) AS C (AccountID, Name, Sex) - RETURNING CharacterID, Name - ) -INSERT INTO CharacterRights(CharacterID, Name) - SELECT C.CharacterID, R.RightName - FROM CharacterIns AS C, - (VALUES - ('NOTATION'), - ('NAMELOCK'), - ('STATEMENT_REPORT'), - ('BANISHMENT'), - ('FINAL_WARNING'), - ('IP_BANISHMENT'), - ('KICK'), - ('HOME_TELEPORT'), - ('GAMEMASTER_BROADCAST'), - ('ANONYMOUS_BROADCAST'), - ('NO_BANISHMENT'), - ('ALLOW_MULTICLIENT'), - ('LOG_COMMUNICATION'), - ('READ_GAMEMASTER_CHANNEL'), - ('READ_TUTOR_CHANNEL'), - ('HIGHLIGHT_HELP_CHANNEL'), - ('SEND_BUGREPORTS'), - ('NAME_INSULTING'), - ('NAME_SENTENCE'), - ('NAME_NONSENSICAL_LETTERS'), - ('NAME_BADLY_FORMATTED'), - ('NAME_NO_PERSON'), - ('NAME_CELEBRITY'), - ('NAME_COUNTRY'), - ('NAME_FAKE_IDENTITY'), - ('NAME_FAKE_POSITION'), - ('STATEMENT_INSULTING'), - ('STATEMENT_SPAMMING'), - ('STATEMENT_ADVERT_OFFTOPIC'), - ('STATEMENT_ADVERT_MONEY'), - ('STATEMENT_NON_ENGLISH'), - ('STATEMENT_CHANNEL_OFFTOPIC'), - ('STATEMENT_VIOLATION_INCITING'), - ('CHEATING_BUG_ABUSE'), - ('CHEATING_GAME_WEAKNESS'), - ('CHEATING_MACRO_USE'), - ('CHEATING_MODIFIED_CLIENT'), - ('CHEATING_HACKING'), - ('CHEATING_MULTI_CLIENT'), - ('CHEATING_ACCOUNT_TRADING'), - ('CHEATING_ACCOUNT_SHARING'), - ('GAMEMASTER_THREATENING'), - ('GAMEMASTER_PRETENDING'), - ('GAMEMASTER_INFLUENCE'), - ('GAMEMASTER_FALSE_REPORTS'), - ('KILLING_EXCESSIVE_UNJUSTIFIED'), - ('DESTRUCTIVE_BEHAVIOUR'), - ('SPOILING_AUCTION'), - ('INVALID_PAYMENT'), - ('TELEPORT_TO_CHARACTER'), - ('TELEPORT_TO_MARK'), - ('TELEPORT_VERTICAL'), - ('TELEPORT_TO_COORDINATE'), - ('LEVITATE'), - ('SPECIAL_MOVEUSE'), - ('MODIFY_GOSTRENGTH'), - ('SHOW_COORDINATE'), - ('RETRIEVE'), - ('ENTER_HOUSES'), - ('OPEN_NAMEDOORS'), - ('INVULNERABLE'), - ('UNLIMITED_MANA'), - ('KEEP_INVENTORY'), - ('ALL_SPELLS'), - ('UNLIMITED_CAPACITY'), - ('ATTACK_EVERYWHERE'), - ('NO_LOGOUT_BLOCK'), - ('GAMEMASTER_OUTFIT'), - ('ILLUMINATE'), - ('CHANGE_PROFESSION'), - ('IGNORED_BY_MONSTERS'), - ('SHOW_KEYHOLE_NUMBERS'), - ('CREATE_OBJECTS'), - ('CREATE_MONEY'), - ('CREATE_MONSTERS'), - ('CHANGE_SKILLS'), - ('CLEANUP_FIELDS'), - ('NO_STATISTICS') - ) AS R (RightName) - WHERE C.Name LIKE 'Gamemaster%' COLLATE "default"; - -COMMIT; diff --git a/postgres/initial-data.sql b/postgres/initial-data.sql new file mode 100644 index 0000000..fbe40c7 --- /dev/null +++ b/postgres/initial-data.sql @@ -0,0 +1,117 @@ +-- NOTE(fusion): This file contains sample initial data. It's wrapped into a +-- transaction to avoid errors from leaving the database in some partial state. +BEGIN; + +-- 111111/tibia +INSERT INTO Accounts (AccountID, Email, Auth) + VALUES (111111, '@tibia', '\x206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); + +-- NOTE(fusion): This would generally be a sequence of insertions but the way +-- PostgreSQL handles unique auto increment columns makes so manually inserted +-- values end up colliding with generated values at some point. +-- On a real scenario, we'd never have manually assigned ids unless we're +-- restoring some database dump, in which case it would also have saved +-- sequences. +WITH + WorldIns AS ( + INSERT INTO Worlds (Name, Type, RebootTime, Host, Port, MaxPlayers, + PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) + VALUES ('Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100) + RETURNING WorldID, Name + ), + CharacterIns AS ( + INSERT INTO Characters (WorldID, AccountID, Name, Sex) + SELECT W.WorldID, C.AccountID, C.Name, C.Sex + FROM WorldIns AS W, + LATERAL (VALUES + (111111, 'Gamemaster on ' || W.Name, 1), + (111111, 'Player on ' || W.Name, 1) + ) AS C (AccountID, Name, Sex) + RETURNING CharacterID, Name + ) +INSERT INTO CharacterRights(CharacterID, Name) + SELECT C.CharacterID, R.RightName + FROM CharacterIns AS C, + (VALUES + ('NOTATION'), + ('NAMELOCK'), + ('STATEMENT_REPORT'), + ('BANISHMENT'), + ('FINAL_WARNING'), + ('IP_BANISHMENT'), + ('KICK'), + ('HOME_TELEPORT'), + ('GAMEMASTER_BROADCAST'), + ('ANONYMOUS_BROADCAST'), + ('NO_BANISHMENT'), + ('ALLOW_MULTICLIENT'), + ('LOG_COMMUNICATION'), + ('READ_GAMEMASTER_CHANNEL'), + ('READ_TUTOR_CHANNEL'), + ('HIGHLIGHT_HELP_CHANNEL'), + ('SEND_BUGREPORTS'), + ('NAME_INSULTING'), + ('NAME_SENTENCE'), + ('NAME_NONSENSICAL_LETTERS'), + ('NAME_BADLY_FORMATTED'), + ('NAME_NO_PERSON'), + ('NAME_CELEBRITY'), + ('NAME_COUNTRY'), + ('NAME_FAKE_IDENTITY'), + ('NAME_FAKE_POSITION'), + ('STATEMENT_INSULTING'), + ('STATEMENT_SPAMMING'), + ('STATEMENT_ADVERT_OFFTOPIC'), + ('STATEMENT_ADVERT_MONEY'), + ('STATEMENT_NON_ENGLISH'), + ('STATEMENT_CHANNEL_OFFTOPIC'), + ('STATEMENT_VIOLATION_INCITING'), + ('CHEATING_BUG_ABUSE'), + ('CHEATING_GAME_WEAKNESS'), + ('CHEATING_MACRO_USE'), + ('CHEATING_MODIFIED_CLIENT'), + ('CHEATING_HACKING'), + ('CHEATING_MULTI_CLIENT'), + ('CHEATING_ACCOUNT_TRADING'), + ('CHEATING_ACCOUNT_SHARING'), + ('GAMEMASTER_THREATENING'), + ('GAMEMASTER_PRETENDING'), + ('GAMEMASTER_INFLUENCE'), + ('GAMEMASTER_FALSE_REPORTS'), + ('KILLING_EXCESSIVE_UNJUSTIFIED'), + ('DESTRUCTIVE_BEHAVIOUR'), + ('SPOILING_AUCTION'), + ('INVALID_PAYMENT'), + ('TELEPORT_TO_CHARACTER'), + ('TELEPORT_TO_MARK'), + ('TELEPORT_VERTICAL'), + ('TELEPORT_TO_COORDINATE'), + ('LEVITATE'), + ('SPECIAL_MOVEUSE'), + ('MODIFY_GOSTRENGTH'), + ('SHOW_COORDINATE'), + ('RETRIEVE'), + ('ENTER_HOUSES'), + ('OPEN_NAMEDOORS'), + ('INVULNERABLE'), + ('UNLIMITED_MANA'), + ('KEEP_INVENTORY'), + ('ALL_SPELLS'), + ('UNLIMITED_CAPACITY'), + ('ATTACK_EVERYWHERE'), + ('NO_LOGOUT_BLOCK'), + ('GAMEMASTER_OUTFIT'), + ('ILLUMINATE'), + ('CHANGE_PROFESSION'), + ('IGNORED_BY_MONSTERS'), + ('SHOW_KEYHOLE_NUMBERS'), + ('CREATE_OBJECTS'), + ('CREATE_MONEY'), + ('CREATE_MONSTERS'), + ('CHANGE_SKILLS'), + ('CLEANUP_FIELDS'), + ('NO_STATISTICS') + ) AS R (RightName) + WHERE C.Name LIKE 'Gamemaster%' COLLATE "default"; + +COMMIT; -- cgit v1.2.3