标签
PostgreSQL , 认证方法 , SCRAM-SHA-256 , scram , scram-sha-256-plus , SASL , Simple Authentication and Security Layer
背景
PostgreSQL的很多设计非常的工业化，比如开放了许多扩展接口（类型、操作符、索引、扫描、采样、数据库编程语言等）。
另外还有一个，认证也是模块化的，比如你不喜欢md5的认证方法，可以随意更换认证模块，提高安全性。
20.3.1. Trust Authentication
20.3.2. Password Authentication
20.3.3. GSSAPI Authentication
20.3.4. SSPI Authentication
20.3.5. Ident Authentication
20.3.6. Peer Authentication
20.3.7. LDAP Authentication
20.3.8. RADIUS Authentication
20.3.9. Certificate Authentication
20.3.10. PAM Authentication
20.3.11. BSD Authentication
PostgreSQL 10.0 通过扩展认证协议，引入了一个全新的通用SASL认证方法，基于SASL，已加入SCRAM-SHA-256算法的支持。
那么接下来我们就看看10.0新增的SCRAM-SHA-256 base on SASL认证吧。
PostgreSQL scram机制认证patch介绍
PostgreSQL SCRAM机制认证patch，基于RFC文档 5802 、 7677。
类似于PG原有的GSS和SSPI认证，由数据库端首先告诉客户端使用哪个SASL认证机制，然后在认证过程中SASL消息通过AuthenticationSASLcontinue、PasswordMessage两个过程进行交换。
虽然目前只支持SCRAM-SHA-256算法，但是基于SASL认证方法，未来可以支持更多的更强的算法。
patch简介如下
Support SCRAM-SHA-256 authentication (RFC 5802 and 7677).
This introduces a new generic SASL authentication method, similar to the
GSS and SSPI methods. The server first tells the client which SASL
authentication mechanism to use, and then the mechanism-specific SASL
messages are exchanged in AuthenticationSASLcontinue and PasswordMessage
messages. Only SCRAM-SHA-256 is supported at the moment, but this allows
adding more SASL mechanisms in the future, without changing the overall
protocol.
Support for channel binding, aka SCRAM-SHA-256-PLUS is left for later.
The SASLPrep algorithm, for pre-processing the password, is not yet
implemented. That could cause trouble, if you use a password with
non-ASCII characters, and a client library that does implement SASLprep.
That will hopefully be added later.
Authorization identities, as specified in the SCRAM-SHA-256 specification,
are ignored. SET SESSION AUTHORIZATION provides more or less the same
functionality, anyway.
If a user doesn't exist, perform a "mock" authentication, by constructing
an authentic-looking challenge on the fly. The challenge is derived from
a new system-wide random value, "mock authentication nonce", which is
created at initdb, and stored in the control file. We go through these
motions, in order to not give away the information on whether the user
exists, to unauthenticated users.
Bumps PG_CONTROL_VERSION, because of the new field in control file.
Patch by Michael Paquier and Heikki Linnakangas, reviewed at different
stages by Robert Haas, Stephen Frost, David Steele, Aleksander Alekseev,
and many others.
Discussion: https://www.postgresql.org/message-id/CAB7nPqRbR3GmFYdedCAhzukfKrgBLTLtMvENOmPrVWREsZkF8g%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/CAB7nPqSMXU35g%3DW9X74HVeQp0uvgJxvYOuA4A-A3M%2B0wfEBv-w%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/55192AFE.6080106@iki.fi
pg_hba.conf 配置
如果你要使用scram认证，配置也非常简单，在pg_hba.conf条目中的method字段，填入scram即可。表示这条规则适用于scram机制认证。
https://www.postgresql.org/docs/devel/static/auth-methods.html#auth-password
The password-based authentication methods are scram , md5 and password.
scram performs SCRAM-SHA-256 authentication, as described in RFC5802.
It is a challenge-response scheme, that prevents password sniffing on untrusted connections.
It is more secure than the md5 method, but might not be supported by older clients.
例子
host all all 127.0.0.1/32 scram
SCRAM认证机制介绍
1. md5认证方法的算法比较简单
客户端可以hack,直接递交 md5（服务端存储的秘钥+SALT），服务端收到后对比存储的 md5（md5(password)+salt）， 如果一致，就会认证通过。所以MD5存储的秘钥，也是非常重要的，不要泄露。
《PostgreSQL 对比 MySQL - MD5秘钥认证》
2. scram机制认证修掉了这个问题，既能保证客户端不被伪装，也能保证服务端不被伪装。
2.1 首先，客户端要将用户名发给服务端。
2.2 服务端收到客户端用户名后，从存储的密文中提取一些认证过程需要的salt, StoredKey, ServerKey, 以及循环次数（循环次数可能所有用户一致）发送给客户端。
2.3 客户端收到认证过程中必要的信息后，使用客户端掌握的用户密码，对信息加工如下
SaltedPassword := Hi(Normalize(password), salt, i)
ClientKey := HMAC(SaltedPassword, "Client Key")
StoredKey := H(ClientKey)
AuthMessage := client-first-message-bare + "," +
server-first-message + "," +
client-final-message-without-proof
ClientSignature := HMAC(StoredKey, AuthMessage)
ClientProof := ClientKey XOR ClientSignature
ServerKey := HMAC(SaltedPassword, "Server Key")
ServerSignature := HMAC(ServerKey, AuthMessage)
服务端与客户端的交互过程会用到以上信息。
服务端操作如下：
2.4 首先使用ClientSignature与ClientProof进行异或，得到ClientKey。
2.5 然后，服务端需要使用哈希函数处理ClientKey，得到的结果与服务端存储的StoredKey进行比较。如果ClientKey正确，说明客户端认证通过。
客户端如果要防止服务端被伪装，也可以使用类似方法，客户端需要计算出ServerSignature，同时与服务端认证过程中返回的服务端根据存储的信息+客户端交换过程中提交的信息计算的（ServerSignature）进行比较，如果匹配则服务端没有被伪装。
安全在哪里？
服务端存储了多次加密后的秘钥，加密方法不可逆转。仅仅泄露这个多次加密后的秘钥，无法攻破数据库。
基于SASL认证方法，可以打造服务端与客户端同时防伪装的认证。
scram机制如下
https://tools.ietf.org/html/rfc5802
To begin with, the SCRAM client is in possession of a username and
password (*) (or a ClientKey/ServerKey, or SaltedPassword). It sends
the username to the server, which retrieves the corresponding
authentication information, i.e., a salt, StoredKey, ServerKey, and
the iteration count i. (Note that a server implementation may choose
to use the same iteration count for all accounts.) The server sends
the salt and the iteration count to the client, which then computes
the following values and sends a ClientProof to the server:
SaltedPassword := Hi(Normalize(password), salt, i)
ClientKey := HMAC(SaltedPassword, "Client Key")
StoredKey := H(ClientKey)
AuthMessage := client-first-message-bare + "," +
server-first-message + "," +
client-final-message-without-proof
ClientSignature := HMAC(StoredKey, AuthMessage)
ClientProof := ClientKey XOR ClientSignature
ServerKey := HMAC(SaltedPassword, "Server Key")
ServerSignature := HMAC(ServerKey, AuthMessage)
The server authenticates the client by computing the ClientSignature,
exclusive-ORing that with the ClientProof to recover the ClientKey
and verifying the correctness of the ClientKey by applying the hash
function and comparing the result to the StoredKey. If the ClientKey
is correct, this proves that the client has access to the user's
password.
Similarly, the client authenticates the server by computing the
ServerSignature and comparing it to the value sent by the server. If
the two are equal, it proves that the server had access to the user's
ServerKey.
The AuthMessage is computed by concatenating messages from the
authentication exchange. The format of these messages is defined in
Section 7.
scram秘钥的分段解释
https://www.postgresql.org/docs/devel/static/catalog-pg-authid.html
pg_authid
| Name | Type | Description |
|---|---|---|
| rolpassword | text | Password (possibly encrypted); null if none. The format depends on the form of encryption used. |
scram的内容分为5段，如下
If the password is encrypted with SCRAM-SHA-256, it consists of 5 fields separated by colons.
The first field is the constant scram-sha-256, to identify the password as a SCRAM-SHA-256 verifier.
The second field is a salt, Base64-encoded,
and the third field is the number of iterations used to generate the password.
The fourth field and fifth field are the stored key and server key, respectively, in hexadecimal format.
PostgreSQL scram实现的代码如下
PostgreSQL SCRAM 认证的消息格式
https://www.postgresql.org/docs/devel/static/protocol-flow.html
AuthenticationSASL
The frontend must now initiate a SASL negotiation, using the SASL mechanism specified in the message.
The frontend will send a PasswordMessage with the first part of the SASL data stream in response to this.
If further messages are needed, the server will respond with AuthenticationSASLContinue.
AuthenticationSASLContinue
This message contains the response data from the previous step of SASL negotiation
(AuthenticationSASL, or a previous AuthenticationSASLContinue).
If the SASL data in this message indicates more data is needed to complete the authentication, the frontend must send that data as another PasswordMessage.
If SASL authentication is completed by this message, the server will next send AuthenticationOk to indicate successful authentication or ErrorResponse to indicate failure.
报文如下
https://www.postgresql.org/docs/devel/static/protocol-message-formats.html
AuthenticationSASL (B)
Byte1('R')
Identifies the message as an authentication request.
Int32
Length of message contents in bytes, including self.
Int32(10)
Specifies that SASL authentication is started.
String
Name of a SASL authentication mechanism.
AuthenticationSASLContinue (B)
Byte1('R')
Identifies the message as an authentication request.
Int32
Length of message contents in bytes, including self.
Int32(11)
Specifies that this message contains SASL-mechanism specific data.
Byten
SASL data, specific to the SASL mechanism being used.
postgresql.conf 配置
默认依旧是md5封装
#password_encryption = md5 # md5, scram or plain
测试
wget https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2
安装略
修改配置
vi postgresql.conf
port=1922
unix_socket_directories = '.'
log_destination = 'csvlog'
#password_encryption = md5 # md5, scram or plain
修改认证方式为scram
vi pg_hba.conf
host all all 127.0.0.1/32 scram
创建md5秘钥存储用户
psql -h $PGDATA -p 1922 -U postgres postgres
postgres=# create role digoal01 encrypted password 'digoal' login;
CREATE ROLE
创建scram秘钥存储用户
postgres=# set password_encryption =scram;
SET
postgres=# create role digoal02 encrypted password 'digoal' login;
CREATE ROLE
查看md5和scram存储的区别
postgres=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
-------------------+-----------------------------------------------------------------------------------------------
digoal01 | md59f88b70376618eb719e58f630eee13ad
digoal02 | scram-sha-256:wEroBV0GNfOIZw==:4096:6149c711825bfc6b0c0e61f3cee1341b8eeab770f784fe670af133a70d6a7cdf:1ca9859b95eb138c0606bfb59414e70eec83a3d05ef3c9bfa7e76353e9032e52
scram认证测试
-> psql -h 127.0.0.1 -p 1922 -U digoal01 postgres
Password for user digoal01:
psql: error received from server in SASL exchange: invalid-proof
-> psql -h 127.0.0.1 -p 1922 -U digoal02 postgres
Password for user digoal02:
psql (10devel)
Type "help" for help.
postgres=>
恢复为md5认证
vi pg_hba.conf
host all all 127.0.0.1/32 md5
pg_ctl reload
-> psql -h 127.0.0.1 -p 1922 -U digoal01 postgres
Password for user digoal01:
psql (10devel)
Type "help" for help.
postgres=> \q
-> psql -h 127.0.0.1 -p 1922 -U digoal02 postgres
Password for user digoal02:
psql: FATAL: password authentication failed for user "digoal02"
小结
1. PostgreSQL模块化的认证方法，给PostgreSQL的安全加固提供了很好的便利。
2. SCRAM相比MD5，可以避免因为数据库存储的加密秘钥都是，客户端可以篡改认证协议连接数据库的危险。
3. scram认证方法和md5认证方法是不兼容的，二者选一，旧的客户端不支持scram认证。
4. PostgreSQL 10.0 通过扩展认证协议，引入了一个全新的通用SASL认证方法，目前基于SASL，已加入SCRAM-SHA-256算法的支持，未来可以支持更多的算法。
