0

I am making columns immutable using BEFORE triggers and my current function works fine to protect columns from UPDATE, but I am having some trouble with INSERT.

Basically the problem is that my current implementation does not take into account default values for columns.

CREATE FUNCTION guard_columns()
  RETURNS TRIGGER
  AS $$
DECLARE
  _column TEXT;
  _old_value TEXT;
  _new_value TEXT;
BEGIN
  IF CURRENT_USER != 'postgres' THEN
    FOR i IN 0..TG_NARGS - 1 LOOP
      _column := TG_ARGV[i];
      
      EXECUTE FORMAT('SELECT ($1).%I::TEXT', _column)
      USING OLD INTO _old_value;
      EXECUTE FORMAT('SELECT ($1).%I::TEXT', _column)
      USING NEW INTO _new_value;

      IF TG_OP = 'UPDATE' AND _old_value IS DISTINCT FROM _new_value THEN
        RAISE invalid_parameter_value
        USING message = FORMAT('Attempt to modify immutable column: %I', _column);
      ELSIF TG_OP = 'INSERT' AND _old_value IS NOT NULL THEN
        RAISE invalid_parameter_value
        USING message = FORMAT('Attempt to set value for immutable column: %I', _column);
      END IF;
    END LOOP;
  END IF;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER _protect_column
  BEFORE INSERT OR UPDATE ON protected_table
  FOR EACH ROW
  EXECUTE FUNCTION guard_columns ('id', 'user_id', 'created_at', 'last_modified_at', 'last_modified_by');

In this case, even if no id is given for an INSERT operation, it still throws the error "Attempt to set value for immutable column: id" because it does not know that there is a default value (DEFAULT gen_random_uuid ()) for the column. And the same for the other columns.

How can I work around this problem?

I tried to query pg_attribute and a.attrelid, but without success. And maybe this is not even necessary.

7
  • Per docs plpgsql trigger: Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row).. Commented Sep 23, 2024 at 20:30
  • 1
    Just REVOKE the permission to update these columns? Commented Sep 23, 2024 at 20:48
  • Also there will be no _old_value value for an INSERT. Commented Sep 23, 2024 at 21:01
  • There is also Row level security. Commented Sep 23, 2024 at 21:08
  • @FrankHeikens I first wanted to use GRANT with column names to explicitly set what columns the user should be able to update, but one flaw with that is that since you cannot blacklist, whenever i add new columns i also have to modify the GRANT. And I guess if I first GRANT to all columns and then REVOKE for a few, I would also have to rerun the GRANT when my columns change? Commented Sep 24, 2024 at 5:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.