postgresql lock user without activity

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

Solution

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.