1.限制用户连接数
max_connections根据应用并发量设置合理的值，对普通数据库用户设置限制连接数，并设置少量的保留超级用户登录连接数。
postgres=# create user app1 connection limit 5;
CREATE ROLE
2.修改默认端口
postgres=# select * from current_setting('port');
current_setting
-----------------
6000
(1 row)
外界通过TCP发送cancel请求给postmaster去处理cancel请求，绕过了pg_hba.conf的认证，所以需要修改默认端口。
3.不使用默认postgres数据库
建议1：新建数据库后删除默认的postgres数据库
建议2：保留一个干净无污染的数据库以便发生故障时连接
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 DATABASE
4.自定义超级用户
建议删除默认超级用户postgres，新建不同名称超级用户，也可再initdb时使用-U指定。
$ initdb -D /opt/data6000/ -U admin -W
5.修改监听地址
根据业务场景设置，比如设置为localhost或服务器IP。
#listen_addresses = 'localhost'
#listen_addresses = '192.168.99.100'
#listen_addresses = '192.168.99.100,192.168.99.200'
6.配置客户端认证
参考配置如下
# 服务端本地数据库用户免密登录
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
密码口令认证方式推荐使用scram-sha-256
7.设置socket访问方式
参考配置如下
unix_socket_directories='$PGDATA'
unix_socket_group=''
unix_socket_permissions='0700'
8.使用pgpass文件
linux和window都建议使用pgpass文件
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:yourPassword
9.安全创建用户
为避免用户密码记录到数据库日志文件、history文件、pg_stat_activity、pg_stat_statements插件、堡垒机等，推荐使用createuser及-W选项提示输入密码
$ createuser user_app1 --no-superuser -p6000 -Upostgres --pwprompt
Enter password for new role:
Enter it again:
修改用户密码建议使用\password来修改
10.用户密码安全策略
可参考《PostgreSQL用户密码安全策略管理》
11.权限配置
表权限按最小最需原则分配
revoke all on DATABASE XXX from public;
revoke select on PG_PROC from public;
更多参考例子《PostgreSQL用户表权限设计与配置》
12.记录数据库日志并清理
配置合理的日志级别，常见级别如warn
配置日期过期策略，如配置最大100个循环写文件，或按周循环等。
13.备份恢复测试
初始化数据目录时建议打开checksums，数据库备份后需要做恢复测试，确保有效性，可以使用第三方备份恢复管理器，如pg_rman,pg_probackup,pgBackRest等。
14.备份数据文件加密存储
备份文件可以手工压缩加密或者借助第三方备份工具，如pgBackRest等。
15.表空间安全
主备环境下创建表空间时，务必提前在备机上同时创建好文件目录，
如果忘记创建，主库执行创建表空间命令后备库会宕机。
16.防恶意攻击
官网补丁升级
限制数据库文件读写权限
pg_read_file
pg_read_server_files
pg_execute_server_program
pg_write_server_files
视图安全
下面是一个例子：
CREATE FUNCTION tricky(text, text) RETURNS bool AS $$
BEGIN
RAISE NOTICE '% => %', $1, $2;
RETURN true;
END
$$ LANGUAGE plpgsql COST 0.0000000000000000000001;
SELECT * FROM phone_number WHERE tricky(person, phone);
查询视图phone_number时使用了查询条件tricky函数，由于函数的cost设置一个较低的值。
函数体里以消息的形式获得了基表的全部数据。
下面使用security_barrier选项后优化器不起作用, 但会有性能损失
CREATE VIEW phone_number WITH (security_barrier) AS
SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
函数调用安全
定义函数时有两种执行方式，一种是以定义者权限运行，一种是调用者权限运行。
第二种方式普通用户定义的函数体包含高权限的高危操作时，虽然自己没有权限执行，但会形成陷阱，管理员用户执行时会触发。
下面是一个规范的示例，test2用户可以通过函数安全访问test用户数据：
test用户创建下面函数
create or replace function f1(v_id int) returns void as $$
begin
insert into test.t1 values(v_id);
end;
$$ language plpgsql security definer set search_path to 'test';
create or replace function f2() returns setof t1 as $$
begin
return query select * from test.t1 where id<200;
end;
$$ language plpgsql security definer set search_path to 'test';
test用户执行
select f1(200);
test=> select * from test.t1;
id
-----
100
200
(2 rows)
test=> grant USAGE on SCHEMA test to test2;
GRANT
用test2用户测试
test=> \c test test2
You are now connected to database "test" as user "test2".
可以通过函数f1执行插入
test=> select test.f1(300);
f1
----
(1 row)
可通过函数f2查询数据
test=> select * from test.f2();
id
-----
100
(1 row)
直接操作表是不可以的
test=> select * from test.t1;
ERROR: permission denied for table t1
test=> insert into test.t1 values(900);
ERROR: permission denied for table t1
防SQL注入
使用绑定变量
防密码暴力破解
使用auth_delay延长暴力破解的时间
shared_preload_libraries = 'auth_delay'
auth_delay.milliseconds = '500'
保持联系
从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴加我微信进群吹牛唠嗑，交流技术。
