begriffs open source - ai-pg/blob - full-docs/txt/event-trigger-database-login-example.txt

   1
   2 38.5. A Database Login Event Trigger Example #
   3
   4    The event trigger on the login event can be useful for logging user
   5    logins, for verifying the connection and assigning roles according to
   6    current circumstances, or for session data initialization. It is very
   7    important that any event trigger using the login event checks whether
   8    or not the database is in recovery before performing any writes.
   9    Writing to a standby server will make it inaccessible.
  10
  11    The following example demonstrates these options.
  12 -- create test tables and roles
  13 CREATE TABLE user_login_log (
  14   "user" text,
  15   "session_start" timestamp with time zone
  16 );
  17 CREATE ROLE day_worker;
  18 CREATE ROLE night_worker;
  19
  20 -- the example trigger function
  21 CREATE OR REPLACE FUNCTION init_session()
  22   RETURNS event_trigger SECURITY DEFINER
  23   LANGUAGE plpgsql AS
  24 $$
  25 DECLARE
  26   hour integer = EXTRACT('hour' FROM current_time at time zone 'utc');
  27   rec boolean;
  28 BEGIN
  29 -- 1. Forbid logging in between 2AM and 4AM.
  30 IF hour BETWEEN 2 AND 4 THEN
  31   RAISE EXCEPTION 'Login forbidden';
  32 END IF;
  33
  34 -- The checks below cannot be performed on standby servers so
  35 -- ensure the database is not in recovery before we perform any
  36 -- operations.
  37 SELECT pg_is_in_recovery() INTO rec;
  38 IF rec THEN
  39   RETURN;
  40 END IF;
  41
  42 -- 2. Assign some roles. At daytime, grant the day_worker role, else the
  43 -- night_worker role.
  44 IF hour BETWEEN 8 AND 20 THEN
  45   EXECUTE 'REVOKE night_worker FROM ' || quote_ident(session_user);
  46   EXECUTE 'GRANT day_worker TO ' || quote_ident(session_user);
  47 ELSE
  48   EXECUTE 'REVOKE day_worker FROM ' || quote_ident(session_user);
  49   EXECUTE 'GRANT night_worker TO ' || quote_ident(session_user);
  50 END IF;
  51
  52 -- 3. Initialize user session data
  53 CREATE TEMP TABLE session_storage (x float, y integer);
  54 ALTER TABLE session_storage OWNER TO session_user;
  55
  56 -- 4. Log the connection time
  57 INSERT INTO public.user_login_log VALUES (session_user, current_timestamp);
  58
  59 END;
  60 $$;
  61
  62 -- trigger definition
  63 CREATE EVENT TRIGGER init_session
  64   ON login
  65   EXECUTE FUNCTION init_session();
  66 ALTER EVENT TRIGGER init_session ENABLE ALWAYS;