七月 12, 2024
摘要：在本教程中，您将学习如何启用列级安全性，来控制对表中各个列的访问。
目录
列级安全性简介
顾名思义，在这种安全级别下，我们希望允许用户仅能查看特定的列或列集，通过阻止对所有其他列的访问，来使所有其他列成为私有列，因此用户在查询或排序时无法查看或使用这些列。现在，让我们来看看如何实现这一点。
如何启用列级安全性
这可以通过各种方法实现。让我们来依次探索这些方法。
使用表视图
实现列级安全性的最简单方法是，创建一个视图，该视图仅包含要向用户显示的列，并向用户提供视图名称，而不是表名称。
表视图示例
我有一个 employee 表，其中包含了基本的员工详细信息，和与工资相关的信息。我想向 admin 用户提供信息，但不想向 admin 显示有关员工工资和帐号的信息。
让我们创建一个用户，和包含一些数据的表：
create user admin;
create table employee ( empno int, ename text, address text, salary int, account_number text );
insert into employee values (1, 'john', '2 down str', 20000, 'HDFC-22001' );
insert into employee values (2, 'clark', '132 south avn', 80000, 'HDFC-23029' );
insert into employee values (3, 'soojie', 'Down st 17th', 60000, 'ICICI-19022' );
select * from employee;
empno | ename | address | salary | account_number
-------+--------+---------------+--------+----------------
1 | john | 2 down str | 20000 | HDFC-22001
2 | clark | 132 south avn | 80000 | HDFC-23029
3 | soojie | Down st 17th | 60000 | ICICI-19022
(3 rows)
对 employee 表具有完全访问权限的 admin 用户，目前可以访问工资信息，因此我们在这里要做的第一件事是，撤消 admin 用户对 employee 表的访问权限，然后创建一个仅包含必需列（empno、ename 和 address）的视图，并将此视图访问权限提供给 admin 用户。
revoke SELECT on employee from admin;
create view emp_info as select empno, ename, address from employee;
grant SELECT on emp_info TO admin;
\c postgres admin
You are now connected to database "postgres" as user "admin".
select * from employee;
ERROR: permission denied for table employee
select * from emp_info;
empno | ename | address
-------+--------+---------------
1 | john | 2 down str
2 | clark | 132 south avn
3 | soojie | Down st 17th
(3 rows)
select * from emp_info where salary > 200;
ERROR: column "salary" does not exist
LINE 1: select * from emp_info where salary > 200;
正如我们所看到的，admin 可以通过emp_info视图来查找员工信息，但无法访问表中的salary和account_number列。
列级权限
保护列的另一个好方法是，仅向目标用户授予对特定列的访问权限。在上面的示例中，我们不希望管理员用户访问 employee 表的 salary 和 account_number 列。我们可以不创建视图，而是提供对除salary和account_number之外的所有列的访问。
列级权限示例
让我们看一下如何使用查询来实现。我们已经撤消了 employee 表的 SELECT 权限，因此 admin 无法访问员工信息。
\c postgres admin
You are now connected to database "postgres" as user "admin".
select * from employee;
ERROR: permission denied for table employee
现在，让我们对除 salary 和 account_number 之外的所有列授予 SELECT 权限：
\c postgres postgres
You are now connected to database "postgres" as user "postgres".
grant select (empno, ename, address) on employee to admin;
\c postgres admin
You are now connected to database "postgres" as user "admin".
select empno, ename, address, salary from employee;
ERROR: permission denied for table employee
select empno, ename, address from employee;
empno | ename | address
-------+--------+---------------
1 | john | 2 down str
2 | clark | 132 south avn
3 | soojie | Down st 17th
(3 rows)
正如我们所看到的，admin 用户有权访问 employee 表的列，但salary和account_number除外。
在这种情况下，要记住的重要一点是，用户不应该在表上具有 GRANT 访问权限。您必须撤消对表的 SELECT 访问权限，并仅提供您希望用户访问的列的列访问权限。如果用户已经对整个表具有 SELECT 访问权限，则对特定列的列访问权限将不起作用。
列级加密
保护列的另一种方法是对列数据加密，因此用户可以访问列，但无法看到实际数据。PostgreSQL 有一个用于此目的的 pgcrypto 模块。让我们借助一个基本示例，来探索下该方法。
列级加密示例
在这里，我们希望用户 admin 看到 account_number 列，但看不到该列的确切数据；同时，我们希望另一个用户 finance 能够访问实际的account_number信息。为此，我们将使用pgcrypto函数和密钥，向 employee 表插入数据。
\c postgres postgres
You are now connected to database "postgres" as user "postgres".
create user finance;
grant select (empno, ename, address,account_number) on employee to finance;
CREATE EXTENSION pgcrypto;
TRUNCATE TABLE employee;
insert into employee values (1, 'john', '2 down str', 20000, pgp_sym_encrypt('HDFC-22001','emp_sec_key'));
insert into employee values (2, 'clark', '132 south avn', 80000, pgp_sym_encrypt('HDFC-23029', 'emp_sec_key'));
insert into employee values (3, 'soojie', 'Down st 17th', 60000, pgp_sym_encrypt('ICICI-19022','emp_sec_key'));
select * from employee;
empno | ename | address | salary | account_number
-------+--------+---------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | john | 2 down str | 20000 | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302
2 | clark | 132 south avn | 80000 | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf
3 | soojie | Down st 17th | 60000 | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535
(3 rows)
revoke SELECT on employee from admin;
grant select (empno, ename, address,account_number) on employee to admin;
正如我们所看到的，从 employee 表的account_number列中查出的数据，显示的是密文。现在，如果 admin 用户想要查看数据，他可以看到数据，但是它是加密形式的。
\c postgres admin
You are now connected to database "postgres" as user "admin".
select empno, ename, address,account_number from employee;
empno | ename | address | account_number
-------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | john | 2 down str | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302
2 | clark | 132 south avn | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf
3 | soojie | Down st 17th | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535
(3 rows)
如果表的所有者希望与 finance 用户共享实际数据，则可以共享密钥，finance 就可以查看实际数据：
\c postgres finance
You are now connected to database "postgres" as user "finance".
select empno, ename, address, account_number from employee;
empno | ename | address | account_number
-------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | john | 2 down str | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302
2 | clark | 132 south avn | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf
3 | soojie | Down st 17th | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535
(3 rows)
select empno, ename, address,pgp_sym_decrypt(account_number::bytea,'emp_sec_key') from employee;
empno | ename | address | pgp_sym_decrypt
-------+--------+---------------+-----------------
1 | john | 2 down str | HDFC-22001
2 | clark | 132 south avn | HDFC-23029
3 | soojie | Down st 17th | ICICI-19022
(3 rows)
当没有密钥的用户尝试使用随机密钥来查看数据时，他们会收到错误：
\c postgres admin
You are now connected to database "postgres" as user "admin".
select empno, ename, address,pgp_sym_decrypt(account_number::bytea,'random_key') from employee;
ERROR: Wrong key or corrupt data
上面显示的方法高度基于信任。pgcrypto模块还有使用私钥和公钥执行相同工作的其他方法。