如何为Aurora PostgreSQL设置默认密码过期策略？

完全可行，PostgreSQL虽然没有全局的default_password_lifetime配置项，但可以通过事件触发器实现强制密码过期策略，确保用户修改密码后自动设置1个月的有效期，同时禁止用户自行控制VALID UNTIL参数。

实现步骤： #

1. 创建触发器函数

创建一个PL/pgSQL函数，用于捕获用户修改密码的操作，并自动设置密码有效期为当前时间+1个月：

CREATE OR REPLACE FUNCTION enforce_password_expiry()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
    user_name text;
BEGIN
    -- 遍历当前执行的DDL语句
    FOR rec IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        -- 仅处理ALTER USER操作
        IF rec.command_tag = 'ALTER ROLE' THEN
            -- 提取用户名
            user_name := split_part(rec.object_identity, '.', 2);
            IF user_name IS NULL THEN
                user_name := rec.object_identity;
            END IF;
            
            -- 强制设置密码有效期为当前时间+1个月，覆盖用户可能自行设置的VALID UNTIL
            EXECUTE format('ALTER USER %I VALID UNTIL %L', user_name, NOW() + interval '1 month');
        END IF;
    END LOOP;
END;
$$;

2. 创建事件触发器

绑定上述函数到ddl_command_end事件，确保每次执行ALTER USER操作后触发：

CREATE EVENT TRIGGER trigger_enforce_password_expiry
ON ddl_command_end
WHEN TAG IN ('ALTER ROLE')
EXECUTE FUNCTION enforce_password_expiry();

3. 限制用户权限（可选但推荐）

为了彻底禁止普通用户自行设置VALID UNTIL，可以撤销他们的ALTER USER权限（仅保留修改自身密码的权限）：

-- 撤销普通用户的ALTER USER权限
REVOKE ALL PRIVILEGES ON ALL ROLES IN SCHEMA public FROM PUBLIC;
-- 允许用户修改自身密码
GRANT USAGE ON ROLE myuser TO myuser;
ALTER ROLE myuser WITH LOGIN;

验证效果 #

1. 普通用户修改自身密码：

ALTER USER myuser PASSWORD 'new_secure_password';

2. 查询密码有效期，确认已自动设置为1个月后：

SELECT usename, valuntil FROM pg_user WHERE usename = 'myuser';

初始用户创建 #

管理员创建用户时，也可以直接设置初始有效期：

CREATE USER myuser WITH PASSWORD 'initial_password' VALID UNTIL NOW() + interval '1 month';

内容的提问来源于stack exchange，提问作者jeongmin.cha