Vqn98wWv

1/**************************************************************************************************
2Assumptions:
3	You installed, have access to, and permissions to create objects on a PostgreSQL 8.4+ database server.
4	You have created a user/role account called "security", and a database by that name, which is owned by
5		the "security" account.
6	You are running this script in that database, which should BE EMPTY.
7**************************************************************************************************/
8
9SET statement_timeout = 0;
10SET client_encoding = 'UTF8';
11SET standard_conforming_strings = on;
12SET check_function_bodies = true;
13SET client_min_messages = warning;
14SET search_path = public, pg_catalog;
15SET escape_string_warning = off;
16SET default_tablespace = '';
17SET default_with_oids = false;
18
19-- See the bottom of this script to change the default owner of the database objects.
20
21CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
22COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
23
24-- Create the users table
25DROP TABLE IF EXISTS users CASCADE;
26CREATE TABLE users (
27    user_id integer NOT NULL,
28    user_name character varying(250) NOT NULL,
29	application_name character varying(250) NOT NULL,
30    last_activity timestamp with time zone,
31    created timestamp with time zone DEFAULT now() NOT NULL,
32    email character varying(250),
33    salt character varying(250),
34    password bytea,
35    approved boolean DEFAULT true NOT NULL,
36    last_lockout timestamp with time zone,
37    last_login timestamp with time zone,
38    last_password_changed timestamp with time zone DEFAULT now() NOT NULL,
39    password_question character varying(1000),
40    password_answer bytea,
41    answer_salt character varying(250),
42    comment text
43);
44CREATE SEQUENCE users_user_id_seq
45    START WITH 1
46    INCREMENT BY 1
47    NO MAXVALUE
48    NO MINVALUE
49    CACHE 1;
50ALTER SEQUENCE users_user_id_seq OWNED BY users.user_id;
51ALTER TABLE users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'::regclass);
52ALTER TABLE ONLY users ADD CONSTRAINT pk_users PRIMARY KEY (user_id);
53CREATE INDEX ix_users_email ON users USING btree (application_name, email);
54CREATE INDEX ix_users_last_activity ON users USING btree (application_name, last_activity);
55CREATE UNIQUE INDEX ux_users_user_name_application_name ON users USING btree (application_name, user_name);
56
57-- Create the roles table
58DROP TABLE IF EXISTS roles CASCADE;
59CREATE TABLE roles (
60    role_id integer NOT NULL,
61    role_name character varying(250) NOT NULL,
62	application_name character varying(250) NOT NULL,
63    role_description text NULL
64);
65CREATE SEQUENCE roles_role_id_seq
66    START WITH 1
67    INCREMENT BY 1
68    NO MAXVALUE
69    NO MINVALUE
70    CACHE 1;
71ALTER SEQUENCE roles_role_id_seq OWNED BY roles.role_id;
72ALTER TABLE roles ALTER COLUMN role_id SET DEFAULT nextval('roles_role_id_seq'::regclass);
73ALTER TABLE ONLY roles ADD CONSTRAINT roles_pkey PRIMARY KEY (role_id);
74CREATE UNIQUE INDEX ux_roles_role_name_application_name ON roles USING btree (role_name, application_name);
75
76-- Create the user_login_activity table
77DROP TABLE IF EXISTS user_login_activity CASCADE;
78CREATE TABLE user_login_activity (
79    activity_id integer NOT NULL,
80    "when" timestamp with time zone DEFAULT now() NOT NULL,
81    "from" character varying(250) NOT NULL,
82    success boolean NOT NULL,
83    user_id integer NOT NULL
84);
85CREATE SEQUENCE user_login_activity_activity_id_seq
86    START WITH 1
87    INCREMENT BY 1
88    NO MAXVALUE
89    NO MINVALUE
90    CACHE 1;
91ALTER SEQUENCE user_login_activity_activity_id_seq OWNED BY user_login_activity.activity_id;
92ALTER TABLE user_login_activity ALTER COLUMN activity_id SET DEFAULT nextval('user_login_activity_activity_id_seq'::regclass);
93ALTER TABLE ONLY user_login_activity ADD CONSTRAINT pk_user_login_activity PRIMARY KEY (activity_id);
94CREATE INDEX ix_user_login_activity_user_id ON user_login_activity USING btree (user_id, "when");
95
96-- Create the users_roles table
97DROP TABLE IF EXISTS users_roles CASCADE;
98CREATE TABLE users_roles (
99    user_id integer NOT NULL,
100    role_id integer NOT NULL
101);
102ALTER TABLE ONLY users_roles ADD CONSTRAINT users_roles_pkey PRIMARY KEY (user_id, role_id);
103ALTER TABLE ONLY users_roles ADD CONSTRAINT users_roles_role_id_fkey FOREIGN KEY (role_id) REFERENCES roles(role_id) ON UPDATE CASCADE ON DELETE CASCADE;
104ALTER TABLE ONLY users_roles ADD CONSTRAINT users_roles_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
105
106
107/***************************************************************************************************************
108Functions and types
109***************************************************************************************************************/
110drop type if exists user_record cascade;
111create type user_record as(
112	user_id int,
113	user_name varchar(250),
114	last_activity timestamp with time zone,
115	created timestamp with time zone,
116	email varchar(250),
117	approved boolean,
118	last_lockout timestamp with time zone,
119	last_login timestamp with time zone,
120	last_password_changed timestamp with time zone,
121	password_question varchar(1000),
122	comment text
123);
124
125create function get_all_users(
126	_application_name varchar(250)) 
127	returns setof user_record as $$
128begin
129	return query
130	select
131		user_id,
132		user_name,
133		last_activity,
134		created,
135		email,
136		approved,
137		last_lockout,
138		last_login,
139		last_password_changed,
140		password_question,
141		comment
142	from
143		users
144	where
145		application_name = _application_name
146	order by
147		user_id asc;
148
149end;
150$$ language plpgsql;
151
152create function get_users_by_email(
153	partial_email varchar(250),
154	_application_name varchar(250)
155	)returns setof user_record as $$
156begin
157	return query
158	select
159		user_id,
160		user_name,
161		last_activity,
162		created,
163		email,
164		approved,
165		last_lockout,
166		last_login,
167		last_password_changed,
168		password_question,
169		comment
170	from
171		users
172	where
173		application_name = _application_name 
174		and email ilike '%' || partial_email || '%'
175	order by
176		user_id asc;
177end;
178$$ language plpgsql;
179
180create function get_users_by_username(
181	partial_username varchar(250),
182	_application_name varchar(250)
183	)returns setof user_record as $$
184begin
185	return query
186	select
187		user_id,
188		user_name,
189		last_activity,
190		created,
191		email,
192		approved,
193		last_lockout,
194		last_login,
195		last_password_changed,
196		password_question,
197		comment
198	from
199		users
200	where
201		application_name = _application_name
202		and user_name ilike '%' || partial_username || '%'
203	order by
204		user_id asc;
205end;
206$$ language plpgsql;
207
208create function get_users_online(
209	_session_timeout integer,
210	_appliction_name varchar(250)
211	) returns setof user_record as $$
212begin
213
214	return query
215	select
216		user_id,
217		user_name,
218		last_activity,
219		created,
220		email,
221		approved,
222		last_lockout,
223		last_login,
224		last_password_changed,
225		password_question,
226		comment
227	from
228		users
229	where
230		application_name = _application_name
231		and last_activity::time + cast(_session_timeout || ' minutes' as interval) < current_timestamp;	
232end;
233$$ language plpgsql;
234
235create or replace function get_online_count(
236	_session_timeout integer,
237	_application_name varchar(250)
238	) returns integer as $$
239begin
240	return (
241		select 
242			count(*) 
243		from 
244			users
245		where 
246			application_name = _application_name
247			and last_activity::time + cast(_session_timeout + ' minutes' as interval) < current_timestamp);
248end;
249$$ language plpgsql;
250
251create function get_user_by_username(
252	_user_name varchar(250),
253	_application_name varchar(250),
254	_online boolean
255	) returns setof user_record as $$
256begin
257if _online then
258	return query
259	update users
260	set 
261		last_activity = current_timestamp
262	where 
263		application_name = _application_name
264		and user_name = _user_name
265	returning
266		user_id,
267		user_name,
268		last_activity,
269		created,
270		email,
271		approved,
272		last_lockout,
273		last_login,
274		last_password_changed,
275		password_question,
276		comment;
277else
278	return query
279	select
280		user_id,
281		user_name,
282		last_activity,
283		created,
284		email,
285		approved,
286		last_lockout,
287		last_login,
288		last_password_changed,
289		password_question,
290		comment
291	from
292		users
293	where
294		application_name = _application_name
295		and user_name = _user_name;
296end if;
297end;
298$$ language plpgsql;
299
300create function get_user_by_id(
301	_user_id integer,
302	_online boolean
303	) returns setof user_record as $$
304begin
305if _online then
306	return query
307	update users
308	set 
309		last_activity = current_timestamp
310	where 
311		user_id = _user_id
312	returning
313		user_id,
314		user_name,
315		last_activity,
316		created,
317		email,
318		approved,
319		last_lockout,
320		last_login,
321		last_password_changed,
322		password_question,
323		comment;
324else
325	return query
326	select
327		user_id,
328		user_name,
329		last_activity,
330		created,
331		email,
332		approved,
333		last_lockout,
334		last_login,
335		last_password_changed,
336		password_question,
337		comment
338	from
339		users
340	where
341		user_id = _user_id;
342end if;
343end;
344$$ language plpgsql;
345
346create or replace function create_role(
347	_role_name varchar(250),
348	_application_name varchar(250),
349	_role_description text
350	) returns int as $$
351declare _role_id integer;
352begin
353	-- per the role provider pattern, need to throw an exception if the role exists already.
354	if exists(select null from roles where application_name = _application_name and role_name = _role_name) then
355		raise exception 'The role already exists in this application.' using errcode='DUPRL';
356	end if;
357
358	insert into roles
359		(
360		role_name,
361		application_name,
362		role_description
363		)
364	values
365		(
366		_role_name,
367		_application_name,
368		_role_description
369		)
370	returning role_id into _role_id;
371	return _role_id;
372end;
373$$ language plpgsql;
374
375create or replace function create_user(
376	_user_name varchar(250),
377	_application_name varchar(250),
378	_email varchar(250),
379	_approved boolean,
380	_email_is_unique boolean
381	) returns int as $$
382	declare userid integer;
383	begin
384		
385	if _email_is_unique
386		and exists(select null from users as u where u.application_name = _application_name and  u.email = _email) then
387		raise exception 'The email address specified is already in use for this application, and email addresses are configured to be unique.' using errcode='DUPEM';
388	end if;
389
390	if exists(select null from users as u where u.application_name = _application_name and u.user_name = _user_name) then
391		raise exception 'The user name specified is already in use for this application.' using errcode='DUPUN';
392	end if;
393
394	insert into users
395		(
396		user_name,
397		application_name,
398		email,
399		approved
400		)
401	values
402		(
403		_user_name,
404		_application_name,
405		_email,
406		_approved
407		)
408	returning user_id into userid;
409		
410	return userid;
411	end;
412	
413$$ language plpgsql;
414
415create or replace function delete_role(
416	_role_name varchar(250),
417	_application_name varchar(250),
418	_throw_on_populated boolean
419	) returns boolean as $$
420declare _role_id integer;
421begin
422	if _throw_on_populated and exists(
423		select null from roles as r
424		inner join users_roles as ur
425		on ur.role_id = r.role_id
426		where r.application_name = _application_name 
427		and r.role_name = _role_name
428		) then
429		raise exception 'The specified role is populated; cannot delete.' using errcode='RLPOP';
430	end if;
431
432	if not exists(select null from roles where application_name = _application_name and role_name = _role_name) then
433		raise exception 'The specified role does not exist.' using errcode='NOROL';
434	end if;
435
436	delete from roles
437	where application_name = _application_name
438		and role_name = _role_name;
439	return true;
440end;
441$$ language plpgsql;
442
443create or replace function delete_user(
444	_user_name varchar(250),
445	_application_name varchar(250),
446	_delete_related boolean
447	) returns boolean as $$
448begin
449if _delete_related then
450	delete from user_login_activity as ula
451	using users as u
452	where u.application_name = _application_name
453		and u.user_name = _user_name
454		and u.user_id = ula.user_id;
455end if;
456delete from users
457where application_name = _application_name 
458	and user_name = _user_name;
459return true;
460end;
461$$ language plpgsql;
462
463drop type if exists creds cascade;
464create type creds as (
465	"salt" varchar(250),
466	"password" bytea,
467	"password_answer" bytea,
468	"answer_salt" varchar(250),
469	"last_lockout" timestamp with time zone
470	);
471
472create or replace function get_user_credentials(
473	_user_name varchar(250),
474	_application_name varchar(250)
475	) returns setof creds as $$
476begin
477	return query
478	select
479		salt,
480		password,
481		password_answer,
482		answer_salt,
483		last_lockout
484	from
485		users
486	where
487		application_name = _application_name
488		and user_name = _user_name
489	limit 1;
490end;
491$$ language plpgsql;
492
493create or replace function get_user_name_by_email(
494	_email varchar(250),
495	_application_name varchar(250)
496	) returns varchar(250) as $$
497declare username varchar(250);
498begin
499	select
500		user_name
501	from
502		users
503	where
504		application_name = _application_name 
505		and email = _email
506	limit 1
507	into 
508		username;
509	return username;
510end;
511$$ language plpgsql;
512
513create or replace function record_login_event(
514	_user_name varchar(250),
515	_application_name varchar(250),
516	_origin varchar(250),
517	_success_indicator boolean,
518	_attempt_window integer,
519	_attempt_count integer
520	) returns boolean as $$
521declare userid integer;
522begin
523select
524	user_id
525from users as u
526where u.application_name = _application_name
527	and u.user_name = _user_name
528limit 1 into userid;
529
530insert into user_login_activity
531	(
532	"from",
533	success,
534	"user_id"
535	)
536values
537	(
538	_origin,
539	_success_indicator,
540	userid
541	);
542
543-- check for last x attempts if failure
544if not _success_indicator and _attempt_count <> 0 then
545	if (select count(*) from 
546		(select "success" from user_login_activity as ula
547			where ula.user_id = userid
548			and ula.when > current_timestamp - cast(_attempt_window || ' minutes' as interval)
549			order by ula.when desc limit _attempt_count) as last_login_attempts
550		where "success" = false) >= _attempt_count then
551
552	update users
553	set
554		last_lockout = current_timestamp
555	where
556		user_id = userid;
557	end if;
558end if;
559return true;
560end;
561$$ language plpgsql;
562
563create or replace function unlock_user(
564	_user_name varchar(250),
565	_application_name varchar(250)
566	) returns boolean as $$
567begin	
568	update users
569	set
570		last_lockout = null
571	where
572		application_name = _application_name
573		and user_name = _user_name;
574
575	return true;
576end;
577$$ language plpgsql;
578
579create or replace function update_user(
580	_user_id int,
581	_user_name varchar(250),
582	_application_name varchar(250),
583	_email varchar(250),
584	_approved boolean,
585	_comment text,
586	_email_is_unique boolean
587	) returns boolean as $$
588begin
589if _email_is_unique and exists(
590	select null from users where application_name = _application_name
591		and email = _email and user_id <> _user_id) then
592	raise exception 'The email address specified is already in use by another user.' using errcode='DUPEM';
593end if;
594update users
595set
596	user_name = _user_name,
597	email = _email,
598	approved = _approved,
599	comment = _comment
600where
601	user_id = _user_id;
602return true;
603end;
604$$ language plpgsql;
605
606create or replace function update_user_password(
607	_user_name varchar(250),
608	_application_name varchar(250),
609	_salt varchar(250),
610	_password bytea
611	) returns boolean as $$
612begin
613update users
614set
615	salt = _salt,
616	password = _password,
617	last_password_changed = current_timestamp
618where
619	application_name = _application_name
620	and user_name = _user_name;
621return true;
622end;
623$$ language plpgsql;
624
625create or replace function update_user_q_and_a(
626	_user_name varchar(250),
627	_application_name varchar(250),
628	_password_question varchar(1000),
629	_answer_salt varchar(250),
630	_password_answer bytea
631	) returns boolean as $$
632begin
633update users
634set
635	password_question = _password_question,
636	password_answer = _password_answer,
637	answer_salt = _answer_salt
638where
639	application_name = _application_name
640	and user_name = _user_name;
641return true;
642end;
643$$ language plpgsql;
644
645create or replace function get_users_in_role(
646	_role_name varchar(250),
647	_application_name varchar(250),
648	_partial_username varchar(250)
649	) returns setof user_record as $$
650begin
651
652if not exists (select null from roles where application_name = _application_name and role_name = _role_name) then
653	raise exception 'The specified role does not exist.' using errcode='ROLNA';
654end if;
655
656return query
657select
658	u.user_id,
659	u.user_name,
660	u.last_activity,
661	u.created,
662	u.email,
663	u.approved,
664	u.last_lockout,
665	u.last_login,
666	u.last_password_changed,
667	u.password_question,
668	u.comment
669from
670	roles as r
671inner join
672	users_roles as ur
673	on ur.role_id = r.role_id
674inner join
675	users as u
676	on u.user_id = ur.user_id
677where
678	r.role_name = _role_name
679	and r.application_name = _application_name
680	and u.user_name ilike '%' || _partial_username || '%'
681	and u.application_name = _application_name
682order by
683	u.user_name;
684end;
685$$ language plpgsql;
686
687create or replace function get_all_roles(
688	_application_name varchar(250)
689	) returns setof roles as $$
690begin
691return query
692select
693	*
694from
695	roles
696where
697	application_name = _application_name
698order by
699	role_name;
700end;
701$$ language plpgsql;
702
703create or replace function get_roles_for_user(
704	_user_name varchar(250),
705	_application_name varchar(250)
706	) returns setof roles as $$
707begin
708return query
709select
710	r.*
711from
712	users as u
713inner join
714	users_roles as ur
715	on ur.user_id = u.user_id
716inner join
717	roles as r
718	on r.role_id = ur.role_id
719where
720	u.user_name = _user_name
721	and u.application_name = _application_name
722	and r.application_name = _application_name;
723end;
724$$ language plpgsql;
725
726create or replace function user_is_in_role(
727	_user_name varchar(250),
728	_role_name varchar(250),
729	_application_name varchar(250)
730	) returns boolean as $$
731declare
732	retval boolean;
733begin
734-- per the roleprovider pattern, throw an exception if the role does not exist
735if not exists(select null from roles where role_name = _role_name and application_name = _application_name) then
736	raise exception 'The specified role does not exist.' using errcode='NOROL';
737end if;
738-- per the roleprovider pattern, throw an exception if the user does not exist
739if not exists(select null from users where user_name = _user_name and application_name = _application_name) then
740	raise exception 'The specified user does not exist.' using errcode='NOUSR';
741end if;
742
743select
744	exists(
745		select null
746		from users as u
747		inner join users_roles as ur
748		on ur.user_id = u.user_id
749		inner join roles as r
750		on r.role_id = ur.role_id
751		where
752			u.user_name = _user_name
753			and u.application_name = _application_name
754			and r.role_name = _role_name
755			and r.application_name = _application_name)
756into retval;
757return retval;
758end;
759$$ language plpgsql;
760
761create or replace function remove_users_from_roles(
762	_users varchar(250)[],
763	_roles varchar(250)[],
764	_application_name varchar(250)
765	) returns boolean as $$
766begin
767create temporary table usernames (username varchar(250) not null primary key) on commit drop;
768create temporary table rolenames (rolename varchar(250) not null primary key) on commit drop;
769-- Create the tables based off the arrays.
770insert into usernames
771	(
772	username
773	)
774select distinct
775	username
776from
777	unnest(_users) as username;
778
779insert into rolenames
780	(
781	rolename
782	)
783select distinct
784	rolename
785from
786	unnest(_roles) as rolename;
787
788-- Per the role provider pattern, an exception is to be thrown if any of the role names or user names specified
789-- do not exist for the given application name.
790if exists (select null from usernames as un left outer join users as u on u.application_name = _application_name and u.user_name = un.username where u.user_id is null)
791	or exists (select null from rolenames as rn left outer join roles as r on r.application_name = _application_name and r.role_name = rn.rolename where r.role_id is null) then
792	raise exception 'At least one user name or role specified does not exist in the application scope.' using errcode='MSING';
793end if;
794
795-- Insert the records linking the users to the roles, excluding pre-existing relationships.
796delete from users_roles
797using
798	usernames as un
799inner join
800	users as u on u.application_name = _application_name and u.user_name = un.username
801cross join
802	rolenames as rn
803inner join
804	roles as r on r.application_name = _application_name and r.role_name = rn.rolename
805where 
806	users_roles.user_id = u.user_id
807	and users_roles.role_id = r.role_id;
808return true;
809end;
810$$ language plpgsql;
811
812create or replace function role_exists(
813	_role_name varchar(250),
814	_application_name varchar(250)
815	) returns boolean as $$
816declare
817	retval boolean;
818begin
819
820select exists(
821	select null from roles where application_name = _application_name and role_name = _role_name)
822into retval;
823
824return retval;
825end;
826$$ language plpgsql;
827
828create or replace function assign_users_to_roles(
829	_users varchar(250)[],
830	_roles varchar(250)[],
831	_application_name varchar(250)
832	) returns boolean as $$
833begin
834create temporary table usernames (username varchar(250) not null primary key) on commit drop;
835create temporary table rolenames (rolename varchar(250) not null primary key) on commit drop;
836-- Create the tables based off the arrays.
837insert into usernames
838	(
839	username
840	)
841select distinct
842	username
843from
844	unnest(_users) as username;
845
846insert into rolenames
847	(
848	rolename
849	)
850select distinct
851	rolename
852from
853	unnest(_roles) as rolename;
854
855-- Per the role provider pattern, an exception is to be thrown if any of the role names or user names specified
856-- do not exist for the given application name.
857if exists (select null from usernames as un left outer join users as u on u.application_name = _application_name and u.user_name = un.username where u.user_id is null)
858	or exists (select null from rolenames as rn left outer join roles as r on r.application_name = _application_name and r.role_name = rn.rolename where r.role_id is null) then
859	raise exception 'At least one user name or role specified does not exist in the application scope.' using errcode='MSING';
860end if;
861
862-- Insert the records linking the users to the roles, excluding pre-existing relationships.
863insert into users_roles
864	(
865	user_id,
866	role_id
867	)
868select distinct
869	u.user_id,
870	r.role_id
871from
872	usernames as un
873inner join
874	users as u on u.application_name = _application_name and u.user_name = un.username
875cross join
876	rolenames as rn
877inner join
878	roles as r on r.application_name = _application_name and r.role_name = rn.rolename
879where not exists(
880	select null
881	from users_roles
882	where
883		user_id = u.user_id
884		and role_id = r.role_id
885	);
886
887return true;
888end;
889$$ language plpgsql;
890
891drop type if exists key_value_pair;
892create type key_value_pair as (
893	k varchar(250),
894	v text
895	);
896
897/**********************************************************************************************************
898Set object owners
899**********************************************************************************************************/
900update pg_class SET relowner = (SELECT oid FROM pg_roles WHERE rolname = 'security')
901where relnamespace = (select oid from pg_namespace where nspname = 'public' limit 1);
902
903update pg_proc set proowner = (select oid from pg_roles where rolname = 'security')
904where pronamespace = (select oid from pg_namespace where nspname = 'public' limit 1);