为什么在 PostgreSQL 中用于触发器的条件从未满足?

huangapple go评论60阅读模式
英文:

Why is the condition used for the trigger never satisfied on PostgreSQL?

问题

我试图创建一个简单的触发器来确保"from"或"to"(数据库用户名)等于current_user,然后可以删除记录。但它总是引发异常。

表模式:

CREATE TABLE policy_test (
 id serial not null,
 val varchar not null,
 "from" varchar not null,
 "to" varchar not null
)

我授予所有数据库用户对表policy_test的选择、插入、更新、删除权限。
我确保"from"和"to"列始终是数据库用户名。

我创建了一个触发器函数:

CREATE OR REPLACE FUNCTION can_delete_policy_test()
  RETURNS TRIGGER AS $func$
BEGIN
  IF NEW."from" = current_user::varchar OR 
  NEW."to" = current_user::varchar THEN
    RETURN NEW;
  ELSE 
    RAISE EXCEPTION 'You have no permission to delete this record.';
  END IF;
END;
$func$ LANGUAGE plpgsql;

我这样在'policy_test'表上使用触发器:

CREATE TRIGGER policy_test_delete_trigger
BEFORE DELETE ON policy_test
FOR EACH ROW 
EXECUTE FUNCTION can_delete_policy_test();

在调用该语句之前,我确保连接的数据库用户名在policy_test表的某些记录中。

然后,我调用以下语句来删除记录:

DELETE FROM policy_test
WHERE "from" = current_user
OR "to" = current_user;
英文:

I am trying to create simple trigger to ensure the "from" or "to" (database usernames) is equal to current_user, then can delete the record. But its always raises an exception.

Table Schema:

CREATE TABLE policy_test (
 id serial not null,
 val varchar not null,
 "from" varchar not null,
 "to" varchar not null
)

I give grant to all database users to select, insert, update, delete on table policy_test.
I ensured the "from" and "to" columns are always database username.

I Created a trigger function:

CREATE OR REPLACE FUNCTION can_delete_policy_test()
  RETURNS TRIGGER AS $func$
BEGIN
  IF NEW."from" = current_user::varchar OR 
  NEW."to" = current_user::varchar THEN
    RETURN NEW;
  ELSE 
    RAISE EXCEPTION 'You have no permission to delete this record.';
  END IF;
END;
$func$ LANGUAGE plpgsql;

I did this to use trigger on 'policy_test' table:

CREATE TRIGGER policy_test_delete_trigger
BEFORE DELETE ON policy_test
FOR EACH ROW 
EXECUTE FUNCTION can_delete_policy_test();

Before call the statement, I ensured the connected database username in some record on policy_test table.

Then i called this statement to delete records:

DELETE FROM policy_test
WHERE "from" = current_user
OR "to" = current_user;

答案1

得分: 2

问题是你正在使用 NEW。在 DELETE 触发器中,NEW 是 NULL,并且从触发器函数返回 NULL 会中止操作。你必须查看 OLD."from"OLD."to" 并且 RETURN OLD;

英文:

The problem is that you are using NEW. NEW is NULL in a DELETE trigger, and returning NULL from a trigger function aborts the operation. You have to look at OLD."from" and OLD."to" and RETURN OLD;.

答案2

得分: 0

我使用了RLS和POLICY来实现我想要的相同功能。

ALTER TABLE policy_test ENABLE ROW LEVEL SECURITY;

GRANT SELECT, DELETE ON TABLE policy_test TO "employee";

CREATE POLICY select_policy_test ON policy_test
FOR SELECT USING ("to" = current_user::varchar );

CREATE POLICY delete_policy_test ON policy_test
FOR DELETE USING (
  "from" = current_user::varchar OR
  "to" = current_user::varchar    
);
英文:

I used RLS and POLICY to archieve same thing i want.

ALTER TABLE policy_test ENABLE ROW LEVEL SECURITY;

GRANT SELECT, DELETE ON TABLE policy_test TO "employee";

CREATE POLICY select_policy_test ON policy_test
FOR SELECT USING ( "to" = current_user::varchar );

CREATE POLICY delete_policy_test ON policy_test
FOR DELETE USING (
  "from" = current_user::varchar OR
  "to" = current_user::varchar    
);

huangapple
  • 本文由 发表于 2023年6月22日 00:15:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76525284.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定