From 74afbb457aac6a1f6e2d4a2357768f710977a640 Mon Sep 17 00:00:00 2001 From: aurynn_cmd Date: Mon, 10 Sep 2007 23:02:09 +0000 Subject: Creation of next-gen admin.pl, admin.pm, and admin.sql systems. Admin.pm defines the new controller admin.pl is the direct view-backing software. POD is not completed, will be provided shortly. Full UI templates provided for the Reconciliation, Vendor, Customer, and Employee as well. UI/ is being delineated by subdirectories for Admin, Vendor, Customer and Employee, to date. Minor whitespace cleanup. git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1557 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/modules/admin.sql | 403 ++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 403 insertions(+) create mode 100644 sql/modules/admin.sql (limited to 'sql') diff --git a/sql/modules/admin.sql b/sql/modules/admin.sql new file mode 100644 index 00000000..103c10c1 --- /dev/null +++ b/sql/modules/admin.sql @@ -0,0 +1,403 @@ +CREATE OR REPLACE FUNCTION admin_add_user_to_role(in_user TEXT, in_role TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_user name; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.'; + END IF; + + select rolname into a_user from pg_roles where rolname = in_user; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.'; + END IF; + + stmt := 'GRANT '|| in_role ||' to '|| in_user; + + EXECUTE stmt; + + return 1; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_remove_user_from_role(in_user TEXT, in_role TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_user name; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.'; + END IF; + + select rolname into a_user from pg_roles where rolname = in_user; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions from a non-existant user.'; + END IF; + + stmt := 'REVOKE '|| in_role ||' FROM '|| in_user; + + EXECUTE stmt; + + return 1; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_add_function_to_group(in_func TEXT, in_role TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_user name; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.'; + END IF; + + select rolname into a_user from pg_roles where rolname = in_user; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.'; + END IF; + + stmt := 'GRANT EXECUTE ON FUNCTION '|| in_func ||' to '|| in_role; + + EXECUTE stmt; + + return 1; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_remove_function_from_group(in_func TEXT, in_role TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_user name; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.'; + END IF; + + select rolname into a_user from pg_roles where rolname = in_user; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions from a non-existant function.'; + END IF; + + stmt := 'REVOKE EXECUTE ON FUNCTION '|| in_func ||' FROM '|| in_role; + + EXECUTE stmt; + + return 1; + END; + + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_add_table_to_group(in_table TEXT, in_role TEXT, in_perm TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_user name; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.'; + END IF; + + select table_name into a_table from information_schema.tables + where table_schema NOT IN ('information_schema','pg_catalog','pg_toast') + and table_type='BASE TABLE' + and table_name = in_table; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions to a non-existant table.'; + END IF; + + if lower(in_perm) not in ('select','insert','update','delete') THEN + raise exception 'Cannot add unknown permission'; + END IF; + + stmt := 'GRANT '|| in_perm|| 'ON TABLE '|| in_table ||' to '|| in_role; + + EXECUTE stmt; + + return 1; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_remove_table_from_group(in_table TEXT, in_role TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_table text; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.'; + END IF; + + SELECT table_schema, table_name from + + select table_name into a_table from information_schema.tables + where table_schema NOT IN ('information_schema','pg_catalog','pg_toast') + and table_type='BASE TABLE' + and table_name = in_table; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions from a non-existant table.'; + END IF; + + stmt := 'REVOKE '|| in_role ||' FROM '|| in_user; + + EXECUTE stmt; + + return 1; + END; + +$$ language 'plpgsql'; + +create or replace function admin_get_user(in_user TEXT) returns setof user as $$ + + DECLARE + a_user user; + BEGIN + + select * into a_user from user where username = in_user; + IF NOT FOUND THEN + RAISE EXCEPTION 'cannot find user %', in_user; + END IF; + + return a_user; + + END; +$$ language plpgsql; + +create or replace function admin_get_roles_for_user(in_user TEXT) returns setof lsmb_roles as $$ + + declare + u_role lsmb_roles; + a_user user; + begin + select * into a_user from admin_get_user(in_user); + + FOR u_role IN select * from lsmb_roles WHERE user = a_user.id LOOP + + RETURN NEXT a_role; + + END LOOP; + RETURN; + end; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_save_user( + in_id int, + in_username text, + in_password TEXT, + in_dbname TEXT, + in_host TEXT, + in_port TEXT +) returns int AS $$ + DECLARE + + a_user user; + v_entity_id int; + p_id int; + l_id int; + stmt text; + BEGIN + + select * into a_user from user where id = in_id; + + IF NOT FOUND THEN + -- Insert cycle + + --- First, create an entity. + + if admin_is_user(in_username) then + + -- uhm, this is bad. + RAISE EXCEPTION + "Fatal exception: Username already exists in Postgres; not + a valid lsmb user."; + end if; + + v_entity_id := nextval('entity_id_seq'); + + INSERT INTO entity (id, name, entity_class) VALUES ( + v_entity_id, + in_first_name || ' ' || in_last_name, + 3 + ); + + -- create an actual user + insert into users (name, entity_id) VALUES ( + in_username, + v_entity_id + ); + + insert into user_connection (entity_id, database, host, port) + VALUES ( + v_entity_id, + in_database, + in_host, + in_port + ); + + -- Finally, issue the create user statement + + stmt := $$CREATE USER $$||in_username||$$WITH ENCRYPTED PASSWORD '$$||in_password||$$;'$$; + execute stmt; + + return v_entity_id; + + ELSIF FOUND THEN + + -- update cycle + + -- Only update if it's changed. Wewt. + UPDATE entity SET name = in_first_name || ' ' || in_last_name + WHERE entity_id = a_user.entity_id and + name <> in_first_name || ' ' || in_last_name; + + stmt := $$ alter user $$ || in_username || $$ with encrypted password $1$$$ || in_password || $$$1$ $$; + execute stmt; + + update user_connection set database = in_database, host = in_host, port = in_port + where database <> in_database + OR host <> in_host + OR port <> in_port; + + return a_user.id; + + END IF; + + END; +$$ language 'plpgsql'; + +create view role_view as + select * from pg_auth_members m join pg_authid a ON (m.roleid = a.oid); + + +create or replace function admin_is_group(in_group_name text) returns bool as $$ + + DECLARE + + existant_role role_view; + stmt text; + + BEGIN + select * into role_view from role_view where rolname = in_group_name; + + if not found then + return 'f'::bool; + + else + return 't'::bool; + end if; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_create_group(in_group_name TEXT, in_dbname TEXT) RETURNS int as $$ + + DECLARE + + stmt text; + + BEGIN + stmt := 'create role '||in_dbname||'_lsmb_$$' || in_group_name || '$$;'; + execute stmt; + return 1; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_delete_user(in_username TEXT) returns INT as $$ + + DECLARE + stmt text; + a_user user; + BEGIN + + select * into a_user from users where username = in_username; + + IF NOT FOUND THEN + + raise exception "User not found."; + ELSIF FOUND THEN + + stmt := $$ drop user $$ || a_user.username ||; + execute stmt; + + -- also gets user_connection + delete from users where id = a_user.id; + delete from entity where id = a_user.entity_id; + + END IF; + END; + +$$ language 'plpgsql'; + +comment on function admin_delete_user(text) is $$ + Drops the provided user, as well as deletes the entity and user configuration data. +$$; + +CREATE OR REPLACE FUNCTION admin_delete_group (in_group_name TEXT) returns bool as $$ + + DECLARE + stmt text; + a_role role_view; + BEGIN + + select * into a_role from role_view where rolname = in_group_name; + + if not found then + return 'f'::bool; + else + stmt := 'drop role $dbname_lsmb_$$' || in_group_name || '$$;'; + execute stmt; + return 't'::bool; + end if; + END; +$$ language 'plpgsql'; + +comment on function admin_delete_group(text) IS $$ + Deletes the input group from the database. Not designed to be used to + remove a login-capable user. +$$; \ No newline at end of file -- cgit v1.2.3