Start to use migrations files for PostgreSQL database

O01eg

Sep 9, 2022, 2:54 PM

IXY6NZLMLIDWQFPI2FBNF2PYJCM2TLNQIZKFUDLTTARHFWFQYCGQC

Dependencies

[2] 3ZVA64T6

Change contents

file addition: sql (d--r------)

[2.1]
file addition: README.md (----------)

[0.15]

# 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.
file addition: 20220909_9_games-schema.sql (----------)

[0.15]

CREATE SCHEMA IF NOT EXISTS games;
GRANT USAGE ON SCHEMA games TO freeorion;
file addition: 20220909_8_auth-email.sql (----------)

[0.15]

-- 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;
file addition: 20220909_7_protocol_email.sql (----------)

[0.15]

-- 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
$$;
file addition: 20220909_6_auth-grants.sql (----------)

[0.15]

-- 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;
file addition: 20220909_5_auth-functions.sql (----------)

[0.15]

-- 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;
file addition: 20220909_4_auth-tables.sql (----------)

[0.15]

-- 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
);
file addition: 20220909_3_auth-scheme.sql (----------)

[0.15]

-- 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;
file addition: 20220909_2_auth-ext.sql (----------)

[0.15]

-- 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;
file addition: 20220909_1_create_chat_history.sql (----------)

[0.15]

-- 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;
file addition: 20220909_0_revoke.sql (----------)

[0.15]

-- Revoke privileges to create table for non-root user
REVOKE CREATE ON SCHEMA public FROM PUBLIC;