PostgreSQL培训：V10用户密码认证及加密算法scram-sha-256
时间：2020-06-16 来源：
官方手册关于用户密码的表述如下：
PostgreSQL数据库口令独立于操作系统用户口令。每个数据库用户的口令被存储在pg_authid系统目录中。
口令可以用 SQL 命令CREATE USER和ALTER ROLE管理，例如CREATE USER foo WITH PASSWORD 'secret'， 或者psql命令\password。如果没有为一个用户设置口令，那么存储的口令为空并且对该用户的口令认证总会失败。
不同的基于密码的身份验证方法的可用性取决于用户的密码在服务器上是如何加密的 (或更准确地说是哈希)。
这是在设置密码时由配置参数 password_encryption(默认值)控制的。
如果使用 scram-sha-256设置对密码进行了加密， 那么它可以用于身份验证方法scram-sha-256和password (但在后一种情况下密码传输将以纯文本形式)。如上所述， 认证方法规范md5会自动切换到使用scram-sha-256方法， 所以它也可以工作。
如果密码是使用md5设置加密的， 那么它只能用于md5和password认证方法规范 (同样，在后一种情况下用明文传输密码)。
(以前的PostgreSQL版本支持以纯文本格式在服务器上存储密码，这已不再可行。) 要查看当前存储的密码哈希值，请查看系统目录pg_authid。
在确保所有正在使用的客户端库足够新以支持SCRAM后， 要将现有安装从md5升级到scram-sha-256， 在postgresql.conf中设置 password_encryption = 'scram-sha-256'， 让所有用户设置新密码，并将pg_hba.conf 中的认证方法声明更改为scram-sha-256。
以下进行试验验证
在PG V10.3环境进行验证如下内容
1. 按默认参数配置新创建用户，将会以md5加密的形式存放到pg_user表中。
默认参数配置 password_encryption=md5
[postgres@db1 data]$ createuser u6 -W
Password: #输入密码
postgres=# create user u7 password 'Password@app';
CREATE ROLE
postgres=# select * from pg_authid where rolname='u7';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls |
rolconnlimit | rolpassword | rolvaliduntil
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-
-------------+-------------------------------------+---------------
u7 | f | t | f | f | t | f | f |
-1 | md52ff22216c0d97fc881437cea333277c9 |
#会话级别修改用户角色密码加密算法
postgres=# set password_encryption to "scram-sha-256";
SET
postgres=# show password_encryption ;
password_encryption
---------------------
scram-sha-256
(1 row)
postgres=# create user u8 password 'Password@app';
CREATE ROLE
postgres=# select * from pg_authid where rolname='u8';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls |
rolconnlimit | rolpassword
| rolvaliduntil
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-
-------------+-------------------------------------------------------------------------------------------------
--------------------------------------+---------------
u8 | f | t | f | f | t | f | f |
-1 | SCRAM-SHA-256$4096:TZbsCC/eQxS2Iso+g/lTcg==$OLz+2ysTOp6yTxJ3GWciRyu5ktbRNP3gOM4tiHFFEaM=:7z9pTYY
DQsMh5LTJtPCIZm7bPhuWejVjDECe/LRdVzA= |
#修改pg_hba.conf 本地连接的method方法为md5
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
#执行以下指令使修改生效
pg_ctl reload
分别用u7 u8用户连接数据库测试，结果是可以正常连接！
#修改pg_hba.conf 本地连接的method方法为scram-sha-256
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all scram-sha-256
#执行以下指令使修改生效
pg_ctl reload
分别用u7、 u8用户连接数据库测试，测试结果为u7不能连接;u8用户连接正常。
[postgres@db1 data]$ psql -U u7
Password for user u7:
psql: FATAL: password authentication failed for user "u7"
[postgres@db1 data]$ psql -U u8
Password for user u8:
psql (10.3)
Type "help" for help.
postgres=>
证明了用户密码用md5加密存储，如果用连接认证使用scram-sha-256，将会连接受限。
解决方法是重新修改密码用md5加密的用户的密码，使用新的认证加密算法scram-sha-256。
#修改pg_hba.conf 本地连接的method方法为scram-sha-256
# TYPE DATABASE USER ADDRESS trust
#以postgres用户执行以下指令使修改生效
pg_ctl reload
postgres=# show password_encryption
postgres-# ;
password_encryption
---------------------
scram-sha-256
(1 row)
postgres=# alter user u7 password 'Password@app';
ALTER ROLE
postgres=# select * from pg_authid where rolname ='u7';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls |
rolconnlimit | rolpassword
| rolvaliduntil
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-
-------------+-------------------------------------------------------------------------------------------------
--------------------------------------+---------------
u7 | f | t | f | f | t | f | f |
-1 | SCRAM-SHA-256$4096:ulZwjxU4EwJ/hE2ymuoXXA==$4lEaJUNg4e5WabH+jRu0LBo97UFjCjWtuu0IKpoJyjg=:SK9jv1Z
+1QCgl2EAWtL0LN2xAYpRyWBAYYvU5wA+Agw= |
(1 row)
postgres=#
再次通过修改pg_hba.conf的认证方法为SCRAM-SHA-256后，u7用户连接数据库正常。
#修改pg_hba.conf 本地连接的method方法为scram-sha-256
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all scram-sha-256
#执行以下指令使修改生效
[postgres@db1 data]$ pg_ctl reload
server signaled
[postgres@db1 data]$
[postgres@db1 data]$ psql -U u7
Password for user u7: #手动输入密码Password@app
psql (10.3)
Type "help" for help.
postgres=>
小结:如果数据库版本升级到PG V10，处于安全考虑认证及加密方式修改为scram-sha-256后，需要考虑两个地方：
1)原来用户的密码重新按先的加密方法进行修改;
2)保证客户端支持新的认证方法(本节没有验证)。
