PostgreSQL修改用户密码的多种方式

-- 修改当前用户密码
alter user current_user with password 'new_password';

-- 修改指定用户密码（需要权限）
alter user username with password 'new_password';

psql -u username -d database
\password username
# 会提示输入新密码两次

-- 以postgres用户登录后
sudo -u postgres psql
alter user dbuser with password 'new_secure_pass123';

psql -h 192.xxx.xxx.99 -u postgres -d postgres
# 输入当前密码后执行：
alter user app_user with password 'new_pass!456';

-- 设置密码有效期
alter user username valid until '2025-12-31';

-- 使密码永不过期
alter user username valid until 'infinity';

create extension pgcrypto;
alter user username with encrypted password 'complex@pass123';

local   all   postgres   trust

-- 生成批量修改语句
select 'alter user '||usename||' with password '''||
       substr(md5(random()::text), 0, 10)||''';'
from pg_user
where usename not like 'pg_%';

-- 检查密码最后修改时间（需pg_shadow权限）
select usename, passwd from pg_shadow;

password_encryption = scram-sha-256

error: must be superuser to alter superusers

-- 密码包含单引号时
alter user test with password $$don't_use_this$$;