Remove irrelevant grant
[slony-i/user_replication] / 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;