VTZ5IPX6M76B26M42URFL5DY2WCJTHULZNJL7CBR44L2KCY75B3AC
-- Get available roles after authentication
DROP FUNCTION auth.check_otp(player_name_param CITEXT, otp CHAR(6));
CREATE OR REPLACE FUNCTION auth.check_otp(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20))
RETURNS TABLE (
authenticated BOOLEAN,
client_type auth.client_types) AS
$$
BEGIN
CREATE TEMP TABLE tmp_cnts ON COMMIT DROP 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));
RETURN QUERY SELECT t.*, p.client_type
FROM tmp_cnts t
CROSS JOIN games.players p
WHERE p.player_name = player_name_param
AND p.game_uid = game_uid_param;
END
$$ LANGUAGE plpgsql;
-- Add static passwords which could be set via protected with HTTPS web interface
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'users' AND n.nspname = 'auth' AND a.attname = 'web_password'
) THEN
ALTER TABLE auth.users ADD COLUMN web_password TEXT NULL;
END IF;
IF NOT EXISTS (
SELECT 1 FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'users' AND n.nspname = 'auth' AND a.attname = 'game_password'
) THEN
ALTER TABLE auth.users ADD COLUMN game_password TEXT NULL;
END IF;
END
$$;
-- Add allowed client type for user in the game
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_type t
INNER JOIN pg_namespace p ON t.typnamespace=p.oid
WHERE t.typname='client_types' AND p.nspname='auth'
) THEN
CREATE TYPE auth.client_types AS ENUM ('p', 'o', 'm');
END IF;
IF NOT EXISTS (
SELECT 1 FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'players' AND n.nspname = 'games' AND a.attname = 'client_type'
) THEN
ALTER TABLE games.players ADD COLUMN client_type auth.client_types NOT NULL DEFAULT 'p';
END IF;
END
$$;
-- Share chat between servers (2020-10-03)
-- Choose one server where chat will remain alive
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'chat_history' AND n.nspname = 'public' AND a.attname = 'server_id'
) THEN
ALTER TABLE chat_history ADD COLUMN server_id INT NOT NULL DEFAULT 1;
END IF;
END
$$;
-- Alter function to don't send OTP to delegate now:
DROP FUNCTION auth.check_contact;
CREATE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20))
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
INNER 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;
-- Alter function to send OTP to delegate also:
DROP FUNCTION auth.check_contact;
CREATE OR REPLACE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20))
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
INNER 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
UNION ALL
SELECT c.protocol, c.address
FROM games.players p
INNER JOIN auth.users u ON u.player_name = p.delegate_name
INNER JOIN auth.contacts c ON c.player_name = u.player_name
AND c.is_active = TRUE
AND c.delete_ts IS NULL
WHERE p.player_name = player_name_param
AND p.game_uid = game_uid_param
AND delegate_name IS NOT NULL;
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 field for team id:
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'players' AND n.nspname = 'games' AND a.attname = 'team_id'
) THEN
ALTER TABLE games.players ADD COLUMN team_id INT NOT NULL DEFAULT -1;
END IF;
END
$$;
-- Add field for delegate player:
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'players' AND n.nspname = 'games' AND a.attname = 'delegate_name'
) THEN
ALTER TABLE games.players ADD COLUMN delegate_name CITEXT REFERENCES auth.users(player_name) NULL;
END IF;
END
$$;
-- Add new argument to accept game UID to search for player delegate:
DROP FUNCTION IF EXISTS auth.check_contact(player_name_param CITEXT, otp CHAR(6));
DROP FUNCTION IF EXISTS auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid VARCHAR(20));
CREATE OR REPLACE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid VARCHAR(20))
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;
-- Create table itself:
CREATE TABLE IF NOT EXISTS games.games (
game_uid VARCHAR(20) PRIMARY KEY,
start_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL
);
GRANT SELECT ON games.games TO freeorion;
-- Create table to link users and games:
CREATE TABLE IF NOT EXISTS games.players (
game_uid VARCHAR(20) REFERENCES games.games(game_uid),
player_name CITEXT REFERENCES auth.users(player_name),
is_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
species VARCHAR(20) NOT NULL DEFAULT 'RANDOM',
CONSTRAINT pk_players PRIMARY KEY (game_uid, player_name)
);
GRANT SELECT ON games.players TO freeorion;