X-Git-Url: https://git.8kb.co.uk/?p=slony-i%2Fuser_replication;a=blobdiff_plain;f=user_replication.sql;fp=user_replication.sql;h=22d0cea9ec741ae08256e68efd4fedc1b4bee3a5;hp=0000000000000000000000000000000000000000;hb=8dcaa5a94ba2f13f1a513c87f3edb6d463fe9126;hpb=7dee8d1ca54bdcee8e3933e3af70d1199655fbed diff --git a/user_replication.sql b/user_replication.sql new file mode 100644 index 0000000..22d0cea --- /dev/null +++ b/user_replication.sql @@ -0,0 +1,305 @@ +-- *************************************************************************************************************** +-- 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. +-- *************************************************************************************************************** + +SET search_path TO public; + +DROP TABLE IF EXISTS public.replicated_users; +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; \ No newline at end of file