--- /dev/null
+-- ***************************************************************************************************************\r
+-- User management routines - Glyn Astill 21/03/2008\r
+--\r
+-- Creates table and triggers to create, alter and drop users based on inserts into the table\r
+-- Creates a set of functions to manage users via rows in the table\r
+-- Once the table is put into (slony) replication then users sould get replicated on all nodes.\r
+-- ***************************************************************************************************************\r
+\r
+SET search_path TO public;\r
+\r
+DROP TABLE IF EXISTS public.replicated_users;\r
+CREATE TABLE public.replicated_users\r
+(\r
+ username text PRIMARY KEY,\r
+ password BYTEA NOT NULL,\r
+ options text\r
+); \r
+\r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.decrypt_replicated_users(bytea, text);\r
+CREATE OR REPLACE FUNCTION public.decrypt_replicated_users(in_pass bytea, in_username text) \r
+RETURNS text AS \r
+$BODY$\r
+DECLARE\r
+ v_use_hkey boolean;\r
+BEGIN\r
+ v_use_hkey := false;\r
+ IF (v_use_hkey) THEN\r
+ RETURN pgp_sym_decrypt(in_pass, hkey(in_username));\r
+ ELSE\r
+ RETURN convert_from(in_pass, 'latin1');\r
+ END IF;\r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql IMMUTABLE;\r
+REVOKE ALL ON FUNCTION public.decrypt_replicated_users(bytea, text) FROM PUBLIC;\r
+ \r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.encrypt_replicated_users(text, text);\r
+CREATE OR REPLACE FUNCTION public.encrypt_replicated_users(in_pass text, in_username text) \r
+RETURNS bytea AS \r
+$BODY$\r
+DECLARE\r
+ v_use_hkey boolean;\r
+BEGIN\r
+ v_use_hkey := false;\r
+ IF (v_use_hkey) THEN\r
+ RETURN pgp_sym_encrypt(in_pass, hkey(in_username));\r
+ ELSE\r
+ RETURN convert_to('md5' || md5(in_pass || in_username), 'latin1');\r
+ END IF;\r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql IMMUTABLE;\r
+REVOKE ALL ON FUNCTION public.encrypt_replicated_users(text, text) FROM PUBLIC;\r
+ \r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.replicate_users();\r
+CREATE OR REPLACE FUNCTION public.replicate_users() \r
+RETURNS TRIGGER AS \r
+$BODY$\r
+DECLARE\r
+ v_query text;\r
+ v_query2 text;\r
+ v_query3 text;\r
+ v_notice text;\r
+BEGIN\r
+\r
+ v_query := '';\r
+ v_query2 := '';\r
+ v_query3 := '';\r
+ v_notice := '';\r
+\r
+ IF (TG_OP <> 'DELETE') THEN\r
+ IF ((upper(NEW.options) ~ 'SUPERUSER') OR (upper(NEW.options) ~ 'CREATEDB') OR (upper(NEW.options) ~ 'CREATEROLE')) THEN\r
+ RAISE NOTICE 'USER REPLICATION SYSTEM: Sorry, restricted to creating users without SUPERUSER or CREATE options.';\r
+ RETURN NULL;\r
+ END IF;\r
+ END IF;\r
+\r
+ IF (TG_OP = 'INSERT') THEN\r
+ IF ((NEW.username IS NOT NULL) AND (NEW.username <> '')) THEN \r
+ IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NOT NULL) AND (public.decrypt_replicated_users(NEW.password, NEW.username) <> '')) THEN\r
+ v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' WITH ENCRYPTED PASSWORD ' || quote_literal(public.decrypt_replicated_users(NEW.password, NEW.username)) || ' ';\r
+ ELSE \r
+ v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' ';\r
+ END IF;\r
+\r
+ IF ((NEW.options IS NOT NULL) AND (NEW.options <> '') and (upper(NEW.options) ~ 'IN GROUP')) THEN\r
+ v_query := v_query || NEW.options;\r
+ END IF;\r
+\r
+ v_notice := 'Create user: ' || NEW.username;\r
+ ELSE\r
+ v_notice := 'Create user failed: no username supplied';\r
+ END IF;\r
+\r
+ RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;\r
+\r
+ IF (v_query <> '') THEN\r
+ EXECUTE v_query;\r
+ RETURN NEW;\r
+ END IF;\r
+ ELSEIF (TG_OP = 'UPDATE') THEN\r
+ IF (NEW.username = OLD.username) THEN\r
+ IF ((NEW.username IS NOT NULL) AND (NEW.username <> '')) THEN \r
+ IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NOT NULL) AND (public.decrypt_replicated_users(NEW.password, NEW.username) <> '')) THEN\r
+ v_query := 'ALTER USER ' || quote_ident(NEW.username) || ' WITH ENCRYPTED PASSWORD ' || quote_literal(public.decrypt_replicated_users(NEW.password, NEW.username));\r
+ v_notice := 'Alter user: change password for ' || NEW.username;\r
+ END IF;\r
+ IF ((NEW.options IS NOT NULL) AND (NEW.options <> '')) THEN\r
+ v_query2 := 'GRANT ' || replace(replace(NEW.options, 'IN GROUP ', ''),'in group ','') || ' TO ' || quote_ident(NEW.username);\r
+\r
+ IF (v_notice = '') THEN\r
+ v_notice := 'Alter user: ' || NEW.username || ' ' || NEW.options;\r
+ ELSE \r
+ v_notice := v_notice || ' ' || NEW.options;\r
+ END IF;\r
+\r
+ IF ((OLD.options IS NOT NULL) AND (OLD.options <> '')) THEN\r
+ v_query3 := 'REVOKE ' || replace(replace(OLD.options, 'IN GROUP ', ''),'in group ','') || ' FROM ' || quote_ident(NEW.username);\r
+ v_notice := v_notice || ' (revoked ' || replace(replace(OLD.options, 'IN GROUP ', ''),'in group ','') || ')';\r
+ END IF;\r
+ END IF;\r
+\r
+ 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 \r
+ v_notice := 'Alter user failed: no actions supplied';\r
+ END IF;\r
+ ELSE\r
+ v_notice := 'Alter user failed: no username supplied';\r
+ END IF;\r
+ ELSE \r
+ v_notice := 'Alter user failed: cannot change username';\r
+ END IF;\r
+\r
+ RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;\r
+\r
+ IF ((v_query <> '') or (v_query2 <> '')) THEN\r
+ IF (v_query <> '') THEN\r
+ EXECUTE v_query;\r
+ END IF;\r
+ \r
+ IF (v_query2 <> '') THEN\r
+ EXECUTE v_query3;\r
+ END IF;\r
+\r
+ IF (v_query3 <> '') THEN\r
+ EXECUTE v_query2;\r
+ END IF;\r
+ \r
+ IF ((NEW.options IS NULL) OR (NEW.options = '')) THEN\r
+ NEW.options := OLD.options;\r
+ END IF;\r
+ \r
+ IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NULL) OR (public.decrypt_replicated_users(NEW.password, NEW.username) = '')) THEN\r
+ NEW.password := OLD.password;\r
+ END IF;\r
+ \r
+ RETURN NEW;\r
+ END IF;\r
+ ELSEIF (TG_OP = 'DELETE') THEN\r
+ IF ((OLD.username IS NOT NULL) AND (OLD.username <> '')) THEN\r
+ v_query := 'DROP USER ' || quote_ident(OLD.username);\r
+ v_notice := 'Drop user: ' || OLD.username;\r
+ ELSE\r
+ v_notice := 'Drop user failed: no username supplied';\r
+ END IF;\r
+\r
+ RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;\r
+\r
+ IF (v_query <> '') THEN\r
+ EXECUTE v_query;\r
+ RETURN OLD;\r
+ END IF;\r
+ END IF;\r
+\r
+ RETURN NULL;\r
+ \r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql VOLATILE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.replicate_users() FROM PUBLIC;\r
+\r
+--\r
+\r
+CREATE TRIGGER replicate_users_trigger \r
+BEFORE INSERT OR UPDATE OR DELETE ON public.replicated_users\r
+FOR EACH ROW EXECUTE PROCEDURE public.replicate_users() ;\r
+ALTER TABLE public.replicated_users ENABLE ALWAYS TRIGGER replicate_users_trigger;\r
+\r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.create_replicated_user(text, text, text);\r
+CREATE OR REPLACE FUNCTION public.create_replicated_user(cusername text, cpassword text, coptions text) \r
+RETURNS boolean AS $BODY$ \r
+BEGIN \r
+ INSERT into public.replicated_users (username, password, options) VALUES (cusername,public.encrypt_replicated_users(cpassword, cusername),coptions);\r
+ IF FOUND THEN\r
+ RETURN true;\r
+ ELSE\r
+ RETURN false;\r
+ END IF;\r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql VOLATILE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.create_replicated_user(cusername text, cpassword text, coptions text) FROM PUBLIC;\r
+ \r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.drop_replicated_user(text);\r
+CREATE OR REPLACE FUNCTION public.drop_replicated_user(cusername text) \r
+RETURNS boolean AS \r
+$BODY$\r
+BEGIN\r
+ DELETE FROM public.replicated_users WHERE username=cusername; \r
+ IF FOUND THEN\r
+ RETURN true;\r
+ ELSE\r
+ RETURN false;\r
+ END IF;\r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql VOLATILE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.drop_replicated_user(cusername text) FROM PUBLIC;\r
+\r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.alter_replicated_user(text, text, text);\r
+CREATE OR REPLACE FUNCTION public.alter_replicated_user(cusername text, cpassword text, coptions text) RETURNS boolean AS $BODY$\r
+BEGIN \r
+ UPDATE public.replicated_users SET password = public.encrypt_replicated_users(cpassword, cusername), options = coptions WHERE username = cusername;\r
+ IF FOUND THEN\r
+ RETURN true;\r
+ ELSE\r
+ RETURN false;\r
+ END IF;\r
+END;\r
+$BODY$\r
+LANGUAGE plpgsql VOLATILE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.alter_replicated_user(cusername text, cpassword text, coptions text) FROM PUBLIC;\r
+\r
+--\r
+ \r
+DROP FUNCTION IF EXISTS public.check_replicated_user(text);\r
+CREATE OR REPLACE FUNCTION public.check_replicated_user(usr text) \r
+RETURNS integer AS\r
+$BODY$\r
+DECLARE \r
+ v_num integer;\r
+BEGIN\r
+ SELECT INTO v_num count(*) FROM public.replicated_users WHERE username = usr;\r
+ RETURN v_num;\r
+END;\r
+$BODY$\r
+LANGUAGE plpgsql STABLE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.check_replicated_user(usr text) FROM PUBLIC;\r
+\r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.detail_replicated_user(text);\r
+CREATE OR REPLACE FUNCTION public.detail_replicated_user(cusername text) \r
+RETURNS text AS \r
+$BODY$\r
+DECLARE\r
+ v_user_detail_rec record;\r
+ v_strresult text;\r
+BEGIN\r
+ v_strresult := '';\r
+ SELECT INTO v_user_detail_rec username, public.decrypt_replicated_users(password, username) AS "password", options FROM public.replicated_users WHERE username=cusername;\r
+ IF FOUND THEN\r
+ v_strresult := 'Username : ' || v_user_detail_rec.username || E'\nPassword : ' || v_user_detail_rec.password || E'\nOptions : ' || v_user_detail_rec.options || E'\n';\r
+ RETURN v_strresult;\r
+ ELSE\r
+ v_strresult := 'Replicated user not found : ' || cusername || E'\n';\r
+ RETURN v_strresult;\r
+ END IF;\r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql STABLE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.detail_replicated_user(cusername text) FROM PUBLIC;\r
+\r
+-- ***************************************************************************************************************\r
+-- Other unrelated useful functions\r
+-- ***************************************************************************************************************\r
+\r
+-- Check for logged in user sessions > 1. Note that pooled sessions persist for connection_life_time or equivalent after logout. \r
+-- DROP FUNCTION IF EXISTS public.check_user_session(text, text);\r
+-- CREATE OR REPLACE FUNCTION public.check_user_session(uame text, dname text) \r
+-- RETURNS boolean AS\r
+-- $BODY$\r
+-- SELECT CASE WHEN count(*) > 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2;\r
+-- $BODY$ \r
+-- LANGUAGE sql STABLE;\r
+\r
+-- Check if a user is logged in\r
+-- DROP FUNCTION IF EXISTS public.check_user_logged(text, text);\r
+-- CREATE OR REPLACE FUNCTION public.check_user_logged(uame text, dname text)\r
+-- RETURNS boolean AS\r
+-- $BODY$\r
+-- SELECT CASE WHEN count(*) >= 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2;\r
+-- $BODY$\r
+-- LANGUAGE sql STABLE;
\ No newline at end of file
projects / slony-i / user_replication / blobdiff