git.8kb.co.uk Git - slony-i/user_replication/blob - user_replication.sql

   1 -- ***************************************************************************************************************\r
   2 -- User management routines - Glyn Astill 21/03/2008\r
   3 --\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
   8 \r
   9 SET search_path TO public;\r
  10 \r
  11 DROP TABLE IF EXISTS public.replicated_users;\r
  12 CREATE TABLE public.replicated_users\r
  13 (\r
  14   username text PRIMARY KEY,\r
  15   password BYTEA NOT NULL,\r
  16   options text\r
  17 );  \r
  18 \r
  19 --\r
  20 \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
  23 RETURNS text AS \r
  24 $BODY$\r
  25 DECLARE\r
  26         v_use_hkey boolean;\r
  27 BEGIN\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
  31         ELSE\r
  32                 RETURN convert_from(in_pass, 'latin1');\r
  33         END IF;\r
  34 END;\r
  35 $BODY$ \r
  36 LANGUAGE plpgsql IMMUTABLE;\r
  37 REVOKE ALL ON FUNCTION public.decrypt_replicated_users(bytea, text) FROM PUBLIC;\r
  38   \r
  39 --\r
  40 \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
  43 RETURNS bytea AS \r
  44 $BODY$\r
  45 DECLARE\r
  46         v_use_hkey boolean;\r
  47 BEGIN\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
  51         ELSE\r
  52                 RETURN convert_to('md5' || md5(in_pass  || in_username), 'latin1');\r
  53         END IF;\r
  54 END;\r
  55 $BODY$ \r
  56 LANGUAGE plpgsql IMMUTABLE;\r
  57 REVOKE ALL ON FUNCTION public.encrypt_replicated_users(text, text) FROM PUBLIC;\r
  58   \r
  59 --\r
  60 \r
  61 DROP FUNCTION IF EXISTS public.replicate_users();\r
  62 CREATE OR REPLACE FUNCTION public.replicate_users() \r
  63 RETURNS TRIGGER AS \r
  64 $BODY$\r
  65 DECLARE\r
  66         v_query text;\r
  67         v_query2 text;\r
  68         v_query3 text;\r
  69         v_notice text;\r
  70 BEGIN\r
  71 \r
  72         v_query := '';\r
  73         v_query2 := '';\r
  74         v_query3 := '';\r
  75         v_notice := '';\r
  76 \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
  80                         RETURN NULL;\r
  81                 END IF;\r
  82         END IF;\r
  83 \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
  88                         ELSE  \r
  89                                 v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' ';\r
  90                         END IF;\r
  91 \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
  94                         END IF;\r
  95 \r
  96                         v_notice := 'Create user: ' || NEW.username;\r
  97                 ELSE\r
  98                         v_notice := 'Create user failed: no username supplied';\r
  99                 END IF;\r
 100 \r
 101                 RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;\r
 102 \r
 103                 IF (v_query <> '') THEN\r
 104                         EXECUTE v_query;\r
 105                         RETURN NEW;\r
 106                 END IF;\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
 113                                 END IF;\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
 116 \r
 117                                         IF (v_notice = '') THEN\r
 118                                                 v_notice := 'Alter user: ' || NEW.username || ' ' || NEW.options;\r
 119                                         ELSE \r
 120                                                 v_notice := v_notice || ' ' || NEW.options;\r
 121                                         END IF;\r
 122 \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
 126                                         END IF;\r
 127                                 END IF;\r
 128 \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
 131                                 END IF;\r
 132                         ELSE\r
 133                                 v_notice := 'Alter user failed: no username supplied';\r
 134                         END IF;\r
 135                 ELSE \r
 136                         v_notice := 'Alter user failed: cannot change username';\r
 137                 END IF;\r
 138 \r
 139                 RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;\r
 140 \r
 141                 IF ((v_query <> '') or (v_query2 <> '')) THEN\r
 142                         IF (v_query <> '') THEN\r
 143                                 EXECUTE v_query;\r
 144                         END IF;\r
 145         \r
 146                         IF (v_query2 <> '') THEN\r
 147                                 EXECUTE v_query3;\r
 148                         END IF;\r
 149 \r
 150                         IF (v_query3 <> '') THEN\r
 151                                 EXECUTE v_query2;\r
 152                         END IF;\r
 153         \r
 154                         IF ((NEW.options IS NULL) OR (NEW.options = '')) THEN\r
 155                                 NEW.options := OLD.options;\r
 156                         END IF;\r
 157         \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
 160                         END IF;\r
 161         \r
 162                         RETURN NEW;\r
 163                 END IF;\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
 168                 ELSE\r
 169                         v_notice := 'Drop user failed: no username supplied';\r
 170                 END IF;\r
 171 \r
 172                 RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;\r
 173 \r
 174                 IF (v_query <> '') THEN\r
 175                         EXECUTE v_query;\r
 176                         RETURN OLD;\r
 177                 END IF;\r
 178         END IF;\r
 179 \r
 180         RETURN NULL;\r
 181   \r
 182 END;\r
 183 $BODY$ \r
 184 LANGUAGE plpgsql VOLATILE SECURITY DEFINER;\r
 185 REVOKE ALL ON FUNCTION public.replicate_users() FROM PUBLIC;\r
 186 \r
 187 --\r
 188 \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
 193 \r
 194 --\r
 195 \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
 199 BEGIN  \r
 200         INSERT into public.replicated_users (username, password, options) VALUES (cusername,public.encrypt_replicated_users(cpassword, cusername),coptions);\r
 201         IF FOUND THEN\r
 202                 RETURN true;\r
 203         ELSE\r
 204                 RETURN false;\r
 205         END IF;\r
 206 END;\r
 207 $BODY$ \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
 210    \r
 211 --\r
 212 \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
 216 $BODY$\r
 217 BEGIN\r
 218         DELETE FROM public.replicated_users WHERE username=cusername; \r
 219         IF FOUND THEN\r
 220                 RETURN true;\r
 221         ELSE\r
 222                 RETURN false;\r
 223         END IF;\r
 224 END;\r
 225 $BODY$ \r
 226 LANGUAGE plpgsql VOLATILE SECURITY DEFINER;\r
 227 REVOKE ALL ON FUNCTION public.drop_replicated_user(cusername text) FROM PUBLIC;\r
 228 \r
 229 --\r
 230 \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
 233 BEGIN  \r
 234         UPDATE public.replicated_users SET password = public.encrypt_replicated_users(cpassword, cusername), options =  coptions WHERE username = cusername;\r
 235         IF FOUND THEN\r
 236                 RETURN true;\r
 237         ELSE\r
 238                 RETURN false;\r
 239         END IF;\r
 240 END;\r
 241 $BODY$\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
 244 \r
 245 --\r
 246    \r
 247 DROP FUNCTION IF EXISTS public.check_replicated_user(text);\r
 248 CREATE OR REPLACE FUNCTION public.check_replicated_user(usr text) \r
 249 RETURNS integer AS\r
 250 $BODY$\r
 251 DECLARE \r
 252         v_num integer;\r
 253 BEGIN\r
 254         SELECT INTO v_num count(*) FROM public.replicated_users WHERE username = usr;\r
 255         RETURN v_num;\r
 256 END;\r
 257 $BODY$\r
 258 LANGUAGE plpgsql STABLE SECURITY DEFINER;\r
 259 REVOKE ALL ON FUNCTION public.check_replicated_user(usr text) FROM PUBLIC;\r
 260 \r
 261 --\r
 262 \r
 263 DROP FUNCTION IF EXISTS public.detail_replicated_user(text);\r
 264 CREATE OR REPLACE FUNCTION public.detail_replicated_user(cusername text) \r
 265 RETURNS text AS \r
 266 $BODY$\r
 267 DECLARE\r
 268         v_user_detail_rec record;\r
 269         v_strresult text;\r
 270 BEGIN\r
 271         v_strresult := '';\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
 273         IF FOUND THEN\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
 276         ELSE\r
 277                 v_strresult := 'Replicated user not found : ' || cusername || E'\n';\r
 278                 RETURN v_strresult;\r
 279         END IF;\r
 280 END;\r
 281 $BODY$ \r
 282 LANGUAGE plpgsql STABLE SECURITY DEFINER;\r
 283 REVOKE ALL ON FUNCTION public.detail_replicated_user(cusername text) FROM PUBLIC;\r
 284 \r
 285 -- ***************************************************************************************************************\r
 286 -- Other unrelated useful functions\r
 287 -- ***************************************************************************************************************\r
 288 \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
 293 -- $BODY$\r
 294 --      SELECT CASE WHEN count(*) > 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2;\r
 295 -- $BODY$ \r
 296 -- LANGUAGE sql STABLE;\r
 297 \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
 302 -- $BODY$\r
 303 --      SELECT CASE WHEN count(*) >= 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2;\r
 304 -- $BODY$\r
 305 -- LANGUAGE sql STABLE;