Schemaverse - Multi Instance Framework

galaxy.pl

#!/usr/bin/perl
#############################
# 	Galaxy v0.0.0.0.1       #
# Created by Josh McDougall #
#############################
# Sit this in a screen and walk away


# use module
use DBI; 
 

# Config Variables
my $db_name 	= "schemaverse";
my $db_username = "schemaverse";

my $instance_folder = "/home/abstract/sv/games";

my $sleep_time = 10;


$next_game_sql = <<SQLSTATEMENT;
		SELECT 
			galaxy.set_status(id, 'prep'), id, game_type_id, schema_name 
		FROM 
			galaxy.game
		WHERE  now() > (start_time-'1 hour'::interval) 
				AND status = 'waiting' AND NOT deleted LIMIT 1;
SQLSTATEMENT
 
$schema_status_update_sql = "SELECT galaxy.set_status(?,?)";
	
$game_type_sql = "SELECT bootstrap_sh, schema_sql FROM galaxy.game_type WHERE id=?";
	
$schema_name_check_sql = "SELECT exists(select schema_name FROM information_schema.schemata WHERE lower(schema_name) = lower(?))";

	
	
	 
#My quick off switch
while (1){ 

	
	# Make the master database connection
	my $master_connection = DBI->connect("dbi:Pg:dbname=${db_name};host=localhost", $db_username);
	$master_connection->{PrintError} = 0; 


	$schema_status_rs = $master_connection->prepare($schema_status_update_sql);

	# Find next game 
	$next_game_rs = $master_connection->prepare($next_game_sql); 
	$next_game_rs->execute();
	
	while (($prep, $game_id, $game_type_id, $schema_name) = $next_game_rs->fetchrow()) {
		
		
		
		$schema_name_check_rs = $master_connection->prepare($schema_name_check_sql);
		$schema_name_check_rs->execute($schema_name);
		($schema_name_check) = $schema_name_check_rs->fetchrow();
		$schema_name_check_rs->finish();
		
	
		if ($schema_name_check)
		{
	
			print "Tried to setup game ${game_id} but schema name was taken \n";
			$schema_status_rs->execute($game_id, 'error');
			$schema_status_rs->finish();
			
			break;
		
		} else {
	
			print "Creating Schema ${schema_name} for game ${game_id} \n";
			
			$master_connection->do("CREATE SCHEMA ${schema_name}");
			$master_connection->do("SET search_path TO ${schema_name}, galaxy");
			
		}

		
		$game_type_rs = $master_connection->prepare($game_type_sql); 
		$game_type_rs->execute($game_type_id);
	
		while (($bootstrap_sh, $schema_sql) = $game_type_rs->fetchrow()) {
			
			# Run the SQL to setup the Schema
			
			$master_connection->do($schema_sql);
			
			if( $master_connection->err ne undef ) {

				print "SQL for game ${game_id} has failed \n";
				$master_connection->do("DROP SCHEMA ${schema_name}");
				$schema_status_rs->execute($game_id, 'error');			
				$schema_status_rs->finish();
				
				break;

			} else {
				print "SQL for game ${game_id} has been run successfully \n";		
			}
	
			
			#setup the directory and run the bootstrap
			
			if (system ("mkdir -p ${instance_folder}/${game_id} 2> /dev/null") != 0) {
				print "Folder for game ${game_id} could not be created \n";
				$master_connection->do("DROP SCHEMA ${schema_name} CASCADE");
				$schema_status_rs->execute($game_id, 'error');			
				$schema_status_rs->finish();
			
				break;			
			} else {
				print "Folder for game ${game_id} has been created \n";							
			}
				
			open ($bootstrap_file, '>' ,"${instance_folder}/${game_id}/bootstrap.sh");
			print $bootstrap_file $bootstrap_sh;
			close $bootstrap_file; 
		
			system ("bash ${instance_folder}/${game_id}/bootstrap.sh");
		
		}
		$game_type_rs->finish();
	}
	
	
		
	$next_game_rs->finish();
	$master_connection->disconnect();
	
	
	sleep($sleep_time);

}

galaxy_schema.sql

CREATE SCHEMA galaxy;

CREATE ROLE players
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
  
  
-- Utilities

CREATE OR REPLACE FUNCTION galaxy.generate_string(len integer) RETURNS CHARACTER VARYING AS 
$generate_string$
BEGIN
	RETURN array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer) FROM generate_series(1,len)), '');
END
$generate_string$ LANGUAGE plpgsql;



---------------------------------------------------------------------------------------------------------------
-- Create the main player/user table for the galaxy with all required views and funcitons

CREATE TABLE galaxy.player
(
  id integer NOT NULL,
  username character varying NOT NULL,
  password character(40) NOT NULL,
  created timestamp without time zone NOT NULL DEFAULT now(),
  updated timestamp without time zone NOT NULL DEFAULT now(),
  default_library_id integer,
  symbol character(1),
  rgb character(6),
  CONSTRAINT player_pkey PRIMARY KEY (id),
  CONSTRAINT player_username_key UNIQUE (username),
  CONSTRAINT player_unq_symbol UNIQUE (symbol, rgb)
);

CREATE SEQUENCE galaxy.player_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;


-- Create the view so players can see their own data
CREATE OR REPLACE VIEW galaxy.my_player  WITH (security_barrier) AS SELECT * FROM galaxy.player WHERE username = SESSION_USER; 

CREATE RULE my_player_update AS ON UPDATE TO galaxy.my_player 
	DO INSTEAD UPDATE galaxy.player
		SET 
			password='md5' || encode(digest(NEW.password || lower(SESSION_USER),'md5'),'hex'),
			symbol=NEW.symbol,
			rgb=NEW.rgb
		WHERE id=NEW.id;

-- Create the overall players view so that players can see others in the system.
-- No rules required for this view
CREATE OR REPLACE VIEW galaxy.players AS SELECT id, username, created, updated, symbol, rgb FROM galaxy.player; 


-- Add the Schemaverse user as a placeholder account
INSERT INTO galaxy.player(id, username, password, symbol, rgb) VALUES(0,'schemaverse','nopass','#','000000'); 


-- Player creation trigger. This just creates the backend user now as game data is dealt with in the instance. 
-- Only trusted users should have the authority to insert into the player table and create new users. 
CREATE FUNCTION galaxy.player_creation() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS 
$player_creation$
BEGIN
	EXECUTE 'CREATE ROLE ' || NEW.username || ' WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE ENCRYPTED PASSWORD '''|| NEW.password ||'''  IN GROUP players'; 
	RETURN NEW;
END
$player_creation$;

CREATE TRIGGER player_creation AFTER INSERT ON galaxy.player
  FOR EACH ROW EXECUTE PROCEDURE galaxy.player_creation(); 

-- Player update trigger, handles updating the timestamp and the password
CREATE FUNCTION galaxy.player_update() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS 
$player_update$
BEGIN

	IF OLD.password <> NEW.password THEN
		EXECUTE 'ALTER ROLE ' || OLD.username || ' WITH ENCRYPTED PASSWORD '''|| NEW.password ||''' ';
	END IF;
	NEW.updated=NOW(); 
	RETURN NEW;
END
$player_update$;

CREATE TRIGGER player_update BEFORE UPDATE ON galaxy.player
  FOR EACH ROW EXECUTE PROCEDURE galaxy.player_update(); 

-- Helper functions for players

--Credit to xocolatl for optimizing this function
CREATE OR REPLACE FUNCTION galaxy.get_player_id(check_username name) RETURNS integer AS 
$get_player_id$
	SELECT id FROM galaxy.player WHERE username=$1;
$get_player_id$ LANGUAGE sql STABLE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION galaxy.get_player_username(player_id integer) RETURNS character varying AS 
$get_player_username$
	SELECT username FROM galaxy.player WHERE id=player_id;
$get_player_username$ LANGUAGE sql STABLE SECURITY DEFINER;



CREATE VIEW galaxy.online_players AS
	SELECT id, username FROM galaxy.player
		WHERE username in (SELECT DISTINCT usename FROM pg_stat_activity);


---------------------------------------------------------------------------------------------------------
-- The settings table and required views ----------------------------------------------------------------

CREATE TABLE galaxy.setting
(
	name character varying NOT NULL,
	numeric_value integer,
	char_value character varying,
	description TEXT,
	CONSTRAINT pk_setting PRIMARY KEY (name)
);


INSERT INTO galaxy.setting VALUES 
	('NAME',NULL,'The Elephant Way','The name of the galaxy'::TEXT),
	('HOST',NULL,'saucetel.com','The name of the galaxy'::TEXT),
	('MAX_INSTANCES',10,NULL,'The maximum number of instances on the system at any given time'::TEXT),
	('OMNI_USERNAME',NULL,'elephant','Username used to connect to the omni database'::TEXT),
	('OMNI_HOST', NULL, 'localhost','Host where the omni database is found'::TEXT),
	('OMNI_DATABASE',NULL,'omni','The name of the database on the omni host'::TEXT),
	('TRUSTED_USERS',NULL,'{0}','An int[] value used to restrict details, like game types, to only specific users');


CREATE OR REPLACE FUNCTION galaxy.get_numeric_setting(setting_name character varying) RETURNS integer AS 
$get_numeric_setting$
		SELECT numeric_value FROM galaxy.setting WHERE name = $1; 
$get_numeric_setting$  LANGUAGE sql;


CREATE OR REPLACE FUNCTION galaxy.get_char_setting(setting_name character varying) RETURNS character varying AS 
$get_char_setting$
		SELECT char_value FROM galaxy.setting WHERE name = $1;
$get_char_setting$ LANGUAGE sql;



------------------------------------------------------------------------------------------------------------------
-- The game type section 

CREATE TABLE galaxy.game_type 
(
	id INTEGER NOT NULL,
	permissions JSON NOT NULL DEFAULT '['|| SESSION_USER ||',"owner"]'::JSON,
	name CHARACTER VARYING NOT NULL,
	description TEXT,
	default_rules JSON,
	firm_rules JSON,
	schema_sql TEXT,
	bootstrap_sh TEXT,
	hide boolean DEFAULT 't',
	deleted boolean DEFAULT 'f',
	CONSTRAINT game_type_pkey PRIMARY KEY (id)
);

CREATE SEQUENCE galaxy.game_type_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;


CREATE OR REPLACE FUNCTION galaxy.check_game_type_permissions(permissions json, player_id integer, permission character varying default 'owner') RETURNS boolean AS
$$
 var p = JSON.parse(permission);
 if (p[player_id] == permission)
 	return true;
 return false;
$$ LANGUAGE PLV8;

CREATE OR REPLACE VIEW galaxy.game_types  WITH (security_barrier) AS 
	SELECT * FROM galaxy.game_type WHERE deleted = 'f' AND (  owners ? galaxy.get_player_id(SESSION_USER)::TEXT OR hide = 'f' );


CREATE RULE game_types_insert AS ON INSERT TO galaxy.game_types 
	DO INSTEAD INSERT INTO galaxy.game_type (owners, name, description, default_rules, firm_rules, schema_sql, bootstrap_sh, hide)
		VALUES(	HSTORE(galaxy.get_player_id(SESSION_USER)::TEXT,'owner'::TEXT),
				NEW.name,
				NEW.description,
				NEW.default_rules,
				NEW.firm_rules,
				NEW.schema_sql,
				NEW.bootstrap_sh,
				COALESCE(NEW.hide, 't'));
	
CREATE RULE game_types_update AS ON UPDATE TO galaxy.game_types 
	DO INSTEAD UPDATE galaxy.game_type
		SET 
			owners=NEW.owners,
			name=NEW.name,
			description=NEW.description,
			default_rules=NEW.default_rules,
			firm_rules=NEW.firm_rules,
			schema_sql=NEW.schema_sql,
			bootstrap_sh=NEW.bootstrap_sh,
			hide=NEW.hide	
		WHERE id=NEW.id;

CREATE RULE game_types_delete AS ON DELETE TO galaxy.game_types 
	DO INSTEAD UPDATE galaxy.game_type SET deleted = 't' WHERE id=OLD.id;


---------------------------------------------------------------------------------------------
-- Game tables, views and etc

CREATE TABLE galaxy.game
(
	id INTEGER NOT NULL,
	player_id INTEGER NOT NULL REFERENCES galaxy.player(id) ON DELETE CASCADE, 
	game_type_id INTEGER NOT NULL REFERENCES galaxy.game_type(id) ON DELETE CASCADE,
	schema_name CHARACTER VARYING NOT NULL DEFAULT 'galaxy_' || galaxy.generate_string(5),
	rules JSON,
	max_players INTEGER DEFAULT 5,
	start_time TIMESTAMP NOT NULL DEFAULT NOW(),
	repeat BOOLEAN DEFAULT true,
	status CHARACTER VARYING DEFAULT 'prep',
	hide BOOLEAN DEFAULT 'f',
	deleted BOOLEAN DEFAULT 'f',
	players_whitelist HSTORE,
	players_blacklist HSTORE,
	players HSTORE,
	CONSTRAINT game_pkey PRIMARY KEY (id),
	CONSTRAINT ck_game_status CHECK (lower(status) IN ('prep', 'waiting', 'setup', 'error', 'ready', 'underway', 'scoring', 'completed'))
);


CREATE SEQUENCE galaxy.game_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE OR REPLACE VIEW galaxy.games AS 
	SELECT * FROM galaxy.game WHERE NOT deleted AND (NOT hide OR player_id=galaxy.get_player_id(SESSION_USER));
	

CREATE RULE games_insert AS ON INSERT TO galaxy.games 
	DO INSTEAD INSERT INTO galaxy.game (player_id, game_type_id, rules, players, players_whitelist, players_blacklist, max_players, start_time, repeat, hide)
		VALUES(	galaxy.get_player_id(SESSION_USER),
				NEW.game_type_id,
				NEW.rules,
				NEW.players,
				NEW.players_whitelist,
				NEW.players_blacklist,
				NEW.max_players,
				COALESCE(NEW.start_time, NOW()),
				COALESCE(NEW.repeat, 'f'),
				COALESCE(NEW.hide, 'f'));
	
CREATE RULE games_update AS ON UPDATE TO galaxy.games 
	DO INSTEAD UPDATE galaxy.game
		SET 
			game_type_id=NEW.game_type_id,
			rules=NEW.rules,
			players=NEW.players,
			players_whitelist=NEW.players_whitelist,
			players_blacklist=NEW.players_blacklist,
			max_players=NEW.max_players,
			start_time=NEW.start_time,
			repeat=NEW.repeat,
			hide=NEW.hide
		WHERE id=NEW.id;

CREATE RULE games_delete AS ON DELETE TO galaxy.games 
	DO INSTEAD UPDATE galaxy.game SET deleted = 't' WHERE id=OLD.id;
	

-- Game Functions

CREATE OR REPLACE FUNCTION galaxy.join_game(game_id INTEGER DEFAULT NULL) RETURNS INTEGER AS 
$join_game$
DECLARE 
	player TEXT;
BEGIN
	player := galaxy.get_player_id(SESSION_USER)::TEXT;
	
	IF game_id IS NULL THEN
		SELECT id INTO game_id FROM galaxy.game WHERE 		
			status IN ('prep', 'waiting', 'setup', 'ready') 
			AND array_length(players, 1) < max_players 
			AND 
			(
				(
					(players_whitelist IS NOT NULL AND players_whitelist ? player)
					AND
					(players_blacklist IS NOT NULL AND NOT players_blacklist ? player)
				)
				OR 
				(players_blacklist IS NULL AND players_whitelist IS NULL)
			) ORDER BY start_time ASC LIMIT 1; 
	END IF;
	
	-- Concatenate to the players array
	UPDATE galaxy.game SET players = players || hstore(player, 'waiting'::TEXT) 	WHERE id = game_id;
	
	RETURN game_id;		
	   
END
$join_game$ LANGUAGE plpgsql SECURITY DEFINER;


CREATE OR REPLACE FUNCTION galaxy.leave_game(game_id INTEGER) RETURNS BOOLEAN AS 
$leave_game$
BEGIN

	UPDATE galaxy.game SET players = players - galaxy.get_player_id(SESSION_USER)::TEXT WHERE id = game_id;

	RETURN 't';		
	   
END
$leave_game$ LANGUAGE plpgsql SECURITY DEFINER;


CREATE OR REPLACE FUNCTION galaxy.warp(game_id integer) returns void AS 
$warp$
DECLARE
	_schema_name character varying;
BEGIN
	SELECT schema_name INTO _schema_name FROM galaxy.games WHERE id = game_id;  
	
	IF _schema_name IS NOT NULL AND NOT SESSION_USER = 'schemaverse' THEN
		EXECUTE 'ALTER USER '|| SESSION_USER || ' SET search_path TO galaxy, ' || _schema_name;		
		EXECUTE 'SET search_path TO galaxy, ' || _schema_name;
	END IF;
END
$warp$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION galaxy.set_status(_game_id INTEGER,_status character varying) RETURNS BOOLEAN AS 
$set_status$
BEGIN

	UPDATE galaxy.game SET status = _status WHERE id = _game_id;

	RETURN 't';		
	   
END
$set_status$ LANGUAGE plpgsql SECURITY DEFINER;

---------------------------------------------------------------------------------------------
-- Security Functions and triggers


-- This trigger forces complete control over ID's to this one function. 
-- Preventing any user form updating an ID or inserting an ID out of sequence
CREATE OR REPLACE FUNCTION galaxy.id_dealer() RETURNS trigger AS 
$id_dealer$
BEGIN

	IF (TG_OP = 'INSERT') THEN 
		NEW.id = nextval('galaxy.' || TG_TABLE_NAME || '_id_seq');
	ELSEIF (TG_OP = 'UPDATE') THEN
		NEW.id = OLD.id;
	END IF;
RETURN NEW;
END
$id_dealer$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER player_id_dealer BEFORE INSERT OR UPDATE ON galaxy.player
  FOR EACH ROW EXECUTE PROCEDURE galaxy.id_dealer(); 

CREATE TRIGGER game_type_id_dealer BEFORE INSERT OR UPDATE ON galaxy.game_type
  FOR EACH ROW EXECUTE PROCEDURE galaxy.id_dealer(); 
  
CREATE TRIGGER game_id_dealer BEFORE INSERT OR UPDATE ON galaxy.game
  FOR EACH ROW EXECUTE PROCEDURE galaxy.id_dealer();  




CREATE OR REPLACE FUNCTION galaxy.general_permission_check() RETURNS trigger AS
$$
	var plan = plv8.prepare( 'SELECT galaxy.get_player_username(SESSION_USER) as player_id, SESSION_USER as session_user, CURRENT_USER as current_user');
	var rows = plan.execute();
	
	var session_user = rows[0]['session_user'];
	var current_user = rows[0]['current_user'];
	var player_id= rows[0]['player_id'];

	plan.free();
	
 
    if (TG_OP == "UPDATE") {
        NEW.i = 102;
        return NEW;
    }
    
    if (session_user == 'schemaverse') { return NEW; } 
    
    if (TG_TABLE_NAME == 'game_type') {
    	if (TG_OP = 'UPDATE')
     	
    } 
     
    if (TG_TABLE_NAME == 'game'){
     	
    } 
    
    return null;
$$
LANGUAGE "plv8";

CREATE OR REPLACE FUNCTION general_permission_check() RETURNS trigger AS 
$general_permission_check$
DECLARE
        real_player_id integer;
        checked_player_id integer;
BEGIN
   IF SESSION_USER = 'schemaverse' THEN
   	RETURN NEW;
  ELSEIF CURRENT_USER = 'schemaverse' THEN
  	SELECT id INTO real_player_id FROM player WHERE username=SESSION_USER;

    IF TG_TABLE_NAME IN ('ship','fleet','trade_item') THEN
    	IF (TG_OP = 'DELETE') THEN
			RETURN OLD;
		ELSE 
			RETURN NEW;
		END IF;
    ELSEIF TG_TABLE_NAME = 'trade' THEN
       	IF TG_OP = 'UPDATE' THEN
        	IF (OLD.player_id_1 != NEW.player_id_1) OR (OLD.player_id_2 != NEW.player_id_2) THEN
            	RETURN NULL;
            END IF;
            IF NEW.confirmation_1 != OLD.confirmation_1 AND NEW.player_id_1 != real_player_id THEN
                                        RETURN NULL;
            END IF;
            IF NEW.confirmation_2 != OLD.confirmation_2 AND NEW.player_id_2 != real_player_id THEN
                                        RETURN NULL;
            END IF;
        ELSEIF TG_OP = 'DELETE' THEN
	        IF real_player_id in (OLD.player_id_1, OLD.player_id_2) THEN
					RETURN OLD;
			ELSE 
					RETURN NULL;
			END IF;
		END IF;
			
        IF real_player_id in (NEW.player_id_1, NEW.player_id_2) THEN
                                RETURN NEW;
        END IF;
    ELSEIF TG_TABLE_NAME in ('ship_control') THEN
        IF TG_OP = 'UPDATE' THEN
        	IF OLD.ship_id != NEW.ship_id THEN
                                        RETURN NULL;
            END IF;
        END IF;
        SELECT player_id INTO checked_player_id FROM ship WHERE id=NEW.ship_id;
        IF real_player_id = checked_player_id THEN
           	RETURN NEW;
               	END IF;
            END IF;
        ELSE

        	SELECT id INTO real_player_id FROM player WHERE username=SESSION_USER;

            IF TG_TABLE_NAME IN ('ship','fleet','trade_item') THEN
                    IF TG_OP = 'UPDATE' THEN
                                IF OLD.player_id != NEW.player_id THEN
                                        RETURN NULL;
                                END IF;
                        END IF;
                        NEW.player_id = real_player_id;
                        RETURN NEW;

                ELSEIF TG_TABLE_NAME = 'trade' THEN
                        IF TG_OP = 'UPDATE' THEN
                                IF (OLD.player_id_1 != NEW.player_id_1) OR (OLD.player_id_2 != NEW.player_id_2) THEN
                                        RETURN NULL;
                                END IF;
                                IF NEW.confirmation_1 != OLD.confirmation_1 AND NEW.player_id_1 != real_player_id THEN
                                        RETURN NULL;
                                END IF;
                                IF NEW.confirmation_2 != OLD.confirmation_2 AND NEW.player_id_2 != real_player_id THEN
                                        RETURN NULL;
                                END IF;
                        END IF;
                         IF real_player_id in (NEW.player_id_1, NEW.player_id_2) THEN
                                RETURN NEW;
                        END IF;
                ELSEIF TG_TABLE_NAME in ('ship_control') THEN
                        IF TG_OP = 'UPDATE' THEN
                                IF OLD.ship_id != NEW.ship_id THEN
                                        RETURN NULL;
				  END IF;
                        END IF;
                        SELECT player_id INTO checked_player_id FROM ship WHERE id=NEW.ship_id;
                        IF real_player_id = checked_player_id THEN
                                RETURN NEW;
                        END IF;
                END IF;
        END IF;
        RETURN NULL;
END
$general_permission_check$ LANGUAGE plpgsql SECURITY DEFINER;


--All start with the letter 'A' so that this check runs before everything else. 
--This should prevent users from forcing charges to another users account
CREATE TRIGGER a_game_type_permission_check BEFORE INSERT OR UPDATE ON galaxy.game_type
  FOR EACH ROW EXECUTE PROCEDURE galaxy.general_permission_check(); 








REVOKE ALL ON SCHEMA galaxy FROM players;
GRANT USAGE ON SCHEMA galaxy TO players;

GRANT ALL ON TABLE galaxy.player TO schemaverse;
REVOKE ALL ON TABLE galaxy.player FROM players;
REVOKE ALL ON TABLE galaxy.my_player FROM players;
REVOKE ALL ON TABLE galaxy.players FROM players;

GRANT SELECT ON TABLE galaxy.players TO players;
GRANT SELECT ON TABLE galaxy.my_player TO players;
GRANT UPDATE ON TABLE galaxy.my_player TO players;

GRANT ALL ON TABLE galaxy.setting TO schemaverse;
REVOKE ALL ON TABLE galaxy.setting FROM players;

GRANT ALL ON TABLE galaxy.game_type TO schemaverse;
REVOKE ALL ON TABLE galaxy.game_type FROM players;

GRANT SELECT ON TABLE galaxy.game_types TO players;
GRANT INSERT ON TABLE galaxy.game_types TO players;
GRANT UPDATE ON TABLE galaxy.game_types TO players;
GRANT DELETE ON TABLE galaxy.game_types TO players;

GRANT ALL ON TABLE galaxy.game TO schemaverse;
REVOKE ALL ON TABLE galaxy.game FROM players;

GRANT SELECT ON TABLE galaxy.games TO players;
GRANT INSERT ON TABLE galaxy.games TO players;
GRANT UPDATE ON TABLE galaxy.games TO players;
GRANT DELETE ON TABLE galaxy.games TO players;