Not a 100% technically correct answer most likely but first look suggests they hook into the statement preprocessing pipeline. In the example, they’re hooking into the ALTER ROLE statement and deny execution if the role is a protected one. I don’t think this can be done with triggers without creating triggers on system tables and knowing the exact places where postgres would try to make changes. This seems to be an ott solution and probably a pretty bulletproof one.

Very timely article, I wonder if this works with yugabyte...