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