SRE文档1.PostgreSQL pg_hba.conf
该文件用于控制访问安全性，管理客户端对于PostgreSQL服务器的访问权限，
内容包括：
允许哪些用户连接到哪个数据库，允许哪些IP或者哪个网段的IP连接到本服务器，以及指定连接时使用的身份验证模式
bash
$ cat /data/pgdata/data/pg_hba.conf.bak
# TYPE DATABASE USER ADDRESS METHOD
local database user auth-method [auth-options]
host database user address auth-method [auth-options]
hostssl database user address auth-method [auth-options]
hostnossl database user address auth-method [auth-options]
host database user IP-address IP-mask auth-method [auth-options]
hostssl database user IP-address IP-mask auth-method [auth-options]
hostnossl database user IP-address IP-mask auth-method [auth-options]
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust$ cat /data/pgdata/data/pg_hba.conf.bak
# TYPE DATABASE USER ADDRESS METHOD
local database user auth-method [auth-options]
host database user address auth-method [auth-options]
hostssl database user address auth-method [auth-options]
hostnossl database user address auth-method [auth-options]
host database user IP-address IP-mask auth-method [auth-options]
hostssl database user IP-address IP-mask auth-method [auth-options]
hostnossl database user IP-address IP-mask auth-method [auth-options]
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust1.1TYPE
定义了多种连接PostgreSQL的方式，一般分为：
| type | 描述 |
|---|---|
| local | 匹配使用Unix域套接字的连接，如果没有此类型的记录，则不允许使用Unix域套接字连接 |
| host | 匹配使用TCP/IP进行的连接，主机记录匹配SSL或非SSL连接，需要配置listen_addresses |
| hostssl | 匹配使用TCP/IP进行的连接，仅限于使用SSL加密进行连接，需要配置ssl参数 |
| hostnossl | 匹配通过TCP/IP进行的连接，不使用SSL的连接 |
| database | 匹配的数据库名称，all指定它匹配所有数据库。如果请求的数据库与请求的用户具有相同的名称则可以使用samerole值。复制（replication）不指定数据库，多个数据库可以用逗号分隔 |
| user | 匹配的数据库用户名，值all指定它匹配所有用户。 可以通过用逗号分隔来提供多个用户名 |
| address | 匹配的客户端计算机地址，可以包含主机名，IP地址范围。如：172.20.143.89/32、172.20.143.0/24、10.6.0.0/16、:: 1/128。 0.0.0.0/0表示所有IPv4地址，:: 0/0表示所有IPv6地址。要指定单个主机，请使用掩码长度32（对于IPv4）或128（对于IPv6）。all以匹配任何IP地址 |
| IP-address、IP-mask | 这两个字段可用作IP地址/掩码长度，如：127.0.0.1 255.255.255.255 |
| auth-method | 指定连接与此记录匹配时要使用的身份验证方法：trust、reject、scram-sha-256、md5、password、gss、sspi、ident、peer、ldap、radius、cert、pam、bsd |
1.2METHOD
身份验证模式一般分为：ident、trust、md5、password、peer、reject。其中ident和peer模式仅适用于Linux、Unix和Max，不适用于Windows
| 模式 | 描述 |
|---|---|
| trust | 该模式可以不用密码直接连接数据库，不安全 |
| md5 | 该模式很常用，要求连接发起者携带用md5算法加密的密码 |
| password | 该模式是使用明文密码进行身份验证，也不安全，不推荐 |
| ident | 该模式下系统会将请求发起者的操作系统用户映射为PostgesSQL数据库内部用户，并以该内部用户的权限登录，且此时无需提供登录密码。操作系统用户与数据库内部用户之间的映射关系会记录在pg_ident.conf文件中 |
| peer | 该模式使用连接发起端的操作系统名进行身份验证。仅限于Linux、BSD、Mac OS X和Solaris，并且仅可用于本地服务器发起的连接 |
| reject | 该模式表示拒绝所有请求 |
| radius | 使用RADIUS服务器进行身份验证 |
| cert | 使用SSL客户端证书进行身份验证 |
| pam | 使用操作系统提供的可插入身份验证模块（PAM）服务进行身份验证 |
| bsd | 使用操作系统提供的BSD身份验证服务进行身份验证 |
| ldap | 使用LDAP服务器进行身份验证 |
| gss | 使用GSSAPI对用户进行身份验证，这仅适用于TCP / IP连接 |
| sspi | 使用SSPI对用户进行身份验证，这仅适用于Windows |
| scram-sha-256 | 执行SCRAM-SHA-256身份验证以验证用户的密码 |
注意，常见错误就是把规则放错，例如若将0.0.0.0/0 reject规则放到127.0.0.1/32 trust前面，那么所有本地用户都无法连接，即使下面有规则允许也不行
例子
1、允许IP为10.0.8.100的username以密码方式访问数据库dbname
host dbname username 10.0.8.100/32 md5
2、允许IP为10.1.1.0~10.1.1.255网段的用户以密码方式登录数据库
host all all 10.1.1.0/24 md5
3、允许IP为192.168.1.0的用户可以不使用密码直接登录数据库
host all all 192.168.1.0/32 trust
4、指定整个网段
host test pgtest 0.0.0.0/0 md5
auth-options：在auth-method字段之后，可以存在name = value形式的字段，用于指定认证方法的选项。 例子：
bash
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
--在本地允许任何用户无密码登录
local all all peer
--操作系统的登录用户和pg的用户是否一致，一致则可以登录
local all all ident
--操作系统的登录用户和pg的用户是否一致，一致则可以登录
host all all 192.168.163.0/24 md5
--指定客户端IP访问通过md5身份验证进行登录
host all all 192.168.163.132/32 password
--指定客户端IP通过passwotd身份验证进行登录
host all all 192.168.54.1/32 reject
host all all 192.168.0.0/16 ident
host all all 127.0.0.1 255.255.255.255 trust
...# TYPE DATABASE USER ADDRESS METHOD
local all all trust
--在本地允许任何用户无密码登录
local all all peer
--操作系统的登录用户和pg的用户是否一致，一致则可以登录
local all all ident
--操作系统的登录用户和pg的用户是否一致，一致则可以登录
host all all 192.168.163.0/24 md5
--指定客户端IP访问通过md5身份验证进行登录
host all all 192.168.163.132/32 password
--指定客户端IP通过passwotd身份验证进行登录
host all all 192.168.54.1/32 reject
host all all 192.168.0.0/16 ident
host all all 127.0.0.1 255.255.255.255 trust
...设置完之后可以通过查看表来查看hba
sql
zjy=# select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+-----------+---------------+-----------------------------------------+-------------+---------+-------
| host | {all} | {all} | 192.168.163.0 | 255.255.255.0 | md5 | |
| local | {all} | {all} | | | peer | |
| host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |
| host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |
| local | {replication} | {all} | | | peer | |
| host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |
| host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |
-- 查看hba文件
postgres@postgres=>SHOW hba_file;
hba_file
-------------------------------
/data/pgdata/data/pg_hba.conf
(1 row)zjy=# select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+-----------+---------------+-----------------------------------------+-------------+---------+-------
| host | {all} | {all} | 192.168.163.0 | 255.255.255.0 | md5 | |
| local | {all} | {all} | | | peer | |
| host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |
| host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |
| local | {replication} | {all} | | | peer | |
| host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |
| host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |
-- 查看hba文件
postgres@postgres=>SHOW hba_file;
hba_file
-------------------------------
/data/pgdata/data/pg_hba.conf
(1 row)修改完pg_hba.conf文件之后，需要重新加载配置，不用重启数据库
sql
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
tpostgres=# select pg_reload_conf();
pg_reload_conf
----------------
t许多配置文件修改后需要重启postgres服务才能生效，但有的只需执行一下重新加载即可生效。重新加载并不会中断连接
命令：pg_ctl reload -D your_data_directory_here
如果是在linux以服务的形式安装的，那么执行：
命令：service postgresql-9.5 reload
另外一种加载配置文件的方法是以超级用户登录到任何一个数据库后执行：
命令：select pg_reload_conf();
# TYPE DATABASE USER ADDRESS METHOD
host postgres all 0.0.0.0/0 reject
host template0 all 0.0.0.0/0 reject
host template1 all 0.0.0.0/0 reject# TYPE DATABASE USER ADDRESS METHOD
host postgres all 0.0.0.0/0 reject
host template0 all 0.0.0.0/0 reject
host template1 all 0.0.0.0/0 reject2.pg_ident.conf
https://www.postgresql.org/docs/12/auth-username-maps.html
pg_ident.conf的格式
sql
# MAPNAME SYSTEM-USERNAME PG-USERNAME
usermap username dbuser# MAPNAME SYSTEM-USERNAME PG-USERNAME
usermap username dbuserusermap为映射名，要在pg_hba.conf中用到，多个映射可以共用同一个映射名，
username为操作系统用户名，
dbuser为映射到的数据库用户
例：操作系统用户userhg，使用数据库用户highgo连接数据库，而操作系统用户usergp，使用数据库用户gpadmin连接数据库
pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
maphg userhg highgo
maphg usergp gpadmin# MAPNAME SYSTEM-USERNAME PG-USERNAME
maphg userhg highgo
maphg usergp gpadminpg_hba.conf如下：
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all ident map=maphg# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all ident map=maphgmap为pg_hba.conf的auth-options项，map=mapzy指示该认证条件使用mapzy映射。指定映射后原本的同名操作系统用户就不能连接数据库
3,PostgreSQL安全加固
1.限制用户连接数
max_connections根据应用并发量设置合理的值，对普通数据库用户设置限制连接数，并设置少量的保留超级用户登录连接数
sql
postgres=# create user app1 connection limit 5;
CREATE ROLEpostgres=# create user app1 connection limit 5;
CREATE ROLE2.修改默认端口
sql
postgres=# select * from current_setting('port');
current_setting
-----------------
6000postgres=# select * from current_setting('port');
current_setting
-----------------
60003.不使用默认postgres数据库
建议1：新建数据库后删除默认的postgres数据库
建议2：保留一个干净无污染的数据库以便发生故障时连接
sql
postgres=# create database my_app_db owner app1;
CREATE DATABASE
postgres=# \c my_app_db
You are now connected to database "my_app_db" as user "postgres".
my_app_db=# drop database postgres;
DROP DATABASEpostgres=# create database my_app_db owner app1;
CREATE DATABASE
postgres=# \c my_app_db
You are now connected to database "my_app_db" as user "postgres".
my_app_db=# drop database postgres;
DROP DATABASE4.自定义超级用户
建议删除默认超级用户postgres，新建不同名称超级用户，也可再initdb时使用-U指定
$ initdb -D /opt/data6000/ -U admin -W$ initdb -D /opt/data6000/ -U admin -W5.修改监听地址
根据业务场景设置，比如设置为localhost或服务器IP
#listen_addresses = 'localhost'
#listen_addresses = '192.168.99.100'
#listen_addresses = '192.168.99.100,192.168.99.200'#listen_addresses = 'localhost'
#listen_addresses = '192.168.99.100'
#listen_addresses = '192.168.99.100,192.168.99.200'6.配置客户端认证
bash
# 服务端本地数据库用户免密登录
local all all trust
# 拒绝超级用户从网络登录
host all postgres 0.0.0.0/0 reject
# 其它用户密码验证登陆
host all all 0.0.0.0/0 scram-sha-256
# 流复制用户密码验证登录
host replication replica 192.168.99.101/32 md5# 服务端本地数据库用户免密登录
local all all trust
# 拒绝超级用户从网络登录
host all postgres 0.0.0.0/0 reject
# 其它用户密码验证登陆
host all all 0.0.0.0/0 scram-sha-256
# 流复制用户密码验证登录
host replication replica 192.168.99.101/32 md57.设置socket访问方式
bash
unix_socket_directories='$PGDATA'
unix_socket_group=''
unix_socket_permissions='0700'unix_socket_directories='$PGDATA'
unix_socket_group=''
unix_socket_permissions='0700'8.使用pgpass文件
linux和window都建议使用pgpass文件
bash
Linux
$ cat .pgpass
192.168.99.200:5432:postgres:postgres:admin
192.168.99.227:6432:*:app1:yourPassword
win10
C:\Users\pcsuc\AppData\Roaming\postgresql\pgpass.conf
localhost:5432:*:postgres:admin
192.168.99.227:6432:*:app1:yourPasswordLinux
$ cat .pgpass
192.168.99.200:5432:postgres:postgres:admin
192.168.99.227:6432:*:app1:yourPassword
win10
C:\Users\pcsuc\AppData\Roaming\postgresql\pgpass.conf
localhost:5432:*:postgres:admin
192.168.99.227:6432:*:app1:yourPassword9.安全创建用户
bash
$ createuser user_app1 --no-superuser -p6000 -Upostgres --pwprompt
Enter password for new role:
Enter it again:$ createuser user_app1 --no-superuser -p6000 -Upostgres --pwprompt
Enter password for new role:
Enter it again:10.用户密码安全策略
1.简单密码策略
PostgreSQL提供了一个插件passwordcheck可以满足简单的密码复杂度测验，防止使用过短，或者与包含用户名的密码
bash
$ vi postgresql.conf
#安装扩展
shared_preload_libraries = 'passwordcheck'$ vi postgresql.conf
#安装扩展
shared_preload_libraries = 'passwordcheck'sql
--测试SQL
CREATE USER regress_user1;
-- ok
ALTER USER regress_user1 PASSWORD 'a_nice_long_password';
-- error: too short
ALTER USER regress_user1 PASSWORD 'tooshrt';
ERROR: password is too short
-- error: contains user name
ALTER USER regress_user1 PASSWORD 'xyzregress_user1';
ERROR: password must not contain user name
-- error: contains only letters
ALTER USER regress_user1 PASSWORD 'alessnicelongpassword';
ERROR: password must contain both letters and nonletters
-- encrypted ok (password is "secret")
ALTER USER regress_user1 PASSWORD 'md51a44d829a20a23eac686d9f0d258af13';
-- error: password is user name
ALTER USER regress_user1 PASSWORD 'md5e589150ae7d28f93333afae92b36ef48';
ERROR: password must not equal user name
DROP USER regress_user1;--测试SQL
CREATE USER regress_user1;
-- ok
ALTER USER regress_user1 PASSWORD 'a_nice_long_password';
-- error: too short
ALTER USER regress_user1 PASSWORD 'tooshrt';
ERROR: password is too short
-- error: contains user name
ALTER USER regress_user1 PASSWORD 'xyzregress_user1';
ERROR: password must not contain user name
-- error: contains only letters
ALTER USER regress_user1 PASSWORD 'alessnicelongpassword';
ERROR: password must contain both letters and nonletters
-- encrypted ok (password is "secret")
ALTER USER regress_user1 PASSWORD 'md51a44d829a20a23eac686d9f0d258af13';
-- error: password is user name
ALTER USER regress_user1 PASSWORD 'md5e589150ae7d28f93333afae92b36ef48';
ERROR: password must not equal user name
DROP USER regress_user1;2.自定义配置复杂策略
bash
2.1修改passwordcheck.c源文件
参考例子
https://github.com/Luckyness/passwordcheck/blob/master/passwordcheck.c
2.2重新编译
$ gmake clean
$ gmake
$ gmake install
2.3配置重启服务
$ vi postgresql.conf
shared_preload_libraries = 'passwordcheck'
passwordcheck.level = 'true'
2.4测试
postgres=# alter role postgres encrypted password 'abcde123';
2020-02-12 16:45:11.210 CST [4763] ERROR: password must contain both letters and number and specialchar
2020-02-12 16:45:11.210 CST [4763] STATEMENT: alter role postgres encrypted password 'abcde123';
ERROR: password must contain both letters and number and specialchar2.1修改passwordcheck.c源文件
参考例子
https://github.com/Luckyness/passwordcheck/blob/master/passwordcheck.c
2.2重新编译
$ gmake clean
$ gmake
$ gmake install
2.3配置重启服务
$ vi postgresql.conf
shared_preload_libraries = 'passwordcheck'
passwordcheck.level = 'true'
2.4测试
postgres=# alter role postgres encrypted password 'abcde123';
2020-02-12 16:45:11.210 CST [4763] ERROR: password must contain both letters and number and specialchar
2020-02-12 16:45:11.210 CST [4763] STATEMENT: alter role postgres encrypted password 'abcde123';
ERROR: password must contain both letters and number and specialchar3.密码验证失败延迟
主要用于防止暴力破解，验证失败后，延迟一个时间窗口才能继续验证
bash
3.1安装
$ cd /opt/postgresql-12.1/contrib/auth_delay/
$ gmake clean
$ gmake
$ gmake install
3.2配置
$ vi postgresql.conf
shared_preload_libraries = 'auth_delay,passwordcheck'
auth_delay.milliseconds = 5000
3.3测试
$ /opt/pgsql/bin/psql -h127.0.0.1 -Uregress_user1 postgres
Password for user regress_user1:
密码输入错误后, 需要等待5秒返回认证失败. 防止暴力破解密码3.1安装
$ cd /opt/postgresql-12.1/contrib/auth_delay/
$ gmake clean
$ gmake
$ gmake install
3.2配置
$ vi postgresql.conf
shared_preload_libraries = 'auth_delay,passwordcheck'
auth_delay.milliseconds = 5000
3.3测试
$ /opt/pgsql/bin/psql -h127.0.0.1 -Uregress_user1 postgres
Password for user regress_user1:
密码输入错误后, 需要等待5秒返回认证失败. 防止暴力破解密码4.密码有效期
bash
4.1 配置
postgres=# alter role postgres valid until '2020-03-01';
4.2 查看用户密码到期时间
postgres=# \du+ postgres
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS+| {} |
| Password valid until 2020-03-01 00:00:00+08 | |4.1 配置
postgres=# alter role postgres valid until '2020-03-01';
4.2 查看用户密码到期时间
postgres=# \du+ postgres
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS+| {} |
| Password valid until 2020-03-01 00:00:00+08 | |