PostgreSQL普通用户修改密码提示permission denied错误的原因是什么

非超级用户执行带VALID UNTIL参数的ALTER ROLE语句时报permission denied错误，是因为PostgreSQL默认仅允许普通角色修改自身的密码属性，VALID UNTIL（角色有效期）属于受限属性，需要超级用户或持有CREATEROLE权限的角色才可修改，普通角色无该操作权限。

以下为适配每3个月更新密码安全要求的可落地方案：

方案1：普通用户自助改密码，有效期由管理员统一管控 #

• 普通用户修改密码时去掉VALID UNTIL参数，执行以下语句即可正常执行：

ALTER ROLE [你的用户名] WITH ENCRYPTED PASSWORD '[新密码]';

• 管理员侧用超级用户身份定期执行批量任务，统一将所有普通用户的密码有效期设置为当前时间加90天，适配3个月更新的安全要求。

方案2：封装安全存储过程支持用户自助设置有效期 #

如果需要让用户改密码时自动更新有效期，可由超级用户创建一个SECURITY DEFINER属性的专属存储过程，权限仅开放给普通用户修改自身信息，避免权限泄露：

1. 超级用户执行以下语句创建存储过程：

CREATE OR REPLACE FUNCTION change_own_password(new_pwd text)
RETURNS void AS $$
BEGIN
  -- 仅允许修改当前登录用户的密码和有效期
  EXECUTE format('ALTER ROLE %I WITH ENCRYPTED PASSWORD %L VALID UNTIL %L',
    current_user,
    new_pwd,
    (NOW() + INTERVAL '3 months')::date
  );
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
-- 限制函数所有者为超级用户，设置安全搜索路径避免注入
SET search_path = pg_catalog, pg_temp;

2. 给所有普通用户授予该函数的执行权限：

GRANT EXECUTE ON FUNCTION change_own_password(text) TO PUBLIC;

3. 普通用户后续修改密码只需执行以下语句即可，自动设置3个月有效期：

SELECT change_own_password('你的新密码');

注意事项 #

• 不要给普通用户直接授予CREATEROLE权限，该权限可修改其他用户的属性，存在严重安全风险
• 使用SECURITY DEFINER函数时必须固定search_path，避免SQL注入风险

内容的提问来源于stack exchange，提问作者M. Andersen