如何创建PostgreSQL插入策略：验证动物版本归属权限

需求说明 #

现有animals表结构及数据如下：

表在(name, version)上有唯一索引，需实现以下插入规则：

• 用户b执行insert into animals (name, version) values ('cat', 3);时插入失败
• 用户a执行上述插入语句时成功
• 任意用户执行insert into animals (name, version) values ('dog', 1);时插入成功

原有策略问题 #

原策略因name = name恒成立（子查询中未关联拟插入的新行，仅做同表字段自比较），无法生效：

create policy animal_version_owner on animals as permissive for
INSERT WITH CHECK (
    exists (select 1 from animals where name = name and owner = current_user())
);

正确实现方案 #

要关联拟插入的新行数据，需用NEW关键字引用新行字段，结合两种场景判断：

1. 插入新动物名称（表中无对应name记录）：允许所有用户插入
2. 插入已有动物名称的新版本：仅该名称的所有者可插入

对应的策略SQL如下：

CREATE POLICY animal_version_owner ON animals
AS PERMISSIVE FOR INSERT
WITH CHECK (
    -- 场景1：插入新动物，无现有记录，允许所有用户
    NOT EXISTS (SELECT 1 FROM animals WHERE name = NEW.name)
    OR
    -- 场景2：插入已有动物的新版本，仅所有者可操作
    EXISTS (SELECT 1 FROM animals WHERE name = NEW.name AND owner = current_user())
);

疑问解答 #

完全可以查询数据库现有记录并与插入值对比，核心是正确引用拟插入的新行数据：

• 在WITH CHECK表达式中，NEW关键字代表拟插入的新行，用NEW.name可获取要插入的动物名称
• 原策略的问题是未使用NEW，子查询里的name = name是同表字段自比较，永远为真，导致条件失效
• 官方文档提到的"check_expression是针对拟插入的新行内容计算"，指的是表达式可以通过NEW获取新行数据，同时完全允许查询现有表记录进行对比。

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