3 INSERT INTO config.upgrade_log (version) VALUES ('XXXX');
5 INSERT INTO permission.perm_list ( id, code, description ) VALUES
6 ( 615, 'ADMIN_REMOTEAUTH', oils_i18n_gettext( 615,
7 'Administer remote patron authentication', 'ppl', 'description' ));
9 CREATE TABLE config.remoteauth_profile (
10 name TEXT PRIMARY KEY,
12 context_org INT NOT NULL REFERENCES actor.org_unit(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
13 enabled BOOLEAN NOT NULL DEFAULT FALSE,
14 perm INT NOT NULL REFERENCES permission.perm_list(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
15 restrict_to_org BOOLEAN NOT NULL DEFAULT TRUE,
16 allow_inactive BOOL NOT NULL DEFAULT FALSE,
17 allow_expired BOOL NOT NULL DEFAULT FALSE,
21 CREATE OR REPLACE FUNCTION actor.permit_remoteauth (profile_name TEXT, userid BIGINT) RETURNS TEXT AS $func$
23 usr actor.usr%ROWTYPE;
24 profile config.remoteauth_profile%ROWTYPE;
26 context_org_list INT[];
32 SELECT INTO usr * FROM actor.usr WHERE id = userid AND NOT deleted;
37 IF usr.barred IS TRUE THEN
41 SELECT INTO profile * FROM config.remoteauth_profile WHERE name = profile_name;
42 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( profile.context_org );
44 -- user's home library must be within the context org
45 IF profile.restrict_to_org IS TRUE AND usr.home_ou NOT IN (SELECT * FROM UNNEST(context_org_list)) THEN
49 SELECT INTO perm code FROM permission.perm_list WHERE id = profile.perm;
50 IF permission.usr_has_perm(usr.id, perm, profile.context_org) IS FALSE THEN
54 IF usr.expire_date < NOW() AND profile.allow_expired IS FALSE THEN
58 IF usr.active IS FALSE AND profile.allow_inactive IS FALSE THEN
62 -- Proximity of user's home_ou to context_org to see if penalties should be ignored.
63 SELECT INTO home_prox prox FROM actor.org_unit_proximity WHERE from_org = usr.home_ou AND to_org = profile.context_org;
65 -- Loop through the block list to see if the user has any matching penalties.
66 IF profile.block_list IS NOT NULL THEN
67 FOR block IN SELECT UNNEST(STRING_TO_ARRAY(profile.block_list, '|')) LOOP
68 SELECT INTO penalty_count COUNT(DISTINCT csp.*)
69 FROM actor.usr_standing_penalty usp
70 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
71 WHERE usp.usr = usr.id
72 AND usp.org_unit IN ( SELECT * FROM UNNEST(context_org_list) )
73 AND ( usp.stop_date IS NULL or usp.stop_date > NOW() )
74 AND ( csp.ignore_proximity IS NULL OR csp.ignore_proximity < home_prox )
75 AND csp.block_list ~ block;
76 IF penalty_count > 0 THEN
77 -- User has penalties that match this block, so auth is not permitted.
78 -- Don't bother testing the rest of the block list.
84 -- User has passed all tests.
88 $func$ LANGUAGE plpgsql;