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
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.