summaryrefslogtreecommitdiff
path: root/sql/modules/admin.sql
blob: 5cb6773ad179ffcbf7631984d1eab0dfc0f2ee96 (plain)
  1. CREATE OR REPLACE FUNCTION admin_add_user_to_role(in_user TEXT, in_role TEXT) returns INT AS $$
  2. declare
  3. stmt TEXT;
  4. a_role name;
  5. a_user name;
  6. BEGIN
  7. -- Issue the grant
  8. select rolname into a_role from pg_roles where rolname = in_role;
  9. IF NOT FOUND THEN
  10. RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.';
  11. END IF;
  12. select rolname into a_user from pg_roles where rolname = in_user;
  13. IF NOT FOUND THEN
  14. RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.';
  15. END IF;
  16. stmt := 'GRANT '|| in_role ||' to '|| in_user;
  17. EXECUTE stmt;
  18. return 1;
  19. END;
  20. $$ language 'plpgsql';
  21. CREATE OR REPLACE FUNCTION admin_remove_user_from_role(in_user TEXT, in_role TEXT) returns INT AS $$
  22. declare
  23. stmt TEXT;
  24. a_role name;
  25. a_user name;
  26. BEGIN
  27. -- Issue the grant
  28. select rolname into a_role from pg_roles where rolname = in_role;
  29. IF NOT FOUND THEN
  30. RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.';
  31. END IF;
  32. select rolname into a_user from pg_roles where rolname = in_user;
  33. IF NOT FOUND THEN
  34. RAISE EXCEPTION 'Cannot revoke permissions from a non-existant user.';
  35. END IF;
  36. stmt := 'REVOKE '|| in_role ||' FROM '|| in_user;
  37. EXECUTE stmt;
  38. return 1;
  39. END;
  40. $$ language 'plpgsql';
  41. CREATE OR REPLACE FUNCTION admin_add_function_to_group(in_func TEXT, in_role TEXT) returns INT AS $$
  42. declare
  43. stmt TEXT;
  44. a_role name;
  45. a_user name;
  46. BEGIN
  47. -- Issue the grant
  48. select rolname into a_role from pg_roles where rolname = in_role;
  49. IF NOT FOUND THEN
  50. RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.';
  51. END IF;
  52. select rolname into a_user from pg_roles where rolname = in_user;
  53. IF NOT FOUND THEN
  54. RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.';
  55. END IF;
  56. stmt := 'GRANT EXECUTE ON FUNCTION '|| in_func ||' to '|| in_role;
  57. EXECUTE stmt;
  58. return 1;
  59. END;
  60. $$ language 'plpgsql';
  61. CREATE OR REPLACE FUNCTION admin_remove_function_from_group(in_func TEXT, in_role TEXT) returns INT AS $$
  62. declare
  63. stmt TEXT;
  64. a_role name;
  65. a_user name;
  66. BEGIN
  67. -- Issue the grant
  68. select rolname into a_role from pg_roles where rolname = in_role;
  69. IF NOT FOUND THEN
  70. RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.';
  71. END IF;
  72. select rolname into a_user from pg_roles where rolname = in_user;
  73. IF NOT FOUND THEN
  74. RAISE EXCEPTION 'Cannot revoke permissions from a non-existant function.';
  75. END IF;
  76. stmt := 'REVOKE EXECUTE ON FUNCTION '|| in_func ||' FROM '|| in_role;
  77. EXECUTE stmt;
  78. return 1;
  79. END;
  80. $$ language 'plpgsql';
  81. CREATE OR REPLACE FUNCTION admin_add_table_to_group(in_table TEXT, in_role TEXT, in_perm TEXT) returns INT AS $$
  82. declare
  83. stmt TEXT;
  84. a_role name;
  85. a_user name;
  86. BEGIN
  87. -- Issue the grant
  88. select rolname into a_role from pg_roles where rolname = in_role;
  89. IF NOT FOUND THEN
  90. RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.';
  91. END IF;
  92. select table_name into a_table from information_schema.tables
  93. where table_schema NOT IN ('information_schema','pg_catalog','pg_toast')
  94. and table_type='BASE TABLE'
  95. and table_name = in_table;
  96. IF NOT FOUND THEN
  97. RAISE EXCEPTION 'Cannot grant permissions to a non-existant table.';
  98. END IF;
  99. if lower(in_perm) not in ('select','insert','update','delete') THEN
  100. raise exception 'Cannot add unknown permission';
  101. END IF;
  102. stmt := 'GRANT '|| in_perm|| 'ON TABLE '|| in_table ||' to '|| in_role;
  103. EXECUTE stmt;
  104. return 1;
  105. END;
  106. $$ language 'plpgsql';
  107. CREATE OR REPLACE FUNCTION admin_remove_table_from_group(in_table TEXT, in_role TEXT) returns INT AS $$
  108. declare
  109. stmt TEXT;
  110. a_role name;
  111. a_table text;
  112. BEGIN
  113. -- Issue the grant
  114. select rolname into a_role from pg_roles where rolname = in_role;
  115. IF NOT FOUND THEN
  116. RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.';
  117. END IF;
  118. SELECT table_schema, table_name from
  119. select table_name into a_table from information_schema.tables
  120. where table_schema NOT IN ('information_schema','pg_catalog','pg_toast')
  121. and table_type='BASE TABLE'
  122. and table_name = in_table;
  123. IF NOT FOUND THEN
  124. RAISE EXCEPTION 'Cannot revoke permissions from a non-existant table.';
  125. END IF;
  126. stmt := 'REVOKE '|| in_role ||' FROM '|| in_user;
  127. EXECUTE stmt;
  128. return 1;
  129. END;
  130. $$ language 'plpgsql';
  131. create or replace function admin_get_user(in_user TEXT) returns setof user as $$
  132. DECLARE
  133. a_user user;
  134. BEGIN
  135. select * into a_user from user where username = in_user;
  136. IF NOT FOUND THEN
  137. RAISE EXCEPTION 'cannot find user %', in_user;
  138. END IF;
  139. return a_user;
  140. END;
  141. $$ language plpgsql;
  142. create or replace function admin_get_roles_for_user(in_user TEXT) returns setof lsmb_roles as $$
  143. declare
  144. u_role lsmb_roles;
  145. a_user user;
  146. begin
  147. select * into a_user from admin_get_user(in_user);
  148. FOR u_role IN select * from lsmb_roles WHERE user = a_user.id LOOP
  149. RETURN NEXT a_role;
  150. END LOOP;
  151. RETURN;
  152. end;
  153. $$ language 'plpgsql';
  154. CREATE OR REPLACE FUNCTION admin_save_user(
  155. in_id int,
  156. in_username text,
  157. in_password TEXT,
  158. in_dbname TEXT,
  159. in_host TEXT,
  160. in_port TEXT
  161. ) returns int AS $$
  162. DECLARE
  163. a_user user;
  164. v_entity_id int;
  165. p_id int;
  166. l_id int;
  167. stmt text;
  168. BEGIN
  169. select * into a_user from user where id = in_id;
  170. IF NOT FOUND THEN
  171. -- Insert cycle
  172. --- First, create an entity.
  173. if admin_is_user(in_username) then
  174. -- uhm, this is bad.
  175. RAISE EXCEPTION
  176. "Fatal exception: Username already exists in Postgres; not
  177. a valid lsmb user.";
  178. end if;
  179. v_entity_id := nextval('entity_id_seq');
  180. INSERT INTO entity (id, name, entity_class) VALUES (
  181. v_entity_id,
  182. in_first_name || ' ' || in_last_name,
  183. 3
  184. );
  185. -- create an actual user
  186. insert into users (name, entity_id) VALUES (
  187. in_username,
  188. v_entity_id
  189. );
  190. insert into user_connection (entity_id, database, host, port)
  191. VALUES (
  192. v_entity_id,
  193. in_database,
  194. in_host,
  195. in_port
  196. );
  197. -- Finally, issue the create user statement
  198. stmt := $$CREATE USER $$||in_username||$$WITH ENCRYPTED PASSWORD '$$||in_password||$$;'$$;
  199. execute stmt;
  200. return v_entity_id;
  201. ELSIF FOUND THEN
  202. -- update cycle
  203. -- Only update if it's changed. Wewt.
  204. UPDATE entity SET name = in_first_name || ' ' || in_last_name
  205. WHERE entity_id = a_user.entity_id and
  206. name <> in_first_name || ' ' || in_last_name;
  207. stmt := $$ alter user $$ || in_username || $$ with encrypted password $1$$$ || in_password || $$$1$ $$;
  208. execute stmt;
  209. update user_connection set database = in_database, host = in_host, port = in_port
  210. where database <> in_database
  211. OR host <> in_host
  212. OR port <> in_port;
  213. return a_user.id;
  214. END IF;
  215. END;
  216. $$ language 'plpgsql';
  217. create view role_view as
  218. select * from pg_auth_members m join pg_authid a ON (m.roleid = a.oid);
  219. create or replace function admin_is_group(in_group_name text) returns bool as $$
  220. DECLARE
  221. existant_role role_view;
  222. stmt text;
  223. BEGIN
  224. select * into role_view from role_view where rolname = in_group_name;
  225. if not found then
  226. return 'f'::bool;
  227. else
  228. return 't'::bool;
  229. end if;
  230. END;
  231. $$ language 'plpgsql';
  232. CREATE OR REPLACE FUNCTION admin_create_group(in_group_name TEXT, in_dbname TEXT) RETURNS int as $$
  233. DECLARE
  234. stmt text;
  235. BEGIN
  236. stmt := 'create role '||in_dbname||'_lsmb_$$' || in_group_name || '$$;';
  237. execute stmt;
  238. return 1;
  239. END;
  240. $$ language 'plpgsql';
  241. CREATE OR REPLACE FUNCTION admin_delete_user(in_username TEXT) returns INT as $$
  242. DECLARE
  243. stmt text;
  244. a_user user;
  245. BEGIN
  246. select * into a_user from users where username = in_username;
  247. IF NOT FOUND THEN
  248. raise exception "User not found.";
  249. ELSIF FOUND THEN
  250. stmt := $$ drop user $$ || a_user.username ||;
  251. execute stmt;
  252. -- also gets user_connection
  253. delete from users where id = a_user.id;
  254. delete from entity where id = a_user.entity_id;
  255. END IF;
  256. END;
  257. $$ language 'plpgsql';
  258. comment on function admin_delete_user(text) is $$
  259. Drops the provided user, as well as deletes the entity and user configuration data.
  260. $$;
  261. CREATE OR REPLACE FUNCTION admin_delete_group (in_group_name TEXT) returns bool as $$
  262. DECLARE
  263. stmt text;
  264. a_role role_view;
  265. BEGIN
  266. select * into a_role from role_view where rolname = in_group_name;
  267. if not found then
  268. return 'f'::bool;
  269. else
  270. stmt := 'drop role $dbname_lsmb_$$' || in_group_name || '$$;';
  271. execute stmt;
  272. return 't'::bool;
  273. end if;
  274. END;
  275. $$ language 'plpgsql';
  276. comment on function admin_delete_group(text) IS $$
  277. Deletes the input group from the database. Not designed to be used to
  278. remove a login-capable user.
  279. $$;
  280. CREATE OR REPLACE FUNCTION admin_list_roles(in_username text)
  281. RETURNS SETOF text AS
  282. $$
  283. DECLARE out_rolename RECORD;
  284. BEGIN
  285. FOR out_rolename IN
  286. SELECT rolname FROM pg_authid
  287. WHERE oid IN (SELECT id FROM connectby(
  288. '(SELECT m.member, m.roleid, r.oid FROM pg_authid r
  289. LEFT JOIN pg_auth_members m ON (r.oid = m.roleid)) a',
  290. 'oid', 'member', 'oid', '320461', '0', ','
  291. ) c(id integer, parent integer, "level" integer,
  292. path text, list_order integer)
  293. )
  294. LOOP
  295. RETURN NEXT out_rolename.rolname;
  296. END LOOP;
  297. END;
  298. $$ LANGUAGE PLPGSQL;
  299. -- TODO: Add admin user
  300. CREATE OR REPLACE FUNCTION admin_audit_log () returns int as $$
  301. $$ language plpgsql;