英文:
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
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论