3vxtrv9r

1
2-- authorize_web_method is used by the web service engine to authenticate a user and save them
3-- in a session table for other functions to retreive
4CREATE OR REPLACE FUNCTION authorize_web_method(username varchar, password varchar, procname varchar)
5RETURNS bool AS
6
7$BODY$
8DECLARE
9  authenticated bool;
10BEGIN
11 
12  -- Authenticate the user
13  SELECT INTO authenticated authenticate_user(username, password);
14  
15  -- Drop temp table if it exists
16  IF EXISTS (SELECT relname FROM pg_class WHERE relname = 'active_user') THEN
17    RAISE NOTICE 'Temp table exists';
18    DROP TABLE active_user;
19  END IF;
20
21  -- Create temp table
22  CREATE TEMPORARY TABLE active_user (user_name varchar(20) NOT NULL);
23
24  -- If we did not authenticate return false
25  IF NOT authenticated THEN
26    RETURN FALSE;
27  END IF;
28
29  -- If the user authenticated populate the active_user tempory table
30  IF authenticated THEN
31    INSERT INTO active_user (user_name) VALUES (username);
32  END IF;
33  
34  -- Is this user allowed to execute the procname? If not return false
35  IF NOT EXISTS(  
36                  SELECT role FROM web_method_roles WHERE procedure = procname AND role IN 
37                  (SELECT role FROM user_roles WHERE user_name = username)
38                ) THEN
39    RETURN FALSE;
40  END IF;
41  
42  -- The user can execute the method
43  RETURN TRUE;
44
45END;
46$BODY$
47LANGUAGE 'plpgsql' VOLATILE;