之前在专栏写了一篇《Postgresql基本访问》，本篇是针对上次的文档进行一些补充；主要是讲解在pg_hba.conf中的外部文件使用和非cidr使用
1 使用用户外部文件对数据库访问
| IP | 主机名 | 说明 |
|---|---|---|
| 192.168.245.119 | pgserver12 | 客户端使用 |
| 192.168.245.143 | PGserver2 | 数据库服务使用 |
1.1 创建测试数据库
postgres@[local]:5432=#create database db1 ;
CREATE DATABASE
postgres@[local]:5432=#\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | yanwei | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/yanwei +
| | | | | yanwei=CTc/yanwei
postgres@[local]:5432=#\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
yanwei | | {}
yanwei1 | Superuser
1.2 配置外部文件访问规则
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
#host all all 192.168.0.0/16 md5
#host all all 192.168.0.0/16 ident map=yanwei
host db1 @userlist 192.168.245.0/24 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
host replication all ::1/128 trust
@userlist 就是外部文件访问方式
[postgres@PGserver2 pgdata]$ cat userlist
yanwei
yanwei1
[postgres@PGserver2 pgdata]$ pg_ctl reload
server signaled
1.3 使用主机 192.168.245.119 进行访问 db1
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db1 -U yanwei -Atq -c "select 1"
1
说明外部文件生效
这里
-Atq的意思是 A：非对称 t：只显示行 q：静默模式
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db1 -U yanwei1 -Atq -c "select 1"
1
[postgres@pgserver12 ~]$
yanwei 和yanwei 都是可以的
1.4 测试非外部表包含的用户测试
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db1 -U yanwei2 -Atq -c "select 1"
psql: error: FATAL: no pg_hba.conf entry for host "192.168.245.119", user "yanwei2", database "db1", SSL off
[postgres@pgserver12 ~]$
yanwei2 没有配置到外部文件，也不包含在hba文件，因此连接不上
1.5 测试外部文件不在$PGDATA下
# IPv4 local connections:
host all all 127.0.0.1/32 trust
#host all all 192.168.0.0/16 md5
#host all all 192.168.0.0/16 ident map=yanwei
host db1 @/home/postgres/userlist 192.168.245.0/24 trust
userlist move到/home/postgres下
重新reload
[postgres@PGserver2 pgdata]$ pg_ctl reload
server signaled
[postgres@PGserver2 pgdata]$
使用主机 192.168.245.119 进行访问 db1
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db1 -U yanwei1 -Atq -c "select 1"
1
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db1 -U yanwei -Atq -c "select 1"
1
说明可以放到外部目录下
1.6 总结
- 如果需要配置的用户比较复杂，数量多，可以利用外部文件编辑的方式进行安全配置
- 外部用户配置文件可以放置于非 $PGDATA 目录下
2 使用数据库外部文件进行配置
2.1 创建多个数据库
postgres@[local]:5432=#\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
db2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
db3 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | yanwei | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/yanwei +
| | | | | yanwei=CTc/yanwei
(7 rows)
2.2 配置数据库的外部文件
[postgres@PGserver2 pgdata]$ cat dblist
db1
db2
db3
# IPv4 local connections:
host all all 127.0.0.1/32 trust
#host all all 192.168.0.0/16 md5
#host all all 192.168.0.0/16 ident map=yanwei
host @dblist @/home/postgres/userlist 192.168.245.0/24 trust
#relpad
[postgres@PGserver2 pgdata]$ pg_ctl reload
server signaled
2.3 使用主机 192.168.245.119 进行访问 db1,db2,db3
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db1 -U yanwei -Atq -c "select 1"
1
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db1 -U yanwei -Atq -c "select 1"
1
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db2 -U yanwei -Atq -c "select 1"
1
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db3 -U yanwei -Atq -c "select 1"
1
说明可以连接
2.4 总结
- 数据库的外部文件使用方式和用户外部文件一致的
- 同时也支持放到默认目录之外
3 单纯配置pg_hba.conf
3.1 配置pg_hba.comf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
#host all all 192.168.0.0/16 md5
#host all all 192.168.0.0/16 ident map=yanwei
host db1,db2,db3 yanwei,yanwei1 192.168.245.0/24 trust
在database和user都写多个，用“，”隔开
3.2 使用主机 192.168.245.119 进行访问 db1,db2,db3
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db1 -U yanwei -Atq -c "select 1"
1
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db1 -U yanwei -Atq -c "select 1"
1
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db2 -U yanwei -Atq -c "select 1"
1
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db3 -U yanwei -Atq -c "select 1"
1
说明可以连接
4 使用非 cidr 方式进行配置基于主机的认证
4.1 创建数据库和用户
postgres@[local]:5432=#create user u1;
CREATE ROLE
postgres@[local]:5432=#create database db4;
CREATE DATABASE
4.2 配置认证文件
[postgres@PGserver2 pgdata]$ grep u1 pg_hba.conf
host db4 u1 192.168.245.0 255.255.255.0 trust
[postgres@PGserver2 pgdata]$
[postgres@PGserver2 pgdata]$ grep u1 pg_hba.conf
host db4 u1 192.168.245.0 255.255.255.0 trust
[postgres@PGserver2 pgdata]$
4.3 测试连接
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db4 -U u1 -Atq -c "select 1"
1
[postgres@pgserver12 ~]$
可以连接
4.4 ip地址和 字网掩码影响认证
[postgres@PGserver2 pgdata]$ grep u1 pg_hba.conf
host db4 u1 192.168.245.0 255.255.255.255 trust
[postgres@PGserver2 pgdata]$
[postgres@PGserver2 pgdata]$ pg_ctl reload
server signaled
这样其实只能192.168.245.0 连接
我们测试一下
[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -p 5432 -d db4 -U u1 -Atq -c "select 1"
psql: error: FATAL: no pg_hba.conf entry for host "192.168.245.119", user "u1", database "db4", SSL off
4.5 数据库中的规则表
postgres@[local]:5432=#select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+------------------+---------------+-----------------------------------------+-------------+---------+------
-
84 | local | {all} | {all} | | | trust | |
86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
89 | host | {db1,db2,db3} | {yanwei,yanwei1} | 192.168.245.0 | 255.255.255.0 | trust | |
90 | host | {db4} | {u1} | 192.168.245.0 | 255.255.255.255 | trust | |
92 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
95 | local | {replication} | {all} | | | trust | |
96 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
97 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
(8 rows)
#如果pg_hba.conf 中配置有误，那么规则表中将会列出相关错误
postgres@[local]:5432=#select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options |
error
-------------+-------+---------------+------------------+---------------+-----------------------------------------+-------------+---------+------
--------------------------------------------
84 | local | {all} | {all} | | | trust | |
86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
89 | host | {db1,db2,db3} | {yanwei,yanwei1} | 192.168.245.0 | 255.255.255.0 | trust | |
90 | | | | | | | | inval
id CIDR mask in address "192.168.245.0/128"
92 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
95 | local | {replication} | {all} | | | trust | |
96 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
97 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
(8 rows)
invalid CIDR mask in address "192.168.245.0/128" 帮你标注了错误
虽然可以使用非cidr方式进行连接，但是方便配置和不容易出错，建议还是老老实实使用cidr方式比较好
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
