pg数据库安全配置

数据库安全配置是数据库管理的重要环节之一。安全配置主要包括密码、网络访问控制、审计等。下面会依次讲解在瀚高数据库中如何实现密码相关的安全性配置。

1 密码加密存储

pg中密码始终以加密方式存储在系统目录中。ENCREPED 关键字没有任何效果, 但被接受向后兼容。加密方式可以通过password_encryption参数配置

--create role test with login encryped password "test";
create role test with login password "test";

show password_encryption;
password_encryption
---------------------
md5
(1 row)

select usename,passwd from pg_shadow where usename="test";
 usename |               passwd                
---------+-------------------------------------
 test    | md505a671c66aefea124cc08b76ea6d30bb
(1 row)

2 密码有效期

pg支持密码有效期配置，可以通过配置密码有效期，制定密码更换周期。

服务器端设置有效期

alter role test valid until "2020-04-24 10:10:00";

select usename,valuntil  from pg_user where usename="test";
 usename |        valuntil        
---------+------------------------
 test    | 2020-12-10 16:58:00+08

客户端连接测试，密码有效期到期后，需重新设置密码

有效期超期报错

date
Fri Apr 24 10:19:02 CST 2020


psql -h 192.168.6.10 -U test -d highgo
Password for user test:
psql: FATAL: password authentication failed for user "test"

修改有效期
alter role test valid until "2020-12-31 10:10:00";

正常登陆
psql -h 192.168.6.10 -U test -d highgo
Password for user test: 
psql (5.6.5)

PSQL: Release 5.6.5
Connected to:
HighGo Database V5.6 Enterprise Edition Release 5.6.5 - 64-bit Production

Type "help" for help.

highgo=> 

注意：

• pg_hba.conf配置了local trust，则可在服务器端登陆，不受限制。

3 密码复杂度策略

passwordcheck.so模块可以实现密码复杂度要求，此模块可以检查密码，如果密码太弱，他会拒绝连接 创建用户或修改用户密码时，强制限制密码的复杂度，限制密码不能重复使用 例如密码长度，包含数字，字母，大小写，特殊字符等，同时排除暴力破解字典中的字符串 参考官方文档

3.1、启用模块

添加"$libdir目录下的passwordcheck"到参数shared_preload_libraries，重启生效 默认so文件都存放在$libdir目录下

select name,setting from pg_settings where name like "%dynamic%";
name | setting
----------------------------+---------
dynamic_library_path | $libdir
dynamic_shared_memory_type | posix
(2 rows)


ls -atl $PGHOME/lib/postgresql/passwordcheck*
-rwxr-xr-x 1 postgres postgres 8616 Feb 27 10:09 /opt/pg122/lib/postgresql/passwordcheck.so

alter system set shared_preload_libraries=passwordcheck;

重启生效

shared_preload_libraries参数使用参考“Postgresql共享库预加载(Shared Library Preloading)”

3.2、复杂度功能验证

密码复杂度检查模块Passwordcheck

• 验证创建的用户密码是否符合规则。 密码：最少8个字符；必须包含数字和字母；密码中不能含有用户名字段。

alter role test with password "test";
ERROR: password is too short
alter role test password "12345678";
ERROR: password must contain both letters and nonletters
alter role test with password "test1234";
ERROR: password must not contain user name
alter role test with password "tttt1234";
ALTER ROLE

4、密码验证失败延迟

auth_delay.so模块会导致服务器在报告身份验证失败之前短暂停留,这个主要用于防止暴力破解. 验证失败后, 延迟一个时间窗口才能继续验证。请注意, 它不会阻止拒绝服务攻击, 甚至可能会加剧这些攻击, 因为在报告身份验证失败之前等待的进程仍将使用连接插槽。

4.1、启用模块

需要配置以下参数，实现密码验证延迟失败延迟

so文件存储在$libdir下

ls -atl $PGHOME/lib/auth_delay*
-rwxr-xr-x 1 postgres postgres 8352 Feb 27 10:09 /opt/pg122/lib/postgresql/auth_delay.so

参数修改

shared_preload_libraries --预加载模块
auth_delay.milliseconds (int) --指定延迟时间
alter system set shared_preload_libraries=passwordcheck,auth_delay;
重启生效
alter system set auth_delay.milliseconds=5000;
reload生效

注意：auth_delay.milliseconds需要启用auth_delay后才可以设置

4.2、验证

psql -h 192.168.6.10 -U test -p 5432 -d postgres
Password for user test:
--5s
psql: FATAL: password authentication failed for user "test"

输入密码后，如果密码不正确，会等待5s，然后返回密码失败提示
psql -h 192.168.6.12 -U test -p 5432 -d postgres
Password for user test:
psql (10.4)
Type "help" for help.
postgres=>

输入密码后，如果密码正确，没有等待。

5、密码验证失败次数限制，失败后锁定, 以及解锁时间

目前PostgreSQL不支持这个安全策略, 目前只能使用auth_delay来延长暴力破解的时间.

6、设置密码时防止密码被记录到数据库日志中

密码的配置命令可能会被记录到history文件及csvlog日志文件中（如果开启了DDL或更高级别审计log_statement），这些文件明文记录了密码，可能造成密码泄露风险。

6.1、密码记录到两个地方

介绍

• HISTFILE The file name that will be used to store the history list. If unset, the file name is taken from the PSQL_HISTORY environment variable. If that is not set either, the default is ~/.psql_history, or %APPDATA%postgresqlpsql_history on Windows. For example, putting: set HISTFILE ~/.psql_history- :DBNAME in ~/.psqlrc will cause psql to maintain a separate history for each database. Note This feature was shamelessly plagiarized from Bash. --
• csvlog 数据库错误日志

记录情况 如以下命令，会记录到HISTFILE和csvlog日志中

postgres=# alter role test with password "tttt1234";

• history file记录 [pg@pg ~]$ cat ~/.psql_history |grep tttt1234 alter role test with password "tttt1234"; [pg@pg ~]$
• csvlog记录 [pg@pg ~]$ cat $PGDATA/postgresql.conf |grep log_statement #log_statement = "none"         # none, ddl, mod, all log_statement = "ddl" #log_statement_stats = off

[pg@pg ~]$ cat $PGDATA/pg_log/postgresql-2019-04-12_092557.csv |grep tttt1234 2019-04-12 09:33:23.036 CST,"pg","postgres",1309,"[local]",5cafeadb.51d,3,"idle",2019-04-12 09:33:15 CST,3/21,0,LOG,00000,"statement: alter role test with password "tttt1234";",,,,,,,,,"psql" [pg@pg ~]$

6.2、解决方式

使用createuser命令行工具-W选项提示输入密码。 。

[pg@pg ~]$ createuser -l -h 127.0.0.1 -p 5432 -U pg -W tuser
Password:
[pg@pg ~]$
[pg@pg ~]$ cat $PGDATA/pg_log/postgresql-2019-04-12_092557.csv |grep tuser
2019-04-12 11:17:48.348 CST,"pg","postgres",1574,"localhost:42560",5cb0035c.626,3,"idle",2019-04-12 11:17:48 CST,3/236,0,LOG,00000,"statement: CREATE ROLE tuser NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;",,,,,,,,,"createuser"
[pg@pg ~]$

• 注意：可使用pg_pool提供的pg_md5工具生成密码, 在psql中使用ALTER ROLE填入md5值。 与上面类似, pg_md5是pgpool提供的一个工具, 实际上就是调用上面的函数

７网络访问控制

在$PGDATA/pg_hba.conf文件中配置网络访问控制，可限制连接网段和IP地址

如：

# IP reject set
host       all           all           192.168.6.1/32             reject
host       all           all           192.168.6.0/24             reject

reload生效 pg_ctl reload

解释： 第一列：类型，host表示TCP/IP连接 第二列：数据库，all表示可以访问所有数据库 第三列：数据库用户，all表示允许所有数据库用户连接 第四列：地址，指定具体的主机名或IP地址 第五列：验证方式，reject表示拒绝连接

注意：

1 在$PGDATA/pg_hba.conf没有匹配记录的IP，会自动拒绝访问 2 每次连接尝试都会按顺序检查pg_hba.conf文件中的记录，因此限制IP记录应该放在靠前的位置

8 权限分配

对于用户权限的分配，建议使用最小权限原则。只授予用户需要的权限。

所有权限的管理

如果是针对对象的所有权限，建议统一使用schema进行管理，大体过程如下 1、新建一个schema

create schema SCHEMA_NAME;

对用户授予管理这个schema的权限

grant all on schema SCHEMA_NAME to USER_NAME;

设置将该schema加入到搜索路径

alter database "benchmarksql" set search_path to "$user", public,SCHEMA_NAME; 

添加新路径即可，不要修改原有的值。

具体对象授权管理

具体对象授权参考相关的语法

可能用到的授权语句语法

授予用户对某个schema下的部分表或所有表的操作权限

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

将某个schema下的函数授予用户执行权限（EXECUTE）或所有权限（ALL）

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTION function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

*授予用户对某个schema下的部分SEQUENCE或所有SEQUENCE的操作权限

GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

授予用户对某个数据库的管理权限

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

授予用户对外部数据的权限

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

授予用户对语言的使用权限，如pl/pgsql、PL/Tcl等

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

授予用户管理schema的权限

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

授予用户管理表空间的权限

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

授予用户管理数据类型的权限

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER

将一个用户的权限授予另一个用户

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

注：在pg中用户是一个特殊的role。USAGE表示只有使用权限，WITH ADMIN OPTION表示接收权限者拥有对其他用户授予该对象的权限。

9审计配置

这里只讨论数据库日志记录的审计方式，pg还有专用的审计插件（pg_audit）用于专业的审计，针对pg_audit请参考相关文章。

配置日志参数，审计连接信息及SQL语句，审计信息生成在数据库日志中。 执行以下语句配置：

alter system set log_destination = "csvlog";
alter system set logging_collector=on;

alter system set log_connections=on;
alter system set log_disconnections=on;
alter system set log_statement = "mod";

reload生效 pg_ctl reload

解释： log_destination：PG记录日志的方法 logging_collector：启动一个后台进程抓取日志信息写入日志文件

log_connections：尝试连接服务器的记录，及完成客户端身份验证的记录 log_disconnections ：会话终止记录，及会话持续时间 log_statement：记录的语句

log_statement参数值：        none，即不记录        ddl(记录create,drop和alter)        mod(记录ddl+insert,delete,update和truncate)        all(mod+select)