暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 【PG安全】-Postgresql 表的行级安全和列级安全

【PG安全】-Postgresql 表的行级安全和列级安全

原创 闫伟 2021-09-02
443

之前在专栏写了一篇《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方式比较好

postgresql安全
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Murkey的PostgreSQL学习之旅（共40篇）
个人PG的学习总结

评论