postgresql加密方式

postgresql加密方式

password_encryption是动态参数，修改配置参数不需要重启服务，可动态加载

方式1.md5

1.存储方式：md5(‘用户名+密码’)
2.在版本14之前，默认加密方式是md5

方式2.scram-sha-256

1.从10+（含10）版本开始支持SCRAM-SHA-256
2.低版本的驱动不支持SCRAM-SHA-256，如JDBC会出现The authentication type 10 is not supported错误信息
是否支持scram的驱动参考表:https://wiki.postgresql.org/wiki/List_of_drivers
3.14+（含14）版本后密码加密默认是scram-sha-256

查看加密方式

select name,setting from pg_settings where name ='password_encryption';
或者
show password_encryption;

sha256需要加载pgcrypto插件

##安装插件
create extension pgcrypto;

##查看插件信息
\dx
或者
select * from pg_available_extensions;

##遇到错误
ERROR: could not open extension control file "/usr/local/pgsql/share/extension/pgcrypto.control": No such file or directory

##解决办法
1）原始的安装包的contrib路径下找到对应的插件信息/data/postgresql-11.4/contrib/pgcrypto
2）cd /data/postgresql-11.4/contrib/pgcrypto
3）make USE_PGXS=1
4）make USE_PGXS=1 install

ps:直接使用make install还是有可能会出现以下错误信息：
Makefile:48: ../../src/Makefile.global: No such file or directory
Makefile:49: /contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target `/contrib/contrib-global.mk'.  Stop.

升级加密方式

##密码相关的表信息,可以看到具体的加密方式
select usename,passwd from pg_shadow;
select rolname,rolpassword from pg_authid;

##看到密码信息是******
select usename,passwd from pg_user;
select rolname,rolpassword from pg_roles;

升级步骤

1）开发确定客户端工具驱动支持scram-sha-256的加密方式，不支持则需要进行客户端升级
2）通过pg_shadow和pg_authid两张表进行查询，确认非scram加密用户

select usename,passwd from pg_shadow where passwd not like 'SCRAM-SHA-256%';

select rolname,rolpassword from pg_authid where rolpassword not like 'SCRAM-SHA-256%';

3）修改对应的加密配置参数

alter system set password_encryption='scram-sha-256';
##热加载
select pg_reload_conf();

4）重设非scram用户密码

\password username

5）查看用户密码是否全部修改成sha-256

select rolname,rolpassword from pg_authid where rolpassword not like 'SCRAM-SHA-256%';

6）检查用户登录是否正常

测试

1.查看用户密码信息
postgres=# select rolname,rolpassword from pg_authid where rolname not like ‘pg%’;

rolname	rolpassword
zhangsan	md5c1cb843f3929978af615fe7dfbf532cb
xiaohong	SCRAM-SHA-256$4096:ZNlDvGgYo9Q7+xLFgLL2nA==$lBfUIDCPefgk2BN7s9nHXk/sJ8kNw+vVtbS9MhAf2CI=:5A/a0qezFeqGtsxa1v7gNlasBeKHmWn8Hq4ZW46TZog=

2.修改pg_hba.conf配置文件的密码认证方式为scram-sha-256之后，原来的md5的加密用户则无法登陆
vi pg_hba.conf

#"local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
#IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             172.16.0.0/16              scram-sha-256

3.修改配置参数

postgres=# alter system set password_encryption='scram-sha-256';
ALTER SYSTEM

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show password_encryption;
 password_encryption
---------------------
 scram-sha-256
(1 row)

4.登陆测试，测试发现登陆认证失败
[postgres@k8s-node1 root]$ psql -U zhangsan -d postgres
Password for user zhangsan:
psql: FATAL: password authentication failed for user “zhangsan”

5.如果修改pg_hba.conf的方式为md5，则兼容md5和scram-sha-256两种加密密码的登录验证方式
vi pg_hba.conf

#"local" is for Unix domain socket connections only
local   all             all                                     md5
#IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/16              md5

aws关于scram-sha-256的文档说明

云测试

• 修改参数通过控制台参数组修改才有效
• 能在客户端修改的参数都是只改变session类参数，无法修改全局性参数，主要权限不够
• 测试版本11.4,发现在修改加密参数后，原方式和新方式的加密都能正常登录（除驱动不支持外）

-- aws禁止配置热加载
postgres=> select pg_reload_conf();
ERROR:  permission denied for function pg_reload_conf

-- 权限不够使用的用户是创建实例的“超级”用户
postgres=> alter system set password_encryption='scram-sha-256';
ERROR:  must be superuser to execute ALTER SYSTEM command

-- 云没有pg_authid表权限
select * from pg_authid;
ERROR:  permission denied for table pg_authid

-- 可以通过log_statement的日志观察用户账号变更为md5还是sha-256

postgresql密码过期

-- 查看用户密码过期时间信息valuntil，默认是永不过期
select * from pg_user;

-- 设置用户的密码过期时间
alter user zhangsan valid until '2021-12-25 16:15:00';

-- 超过设置期限之后，无论是第三方客户端登录，还是本地服务器psql客户端登录都会出现验证失败信息
psql: FATAL:  password authentication failed for user "zhangsan"

• pg密码有限期只是针对客户端有效，数据库服务不受限制。也就是说用户过期后无法登录，但是存在数据库中的账号密码信息依旧存在不会过期，重新修改过期时间后则可以重新使用
• 设置密码有效期时，网络访问控制文件pg_hba.conf中不能配置为trust认证方式