PostgreSQL中如何正确划分元素的访问权限？

首先咱们得先理清核心问题：你当前的权限表设计没有覆盖公开、私有、好友这三类权限的场景，而且查询用了严格的INNER JOIN，过滤掉了大部分本该被用户看到的内容，所以才只返回一条结果。

第一步：优化权限表结构 #

首先修正拼写（t_access_gropes应该是t_access_groups），然后补充必要的字段和表来支撑三类权限：

• 给权限组加access_type字段，明确是公开、私有还是好友组
• 新增t_friends表存储用户间的好友关系，因为好友权限需要关联两个用户

-- 保留原用户和物品表结构
CREATE TABLE t_users ( 
    user_id varchar PRIMARY KEY, 
    user_email varchar 
); 

CREATE TABLE t_items ( 
    item_id varchar PRIMARY KEY, 
    owner_id varchar not null references t_users(user_id), 
    title varchar 
);

-- 修正权限组表，增加权限类型字段
CREATE TABLE t_access_groups ( 
    access_group_id varchar PRIMARY KEY, 
    user_id varchar not null references t_users(user_id),
    access_type varchar(20) not null check (access_type in ('public', 'private', 'friend'))
);

-- 新增好友关系表，记录用户间的好友关联
CREATE TABLE t_friends (
    friend_id varchar PRIMARY KEY,
    user_id varchar not null references t_users(user_id),
    friend_user_id varchar not null references t_users(user_id),
    unique(user_id, friend_user_id) -- 避免重复创建好友关系
);

-- 保留权限关联表，用于绑定物品和对应的权限组
CREATE TABLE t_access_sets ( 
    access_set_id varchar PRIMARY KEY, 
    item_id varchar not null references t_items(item_id), 
    access_group_id varchar not null references t_access_groups(access_group_id) 
);

第二步：插入符合权限逻辑的示例数据 #

咱们给每个用户创建三类权限组，添加好友关系，再给物品分配对应的权限：

-- 插入原用户数据
INSERT INTO t_users VALUES ('us123', 'us123@email.com');
INSERT INTO t_users VALUES ('us456', 'us456@email.com');
INSERT INTO t_users VALUES ('us789', 'us789@email.com');

-- 插入原物品数据
INSERT INTO t_items VALUES ('it123', 'us123', 'title1');
INSERT INTO t_items VALUES ('it456', 'us456', 'title2');
INSERT INTO t_items VALUES ('it678', 'us789', 'title3');
INSERT INTO t_items VALUES ('it323', 'us123', 'title4');
INSERT INTO t_items VALUES ('it764', 'us456', 'title5');
INSERT INTO t_items VALUES ('it826', 'us789', 'title6');
INSERT INTO t_items VALUES ('it568', 'us123', 'title7');
INSERT INTO t_items VALUES ('it038', 'us456', 'title8');
INSERT INTO t_items VALUES ('it728', 'us789', 'title9');

-- 为每个用户创建三类权限组
INSERT INTO t_access_groups VALUES ('ag123_private', 'us123', 'private');
INSERT INTO t_access_groups VALUES ('ag123_public', 'us123', 'public');
INSERT INTO t_access_groups VALUES ('ag123_friend', 'us123', 'friend');

INSERT INTO t_access_groups VALUES ('ag456_private', 'us456', 'private');
INSERT INTO t_access_groups VALUES ('ag456_public', 'us456', 'public');
INSERT INTO t_access_groups VALUES ('ag456_friend', 'us456', 'friend');

INSERT INTO t_access_groups VALUES ('ag789_private', 'us789', 'private');
INSERT INTO t_access_groups VALUES ('ag789_public', 'us789', 'public');
INSERT INTO t_access_groups VALUES ('ag789_friend', 'us789', 'friend');

-- 添加好友关系：us123和us456互为好友
INSERT INTO t_friends VALUES ('fr1', 'us123', 'us456');
INSERT INTO t_friends VALUES ('fr2', 'us456', 'us123');

-- 给物品分配权限
INSERT INTO t_access_sets VALUES ('as1', 'it123', 'ag123_private'); -- us123的私有物品
INSERT INTO t_access_sets VALUES ('as2', 'it456', 'ag456_friend'); -- us456共享给好友的物品
INSERT INTO t_access_sets VALUES ('as3', 'it678', 'ag789_public'); -- us789的公开物品
INSERT INTO t_access_sets VALUES ('as4', 'it323', 'ag123_public'); -- us123的公开物品

第三步：编写正确的查询语句 #

现在查询us123能看到的所有物品，需要覆盖三种场景：自己的私有物品、所有公开物品、好友共享给自己的物品，用UNION整合三类结果：

-- 场景1：用户自己的私有物品
SELECT i.*, u.user_email, g.access_type
FROM t_items i
JOIN t_users u ON i.owner_id = u.user_id
JOIN t_access_sets s ON i.item_id = s.item_id
JOIN t_access_groups g ON s.access_group_id = g.access_group_id
WHERE g.user_id = 'us123' AND g.access_type = 'private'

UNION

-- 场景2：所有公开物品
SELECT i.*, u.user_email, g.access_type
FROM t_items i
JOIN t_users u ON i.owner_id = u.user_id
JOIN t_access_sets s ON i.item_id = s.item_id
JOIN t_access_groups g ON s.access_group_id = g.access_group_id
WHERE g.access_type = 'public'

UNION

-- 场景3：好友共享给当前用户的物品
SELECT i.*, u.user_email, g.access_type
FROM t_items i
JOIN t_users u ON i.owner_id = u.user_id
JOIN t_access_sets s ON i.item_id = s.item_id
JOIN t_access_groups g ON s.access_group_id = g.access_group_id
JOIN t_friends f ON g.user_id = f.user_id
WHERE f.friend_user_id = 'us123' AND g.access_type = 'friend';

原查询只返回一条结果的原因 #

你的原查询用了四层INNER JOIN，要求物品必须同时满足：属于某个用户、该用户有对应的权限组、物品关联到该权限组。而你只给it123和it456关联了ag123组，但ag123属于us123，只有it123满足所有JOIN条件，所以只返回一条结果。

内容的提问来源于stack exchange，提问作者Sergey Kozlov