PostgreSQL 教程: 列级安全性

七月 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视图来查找员工信息,但无法访问表中的salaryaccount_number列。

列级权限

保护列的另一个好方法是,仅向目标用户授予对特定列的访问权限。在上面的示例中,我们不希望管理员用户访问 employee 表的 salary 和 account_number 列。我们可以不创建视图,而是提供对除salaryaccount_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 表的列,但salaryaccount_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模块还有使用私钥和公钥执行相同工作的其他方法。