英文:
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$BEGINIF NEW."from" = current_user::varchar ORNEW."to" = current_user::varchar THENRETURN NEW;ELSERAISE EXCEPTION 'You have no permission to delete this record.';END IF;END;$func$ LANGUAGE plpgsql;
我这样在'policy_test'表上使用触发器：
CREATE TRIGGER policy_test_delete_triggerBEFORE DELETE ON policy_testFOR EACH ROWEXECUTE FUNCTION can_delete_policy_test();
在调用该语句之前，我确保连接的数据库用户名在policy_test表的某些记录中。
然后，我调用以下语句来删除记录：
DELETE FROM policy_testWHERE "from" = current_userOR "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$BEGINIF NEW."from" = current_user::varchar ORNEW."to" = current_user::varchar THENRETURN NEW;ELSERAISE 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_triggerBEFORE DELETE ON policy_testFOR EACH ROWEXECUTE 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_testWHERE "from" = current_userOR "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_testFOR SELECT USING ("to" = current_user::varchar );CREATE POLICY delete_policy_test ON policy_testFOR 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_testFOR SELECT USING ( "to" = current_user::varchar );CREATE POLICY delete_policy_test ON policy_testFOR DELETE USING ("from" = current_user::varchar OR"to" = current_user::varchar);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论