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

Question

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;

Answer 1

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;.