PG口令加密算法從MD5升級到SCRAM-SHA-256

1、前提條件

確保PG實例版本是10及以上版本。

postgres=# select version();version ---------------------------------------------------------------------------------------------------------PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (1 row)

2、配置pg_hba.conf文件

[pg12@wcbpgcm1 data]$ more pg_hba.conf |grep 127 host all all 127.0.0.1/32 md5 host replication all 127.0.0.1/32 trust

3、修改password_encryption參數

# 查詢當前參數值 postgres=# 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) # 修改參數 postgres=# ALTER SYSTEM SET password_encryption TO 'scram-sha-256'; ALTER SYSTEM # 重新加載配置 postgres=# select pg_reload_conf();pg_reload_conf ----------------t (1 row)2022-04-12 19:32:43.951 CST [20238] LOG: received SIGHUP, reloading configuration files # 修改后參數值 postgres=# 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、查詢需要升級密碼的用戶

postgres=# select rolname from pg_authid where rolcanlogin and rolpassword !~ '^SCRAM-SHA-256\$';rolname ----------postgres (1 row)

5、密碼升級

postgres=# \password postgres Enter new password: Enter it again: postgres=# postgres=# select rolname from pg_authid where rolcanlogin and rolpassword !~ '^SCRAM-SHA-256\$'; rolname --------- (0 rows) postgres=# select rolname,rolpassword from pg_authid where rolcanlogin;rolname | rolpassword ----------+------------------------------------------------------------------------------------------------- --------------------------------------postgres | SCRAM-SHA-256$4096:pUuwMuiUuZbnrmuZ6g5cuA==$B0+wk4XoenikWYkh88/QAU2Cys2+1r09D8ftGVneAi0=:+468xof 21ECueRvS07BRFsQYJ3fzWtK/4Kcq2YBUBqk= (1 row)

6、重新配置pg_hba.conf認證

[pg12@wcbpgcm1 data]$ more pg_hba.conf |grep 127 host all all 127.0.0.1/32 scram-sha-256

7、升級注意事項

• 需要在升級認證方式的時候，先處理完未修改密碼的賬號：

select rolname from pg_authid where rolcanlogin and rolpassword !~ '^SCRAM-SHA-256\$';

• 由MD5升級到scram-sha-256時，需要重新修改已經存在的密碼，否則修改pg_hda.conf的認證方式后不能登錄

總結

以上是生活随笔為你收集整理的PG口令加密算法从MD5升级到SCRAM-SHA-256的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。