path: root/sql/modules/admin.sql

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.
$$;