Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL 10.0 preview 安全增强 - SASL认证方法 之 scram-sha-256 安全认证机制

6 minute read

背景

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简介如下

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=818fd4a67d610991757b610755e3065fb99d80a5

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容易引起误解，社区决定修改名字为scram-sha-256

Rename "scram" to "scram-sha-256" in pg_hba.conf and password_encryption.

author	Heikki Linnakangas <heikki.linnakangas@iki.fi>	
Tue, 18 Apr 2017 19:50:50 +0800 (14:50 +0300)
committer	Heikki Linnakangas <heikki.linnakangas@iki.fi>	
Tue, 18 Apr 2017 19:50:50 +0800 (14:50 +0300)
commit	c727f120ff50f624a1ee3abe700d995c18314a0b
tree	a3fb2b94b43e51f386d31dca2b056d004b787ae3	tree | snapshot
parent	123aaffb5b881f3dadaac676877a90b50233a847	commit | diff
Rename "scram" to "scram-sha-256" in pg_hba.conf and password_encryption.

Per discussion, plain "scram" is confusing because we actually implement
SCRAM-SHA-256 rather than the original SCRAM that uses SHA-1 as the hash
algorithm. If we add support for SCRAM-SHA-512 or some other mechanism in
the SCRAM family in the future, that would become even more confusing.

Most of the internal files and functions still use just "scram" as a
shorthand for SCRMA-SHA-256, but I did change PASSWORD_TYPE_SCRAM to
PASSWORD_TYPE_SCRAM_SHA_256, as that could potentially be used by 3rd
party extensions that hook into the password-check hook.

Michael Paquier did this in an earlier version of the SCRAM patch set
already, but I didn't include that in the version that was committed.

Discussion: https://www.postgresql.org/message-id/fde71ff1-5858-90c8-99a9-1c2427e7bafb@iki.fi

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实现的代码如下

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/libpq/auth-scram.c;h=cc4e84403f9abd6baf64754d05ec4ea8b2e8430b;hb=818fd4a67d610991757b610755e3065fb99d80a5

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"

这个patch的讨论，详见邮件组，本文末尾URL。

PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。

小结

1. PostgreSQL模块化的认证方法，给PostgreSQL的安全加固提供了很好的便利。

2. SCRAM相比MD5，可以避免因为数据库存储的加密秘钥都是，客户端可以篡改认证协议连接数据库的危险。

3. scram认证方法和md5认证方法是不兼容的，二者选一，旧的客户端不支持scram认证。

4. PostgreSQL 10.0 通过扩展认证协议，引入了一个全新的通用SASL认证方法，目前基于SASL，已加入SCRAM-SHA-256算法的支持，未来可以支持更多的算法。

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...