1 -- ***************************************************************************************************************
\r
2 -- User management routines - Glyn Astill 21/03/2008
\r
4 -- Creates table and triggers to create, alter and drop users based on inserts into the table
\r
5 -- Creates a set of functions to manage users via rows in the table
\r
6 -- Once the table is put into (slony) replication then users sould get replicated on all nodes.
\r
7 -- ***************************************************************************************************************
\r
9 SET search_path TO public;
\r
11 DROP TABLE IF EXISTS public.replicated_users;
\r
12 CREATE TABLE public.replicated_users
\r
14 username text PRIMARY KEY,
\r
15 password BYTEA NOT NULL,
\r
21 DROP FUNCTION IF EXISTS public.decrypt_replicated_users(bytea, text);
\r
22 CREATE OR REPLACE FUNCTION public.decrypt_replicated_users(in_pass bytea, in_username text)
\r
28 v_use_hkey := false;
\r
29 IF (v_use_hkey) THEN
\r
30 RETURN pgp_sym_decrypt(in_pass, hkey(in_username));
\r
32 RETURN convert_from(in_pass, 'latin1');
\r
36 LANGUAGE plpgsql IMMUTABLE;
\r
37 REVOKE ALL ON FUNCTION public.decrypt_replicated_users(bytea, text) FROM PUBLIC;
\r
41 DROP FUNCTION IF EXISTS public.encrypt_replicated_users(text, text);
\r
42 CREATE OR REPLACE FUNCTION public.encrypt_replicated_users(in_pass text, in_username text)
\r
48 v_use_hkey := false;
\r
49 IF (v_use_hkey) THEN
\r
50 RETURN pgp_sym_encrypt(in_pass, hkey(in_username));
\r
52 RETURN convert_to('md5' || md5(in_pass || in_username), 'latin1');
\r
56 LANGUAGE plpgsql IMMUTABLE;
\r
57 REVOKE ALL ON FUNCTION public.encrypt_replicated_users(text, text) FROM PUBLIC;
\r
61 DROP FUNCTION IF EXISTS public.replicate_users();
\r
62 CREATE OR REPLACE FUNCTION public.replicate_users()
\r
77 IF (TG_OP <> 'DELETE') THEN
\r
78 IF ((upper(NEW.options) ~ 'SUPERUSER') OR (upper(NEW.options) ~ 'CREATEDB') OR (upper(NEW.options) ~ 'CREATEROLE')) THEN
\r
79 RAISE NOTICE 'USER REPLICATION SYSTEM: Sorry, restricted to creating users without SUPERUSER or CREATE options.';
\r
84 IF (TG_OP = 'INSERT') THEN
\r
85 IF ((NEW.username IS NOT NULL) AND (NEW.username <> '')) THEN
\r
86 IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NOT NULL) AND (public.decrypt_replicated_users(NEW.password, NEW.username) <> '')) THEN
\r
87 v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' WITH ENCRYPTED PASSWORD ' || quote_literal(public.decrypt_replicated_users(NEW.password, NEW.username)) || ' ';
\r
89 v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' ';
\r
92 IF ((NEW.options IS NOT NULL) AND (NEW.options <> '') and (upper(NEW.options) ~ 'IN GROUP')) THEN
\r
93 v_query := v_query || NEW.options;
\r
96 v_notice := 'Create user: ' || NEW.username;
\r
98 v_notice := 'Create user failed: no username supplied';
\r
101 RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;
\r
103 IF (v_query <> '') THEN
\r
107 ELSEIF (TG_OP = 'UPDATE') THEN
\r
108 IF (NEW.username = OLD.username) THEN
\r
109 IF ((NEW.username IS NOT NULL) AND (NEW.username <> '')) THEN
\r
110 IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NOT NULL) AND (public.decrypt_replicated_users(NEW.password, NEW.username) <> '')) THEN
\r
111 v_query := 'ALTER USER ' || quote_ident(NEW.username) || ' WITH ENCRYPTED PASSWORD ' || quote_literal(public.decrypt_replicated_users(NEW.password, NEW.username));
\r
112 v_notice := 'Alter user: change password for ' || NEW.username;
\r
114 IF ((NEW.options IS NOT NULL) AND (NEW.options <> '')) THEN
\r
115 v_query2 := 'GRANT ' || replace(replace(NEW.options, 'IN GROUP ', ''),'in group ','') || ' TO ' || quote_ident(NEW.username);
\r
117 IF (v_notice = '') THEN
\r
118 v_notice := 'Alter user: ' || NEW.username || ' ' || NEW.options;
\r
120 v_notice := v_notice || ' ' || NEW.options;
\r
123 IF ((OLD.options IS NOT NULL) AND (OLD.options <> '')) THEN
\r
124 v_query3 := 'REVOKE ' || replace(replace(OLD.options, 'IN GROUP ', ''),'in group ','') || ' FROM ' || quote_ident(NEW.username);
\r
125 v_notice := v_notice || ' (revoked ' || replace(replace(OLD.options, 'IN GROUP ', ''),'in group ','') || ')';
\r
129 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
130 v_notice := 'Alter user failed: no actions supplied';
\r
133 v_notice := 'Alter user failed: no username supplied';
\r
136 v_notice := 'Alter user failed: cannot change username';
\r
139 RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;
\r
141 IF ((v_query <> '') or (v_query2 <> '')) THEN
\r
142 IF (v_query <> '') THEN
\r
146 IF (v_query2 <> '') THEN
\r
150 IF (v_query3 <> '') THEN
\r
154 IF ((NEW.options IS NULL) OR (NEW.options = '')) THEN
\r
155 NEW.options := OLD.options;
\r
158 IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NULL) OR (public.decrypt_replicated_users(NEW.password, NEW.username) = '')) THEN
\r
159 NEW.password := OLD.password;
\r
164 ELSEIF (TG_OP = 'DELETE') THEN
\r
165 IF ((OLD.username IS NOT NULL) AND (OLD.username <> '')) THEN
\r
166 v_query := 'DROP USER ' || quote_ident(OLD.username);
\r
167 v_notice := 'Drop user: ' || OLD.username;
\r
169 v_notice := 'Drop user failed: no username supplied';
\r
172 RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;
\r
174 IF (v_query <> '') THEN
\r
184 LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
\r
185 REVOKE ALL ON FUNCTION public.replicate_users() FROM PUBLIC;
\r
189 CREATE TRIGGER replicate_users_trigger
\r
190 BEFORE INSERT OR UPDATE OR DELETE ON public.replicated_users
\r
191 FOR EACH ROW EXECUTE PROCEDURE public.replicate_users() ;
\r
192 ALTER TABLE public.replicated_users ENABLE ALWAYS TRIGGER replicate_users_trigger;
\r
196 DROP FUNCTION IF EXISTS public.create_replicated_user(text, text, text);
\r
197 CREATE OR REPLACE FUNCTION public.create_replicated_user(cusername text, cpassword text, coptions text)
\r
198 RETURNS boolean AS $BODY$
\r
200 INSERT into public.replicated_users (username, password, options) VALUES (cusername,public.encrypt_replicated_users(cpassword, cusername),coptions);
\r
208 LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
\r
209 REVOKE ALL ON FUNCTION public.create_replicated_user(cusername text, cpassword text, coptions text) FROM PUBLIC;
\r
213 DROP FUNCTION IF EXISTS public.drop_replicated_user(text);
\r
214 CREATE OR REPLACE FUNCTION public.drop_replicated_user(cusername text)
\r
215 RETURNS boolean AS
\r
218 DELETE FROM public.replicated_users WHERE username=cusername;
\r
226 LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
\r
227 REVOKE ALL ON FUNCTION public.drop_replicated_user(cusername text) FROM PUBLIC;
\r
231 DROP FUNCTION IF EXISTS public.alter_replicated_user(text, text, text);
\r
232 CREATE OR REPLACE FUNCTION public.alter_replicated_user(cusername text, cpassword text, coptions text) RETURNS boolean AS $BODY$
\r
234 UPDATE public.replicated_users SET password = public.encrypt_replicated_users(cpassword, cusername), options = coptions WHERE username = cusername;
\r
242 LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
\r
243 REVOKE ALL ON FUNCTION public.alter_replicated_user(cusername text, cpassword text, coptions text) FROM PUBLIC;
\r
247 DROP FUNCTION IF EXISTS public.check_replicated_user(text);
\r
248 CREATE OR REPLACE FUNCTION public.check_replicated_user(usr text)
\r
254 SELECT INTO v_num count(*) FROM public.replicated_users WHERE username = usr;
\r
258 LANGUAGE plpgsql STABLE SECURITY DEFINER;
\r
259 REVOKE ALL ON FUNCTION public.check_replicated_user(usr text) FROM PUBLIC;
\r
263 DROP FUNCTION IF EXISTS public.detail_replicated_user(text);
\r
264 CREATE OR REPLACE FUNCTION public.detail_replicated_user(cusername text)
\r
268 v_user_detail_rec record;
\r
272 SELECT INTO v_user_detail_rec username, public.decrypt_replicated_users(password, username) AS "password", options FROM public.replicated_users WHERE username=cusername;
\r
274 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
275 RETURN v_strresult;
\r
277 v_strresult := 'Replicated user not found : ' || cusername || E'\n';
\r
278 RETURN v_strresult;
\r
282 LANGUAGE plpgsql STABLE SECURITY DEFINER;
\r
283 REVOKE ALL ON FUNCTION public.detail_replicated_user(cusername text) FROM PUBLIC;
\r
285 -- ***************************************************************************************************************
\r
286 -- Other unrelated useful functions
\r
287 -- ***************************************************************************************************************
\r
289 -- Check for logged in user sessions > 1. Note that pooled sessions persist for connection_life_time or equivalent after logout.
\r
290 -- DROP FUNCTION IF EXISTS public.check_user_session(text, text);
\r
291 -- CREATE OR REPLACE FUNCTION public.check_user_session(uame text, dname text)
\r
292 -- RETURNS boolean AS
\r
294 -- SELECT CASE WHEN count(*) > 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2;
\r
296 -- LANGUAGE sql STABLE;
\r
298 -- Check if a user is logged in
\r
299 -- DROP FUNCTION IF EXISTS public.check_user_logged(text, text);
\r
300 -- CREATE OR REPLACE FUNCTION public.check_user_logged(uame text, dname text)
\r
301 -- RETURNS boolean AS
\r
303 -- SELECT CASE WHEN count(*) >= 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2;
\r
305 -- LANGUAGE sql STABLE;