md5升级scram-sha-256认证

环境

系统平台：银河麒麟（飞腾）U系 V4,银河麒麟（龙芯）R系 V4
版本：5.6.5

文档用途

md5认证方式升级为scram-sha-256

详细信息

1.确认数据库版本

#要求内核pg10及以上版本

[highgo@node1 HighGo5.6.5]$ psql -U highgo

psql (5.6.5)

 

PSQL: Release 5.6.5

Connected to:

HighGo Database V5.6 Enterprise Edition Release 5.6.5 - 64-bit Production

 

Type "help" for help.

 

highgo=#  SELECT version();

                                                 version                                                 

----------------------------------------------------------------------------------------------------------

 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (NeoKylin 4.8.5-16), 64-bit

(1 row)

2.查看pg_hba.conf当前认证方式

#TYPE DATABASE USER ADDRESS METHOD

host all all 127.0.0.1/32 md5

3.修改password_encryption参数

highgo=# SELECT name,setting,source,enumvals FROM pg_settings WHERE name = 'password_encryption';

        name         | setting | source  |      enumvals      

---------------------+---------+---------+---------------------

 password_encryption | md5     | default | {md5,scram-sha-256}

(1 row)

 

highgo=# ALTER SYSTEM SET password_encryption TO 'scram-sha-256';

ALTER SYSTEM

highgo=#  SELECT pg_reload_conf();

 pg_reload_conf

----------------

 t

(1 row)

 

highgo=# SELECT name,setting,source,enumvals FROM pg_settings WHERE name = 'password_encryption';

        name         |    setting    |       source       |      enumvals      

---------------------+---------------+--------------------+---------------------

 password_encryption | scram-sha-256 | configuration file | {md5,scram-sha-256}

(1 row)

4.查询需要升级密码的用户

highgo=# SELECT rolname FROM pg_authid WHERE rolcanlogin AND rolpassword !~ '^SCRAM-SHA-256\$';

 rolname

---------

 highgo

(1 row)

5.密码升级

highgo=# \password highgo

Enter new password:

Enter it again:

highgo=#  SELECT rolname FROM pg_authid WHERE rolcanlogin AND rolpassword !~ '^SCRAM-SHA-256\$';

 rolname

---------

(0 rows)           #显示为0表示升级成功

highgo=# SELECT rolname,rolpassword FROM pg_authid WHERE rolcanlogin;

 rolname |                                                              rolpassword                                                             

---------+---------------------------------------------------------------------------------------------------------------------------------------

 highgo  | SCRAM-SHA-256$4096:QiTGFGjtJ75khc8i8vCtAg==$KhSnNCRzTNg3StKNa5UIubJs5qO8VuHDZR7YfD33R5U=:bHoho6Z1kQrCVXMp0iSpuz6d5/VYkeqBvFaOx0kHAHA=

(1 row)

6.重新配置pg_hga.conf文件

#TYPE DATABASE USER ADDRESS METHOD

host all all 127.0.0.1/32 scram-sha-256