# 查看当前配置
$ cat /postgresql/data/pg_hba.conf

# PostgreSQL Client Authentication Configuration File
# TYPE DATABASE USER ADDRESS METHOD

# Local connections
local all all trust
# IPv4 local connections:
fgedu.net.cn all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
fgedu.net.cn all all ::1/128 scram-sha-256

# 查看当前生效的规则
$ psql -U pgsql -c “SELECT * FROM pg_hba_file_rules;”
line_number | type | fgedudb | fgedu_name | address | netmask | auth_method | options | error
————-+——-+————–+———–+———+———+————-+———+——-
78 | local | {all} | {all} | | | trust | |
80 | fgedu.net.cn | {all} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | |
82 | fgedu.net.cn | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | |
(3 行记录)

# 修改前
local all all trust

# 修改后
local all pgsql peer
local all all peer

# 保存后重新加载配置
$ psql -U pgsql -c “SELECT pg_reload_conf();”
pg_reload_conf
—————-
t
(1 行记录)

# 测试peer认证
# 使用postgres用户连接
$ sudo -u pgsql psql
psql (18.3)
输入 “help” 来获取帮助信息.

postgres=# SELECT current_fgedu;
current_fgedu
————–
pgsql (1 行记录)

postgres=# \q

# 使用其他用户连接（需要映射）
$ sudo -u fgedu_fgedu psql -U fgedu_fgedu -d fgedudb
psql: error: connection to server on socket “/tmp/.s.PGSQL.5432” failed: FATAL: Peer authentication failed for fgedu “fgedu_fgedu”

# 配置用户映射
$ vi /postgresql/data/pg_ident.conf

# 添加映射规则
# MAPNAME SYSTEM-USERNAME PG-USERNAME
fgedu_map fgedu_fgedu fgedu_fgedu

# 修改pg_hba.conf使用映射
local all fgedu_fgedu peer map=fgedu_map

# 重新加载配置
$ psql -U pgsql -c “SELECT pg_reload_conf();”

# 再次测试
$ sudo -u fgedu_fgedu psql -U fgedu_fgedu -d fgedudb
psql (18.3)
输入 “help” 来获取帮助信息.

fgedudb=> SELECT current_fgedu;
current_fgedu
————–
fgedu_fgedu
(1 行记录)

# 添加内网访问规则
# TYPE DATABASE USER ADDRESS METHOD

# 本地连接
local all pgsql peer
local all all peer

# IPv4本地连接
fgedu.net.cn all all 127.0.0.1/32 scram-sha-256

# IPv6本地连接
fgedu.net.cn all all ::1/128 scram-sha-256

# 内网管理访问（192.168.1.0/24）
fgedu.net.cn all pgsql 192.168.1.0/24 scram-sha-256

# 应用服务器访问（192.168.2.0/24）
fgedu.net.cn fgedudb fgapp_fgedu 192.168.2.0/24 scram-sha-256
fgedu.net.cn fgedudb readonly_fgedu 192.168.2.0/24 scram-sha-256

# 复制连接（192.168.4.0/24）
fgedu.net.cn replication replicator 192.168.4.0/24 scram-sha-256

# 拒绝其他访问
fgedu.net.cn all all 0.0.0.0/0 reject

# 重新加载配置
$ psql -U pgsql -c “SELECT pg_reload_conf();”
pg_reload_conf
—————-
t
(1 行记录)

# 创建应用用户
$ psql -U pgsql

postgres=# CREATE USER fgapp_fgedu WITH PASSWORD ‘Fgedu@123456’;
CREATE ROLE

postgres=# CREATE USER readonly_fgedu WITH PASSWORD ‘Fgedu@654321’;
CREATE ROLE

postgres=# GRANT CONNECT ON DATABASE fgedudb TO fgapp_fgedu, readonly_fgedu;
GRANT

postgres=# GRANT USAGE ON SCHEMA public TO fgapp_fgedu, readonly_fgedu;
GRANT

postgres=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO fgapp_fgedu;
GRANT

postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_fgedu;
GRANT

fgedudb=> SELECT current_fgedu, inet_server_addr(), inet_server_port();
current_fgedu | inet_server_addr | inet_server_port
————–+——————+——————
fgapp_fgedu | 192.168.1.100 | 5432
(1 行记录)

fgedudb=> CREATE TABLE fgedu_test(id int);
CREATE TABLE fgedu_
fgedudb=> INSERT INTO fgedu_test VALUES(1);
INSERT 0 1

fgedudb=> SELECT * FROM fgedu_test;
id
—-
1
(1 行记录)

fgedudb=> \q

# 从非授权IP测试连接
$ psql -h 192.168.1.100 -p 5432 -U fgapp_fgedu -d fgedudb
psql: error: connection to server at “192.168.1.100”, port 5432 failed: FATAL: no pg_hba.conf entry for fgedu.net.cn “10.0.0.100”, fgedu “fgapp_fgedu”, fgedudb “fgedudb”, no encryption

# 生成CA私钥
$ openssl genrsa -out ca.key 4096
Generating RSA private key, 4096 bit long modulus (2 primes)
…………………………………………………………………..++++
…………++++
e is 65537 (0x010001)

# 生成CA证书
$ openssl req -new -x509 -days 3650 -key ca.key -out ca.crt
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.
—–
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:FGEDU
Organizational Unit Name (eg, section) []:DBA
Common Name (eg, your name or your server’s fgedu.net.cnname) []:fgedu.net.cn
Email Address []:dba@fgedu.net.cn

# 生成服务器私钥
$ openssl genrsa -out server.key 2048
Generating RSA private key, 2048 bit long modulus (2 primes)
…….+++++
…………….+++++
e is 65537 (0x010001)

# 生成服务器证书请求
$ openssl req -new -key server.key -out server.csr
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:FGEDU
Organizational Unit Name (eg, section) []:DBA
Common Name (eg, your name or your server’s fgedu.net.cnname) []:fgedu.net.cn
Email Address []:dba@fgedu.net.cn

Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

# 使用CA签名服务器证书
$ openssl x509 -req -days 3650 -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt
Signature ok
subject=C = CN, ST = Beijing, L = Beijing, O = FGEDU, OU = DBA, CN = fgedu.net.cn, emailAddress = dba@fgedu.net.cn
Getting CA Private Key

# 设置权限
$ chmod 600 *.key
$ chown pgsql: pgsql *

# 复制证书到数据目录
$ cp server.key server.crt ca.crt /postgresql/data/
$ chown pgsql: pgsql /postgresql/data/server.key /postgresql/data/server.crt /postgresql/data/ca.crt
$ chmod 600 /postgresql/data/server.key

# 添加SSL配置
ssl = on
ssl_cert_file = ‘server.crt’
ssl_key_file = ‘server.key’
ssl_ca_file = ‘ca.crt’

# 修改pg_hba.conf强制SSL
$ vi /postgresql/data/pg_hba.conf

# 添加SSL访问规则
# TYPE DATABASE USER ADDRESS METHOD

# 本地连接
local all all peer

# SSL连接（外网）
fgedu.net.cnssl all all 0.0.0.0/0 scram-sha-256

# 拒绝非SSL连接
fgedu.net.cnnossl all all 0.0.0.0/0 reject

# 重新加载配置
$ psql -U pgsql -c “SELECT pg_reload_conf();”
pg_reload_conf
—————-
t
(1 行记录)

# 重启数据库使SSL生效
$ pg_ctl restart -D /postgresql/data
waiting for server to shut down…. done
server stopped
waiting for server to start…. done
server started

# 验证SSL配置
$ psql -U pgsql -c “SHOW ssl;”
ssl
—–
on
(1 行记录)

# 测试SSL连接
$ psql “fgedu.net.cn=192.168.1.100 port=5432 fgedu=fgapp_fgedu fgedudb=fgedudb sslmode=require sslrootcert=/postgresql/data/ca.crt”
用户 fgapp_fgedu 的口令：
psql (18.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
输入 “help” 来获取帮助信息.

fgedudb=> SELECT ssl_is_used();
ssl_is_used
————-
t
(1 行记录)

fgedudb=> \q