Great question! PostgreSQL doesn't natively track role-specific metadata like creation time, password modification timestamps, or last login time. Your current function is designed for table-level row tracking, which doesn't apply to database roles. Here are practical, database-level solutions to capture this information:

1. Create a Dedicated Metadata Table #

First, we'll need a table to store the role metadata. This acts as our custom system table:

CREATE TABLE public.user_metadata (
    username text PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_password_change timestamptz,
    last_login timestamptz
);

2. Capture Role Creation with Event Triggers #

PostgreSQL's event triggers let us intercept DDL commands like CREATE ROLE. We'll use this to auto-populate our metadata table when a new role is created:

-- Function to handle role creation
CREATE OR REPLACE FUNCTION public.capture_role_creation()
RETURNS event_trigger AS $$
BEGIN
    INSERT INTO public.user_metadata (username)
    SELECT objid::regrole::text
    FROM pg_event_trigger_ddl_commands()
    WHERE command_tag = 'CREATE ROLE';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Event trigger to fire on CREATE ROLE
CREATE EVENT TRIGGER track_role_creation
ON ddl_command_end
WHEN TAG IN ('CREATE ROLE')
EXECUTE FUNCTION public.capture_role_creation();

3. Track Password Changes #

Password modifications happen via ALTER ROLE ... PASSWORD. We'll use another event trigger to detect these updates and refresh our metadata:

-- Function to detect password changes
CREATE OR REPLACE FUNCTION public.capture_password_update()
RETURNS event_trigger AS $$
DECLARE
    cmd_record record;
BEGIN
    FOR cmd_record IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'ALTER ROLE' LOOP
        -- Verify the ALTER command is modifying a password
        IF current_query() LIKE '%ALTER ROLE%PASSWORD%' THEN
            UPDATE public.user_metadata
            SET last_password_change = CURRENT_TIMESTAMP
            WHERE username = cmd_record.objid::regrole::text;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Event trigger for ALTER ROLE commands
CREATE EVENT TRIGGER track_password_changes
ON ddl_command_end
WHEN TAG IN ('ALTER ROLE')
EXECUTE FUNCTION public.capture_password_update();

4. Log Last Login Time #

To track when a user logs in, use a database-level ON LOGON trigger (requires PostgreSQL 10+ and superuser privileges):

-- Function to update login timestamp
CREATE OR REPLACE FUNCTION public.capture_user_login()
RETURNS trigger AS $$
BEGIN
    -- Upsert in case the role exists but wasn't added to our metadata table
    INSERT INTO public.user_metadata (username, last_login)
    VALUES (CURRENT_USER, CURRENT_TIMESTAMP)
    ON CONFLICT (username) DO UPDATE
    SET last_login = CURRENT_TIMESTAMP;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Database-level login trigger
CREATE TRIGGER track_user_login
ON LOGON
EXECUTE FUNCTION public.capture_user_login();

Key Notes #

• Security: The SECURITY DEFINER attribute ensures the functions run with the privileges of their creator (typically a superuser), which is necessary to intercept DDL and login events. Be cautious with this to avoid privilege escalation risks.
• Edge Cases: The login trigger uses an upsert to handle roles that were created before the metadata table existed.
• Version Compatibility: ON LOGON triggers are available in PostgreSQL 10 and newer. For older versions, you'd need to parse PostgreSQL's log files (enable log_connections in postgresql.conf) to extract login times.

Your original function works great for table row timestamps, but since you're targeting database roles, these event and login triggers are the right approach to capture the metadata you need.

内容的提问来源于stack exchange，提问作者Sadamh Hussain