PostgreSQL中如何实现列级和行级安全性

2023/11/30 14:53
阅读数 21

作者:拉杰库马尔·拉古万希(Rajkumar Raghuwanshi)
2023年1月19日

前言

本文讨论如何添加列级和行级安全性作为表级安全性的组件,以限制用户访问某些数据。

  1. 列级安全性

  2. 行级安全性

  3. 如何将行级安全性与列授权相结合

  4. 应用程序用户与行级安全性

  5. 行级安全性能

PostgreSQL 是一个安全的数据库,在各个级别都具有广泛的安全功能。
在最顶层,可以使用基于主机的身份验证、不同的身份验证方法(LDAP、PAM)、限制侦听地址以及PostgreSQL中提供的更多安全方法来保护数据库集群免受未经授权的用户的攻击。
当授权用户获得数据库访问权限时,可以通过允许或拒绝对特定对象的访问在对象级别实现进一步的安全性。这可以使用各种基于角色的身份验证措施以及 GRANT 和 REVOKE 命令来完成。

在本文中,我们将讨论更精细级别的安全性,可以保护有权访问该表但不希望其查看特定列或特定行的用户。因此,让我们探索这些选项。

在PostgreSQL中表级安全性可以在两个级别实现。
1.列级安全性
2.行级安全性

让我们先探讨一下列级安全性。

列级安全性

什么是列级安全性?

顾名思义,在此安全级别下,我们希望允许用户仅查看特定列或一组列,通过阻止对它们的访问使所有其他列成为私有列,因此用户在选择或排序时无法查看或使用这些列。现在让我们看看如何实现这一点。

如何启用列级安全性?

这可以通过各种方法实现。让我们一一探讨它们。

方法一:使用视图
实现列级安全性的最简单方法是创建一个仅包含要向用户显示的列的视图,并向用户提供视图名称而不是表名称。

例:我有一个员工表,其中包含基本的员工详细信息和与工资相关的信息。我想向管理员用户提供信息,但不想显示有关员工工资和帐号的管理员信息。
让我们使用一些数据创建一个用户和表:

  
  
  
  1. postgres=# create user admin;

  2. CREATE ROLE

  3. postgres=# create table employee ( empno int, ename text, address text, salary int, account_number text );

  4. CREATE TABLE

  5. postgres=# insert into employee values (1, 'john', '2 down str', 20000, 'HDFC-22001' );

  6. INSERT 0 1

  7. postgres=# insert into employee values (2, 'clark', '132 south avn', 80000, 'HDFC-23029' );

  8. INSERT 0 1

  9. postgres=# insert into employee values (3, 'soojie', 'Down st 17th', 60000, 'ICICI-19022' );

  10. INSERT 0 1

  11. postgres=# select * from employee;

  12. empno | ename | address | salary | account_number

  13. -------+--------+---------------+--------+----------------

  14. 1 | john | 2 down str | 20000 | HDFC-22001

  15. 2 | clark | 132 south avn | 80000 | HDFC-23029

  16. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  17. (3 rows)

对 employee 表具有完全访问权限的管理员用户目前可以访问工资信息,因此我们在这里要做的第一件事是撤消管理员用户对 employee 表的访问权限,然后创建一个仅包含必需列(empno、ename 和 address)的视图,并将此视图访问权限提供给管理员用户。

  
  
  
  1. postgres=# revoke SELECT on employee from admin ;

  2. REVOKE

  3. postgres=# create view emp_info as select empno, ename, address from employee;

  4. CREATE VIEW

  5. postgres=# grant SELECT on emp_info TO admin;

  6. GRANT

  7. postgres=# \c postgres admin

  8. You are now connected to database "postgres" as user "admin".

  9. postgres=> select * from employee;

  10. ERROR: permission denied for table employee

  11. postgres=> select * from emp_info;

  12. empno | ename | address

  13. -------+--------+---------------

  14. 1 | john | 2 down str

  15. 2 | clark | 132 south avn

  16. 3 | soojie | Down st 17th

  17. (3 rows)

  18. postgres=> select * from emp_info where salary > 200;

  19. ERROR: column "salary" does not exist

  20. LINE 1: select * from emp_info where salary > 200;

正如我们所看到的,管理员可以通过emp_info视图查找员工信息,但无法访问表中的工资和account_number列。

方法二:列级权限
保护列的另一个好选择是仅向目标用户授予对特定列的访问权限。在上面的示例中,我们不希望管理员用户访问 employee 表的 salary 和 account_number 列。我们可以提供对除 salary 和 account_number 之外的所有列的访问,而不是创建视图。

例:让我们看一下使用查询是如何工作的。我们已经撤消了 employee 表的 SELECT 权限,因此管理员无法访问员工。

  
  
  
  1. postgres=# \c postgres admin

  2. You are now connected to database "postgres" as user "admin".

  3. postgres=> select * from employee;

  4. ERROR: permission denied for table employee

现在,让我们对除 salary 和 account_number 之外的所有列授予 SELECT 权限:

  
  
  
  1. postgres=> \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# grant select (empno, ename, address) on employee to admin;

  4. GRANT

  5. postgres=# \c postgres admin

  6. You are now connected to database "postgres" as user "admin".

  7. postgres=> select empno, ename, address, salary from employee;

  8. ERROR: permission denied for table employee

  9. postgres=> select empno, ename, address from employee;

  10. empno | ename | address

  11. -------+--------+---------------

  12. 1 | john | 2 down str

  13. 2 | clark | 132 south avn

  14. 3 | soojie | Down st 17th

  15. (3 rows)

正如我们所看到的,admin 用户可以访问 employee 表的列,但 salary 和 account_number 除外。
在这种情况下,要记住的重要一点是,用户不应对表具有 GRANT 访问权限。您必须撤消对表的 SELECT 访问权限,并仅提供您希望用户访问的列的列访问权限。如果用户已对整个表具有 SELECT 访问权限,则对特定列的列访问权限将不起作用。

方法三:列级加密
保护列的另一种方法是仅加密列数据,以便用户可以访问列,但看不到实际数据。PostgreSQL 有一个用于此目的的 pgcrypto 模块。让我们借助一个基本示例来探讨此选项。

例:在这里,我们希望用户管理员看到account_number列,但不能看到该列的确切数据;同时,我们希望另一个用户 Finance 能够访问实际的account_number信息。为此,我们将使用 pgcrypto 函数和密钥在 employee 表中插入数据。

  
  
  
  1. postgres=> \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# create user finance;

  4. CREATE ROLE

  5. postgres=# grant select (empno, ename, address,account_number) on employee to finance;

  6. GRANT

  7. postgres=# CREATE EXTENSION pgcrypto;

  8. CREATE EXTENSION

  9. postgres=# TRUNCATE TABLE employee;

  10. TRUNCATE TABLE

  11. postgres=# insert into employee values (1, 'john', '2 down str', 20000, pgp_sym_encrypt('HDFC-22001','emp_sec_key'));

  12. INSERT 0 1

  13. postgres=# insert into employee values (2, 'clark', '132 south avn', 80000, pgp_sym_encrypt('HDFC-23029', 'emp_sec_key'));

  14. INSERT 0 1

  15. postgres=# insert into employee values (3, 'soojie', 'Down st 17th', 60000, pgp_sym_encrypt('ICICI-19022','emp_sec_key'));

  16. INSERT 0 1

  17. postgres=# select * from employee;

  18. empno | ename | address | salary | account_number

  19. -------+--------+---------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------

  20. 1 | john | 2 down str | 20000 | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302

  21. 2 | clark | 132 south avn | 80000 | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf

  22. 3 | soojie | Down st 17th | 60000 | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535

  23. (3 rows)

  24. postgres=# revoke SELECT on employee from admin;

  25. REVOKE

  26. postgres=# grant select (empno, ename, address,account_number) on employee to admin;

  27. GRANT

正如我们所看到的,从 employee 表的 account_number 列中选择数据将显示加密。现在,如果管理员用户想要查看数据,则可以以加密形式查看数据。

  
  
  
  1. postgres=# \c postgres admin

  2. You are now connected to database "postgres" as user "admin".

  3. postgres=> select empno, ename, address,account_number from employee;

  4. empno | ename | address | account_number

  5. -------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------

  6. 1 | john | 2 down str | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302

  7. 2 | clark | 132 south avn | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf

  8. 3 | soojie | Down st 17th | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535

  9. (3 rows)

如果表所有者希望与财务用户共享实际数据,则可以共享密钥,并且财务可以查看实际数据:

  
  
  
  1. postgres=> \c postgres finance

  2. You are now connected to database "postgres" as user "finance".

  3. postgres=> select empno, ename, address, account_number from employee;

  4. empno | ename | address | account_number

  5. -------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------

  6. 1 | john | 2 down str | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302

  7. 2 | clark | 132 south avn | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf

  8. 3 | soojie | Down st 17th | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535

  9. (3 rows)

  10. postgres=> select empno, ename, address,pgp_sym_decrypt(account_number::bytea,'emp_sec_key') from employee;

  11. empno | ename | address | pgp_sym_decrypt

  12. -------+--------+---------------+-----------------

  13. 1 | john | 2 down str | HDFC-22001

  14. 2 | clark | 132 south avn | HDFC-23029

  15. 3 | soojie | Down st 17th | ICICI-19022

  16. (3 rows)

当没有密钥的用户尝试查看具有随机密钥的数据时,他们会收到错误:

  
  
  
  1. postgres=> \c postgres admin

  2. You are now connected to database "postgres" as user "admin".

  3. postgres=> select empno, ename, address,pgp_sym_decrypt(account_number::bytea,'random_key') from employee;

  4. ERROR: Wrong key or corrupt data

上面显示的方法高度基于信任。pgcrypto 模块还有其他方法,使用私钥和公钥来执行相同的工作。

行级安全性

什么是行级安全性?

行级安全性(简称 RLS)是 PostgreSQL 安全上下文中的一项重要功能。此功能使数据库管理员能够在表上定义策略,以便它可以控制每个用户的数据查看和操作。行级策略可以理解为一个附加筛选器;当用户尝试对表执行操作时,此筛选器将在任何查询条件或筛选之前应用,并且会根据特定策略缩减数据或拒绝访问。
可以创建特定于命令的行级安全策略,例如 SELECT 或 DML 命令(INSERT/UPDATE/DELETE),或者使用 ALL。还可以在特定角色或多个角色上创建行级安全策略。

例:正如我们上面所看到的,我们可以保护列和列数据免受其他用户(如管理员)的侵害,但我们也可以在行级别保护数据,以便只有该行包含其数据的用户才能查看它。因此,让我们删除 employee 表并使用新数据重新创建它:

  
  
  
  1. postgres=> \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# DROP TABLE employee;

  4. DROP TABLE

  5. postgres=# create table employee ( empno int, ename text, address text, salary int, account_number text );

  6. CREATE TABLE

  7. postgres=# insert into employee values (1, 'john', '2 down str', 20000, 'HDFC-22001' );

  8. INSERT 0 1

  9. postgres=# insert into employee values (2, 'clark', '132 south avn', 80000, 'HDFC-23029' );

  10. INSERT 0 1

  11. postgres=# insert into employee values (3, 'soojie', 'Down st 17th', 60000, 'ICICI-19022' );

  12. INSERT 0 1

  13. postgres=# select * from employee;

  14. empno | ename | address | salary | account_number

  15. -------+--------+---------------+--------+----------------

  16. 1 | john | 2 down str | 20000 | HDFC-22001

  17. 2 | clark | 132 south avn | 80000 | HDFC-23029

  18. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  19. (3 rows)

员工 john 只能查看包含 john 信息的行。同样,员工 clark 和 soojie 只能查看各自行中的信息,而超级用户或表所有者可以查看所有信息。现在,让我们看看如何使用行级安全策略实现此用户级安全性。

首先,根据行中的条目创建用户,并提供对它们的表访问权限:

  
  
  
  1. postgres=# create user john;

  2. CREATE ROLE

  3. postgres=# grant select on employee to john;

  4. GRANT

  5. postgres=# create user clark;

  6. CREATE ROLE

  7. postgres=# grant select on employee to clark;

  8. GRANT

  9. postgres=# create user soojie;

  10. CREATE ROLE

  11. postgres=# grant select on employee to soojie;

  12. GRANT

截至目前,每个用户都可以看到所有数据:

  
  
  
  1. postgres=# \c postgres john

  2. You are now connected to database "postgres" as user "john".

  3. postgres=> select * from employee;

  4. empno | ename | address | salary | account_number

  5. -------+--------+---------------+--------+----------------

  6. 1 | john | 2 down str | 20000 | HDFC-22001

  7. 2 | clark | 132 south avn | 80000 | HDFC-23029

  8. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  9. (3 rows)

  10. postgres=> \c postgres clark

  11. You are now connected to database "postgres" as user "clark".

  12. postgres=> select * from employee;

  13. empno | ename | address | salary | account_number

  14. -------+--------+---------------+--------+----------------

  15. 1 | john | 2 down str | 20000 | HDFC-22001

  16. 2 | clark | 132 south avn | 80000 | HDFC-23029

  17. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  18. (3 rows)

  19. postgres=> \c postgres soojie

  20. You are now connected to database "postgres" as user "soojie".

  21. postgres=> select * from employee;

  22. empno | ename | address | salary | account_number

  23. -------+--------+---------------+--------+----------------

  24. 1 | john | 2 down str | 20000 | HDFC-22001

  25. 2 | clark | 132 south avn | 80000 | HDFC-23029

  26. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  27. (3 rows)

创建行级安全策略

现在,让我们创建一个策略:
postgres=> \c postgres edb
You are now connected to database “postgres” as user “edb”.

postgres=# CREATE POLICY emp_rls_policy ON employee FOR ALL TO PUBLIC USING (ename=current_user);
CREATE POLICY

让我们了解上面使用的语法:
1.我们首先连接到超级用户 edb,在本例中,他也是表 employee 的所有者,然后创建了策略。
2.策略的名称 emp_rls_policy 是用户定义的名称。
3.然后,employee 是表的名称。
4.这里的 ALL 代表所有命令,或者,我们可以指定 select/insert/update/delete——我们想要限制的任何操作。
5.此处的 PUBLIC 表示所有角色。或者,我们可以提供策略将应用的特定角色名称。
6.使用 (ename=current_user):这部分称为表达式。它是返回布尔值的筛选条件。正如我们所知,每个角色都在 ename 列的表中,因此我们将 ename 与当前连接到数据库的用户进行了比较。

现在,让我们尝试使用用户 john 访问数据:

  
  
  
  1. postgres=# \c postgres john

  2. You are now connected to database "postgres" as user "john".

  3. postgres=> select * from employee;

  4. empno | ename | address | salary | account_number

  5. -------+--------+---------------+--------+----------------

  6. 1 | john | 2 down str | 20000 | HDFC-22001

  7. 2 | clark | 132 south avn | 80000 | HDFC-23029

  8. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  9. (3 rows)

正如我们所看到的,john 仍然能够查看所有行,因为仅创建策略是不够的;我们必须明确启用它。让我们看看如何启用或禁用策略

如何启用行级安全性?

  
  
  
  1. postgres=> \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

  4. ALTER TABLE

为了启用策略,我们以超级用户身份进行了连接。禁用或强制启用策略的语法类似:

  
  
  
  1. ALTER TABLE ... DISABLE ROW LEVEL SECURITY;

  2. ALTER TABLE .. FORCE ROW LEVEL SECURITY;

  3. ALTER TABLE .. NO FORCE ROW LEVEL SECURITY;

现在,让我们看看每个用户可以从 employee 表中查看哪些内容:

  
  
  
  1. postgres=# \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# select current_user;

  4. current_user

  5. --------------

  6. edb

  7. (1 row)

  8. postgres=# select * from employee;

  9. empno | ename | address | salary | account_number

  10. -------+--------+---------------+--------+----------------

  11. 1 | john | 2 down str | 20000 | HDFC-22001

  12. 2 | clark | 132 south avn | 80000 | HDFC-23029

  13. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  14. (3 rows)

  15. postgres=# \c postgres john

  16. You are now connected to database "postgres" as user "john".

  17. postgres=> select current_user;

  18. current_user

  19. --------------

  20. john

  21. (1 row)

  22. postgres=> select * from employee;

  23. empno | ename | address | salary | account_number

  24. -------+-------+------------+--------+----------------

  25. 1 | john | 2 down str | 20000 | HDFC-22001

  26. (1 row)

  27. postgres=> \c postgres clark

  28. You are now connected to database "postgres" as user "clark".

  29. postgres=> select current_user;

  30. current_user

  31. --------------

  32. clark

  33. (1 row)

  34. postgres=> select * from employee;

  35. empno | ename | address | salary | account_number

  36. -------+-------+---------------+--------+----------------

  37. 2 | clark | 132 south avn | 80000 | HDFC-23029

  38. (1 row)

  39. postgres=> \c postgres soojie

  40. You are now connected to database "postgres" as user "soojie".

  41. postgres=> select current_user;

  42. current_user

  43. --------------

  44. soojie

  45. (1 row)

  46. postgres=> select * from employee;

  47. empno | ename | address | salary | account_number

  48. -------+--------+--------------+--------+----------------

  49. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  50. (1 row)

正如我们所看到的,current_user只能访问他或她自己的行。
如果您希望其中一个用户能够访问所有数据(例如,假设 soojie 在 HR 部门,需要访问所有其他员工数据),让我们看看如何实现这一点。

绕过行级安全性

PostgreSQL 具有 BYPASSRLS 和 NOBYPASSRLS 权限,可以分配给角色;默认情况下分配 NOBYPASSRLS。表所有者和超级用户具有 BYPASSRLS 权限,因此他们可以跳过行级安全策略。
让我们为 soojie 分配相同的权限。

  
  
  
  1. postgres=> \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# alter user soojie bypassrls;

  4. ALTER ROLE

  5. postgres=# \c postgres soojie

  6. You are now connected to database "postgres" as user "soojie".

  7. postgres=> select * from employee;

  8. empno | ename | address | salary | account_number

  9. -------+--------+---------------+--------+----------------

  10. 1 | john | 2 down str | 20000 | HDFC-22001

  11. 2 | clark | 132 south avn | 80000 | HDFC-23029

  12. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  13. (3 rows)

删除策略

让我们来看看如何删除策略。

  
  
  
  1. postgres=> \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# DROP POLICY emp_rls_policy ON employee;

  4. DROP POLICY

语法很简单:只需提供策略名称和表名称即可从该表中删除策略。
现在,让我们尝试访问数据:

  
  
  
  1. postgres=# \c postgres john

  2. You are now connected to database "postgres" as user "john".

  3. postgres=> select current_user;

  4. current_user

  5. --------------

  6. john

  7. (1 row)

  8. postgres=> select * from employee;

  9. empno | ename | address | salary | account_number

  10. -------+-------+---------+--------+----------------

  11. (0 rows)

正如我们所看到的,尽管我们已经删除了该策略,但用户 john 仍然无法查看任何数据。这是因为在 employee 表上仍启用行级安全策略。

如果默认情况下启用了行级安全性,则 PostgreSQL 将使用 default-deny 策略。现在让我们禁用它并尝试访问数据:

  
  
  
  1. postgres=> \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# ALTER TABLE employee DISABLE ROW LEVEL SECURITY;

  4. ALTER TABLE

  5. postgres=# \c postgres john

  6. You are now connected to database "postgres" as user "john".

  7. postgres=> select * from employee;

  8. empno | ename | address | salary | account_number

  9. -------+--------+---------------+--------+----------------

  10. 1 | john | 2 down str | 20000 | HDFC-22001

  11. 2 | clark | 132 south avn | 80000 | HDFC-23029

  12. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  13. (3 rows)

现在 john 可以再次看到所有数据。
如何将行级安全性与列授权相结合
在某些情况下,可能需要在同一表上同时实现行级和列级安全性。
例如,在上表中,所有员工只能查看自己的信息,但假设我们不想向员工显示财务信息。我们也可以在员工级别应用列级权限。
现在,john 可以看到所有信息,因为该策略已被删除,并且行级安全性已禁用。

  
  
  
  1. postgres=> \c postgres john

  2. You are now connected to database "postgres" as user "john".

  3. postgres=> select * from employee;

  4. empno | ename | address | salary | account_number

  5. -------+--------+---------------+--------+----------------

  6. 1 | john | 2 down str | 20000 | HDFC-22001

  7. 2 | clark | 132 south avn | 80000 | HDFC-23029

  8. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  9. (3 rows)

让我们创建一个策略并启用行级安全性。现在,john 只能查看他的信息:

  
  
  
  1. postgres=> \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# CREATE POLICY emp_rls_policy ON employee FOR all TO public USING (ename=current_user);

  4. CREATE POLICY

  5. postgres=# ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

  6. ALTER TABLE

  7. postgres=# \c postgres john

  8. You are now connected to database "postgres" as user "john".

  9. postgres=> select * from employee;

  10. empno | ename | address | salary | account_number

  11. -------+-------+------------+--------+----------------

  12. 1 | john | 2 down str | 20000 | HDFC-22001

  13. 1 row

接下来,让我们从 john 中删除对 employee 表的访问权限,并授予对除 salary 和 account_number 列之外的所有列的访问权限。现在,约翰可以查看除财务信息之外的所有详细信息。

  
  
  
  1. postgres=> \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# revoke SELECT on employee from john;

  4. REVOKE

  5. postgres=# grant select (empno, ename, address) on employee to john;

  6. GRANT

  7. postgres=# \c postgres john

  8. You are now connected to database "postgres" as user "john".

  9. postgres=> select * from employee;

  10. ERROR: permission denied for table employee

  11. postgres=> select empno, ename, address from employee;

  12. empno | ename | address

  13. -------+-------+------------

  14. 1 | john | 2 down str

  15. (1 row)

应用程序用户与行级安全性

在为用户创建策略时,我们使用了current_user并将其与表中的用户条目进行匹配。但在某些情况下,有许多用户(如 Web 应用程序),并且为每个应用程序用户创建显式角色是不可行的。在这些情况下,我们的目标保持不变:用户应该只能查看自己的数据,而不能查看其他人的数据。让我们通过一个基本示例来了解如何实现这一点。

例:让我们在 employee 表中添加更多数据:

  
  
  
  1. postgres=> \c postgres edb

  2. You are now connected to database "postgres" as user "edb".

  3. postgres=# insert into employee values (4, 'smith', 'ash dwn str', 85000, 'HDFC-22121' );

  4. INSERT 0 1

  5. postgres=# insert into employee values (5, 'mark', 'lake river south', 61000, 'ICICI-11119' );

  6. INSERT 0 1

  7. postgres=#

  8. postgres=# select * from employee;

  9. empno | ename | address | salary | account_number

  10. -------+--------+------------------+--------+----------------

  11. 1 | john | 2 down str | 20000 | HDFC-22001

  12. 2 | clark | 132 south avn | 80000 | HDFC-23029

  13. 3 | soojie | Down st 17th | 60000 | ICICI-19022

  14. 4 | smith | ash dwn str | 85000 | HDFC-22121

  15. 5 | mark | lake river south | 61000 | ICICI-11119

  16. (5 rows)

我们已经创建了三个用户 - john、clark 和 soojie,我们不希望为每个新条目创建用户。因此,我们可以将策略更改为使用会话变量,而不是使用 current_user。每次新用户尝试查看数据时,都可以初始化会话变量。
因此,首先让我们向 PUBLIC 授予 select 访问权限,删除旧策略,并使用会话变量创建一个新策略。

  
  
  
  1. postgres=# grant SELECT on employee to PUBLIC;

  2. GRANT

  3. postgres=# DROP POLICY emp_rls_policy ON employee;

  4. DROP POLICY

  5. postgres=# CREATE POLICY emp_rls_policy ON employee FOR all TO public USING (ename=current_setting('rls.ename'));

  6. CREATE POLICY

  7. postgres=# ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

  8. ALTER TABLE

  9. postgres=#

  10. postgres=# \c postgres john

  11. You are now connected to database "postgres" as user "john".

  12. postgres=> set rls.ename = 'smith';

  13. SET

  14. postgres=> select * from employee;

  15. empno | ename | address | salary | account_number

  16. -------+-------+-------------+--------+----------------

  17. 4 | smith | ash dwn str | 85000 | HDFC-22121

  18. (1 row)

  19. postgres=> set rls.ename = 'wrong';

  20. SET

  21. postgres=> select * from employee;

  22. empno | ename | address | salary | account_number

  23. -------+-------+---------+--------+----------------

  24. (0 rows)

正如我们所看到的,smith 是数据库中的一个角色,但通过使用会话变量,smith 只能访问自己的数据。

行级安全性能

如果您在所有示例中都观察到,添加 RLS 只是意味着在每个查询中添加一个 WHERE 子句。每一行都必须满足此 WHERE 子句才能通过行级安全性。当然,这种额外的检查可能会对性能造成一些影响。
行级安全性有一个额外的 CHECK 子句,该子句添加了另一个条件,因此请记住,策略越大,可能面临的性能影响就越大。就像优化任何简单的 SQL 查询一样,可以通过仔细设计这些 CHECK 表达式来优化 RLS。

引用:
https://www.postgresql.org/docs/current/pgcrypto.html
https://www.postgresql.org/docs/current/ddl-rowsecurity.html
https://www.postgresql.org/docs/current/sql-createpolicy.html


本文分享自微信公众号 - 开源软件联盟PostgreSQL分会(kaiyuanlianmeng)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部