IXY6NZLMLIDWQFPI2FBNF2PYJCM2TLNQIZKFUDLTTARHFWFQYCGQC
# Setup SQL data
Public server uses PostgreSQL.
Create `freeorion` database:
```sql
CREATE DATABASE freeorion;
```
Create user `freeorion` for freeoriond server:
```sql
CREATE USER freeorion;
```
# Execute migrations
Migrations have format `YYYYMMDD_N_name...sql` Execute them on `freeorion` database.
CREATE SCHEMA IF NOT EXISTS games;
GRANT USAGE ON SCHEMA games TO freeorion;
-- Allow to use different protocols:
DROP FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6));
CREATE OR REPLACE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6))
RETURNS TABLE (
protocol auth.contact_protocol,
address CITEXT) AS
$$
BEGIN
CREATE TEMP TABLE tmp_cnts ON COMMIT DROP AS
SELECT c.protocol, c.address
FROM auth.users u
LEFT JOIN auth.contacts c ON c.player_name = u.player_name
AND c.is_active = TRUE
AND c.delete_ts IS NULL
WHERE u.player_name = player_name_param;
IF EXISTS (SELECT * FROM tmp_cnts t WHERE t.protocol IS NOT NULL AND t.address IS NOT NULL) THEN
WITH hashed_otp AS (VALUES (crypt(otp, gen_salt('bf', 8))))
INSERT INTO auth.otp (player_name, otp, create_ts)
VALUES (player_name_param, (TABLE hashed_otp), NOW()::timestamp)
ON CONFLICT (player_name) DO UPDATE SET otp = (TABLE hashed_otp), create_ts = NOW()::timestamp;
END IF;
RETURN QUERY SELECT *
FROM tmp_cnts;
END
$$ LANGUAGE plpgsql;
-- Add new type to contact enum
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_type t
INNER JOIN pg_namespace p ON t.typnamespace=p.oid
INNER JOIN pg_enum e ON e.enumtypid = t.oid
WHERE t.typname='contact_protocol' AND p.nspname='auth' AND e.enumlabel = 'email'
) THEN
ALTER TYPE auth.contact_protocol ADD VALUE 'email';
END IF;
END
$$;
-- Granting required priveleges for freeoriond:
GRANT SELECT ON auth.users TO freeorion;
GRANT SELECT ON auth.contacts TO freeorion;
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.otp TO freeorion;
-- Next create functions to work with auth data.
-- First is to check player in database. If player found function should save OTP and return active
-- contacts or `NULL` of there no available contacts. If there no player in database then function
-- returns 0 rows.
CREATE OR REPLACE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6))
RETURNS TABLE (
protocol auth.contact_protocol,
address CITEXT) AS
$$
BEGIN
CREATE TEMP TABLE tmp_cnts ON COMMIT DROP AS
SELECT c.protocol, c.address
FROM auth.users u
LEFT JOIN auth.contacts c ON c.player_name = u.player_name
AND c.is_active = TRUE
AND c.protocol = 'xmpp'
AND c.delete_ts IS NULL
WHERE u.player_name = player_name_param;
IF EXISTS (SELECT * FROM tmp_cnts t WHERE t.protocol IS NOT NULL AND t.address IS NOT NULL) THEN
WITH hashed_otp AS (VALUES (crypt(otp, gen_salt('bf', 8))))
INSERT INTO auth.otp (player_name, otp, create_ts)
VALUES (player_name_param, (TABLE hashed_otp), NOW()::timestamp)
ON CONFLICT (player_name) DO UPDATE SET otp = (TABLE hashed_otp), create_ts = NOW()::timestamp;
END IF;
RETURN QUERY SELECT *
FROM tmp_cnts;
END
$$ LANGUAGE plpgsql;
-- Second function checks if entered OTP correct:
CREATE OR REPLACE FUNCTION auth.check_otp(player_name_param CITEXT, otp CHAR(6))
RETURNS BOOLEAN AS
$$
WITH hashed_otp AS (DELETE FROM auth.otp WHERE otp.player_name = player_name_param RETURNING otp.otp)
SELECT (TABLE hashed_otp) IS NOT NULL AND (TABLE hashed_otp) = crypt(otp, (TABLE hashed_otp));
$$ LANGUAGE sql VOLATILE;
-- Create tables to store users, their contact data and OTPs:
CREATE TABLE IF NOT EXISTS auth.users (
player_name CITEXT PRIMARY KEY,
create_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL
);
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_type t
INNER JOIN pg_namespace p ON t.typnamespace=p.oid
WHERE t.typname='contact_protocol' AND p.nspname='auth'
) THEN
CREATE TYPE auth.contact_protocol AS ENUM ('xmpp');
END IF;
END
$$;
CREATE TABLE IF NOT EXISTS auth.contacts (
player_name CITEXT REFERENCES auth.users(player_name),
protocol auth.contact_protocol NOT NULL,
address CITEXT NOT NULL,
is_active BOOLEAN NOT NULL,
create_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
delete_ts TIMESTAMP WITHOUT TIME ZONE,
CONSTRAINT pk_contact PRIMARY KEY (protocol, address)
);
CREATE TABLE IF NOT EXISTS auth.otp (
player_name CITEXT PRIMARY KEY REFERENCES auth.users(player_name),
otp TEXT NOT NULL,
create_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL
);
-- Use separate schema as it contains sensitive data and grants access to freeoriond user
CREATE SCHEMA IF NOT EXISTS auth;
GRANT USAGE ON SCHEMA auth TO freeorion;
-- Enable `pgcrypto` extension to have safe storage for passwords:
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Enable `citext` extension to have case-insensitive column for logins and addresses;
CREATE EXTENSION IF NOT EXISTS citext;
-- Create table and grant freeoriond server to read from it and write new messages
CREATE TABLE IF NOT EXISTS chat_history(
ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
player_name VARCHAR(100) NULL,
text TEXT NOT NULL,
text_color BIGINT NULL
);
GRANT SELECT, INSERT ON chat_history TO freeorion;
-- Revoke privileges to create table for non-root user
REVOKE CREATE ON SCHEMA public FROM PUBLIC;