· 6 years ago · Apr 18, 2019, 02:16 PM
1-- Here Do some cleanup
2DROP DATABASE IF EXISTS trollrepellent;
3
4CREATE DATABASE trollrepellent TEMPLATE = template0;
5
6\connect trollrepellent
7
8create extension if not exists pgcrypto;
9create extension if not exists "uuid-ossp";
10
11
12drop role if exists anon;
13drop role if exists guest_user;
14
15create role anon;
16create role guest_user;
17
18create schema basic_auth;
19
20create table if not exists
21basic_auth.users (
22 id serial primary key,
23 email varchar(255) unique check ( email ~* '^.+@.+\..+$' ),
24 pass varchar(255) not null,
25 name varchar(255) not null default '',
26 role varchar(32) not null,
27 verified boolean not null default false,
28 created_at timestamp not null default current_timestamp
29);
30
31drop type if exists token_type_enum cascade;
32create type token_type_enum as enum ('validation', 'reset', 'login');
33
34create table if not exists
35basic_auth.tokens (
36 token uuid primary key,
37 token_type token_type_enum not null,
38 user_id int not null REFERENCES basic_auth.users (id)
39 on delete cascade on update cascade,
40 created_at timestamp not null default current_timestamp,
41 expires_at timestamp not null
42);
43
44create table external_tokens (
45 id SERIAL PRIMARY KEY,
46 user_id integer REFERENCES basic_auth.users (id) ON DELETE CASCADE,
47 site varchar(32) not null,
48 token varchar(32) not null,
49 created_at timestamp not null default current_timestamp
50);
51
52/*
53 * Check User Role
54 *
55 */
56create or replace function
57basic_auth.check_role_exists() returns trigger
58 language plpgsql
59 as $$
60begin
61 if not exists (select 1 from pg_roles as r where r.rolname = new.role) then
62 raise foreign_key_violation using message =
63 'unknown database role: ' || new.role;
64 return null;
65 end if;
66 return new;
67end
68$$;
69
70drop trigger if exists ensure_user_role_exists on basic_auth.users;
71create constraint trigger ensure_user_role_exists
72 after insert or update on basic_auth.users
73 for each row
74 execute procedure basic_auth.check_role_exists();
75
76
77/*
78 * Encrypt Password
79 *
80 */
81
82create or replace function
83basic_auth.encrypt_pass() returns trigger
84 language plpgsql
85 as $$
86begin
87 if TG_OP = 'INSERT' or new.pass <> old.pass then
88 new.pass = crypt(new.pass, gen_salt('md5'));
89 end if;
90 return new;
91end
92$$;
93
94
95drop trigger if exists encrypt_pass on basic_auth.users;
96create trigger encrypt_pass
97 before insert or update on basic_auth.users
98 for each row
99 execute procedure basic_auth.encrypt_pass();
100
101/*
102 * generate Signin token when they create user the first time
103 *
104 */
105
106create or replace function
107basic_auth.send_validation() returns trigger
108 language plpgsql
109 as $$
110declare
111 user_id int;
112 tok uuid;
113begin
114 select uuid_generate_v4() into tok;
115
116 insert into basic_auth.tokens (token, token_type, user_id, expires_at)
117 values (tok, 'login', new.id, now() + interval '1 week');
118
119 perform pg_notify('validate',
120 json_build_object(
121 'email', new.email,
122 'token', tok,
123 'token_type', 'login'
124 )::text
125 );
126 return new;
127end
128$$;
129
130drop trigger if exists send_validation on basic_auth.users;
131create trigger send_validation
132 after insert on basic_auth.users
133 for each row
134 execute procedure basic_auth.send_validation();
135
136
137create or replace function
138basic_auth.user_role(email text, pass text) returns name
139 language plpgsql
140 as $$
141begin
142 return (
143 select role from basic_auth.users
144 where users.email = user_role.email
145 and users.pass = crypt(user_role.pass, users.pass)
146 );
147end;
148$$;
149
150
151create or replace function
152basic_auth.current_email() returns text
153 language plpgsql
154 as $$
155begin
156 return current_setting('postgres.claims.email');
157exception
158 -- handle unrecognized configuration parameter error
159 when undefined_object then return '';
160end;
161$$;
162
163create or replace function
164request_password_reset(email text) returns void
165 language plpgsql
166 as $$
167declare
168 tok uuid;
169begin
170 delete from basic_auth.tokens
171 where token_type = 'reset'
172 and tokens.email = request_password_reset.email;
173
174 select uuid_generate_v4() into tok;
175
176 insert into basic_auth.tokens (token, token_type, email, expires_at)
177 values (tok, 'reset', request_password_reset.email, now + interval '4 hours');
178 perform pg_notify(
179 json_build_object(
180 'email', request_password_reset.email,
181 'token', tok,
182 'token_type', 'reset'
183 )::text
184 );
185end;
186$$;
187
188create or replace function
189reset_password(email text, token uuid, pass text)
190 returns void
191 language plpgsql
192 as $$
193declare
194 tok uuid;
195begin
196 if exists(select 1 from basic_auth.tokens
197 where tokens.email = reset_password.email
198 and tokens.token = reset_password.token
199 and tokens.token_type = 'reset'
200 and tokens.expires_at > now())
201 then
202 update basic_auth.users set pass=reset_password.pass
203 where users.email = reset_password.email;
204
205 delete from basic_auth.tokens
206 where tokens.email = reset_password.email
207 and tokens.token = reset_password.token
208 and token_type = 'reset';
209 else
210 raise invalid_password using message =
211 'invalid user or token';
212 end if;
213
214 /* Now Clear up all old tokens from password reset */
215 delete from basic_auth.tokens
216 where token_type = 'reset'
217 and tokens.email = reset_password.email;
218
219 select uuid_generate_v4() into tok;
220 insert into basic_auth.tokens (token, token_type, email)
221 values (tok, 'login', reset_password.email);
222
223 perform pg_notify(
224 json_build_object(
225 'email', reset_password.email,
226 'token', tok
227 )::text
228 );
229end;
230$$;
231
232create or replace function
233login(email_ text, pass_ text) returns json
234language plpgsql as $$
235declare
236 return_id int;
237 return_name varchar(255);
238 new_session_id uuid;
239 message varchar(255);
240 success boolean;
241 found_user basic_auth.users;
242 member_can_login boolean;
243 old_sessions uuid;
244begin
245 select false into success;
246 select 'Invalid username or password' into message;
247
248 select users.id
249 from basic_auth.users as users
250 where users.email = email_
251 and users.pass = crypt(pass_, users.pass)
252 into return_id;
253
254 if not return_id is null then
255 -- make sure the user has confirmed their email
256 select users.verified
257 from basic_auth.users as users
258 where users.id = return_id
259 into member_can_login;
260
261 if member_can_login then
262 -- You can login! and we have an ID
263 select true into success;
264 select 'Successfully logged in' into message;
265
266 select *
267 from basic_auth.users as users
268 where users.id = return_id
269 into found_user;
270
271 -- Find and remove all old tokens
272 if exists(select token from basic_auth.tokens where user_id = return_id and expires_at > now()) then
273 update basic_auth.tokens
274 set expires_at = now()
275 where user_id = return_id
276 and expires_at >= now();
277 end if;
278
279 -- generate a new session
280 select uuid_generate_v4() into new_session_id;
281
282 insert into basic_auth.tokens (token, token_type, user_id, expires_at)
283 values (new_session_id, 'login', return_id, now() + interval '1 week');
284
285 else
286 select 'You must verify your email first' into message;
287
288 end if;
289
290 end if;
291
292
293 return json_build_object(
294 'id', return_id,
295 'email', found_user.email,
296 'success', success,
297 'token', new_session_id,
298 'message', message,
299 'name', found_user.name
300 )::text;
301end;
302$$;
303
304create or replace function
305signup(email text, pass text) returns void
306language plpgsql as $$
307begin
308 insert into basic_auth.users (email, pass, role) values
309 (signup.email, signup.pass, 'guest_user');
310end;
311$$;
312
313insert into basic_auth.users (email, pass, name, role, verified)
314values
315('khasan222@gmail.com', 'testpassword1', 'Khalah Jones Golden', 'guest_user', true);
316('puhrez@gmail.com', 'testpassword1', 'Michael Perez', 'guest_user', true);