-- ***************************************************************************************************************
-- User management routines - Glyn Astill 21/03/2008
--
-- Creates table and triggers to create, alter and drop users based on inserts into the table
-- Creates a set of functions to manage users via rows in the table
-- Once the table is put into (slony) replication then users sould get replicated on all nodes.
-- ***************************************************************************************************************


CREATE TABLE public.replicated_users
(
  username text PRIMARY KEY,
  password BYTEA NOT NULL,
  options text
);  

--

DROP FUNCTION IF EXISTS public.decrypt_replicated_users(bytea, text);
CREATE OR REPLACE FUNCTION public.decrypt_replicated_users(in_pass bytea, in_username text) 
RETURNS text AS 
$BODY$
DECLARE
	v_use_hkey boolean;
BEGIN
  	v_use_hkey := false;
  	IF (v_use_hkey) THEN
  		RETURN pgp_sym_decrypt(in_pass, hkey(in_username));
  	ELSE
  		RETURN convert_from(in_pass, 'latin1');
  	END IF;
END;
$BODY$ 
LANGUAGE plpgsql IMMUTABLE;
REVOKE ALL ON FUNCTION public.decrypt_replicated_users(bytea, text) FROM PUBLIC;
  
--

DROP FUNCTION IF EXISTS public.encrypt_replicated_users(text, text);
CREATE OR REPLACE FUNCTION public.encrypt_replicated_users(in_pass text, in_username text) 
RETURNS bytea AS 
$BODY$
DECLARE
	v_use_hkey boolean;
BEGIN
  	v_use_hkey := false;
  	IF (v_use_hkey) THEN
  		RETURN pgp_sym_encrypt(in_pass, hkey(in_username));
  	ELSE
  		RETURN convert_to('md5' || md5(in_pass  || in_username), 'latin1');
  	END IF;
END;
$BODY$ 
LANGUAGE plpgsql IMMUTABLE;
REVOKE ALL ON FUNCTION public.encrypt_replicated_users(text, text) FROM PUBLIC;
  
--

DROP FUNCTION IF EXISTS public.replicate_users();
CREATE OR REPLACE FUNCTION public.replicate_users() 
RETURNS TRIGGER AS 
$BODY$
DECLARE
	v_query text;
	v_query2 text;
	v_query3 text;
	v_notice text;
BEGIN

	v_query := '';
	v_query2 := '';
	v_query3 := '';
	v_notice := '';

	IF (TG_OP <> 'DELETE') THEN
		IF ((upper(NEW.options) ~ 'SUPERUSER') OR (upper(NEW.options) ~ 'CREATEDB') OR (upper(NEW.options) ~ 'CREATEROLE')) THEN
			RAISE NOTICE 'USER REPLICATION SYSTEM: Sorry, restricted to creating users without SUPERUSER or CREATE options.';
			RETURN NULL;
		END IF;
	END IF;

	IF (TG_OP = 'INSERT') THEN
		IF ((NEW.username IS NOT NULL) AND (NEW.username <> '')) THEN   
			IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NOT NULL) AND (public.decrypt_replicated_users(NEW.password, NEW.username) <> '')) THEN
				v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' WITH ENCRYPTED PASSWORD  ' || quote_literal(public.decrypt_replicated_users(NEW.password, NEW.username)) || ' ';
			ELSE  
				v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' ';
			END IF;

			IF ((NEW.options IS NOT NULL) AND (NEW.options <> '') and (upper(NEW.options) ~ 'IN GROUP')) THEN
				v_query := v_query || NEW.options;
			END IF;

			v_notice := 'Create user: ' || NEW.username;
		ELSE
			v_notice := 'Create user failed: no username supplied';
		END IF;

		RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;

		IF (v_query <> '') THEN
			EXECUTE v_query;
			RETURN NEW;
		END IF;
	ELSEIF (TG_OP = 'UPDATE') THEN
		IF (NEW.username = OLD.username) THEN
			IF ((NEW.username IS NOT NULL) AND (NEW.username <> '')) THEN   
				IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NOT NULL) AND (public.decrypt_replicated_users(NEW.password, NEW.username) <> '')) THEN
					v_query := 'ALTER USER ' || quote_ident(NEW.username) || ' WITH ENCRYPTED PASSWORD  ' || quote_literal(public.decrypt_replicated_users(NEW.password, NEW.username));
					v_notice := 'Alter user: change password for ' || NEW.username;
				END IF;
				IF ((NEW.options IS NOT NULL) AND (NEW.options <> '')) THEN
					v_query2 := 'GRANT ' || replace(replace(NEW.options, 'IN GROUP ', ''),'in group ','') || ' TO ' || quote_ident(NEW.username);

					IF (v_notice = '') THEN
						v_notice := 'Alter user: ' || NEW.username || ' ' || NEW.options;
					ELSE 
	    					v_notice := v_notice || ' ' || NEW.options;
	  				END IF;

	  				IF ((OLD.options IS NOT NULL) AND (OLD.options <> '')) THEN
						v_query3 := 'REVOKE ' || replace(replace(OLD.options, 'IN GROUP ', ''),'in group ','') || ' FROM ' || quote_ident(NEW.username);
						v_notice := v_notice || ' (revoked ' || replace(replace(OLD.options, 'IN GROUP ', ''),'in group ','') || ')';
					END IF;
				END IF;

				IF (((NEW.options IS NULL) OR (NEW.options = '')) AND ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NULL) OR (public.decrypt_replicated_users(NEW.password, NEW.username) = ''))) THEN 
					v_notice := 'Alter user failed: no actions supplied';
				END IF;
			ELSE
				v_notice := 'Alter user failed: no username supplied';
			END IF;
		ELSE 
			v_notice := 'Alter user failed: cannot change username';
		END IF;

		RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;

		IF ((v_query <> '') or (v_query2 <> '')) THEN
			IF (v_query <> '') THEN
				EXECUTE v_query;
			END IF;
	
			IF (v_query2 <> '') THEN
				EXECUTE v_query3;
			END IF;

			IF (v_query3 <> '') THEN
				EXECUTE v_query2;
			END IF;
	
			IF ((NEW.options IS NULL) OR (NEW.options = '')) THEN
				NEW.options := OLD.options;
			END IF;
	
			IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NULL) OR (public.decrypt_replicated_users(NEW.password, NEW.username) = '')) THEN
				NEW.password := OLD.password;
			END IF;
	
			RETURN NEW;
		END IF;
	ELSEIF (TG_OP = 'DELETE') THEN
		IF ((OLD.username IS NOT NULL) AND (OLD.username <> '')) THEN
			v_query := 'DROP USER ' || quote_ident(OLD.username);
			v_notice := 'Drop user: ' || OLD.username;
		ELSE
			v_notice := 'Drop user failed: no username supplied';
		END IF;

		RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;

		IF (v_query <> '') THEN
			EXECUTE v_query;
			RETURN OLD;
		END IF;
	END IF;

	RETURN NULL;
  
END;
$BODY$ 
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
REVOKE ALL ON FUNCTION public.replicate_users() FROM PUBLIC;

--

CREATE TRIGGER replicate_users_trigger 
BEFORE INSERT OR UPDATE OR DELETE ON public.replicated_users
FOR EACH ROW EXECUTE PROCEDURE public.replicate_users() ;
ALTER TABLE public.replicated_users ENABLE ALWAYS TRIGGER replicate_users_trigger;

--

DROP FUNCTION IF EXISTS public.create_replicated_user(text, text, text);
CREATE OR REPLACE FUNCTION public.create_replicated_user(cusername text, cpassword text, coptions text) 
RETURNS boolean AS $BODY$    
BEGIN  
	INSERT into public.replicated_users (username, password, options) VALUES (cusername,public.encrypt_replicated_users(cpassword, cusername),coptions);
	IF FOUND THEN
		RETURN true;
	ELSE
		RETURN false;
	END IF;
END;
$BODY$ 
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
REVOKE ALL ON FUNCTION public.create_replicated_user(cusername text, cpassword text, coptions text) FROM PUBLIC;
   
--

DROP FUNCTION IF EXISTS public.drop_replicated_user(text);
CREATE OR REPLACE FUNCTION public.drop_replicated_user(cusername text) 
RETURNS boolean AS 
$BODY$
BEGIN
	DELETE FROM public.replicated_users WHERE username=cusername; 
	IF FOUND THEN
		RETURN true;
	ELSE
		RETURN false;
	END IF;
END;
$BODY$ 
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
REVOKE ALL ON FUNCTION public.drop_replicated_user(cusername text) FROM PUBLIC;

--

DROP FUNCTION IF EXISTS public.alter_replicated_user(text, text, text);
CREATE OR REPLACE FUNCTION public.alter_replicated_user(cusername text, cpassword text, coptions text) RETURNS boolean AS $BODY$
BEGIN  
	UPDATE public.replicated_users SET password = public.encrypt_replicated_users(cpassword, cusername), options =  coptions WHERE username = cusername;
	IF FOUND THEN
		RETURN true;
	ELSE
		RETURN false;
	END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
REVOKE ALL ON FUNCTION public.alter_replicated_user(cusername text, cpassword text, coptions text) FROM PUBLIC;

--
   
DROP FUNCTION IF EXISTS public.check_replicated_user(text);
CREATE OR REPLACE FUNCTION public.check_replicated_user(usr text) 
RETURNS integer AS
$BODY$
DECLARE 
	v_num integer;
BEGIN
	SELECT INTO v_num count(*) FROM public.replicated_users WHERE username = usr;
	RETURN v_num;
END;
$BODY$
LANGUAGE plpgsql STABLE SECURITY DEFINER;
REVOKE ALL ON FUNCTION public.check_replicated_user(usr text) FROM PUBLIC;

--

DROP FUNCTION IF EXISTS public.detail_replicated_user(text);
CREATE OR REPLACE FUNCTION public.detail_replicated_user(cusername text) 
RETURNS text AS 
$BODY$
DECLARE
	v_user_detail_rec record;
	v_strresult text;
BEGIN
	v_strresult := '';
	SELECT INTO v_user_detail_rec username, public.decrypt_replicated_users(password, username) AS "password", options FROM public.replicated_users WHERE username=cusername;
	IF FOUND THEN
		v_strresult :=  'Username : ' || v_user_detail_rec.username || E'\nPassword : ' || v_user_detail_rec.password || E'\nOptions  : ' || v_user_detail_rec.options || E'\n';
		RETURN v_strresult;
	ELSE
		v_strresult := 'Replicated user not found : ' || cusername || E'\n';
		RETURN v_strresult;
	END IF;
END;
$BODY$ 
LANGUAGE plpgsql STABLE SECURITY DEFINER;
REVOKE ALL ON FUNCTION public.detail_replicated_user(cusername text) FROM PUBLIC;

-- ***************************************************************************************************************
-- Other unrelated useful functions
-- ***************************************************************************************************************

-- Check for logged in user sessions > 1. Note that pooled sessions persist for connection_life_time or equivalent after logout.   
-- DROP FUNCTION IF EXISTS public.check_user_session(text, text);
-- CREATE OR REPLACE FUNCTION public.check_user_session(uame text, dname text) 
-- RETURNS boolean AS
-- $BODY$
-- 	SELECT CASE WHEN count(*) > 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2;
-- $BODY$ 
-- LANGUAGE sql STABLE;

-- Check if a user is logged in
-- DROP FUNCTION IF EXISTS public.check_user_logged(text, text);
-- CREATE OR REPLACE FUNCTION public.check_user_logged(uame text, dname text)
-- RETURNS boolean AS
-- $BODY$
-- 	SELECT CASE WHEN count(*) >= 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2;
-- $BODY$
-- LANGUAGE sql STABLE;