--
-- data model for ACS security
--
-- created by jsalz@mit.edu on Feb 2, 2000
-- adapted from code by kai@arsdigita.com
--

create table sec_sessions (
    -- Unique ID (don't care if everyone knows this)
    session_id            integer primary key,
    user_id               integer references users,
    -- A secret used for unencrypted connections
    token                 varchar(50) not null,
    -- A secret used for encrypted connections only. not generated until needed
    secure_token          varchar(50),
    browser_id            integer not null,
    -- Make sure all hits in this session are from same host
    last_ip               varchar(50) not null,
    -- When was the last hit from this session? (seconds since the epoch)
    last_hit              integer not null
);

create table sec_login_tokens (
    -- A table to track tokens assigned for permanent login. The login_token
    -- is isomorphic to the password, i.e., the user can use the login_token
    -- to log back in.
    user_id	integer references users not null,
    password    varchar(30) not null,
    login_token varchar(50) not null,
    primary key(user_id, password)
);

-- When a user changes his password, delete any login tokens associated
-- with the old password.
create function trig_users_update_login_token()
returns opaque as '
DECLARE
BEGIN
	delete from sec_login_tokens
	where user_id= NEW.user_id and password!= NEW.password;
	return NEW;
END;
' language 'plpgsql';

create trigger users_update_login_token
before update on users
for each row
execute procedure trig_users_update_login_token();

create table sec_session_properties (
    session_id     integer references sec_sessions not null,
    module         varchar(50) not null,
    property_name  varchar(50) not null,
    property_value varchar(4000),
    -- transmitted only across secure connections?
    secure_p       char(1) check(secure_p in ('t','f')),
    primary key(session_id, module, property_name),
    foreign key(session_id) references sec_sessions on delete cascade
);

create table sec_browser_properties (
    browser_id     integer not null,
    module         varchar(50) not null,
    property_name  varchar(50) not null,
    property_value varchar(4000),
    -- transmitted only across secure connections?
    secure_p       char(1) check(secure_p in ('t','f')),
    primary key(browser_id, module, property_name)
);

create sequence sec_id_seq;

-- DRB: the lock in this function would suck bigtime if it weren't
-- for the fact that this is only called every ten minutes per
-- connection *except* when a bunch of inline img etc tags cause
-- a flurry of simultaneous http requests to spawn multiple
-- threads each trying to update information for a single session.

create function sec_rotate_last_visit(integer, integer)
returns integer as '
DECLARE
	v_browser_id alias for $1;
	v_time alias for $2;
BEGIN
    lock table sec_browser_properties;
    delete from sec_browser_properties
        where browser_id = v_browser_id and module = ''acs'' and property_name = ''second_to_last_visit'';
    update sec_browser_properties
        set property_name = ''second_to_last_visit''
        where module = ''acs'' and property_name = ''last_visit'' and browser_id = v_browser_id;
    insert into sec_browser_properties(browser_id, module, property_name, property_value, secure_p)
        values(v_browser_id, ''acs'', ''last_visit'', v_time::char, ''f'');

    return 1;
end;
' language 'plpgsql';