Skip to content

Commit eba55f8

Browse files
fusion32fusion32
committed
update documentation + wrap the few SQLite scripts
1 parent ebf536a commit eba55f8

8 files changed

Lines changed: 175 additions & 15 deletions

File tree

‎README.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@ make clean # remove `build` directory
1616
```
1717

1818
## Running (SQLite)
19-
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.
19+
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.
2020

2121
## Running (PostgreSQL)
2222
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.

‎config.cfg.dist‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,8 +11,8 @@ SQLite.MaxCachedStatements = 100
1111
# Empty values are ignored, meaning their defaults will be used instead.
1212
# For more information see:
1313
# https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
14-
PostgreSQL.Host = "localhost"
15-
PostgreSQL.Port = "5432"
14+
PostgreSQL.Host = ""
15+
PostgreSQL.Port = ""
1616
PostgreSQL.DBName = "tibia"
1717
PostgreSQL.User = "tibia"
1818
PostgreSQL.Password = ""

‎postgres/README.txt‎

Lines changed: 142 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,142 @@
1+
WARNING: This is not meant to be a complete guide on PostgreSQL, but rather a
2+
"first steps" kind of guide. It'll only cover things on the surface level. For
3+
a deep dive into how the database operates, how to properly configure it, and
4+
ultimately properly administrate it, you MUST refer to the PostgreSQL manual
5+
for your version. The most current version of the manual will describe the most
6+
recent features, but not all features are present in all versions.
7+
If anything, you should absolutely consult the section that regards server
8+
administration "III. Server Administration".
9+
10+
MANUAL https://www.postgresql.org/docs/current/index.html
11+
12+
Installation
13+
------------
14+
PostgreSQL is available in most Linux distributions as a package which is
15+
the preferred way to get it installed. Some will automatically setup a service,
16+
create service users, initialize the database cluster, etc... If not, you might
17+
need to do one or more steps manually. If you're having trouble, most systems
18+
will have specific instructions on how to set everything up. Just as an example
19+
here are a few links for common systems:
20+
DEBIAN https://www.postgresql.org/download/linux/debian/
21+
REDHAT https://www.postgresql.org/download/linux/redhat/
22+
SUSE https://www.postgresql.org/download/linux/suse/
23+
UBUNTU https://www.postgresql.org/download/linux/ubuntu/
24+
ARCH https://wiki.archlinux.org/title/PostgreSQL
25+
26+
Configuration
27+
-------------
28+
By default, configuration files will be in the `data` directory which can
29+
change locations depending on how the server was installed but is usually in
30+
`/var/lib/postgres/data`.
31+
All files in the `data` directory are OWNED by the *postgres* SYSTEM user,
32+
meaning you'll only be able to modify them if you're logged in as *postgres*,
33+
by using *sudo* privileges, or both with `sudo su postgres`.
34+
The bulk of the configuration is inside `postgresql.conf` which has multiple
35+
options, but of particular interest are the "CONNECTIONS AND AUTHENTICATION"
36+
options. I won't go over specifics here but if you're planning on accepting
37+
remote connections, you MUST properly configure SSL communication.
38+
Access to the database is controlled with `pg_hba.conf`. This is different
39+
from MySQL where you'd specify users as 'user'@'host' with SQL to restrict
40+
them to certain hosts. Instead you need to specify how certain users/roles
41+
may connect to the database in this file. Properly configuring it is probably
42+
the most important step in securing the database, aside from configuring SSL
43+
communication.
44+
The last file is `pg_ident.conf` which declares mappings from system users
45+
to database users. These mappings alone don't do anything. They must be
46+
explicitly referenced as `map=MAPNAME` in `pg_hba.conf` for supported
47+
authentication methods.
48+
49+
Here is an example of a `pg_hba.conf` + `pg_ident.conf` local access config.
50+
It'll allow *systemuser* to connect as *postgres* to any database using the
51+
*peer* method which checks the system user name. It'll also allow the *tibia*
52+
user to connect to the *tibia* database using the *scram-sha-256* password
53+
authentication scheme. Local connections will use UNIX-domain sockets and for
54+
that matter you'd leave `PostgreSQL.Host` empty.
55+
56+
```
57+
# pg_hba.conf
58+
# TYPE DATABASE USER ADDRESS METHOD
59+
local all postgres peer map=super
60+
local tibia tibia scram-sha-256
61+
62+
# pg_ident.conf
63+
# MAPNAME SYSTEM-USERNAME PG-USERNAME
64+
super systemuser postgres
65+
```
66+
67+
MANUAL https://www.postgresql.org/docs/current/runtime-config.html
68+
MANUAL https://www.postgresql.org/docs/current/client-authentication.html
69+
70+
Database Setup
71+
--------------
72+
It is highly advised to not use a SUPERUSER when connecting to the database
73+
from the query manager, or any other service for that matter. This warrants the
74+
creation of a secondary user that has access, but not administrative privileges.
75+
I figured it would be simpler to have a sequence of *PSQL* commands with their
76+
descriptions. Having a database minimaly ready for the query manager should be
77+
a matter of following this sequence.
78+
79+
Unless a database is specified, *PSQL* will connect to one with the same name
80+
as the specified user. If the user is not explicitly specified, the system user
81+
name will be used. Running `psql -U postgres` will connect to *postgres* as the
82+
user *postgres*. Note that you can't connect without a database, so you'd connect
83+
to *postgres* in order to create new databases.
84+
85+
1 - Create and connect to a new database. Note that the `OWNER = postgres` clause
86+
is redundant here but it's just to show that having the database owned by the
87+
super user is intended.
88+
```
89+
psql -U postgres -c "CREATE DATABASE tibia OWNER = postgres;"
90+
psql -U postgres tibia
91+
```
92+
93+
2 - Set default privileges. Newly created databases may have some default PUBLIC
94+
privileges that we'll want to revoke to make sure the set of users that are able
95+
to connect is tighly controlled. Then, for users that are able to connect, we
96+
want to give default access privileges to tables.
97+
```
98+
REVOKE ALL ON DATABASE tibia FROM PUBLIC;
99+
ALTER DEFAULT PRIVILEGES IN SCHEMA public
100+
GRANT SELECT, INSERT, UPDATE, DELETE
101+
ON TABLES TO PUBLIC;
102+
```
103+
104+
3 - Initialize schema. This is done by executing commands from `postgres/schema.sql`,
105+
and optionally `postgres/initial-data.sql`. Note that since we set default privileges
106+
before creating any tables, they should already have the approppriate privileges.
107+
If done the other way around, we'd need to manually update table privileges.
108+
```
109+
\i postgres/schema.sql
110+
\i postgres/initial-data.sql
111+
```
112+
113+
4 - Create secondary user. This is straighforward. Create a user with *LOGIN*
114+
privileges and a *PASSWORD*. Then grant *CONNECT* privileges to the database.
115+
```
116+
CREATE ROLE tibia WITH LOGIN PASSWORD '********';
117+
GRANT CONNECT ON DATABASE tibia TO tibia;
118+
```
119+
120+
This is just one way. There are probably other, more optimal setups, but
121+
for a small testing bench, it will do. And don't take my word on anything.
122+
You should always check the manual for a complete description on how things
123+
work.
124+
125+
To wrap, here is a list of helpful commands available in *PSQL*. They'll
126+
show up along with a lot of other commands when running `\?`.
127+
```
128+
\q # quit
129+
\l # list databases (will show database privileges)
130+
\du # list users (will show user privileges)
131+
\dO # list collations
132+
\dt # list tables
133+
\dv # list views
134+
\ds # list sequences
135+
\di # list indexes
136+
\d NAME # describe table/view/sequence/index
137+
\dp # list privileges
138+
\ddp # list default privileges
139+
```
140+
141+
MANUAL https://www.postgresql.org/docs/current/sql-commands.html
142+

postgres/init.sql renamed to postgres/initial-data.sql

File renamed without changes.

‎sqlite/patches/.gitignore‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
*
2+
!.gitignore

‎sqlite/z-001-migrate-v01-to-v02.sql‎

Lines changed: 23 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,30 @@
11
-- NOTE(fusion): This file contains the migration script from v0.1 to v0.2. It
2-
-- can be placed into `patches/` to upgrade an existing database. Note that these
3-
-- changes are already present in the latest `schema.sql`, so trying to patch a
4-
-- newly created database will probably result in errors. See `sqlite/README.txt`
5-
-- for more details.
2+
-- must be manually executed as `sqlite3 -bail -echo tibia.db < migration.sql`
3+
-- because the original schema didn't have a `Patches` table which is necessary
4+
-- with the new automatic patching system. Future migration scripts can be placed
5+
-- in `patches/` for automatic execution but not this one unfortunately.
6+
-- These changes are already present in the latest `schema.sql`, so trying to
7+
-- apply it to a newly created database will result in errors. For more details
8+
-- see `sqlite/README.txt`.
69
--==============================================================================
710

8-
ALTER TABLE Worlds RENAME COLUMN OnlineRecord TO OnlinePeak;
9-
ALTER TABLE Worlds RENAME COLUMN OnlineRecordTimestamp TO OnlinePeakTimestamp;
10-
ALTER TABLE CharacterRights RENAME COLUMN Right TO Name;
11+
BEGIN;
12+
13+
PRAGMA application_id = 0x54694442;
14+
15+
PRAGMA user_version = 1;
1116

17+
CREATE TABLE Patches (
18+
FileName TEXT NOT NULL COLLATE NOCASE,
19+
Timestamp INTEGER NOT NULL,
20+
UNIQUE (FileName)
21+
);
22+
23+
ALTER TABLE Worlds RENAME COLUMN OnlineRecord TO OnlinePeak;
24+
ALTER TABLE Worlds RENAME COLUMN OnlineRecordTimestamp TO OnlinePeakTimestamp;
1225
ALTER TABLE Worlds ADD COLUMN LastStartup INTEGER NOT NULL DEFAULT 0;
1326
ALTER TABLE Worlds ADD COLUMN LastShutdown INTEGER NOT NULL DEFAULT 0;
27+
ALTER TABLE CharacterRights RENAME COLUMN Right TO Name;
28+
29+
COMMIT;
1430

sqlite/patches/9999-initial-data.sql renamed to sqlite/z-999-initial-data.sql

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
1-
-- NOTE(fusion): This file contains sample initial data and will be executed
2-
-- automatically as a patch by the query manager. See `sqlite/README.txt` for
3-
-- more details.
1+
-- NOTE(fusion): This file contains sample initial data and can be executed
2+
-- automatically as a patch by the query manager if placed at `sqlite/patches`.
3+
-- See `sqlite/README.txt` for more details.
44
--==============================================================================
55

66
INSERT INTO Worlds (WorldID, Name, Type, RebootTime, Host, Port, MaxPlayers,

‎src/querymanager.cc‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -780,8 +780,8 @@ int main(int argc, const char **argv){
780780
g_Config.SQLite.MaxCachedStatements = 100;
781781

782782
// PostgreSQL Config
783-
StringBufCopy(g_Config.PostgreSQL.Host, "localhost");
784-
StringBufCopy(g_Config.PostgreSQL.Port, "5432");
783+
StringBufCopy(g_Config.PostgreSQL.Host, "");
784+
StringBufCopy(g_Config.PostgreSQL.Port, "");
785785
StringBufCopy(g_Config.PostgreSQL.DBName, "tibia");
786786
StringBufCopy(g_Config.PostgreSQL.User, "tibia");
787787
StringBufCopy(g_Config.PostgreSQL.Password, "");

0 commit comments

Comments
 (0)