8xsKNjNU

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