列级安全与PostgreSQL 14 / PostgreSQL ?

Question

我使用PostgREST作为一个项目的API，现在实现安全功能。

有一件事是我想要的，但它既没有被行级安全性所涵盖，也没有被列级安全性所涵盖，正如我目前所读到的那样，是这个用例:

<<ul>

表strong>用户是公共的SELECT(每个人都可以读取)

用户只能编辑(更新)自己的数据(简单，行级安全)

管理员可以编辑/删除所有用户的数据(简单，基于角色的权限和行级安全)

表中的一些字段应该只对管理员和用户自己可见。具体来说，用户的电话号码不应该是公开信息，但每个人都应该能够看到和编辑自己的电话号码。

从我目前读到的所有内容来看，行级和列级安全性都没有涵盖这个问题，但我确信Postgres可以处理它。如何?

(注意:我知道如何理解当前用户是谁，哪一行属于他，这不是我的问题)

:我的用户不是数据库用户，而是用户表中的用户。我为用户、编辑、管理员等创建了DB用户角色，但我没有为每个注册服务的用户创建DB角色。我可以通过JWT和

区分它们的行级安全性。

current_setting('request.jwt.claims', true)::json->>'user_id'

这就是为什么我不想用视图这个显而易见的解决方案来解决这个问题。

Answer 1

-- Normal user
CREATE TABLE users (
user_name text not null,
user_id text primary key,
phone text,
address text,
users_field1 text,
users_field2 text,
users_field3 text,
users_field4 text,
created_by text DEFAULT CURRENT_USER,
created_at timestamptz DEFAULT now()
);

INSERT INTO users (user_name, phone, address, user_id)
VALUES ('bob', 'misc', 'misc_add','unique1'),
('alice', 'misc1', 'misc_add1','unique2'),
('alice1', 'misc1', 'misc_add1','unique3'),
('alice2', 'misc1', 'misc_add1','unique4');
CREATE ROLE common_users;
CREATE OR REPLACE FUNCTION regress_rls_schema.current_userid()
RETURNS text
LANGUAGE plpgsql
STABLE
AS $function$
BEGIN
RETURN current_setting('regress_rls_schema.current_userid');
EXCEPTION
WHEN undefined_object THEN
RETURN NULL;
END;
$function$;

--two interface. one common interface, everyone can see it.
CREATE VIEW common_view WITH ( security_barrier = TRUE
) AS
SELECT
user_name,
users_field1,
users_field2,
users_field3
FROM
users;
CREATE OR REPLACE VIEW special_view WITH ( security_barrier = TRUE
) AS
SELECT
user_name,
user_id,
phone,
users_field3,
users_field1,
users_field2
FROM
users
WHERE
user_id = current_userid();
GRANT SELECT ON common_view TO public;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO admin_user;
GRANT SELECT, DELETE, UPDATE ON special_view TO common_users;
set role  common_users;
set session  regress_rls_schema.current_userid = 'unique1';
select current_userid();
TABLE common_view; --ok
TABLE special_view; --ok
table users; --permission denied. 

-- permission denied for table users
UPDATE
users
SET
created_by = 'dummy'
RETURNING
*;
UPDATE
special_view
SET
users_field3 = 'hi there'
RETURNING
*; --ok
UPDATE
special_view
SET
phone = '1234'
RETURNING
*;--ok.
set session  regress_rls_schema.current_userid = 'unique2';
TABLE special_view;--ok
UPDATE
special_view
SET
phone = '911246',
users_field2 = 'test view',
users_field1 = ' this is a test'
RETURNING
*;
-- permission denied for table users
UPDATE
users
SET
phone = '9012890'
RETURNING
*;

Answer 2

这类问题的解决方案是只向某些人显示数据的视图。不要忘记在视图上设置security_barrier = on。