4 * Use pgmemcache and memcached to increase the speed of permission tests
5 * ----------------------------------------------------------------------
7 * This set of functions allows the use of memcached as a caching mechanism for
8 * permission checks. It is transparent and optional. If memcache is not set
9 * up, either by not running or the lack of the pgmemcache postgres addon,
10 * then the default, existing behaviour is preserved and live database queries
11 * are used to test all permissions.
14 * On postgres 8.2 and before, pgmemcache 1.1 is required. For this older
15 * version of pgmemcache, configuration of memcached servers is performed by
16 * stored procs. Therefore, the installer of this Evergreen addition must
17 * edit the stored proc called permission.old_mc_servers() to initialize the
18 * appropriate set of memcached servers. For simple, single-database
19 * installations, the default of 'localhost' is most likely the desired
23 * On postgres 8.3 and later, pgmemcache 2.x is required. In this new
24 * pgmemcache the server configuration is controlled from within the
25 * postgresql.conf file via user-defined variables read by the pgmemcache
26 * intialization routines. Please see the README for pgmemcache at
28 * http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgmemcache/pgmemcache/README.pgmemcache?rev=1.21&content-type=text/plain
30 * or in the release tarball that was installed for details on configuration.
33 * TODO: Make the cache timeout configurable via a global setting for EG 2.0
37 CREATE OR REPLACE FUNCTION permission.old_mc_servers() RETURNS BOOL AS $f$
39 PERFORM memcache_server_add('localhost', '11211');
43 CREATE OR REPLACE FUNCTION permission.mc_init() RETURNS BOOL AS $f$
48 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT < 8.3 THEN
50 IF memcache_init() THEN
51 PERFORM permission.old_mc_servers();
53 -- RAISE NOTICE 'Old postgres, must be old pgmemcache';
57 IF memcache_init() THEN
58 PERFORM permission.old_mc_servers();
60 -- RAISE NOTICE 'New postgres, but old pgmemcache';
61 EXCEPTION WHEN OTHERS THEN
66 IF NOT old_memcache THEN
67 PERFORM current_setting('pgmemcache.default_servers');
68 -- RAISE NOTICE 'New postgres, new pgmemcache';
71 -- no exception, we're good
73 EXCEPTION WHEN OTHERS THEN
78 CREATE OR REPLACE FUNCTION permission.set_cached_perm( iusr INT, tperm TEXT, iorg INT, bool_value BOOL, timeout INTERVAL ) RETURNS BOOL AS $f$
80 IF permission.mc_init() THEN
81 -- RAISE NOTICE 'Setting perm cache';
83 EXECUTE $$SELECT memcache_set('oils_permcache_$$ || iusr || tperm || iorg || $$', 't',$$ || quote_literal(timeout) || $$::INTERVAL);$$;
85 EXECUTE $$SELECT memcache_set('oils_permcache_$$ || iusr || tperm || iorg || $$', 'f',$$ || quote_literal(timeout) || $$::INTERVAL);$$;
90 EXCEPTION WHEN OTHERS THEN
93 $f$ LANGUAGE PLPGSQL STABLE;
95 CREATE OR REPLACE FUNCTION permission.set_cached_perm( iusr INT, tperm TEXT, iorg INT, bool_value BOOL ) RETURNS BOOL AS $f$
96 SELECT permission.set_cached_perm( $1, $2, $3, $4, '10 minutes'::INTERVAL);
99 CREATE OR REPLACE FUNCTION permission.check_cached_perm( iusr INT, tperm TEXT, iorg INT ) RETURNS BOOL AS $f$
104 IF permission.mc_init() THEN
105 -- RAISE NOTICE 'Getting perm from cache';
106 EXECUTE $$SELECT memcache_get('oils_permcache_$$ || iusr || tperm || iorg || $$') AS x;$$ INTO cached_value;
107 bool_value := cached_value.x = 't';
111 EXCEPTION WHEN OTHERS THEN
114 $f$ LANGUAGE PLPGSQL STABLE;
116 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $f$
118 WHEN permission.check_cached_perm( $1, $2, $3 ) IS NOT NULL THEN permission.check_cached_perm( $1, $2, $3 )
119 WHEN permission.set_cached_perm($1, $2, $3, permission.usr_has_home_perm( $1, $2, $3 )) THEN TRUE
120 WHEN permission.set_cached_perm($1, $2, $3, permission.usr_has_work_perm( $1, $2, $3 )) THEN TRUE