postgresql lock user without activity

Question

I have to lock users who don t work in the previous three months. I'm very newbie! I looked at the documentation and I do nt think I can do this by configuration. (Is it true?) So I have to develop a daily procedure ( with pgagent?) ,make a query and then lock users but I cant find a system table that has this information. Could you help me? Thanks a lot Gian

Answer 1

That is correct, the time of the last login is not recorded in the database, only in the log file (if logging is suitably configured).

I think you won't be able to implement this without the aid of the application that uses PostgreSQL. I can think of two possibilities:

• Create a table where your application records the last login time. Your periodic job can then use that table to decide if it should lock a user.

• Create a function like this:

  CREATE OR REPLACE FUNCTION expire_me() RETURNS void
     LANGUAGE plpgsql SECURITY DEFINER
     SET search_path = 'pg_catalog' AS
  $$BEGIN
     EXECUTE 'ALTER ROLE ' || session_user || ' VALID UNTIL ''' ||
             (current_timestamp + INTERVAL '3 months') || '''';
  END;$$;
  

  The application then calls the function immediately after a user logs in. If the next login is more than three months later, the user will be locked.

Unfortunately there is no such thing as a “login trigger” is PostgreSQL, that would make this much simpler.