1.查看当前认证方式及所有用户
postgres=# show password_encryption;
password_encryption
---------------------
md5
(1 row)
postgres=# select rolname,rolpassword from pg_authid where rolcanlogin;
rolname | rolpassword
------------+-------------------------------------
postgres | md53175bce1d3201d16594cebf9d7eb3f9d
(1 rows)
2.修改password_encryption参数
vi /pgdb/pgdata/postgresql.conf
password_encryption = scram-sha-256 # md5 or scram-sha-256
3.重新加载数据库配置文件
pg_ctl -D /pgdb/pgdata
4.检查是否加载成功
postgres=# show password_encryption;
password_encryption
---------------------
scram-sha-256
(1 row)
5.再次设置所有密码
postgres=# \password postgres
Enter new password:
Enter it again:
6.配置用户身份映射配置文件
vi /pgdb/pgdata/pg_ident.conf
postgres postgres postgres
7.修改控制客户端认证的配置文件
vi /pgdb/pgdata/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all postgres ident map=postgres
local all all scram-sha-256
# IPv4 local connections:
host all all 127.0.0.1/32 ident map=postgres
# IPv6 local connections:
host all all ::1/128 ident map=postgres
host all all 0.0.0.0/32 scram-sha-256
8.重新加载数据库配置文件
pg_ctl -D /pgdb/pgdata
9.测试下是否生效
psql
psql -U postgres
10.其他
第二步修改password_encryption参数也可以通过语句修改
postgres=# alter system set password_encryption to 'scram-sha-256';
ALTER SYSTEM
这样修改的是postgresql.auto.conf，优先级高于postgresql.conf。
###重新加载配置文件也可以用
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
## 同pg_ctl reload
