写点什么

PostgreSQL 中如何控制行级安全和列级安全

发布于: 23 小时前

彭占元

2018 年 8 月加入去哪儿网,现负责公司 PostgreSQL/GreenPlum 运维工作,对数据库日常运维和日常调优有大量优化实践经验。



1. 需求提出


最近有业务线的同学向 DBA 提出这样的需求:“我的表里有很多敏感数据,怎么给使用者(从 DBA 角度来看就是 DB User)指定查看某些特定行或某些列的权限?”


这是我们经常用到的业务场景,比如最典型全公司的短信数据和用户管理平台场景,如何限制各部门仅可查看属于本部门的某些非敏感数据?


PostgreSQL 为了符合各种场景的安全需求,它的权限控制非常完善,它在各个级别上都具有广泛的安全功能。接下来我们来看一下在 PostgreSQL 中利用行级安全和列级安全来解决上述问题。



2. 行级安全


行级安全(Row Level Security),这一特性首次出现在 PostgreSQL 9.6 中。


顾名思义,就是管理员控制普通用户对于表的查看和操作,我们可以把他理解为一个过滤器,通过指定的策略实现对表中展示数据进行筛选。


通过行级安全控制,我们可以数据避免受到其他用户的破坏,也可以确保该行的数据只有指定的用户可以查看。


2.1. 实验示例

CREATE TABLE passwd (  id                    bigserial primary key,  user_name             varchar(32) UNIQUE NOT NULL,  pwhash                varchar(32),  real_name             varchar(32) NOT NULL,  home_phone            varchar(12),  home_dir              text NOT NULL,  shell                 text NOT NULL); -- 填充表INSERT INTO passwd (user_name,pwhash,real_name,home_phone,home_dir,shell) VALUES    ('appuser','xxxx','appuser','111-222-3333','/root','/bin/dash');INSERT INTO passwd(user_name,pwhash,real_name,home_phone,home_dir,shell) VALUES    ('appuser1','xxxx','appuser1','123-456-7890','/home/appuser1','/bin/zsh');INSERT INTO passwd(user_name,pwhash,real_name,home_phone,home_dir,shell) VALUES    ('appuser2','xxxx','appuser2','098-765-4321','/home/appuser2','/bin/zsh');
复制代码

根据表 passwd 记录的信息,现在我们有这样的需求:用户只能看到包含自己信息的行,而超级用户可以查看所有信息。


首先我们创建对应用户并对用户进行授权操作。

my_testdb=# \c my_testdb appuserYou are now connected to database "my_testdb" as user "appuser". my_testdb=> select * from passwd ; id | user_name | pwhash | real_name |  home_phone  | home_dir |   shell  ----+-----------+--------+-----------+--------------+----------+-----------  1 | appuser   | xxxx   | appuser   | 111-222-3333 | /root    | /bin/dash(1 row) my_testdb=> \c my_testdb appuser1You are now connected to database "my_testdb" as user "appuser1". my_testdb=> select * from passwd ; id | user_name | pwhash | real_name |  home_phone  |    home_dir    |  shell  ----+-----------+--------+-----------+--------------+----------------+----------  2 | appuser1  | xxxx   | appuser1  | 123-456-7890 | /home/appuser1 | /bin/zsh(1 row) my_testdb=> \c my_testdb appuser2You are now connected to database "my_testdb" as user "appuser2". my_testdb=> select * from passwd ; id | user_name | pwhash | real_name |  home_phone  |    home_dir    |  shell  ----+-----------+--------+-----------+--------------+----------------+----------  3 | appuser2  | xxxx   | appuser2  | 098-765-4321 | /home/appuser2 | /bin/zsh(1 row) my_testdb=> \c my_testdb postgresYou are now connected to database "my_testdb" as user "postgres". my_testdb=# select * from passwd ; id | user_name | pwhash | real_name |  home_phone  |    home_dir    |   shell  ----+-----------+--------+-----------+--------------+----------------+-----------  1 | appuser   | xxxx   | appuser   | 111-222-3333 | /root          | /bin/dash  2 | appuser1  | xxxx   | appuser1  | 123-456-7890 | /home/appuser1 | /bin/zsh  3 | appuser2  | xxxx   | appuser2  | 098-765-4321 | /home/appuser2 | /bin/zsh(3 rows)  -- 将表的所有者指定为appuser,那他将可以看到表中全部数据my_testdb=# alter table passwd owner to appuser;ALTER TABLE my_testdb=# \c my_testdb appuserYou are now connected to database "my_testdb" as user "appuser". my_testdb=> select * from passwd ; id | user_name | pwhash | real_name |  home_phone  |    home_dir    |   shell  ----+-----------+--------+-----------+--------------+----------------+-----------  1 | appuser   | xxxx   | appuser   | 111-222-3333 | /root          | /bin/dash  2 | appuser1  | xxxx   | appuser1  | 123-456-7890 | /home/appuser1 | /bin/zsh  3 | appuser2  | xxxx   | appuser2  | 098-765-4321 | /home/appuser2 | /bin/zsh(3 rows)
复制代码

在现有的环境下,如果 appuser1 需要去访问数据表中所有的数据,那么他应该如何“绕过”行级安全策略呢?

-- BYPASSRLS属性只能超级用户才能修改my_testdb=# alter user appuser1 bypassrls;ALTER ROLE  my_testdb=# \c my_testdb appuser1You are now connected to database "my_testdb" as user "appuser1". my_testdb=> select * from passwd ; user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info |    home_dir    |   shell  -----------+--------+-----+-----+-----------+--------------+------------+----------------+----------- appuser   | xxx    |   0 |   0 | appuser   | 111-222-3333 |            | /root          | /bin/dash appuser1  | xxx    |   1 |   1 | appuser1  | 123-456-7890 |            | /home/appuser1 | /bin/zsh appuser2  | xxx    |   2 |   1 | appuser2  | 098-765-4321 |            | /home/appuser2 | /bin/zsh(3 rows)
复制代码

PostgreSQL 具有 BYPASSRLS 和 NOBYPASSRLS 权限,可以将其分配给角色。默认情况下分配 NOBYPASSRLS。表所有者和超级用户具有 BYPASSRLS 权限,拥有 BYPASSRLS 权限可以跳过行级安全策略限制。


当我们删除了这条策略会发现,appuer2 用户变得不能访问表中数据了,这时我们需要禁用行级安全策略

my_testdb=> \c my_testdb appuserYou are now connected to database "my_testdb" as user "appuser". my_testdb=> drop POLICY passwd_rls_policy ON passwd;DROP POLICY my_testdb=> \c my_testdb appuser2You are now connected to database "my_testdb" as user "appuser2". my_testdb=> select * from passwd ; id | user_name | pwhash | real_name | home_phone | home_dir | shell----+-----------+--------+-----------+------------+----------+-------(0 rows) my_testdb=> \c my_testdb appuserYou are now connected to database "my_testdb" as user "appuser". my_testdb=> alter table passwd disable row level security;ALTER TABLE my_testdb=> \c my_testdb appuser2You are now connected to database "my_testdb" as user "appuser2". my_testdb=> select * from passwd ; user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info |    home_dir    |   shell  -----------+--------+-----+-----+-----------+--------------+------------+----------------+----------- appuser   | xxx    |   0 |   0 | appuser   | 111-222-3333 |            | /root          | /bin/dash appuser1  | xxx    |   1 |   1 | appuser1  | 123-456-7890 |            | /home/appuser1 | /bin/zsh appuser2  | xxx    |   2 |   1 | appuser2  | 098-765-4321 |            | /home/appuser2 | /bin/zsh(3 rows)  -- 恢复初始实验环境my_testdb=# alter table passwd owner to postgres;ALTER TABLEmy_testdb=# revoke all ON passwd from appuser;REVOKEmy_testdb=# revoke all ON passwd from appuser1;REVOKEmy_testdb=# revoke all ON passwd from appuser2;REVOKE
复制代码

此时我们的用户又能访问所有数据了,至此行级安全策略限制解除。



3. 列级安全


列级安全是允许用户仅能查看特定的列,对不想要被其他用户查看的列进行隐藏。针对这种需求,PostgreSQL 主要提供了两种方式,创建视图和授权。


创建视图的方式就是要这个视图仅包含要显示给用户指定的列,然后对用户仅提供视图名称而不是表名称。这里不做过多的描述了。


通过授权的方式实现列级访问第一步就是先撤销用户对表访问,然后对指定的列进行单独授权。在这种情况下用户不应该对表有访问权限,否则不能访问指定列。


3.1. 实验示例


my_testdb=# revoke all ON passwd from public;REVOKE my_testdb=# grant select(user_name,home_dir) on passwd to appuser2;GRANT my_testdb=# \c - appuser2You are now connected to database "my_testdb" as user "appuser2". my_testdb=> select * from passwd ;ERROR:  permission denied for table passwd my_testdb=> select user_name,home_dir from passwd ; user_name |    home_dir   -----------+---------------- appuser   | /root appuser1  | /home/appuser1 appuser2  | /home/appuser2(3 rows)  -- 恢复实验环境my_testdb=# revoke select(user_name,home_dir) on passwd from appuser2;REVOKE
复制代码

至此通过权限控制我们实现了对列数据访问的控制。



4. 行列级安全的结合


在日常的应用中,我们在创建行级策略时并不能保证 current_user 与表中存在的用户必然匹配,这时我们可以通过修改会话级变量来实现对数据的访问。


实验中我们依旧使用上面实验的数据,但是我们只使用 appuer 用户来模拟应用连接数据库。

-- 列级安全授权,目的是只允许用户访问指定列信息my_testdb=# grant select(user_name,home_dir) on passwd to appuser;GRANT -- 行级安全授权,目的是允许用户查看指定的行my_testdb=# CREATE POLICY passwd_rls_policy ON passwd FOR ALL TO PUBLIC USING (user_name=current_setting('rls.user_name'));CREATE POLICYmy_testdb=# ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;ALTER TABLE -- 指定会话级变量实现访问不同的数据行需求my_testdb=> set rls.user_name = 'appuser';SET my_testdb=> select * from passwd ;ERROR:  permission denied for table passwd  my_testdb=> select user_name,home_dir from passwd ; user_name | home_dir-----------+---------- appuser   | /root(1 row) my_testdb=> set rls.user_name = 'appuser1';SET my_testdb=> select user_name,home_dir from passwd ; user_name |    home_dir   -----------+---------------- appuser1  | /home/appuser1(1 row)  my_testdb=> set rls.user_name = 'appuser2';SET  my_testdb=> select user_name,home_dir from passwd ; user_name |    home_dir   -----------+---------------- appuser2  | /home/appuser2(1 row)
复制代码

通过这样的方法,我们就可以实现通过指定会话级的变量完成对指定数据的访问了。



5. 小节


添加 RLS 意味着在每个查询中添加了 where 子句,必须满足对应的条件才能够通过行级安全性验证,这样也就自然而然的会影响性能。


同时,行级安全性规则还附加了一个 CHECK 子句,因此制定规则的规模越大,对性能影响也就越大。

因此,数据库行级访问的功能虽好,也需根据实际场景,适当选用。

发布于: 23 小时前阅读数: 8
用户头像

还未添加个人签名 2020.11.28 加入

还未添加个人简介

评论

发布
暂无评论
PostgreSQL 中如何控制行级安全和列级安全