4 ms·

how are these hooks different from triggers?

steve-chavez 5y ago
You mean event triggers[1] right? For the problem mentioned in the blog post — protecting roles — it's not possible to use event triggers because PostgreSQL doesn't fire[2] a DDL event for `CREATE/ALTER/DROP ROLE`. If an event for role modification was added, then maybe the same could be accomplished with event triggers(perhaps in pure SQL). Though hooks will always be more strict, since you can enforce conditions by preloading the extension in C code, without custom SQL. Also, hooks can enforce a condition even in presence of superusers[3], something that event triggers can't because a superuser can drop them. [1]: https://www.postgresql.org/docs/current/sql-createeventtrigger.html https://www.postgresql.org/docs/current/sql-createeventtrigg... [2]: https://www.postgresql.org/docs/current/event-trigger-matrix.html https://www.postgresql.org/docs/current/event-trigger-matrix... (full list of events fired) [3]: https://paquier.xyz/postgresql-2/hooks-in-postgres-super-superuser-restrictions/ https://paquier.xyz/postgresql-2/hooks-in-postgres-super-sup... (example hook that does this)
rad_gruchalski 5y ago
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...
- steve-chavez 5y ago
  > I wonder if this works with yugabyte It should work too! Check this line here[1]. That shows that Yugabyte also has the `ProcessUtility_hook`(same one used in the article) defined. It would be a matter of installing and loading the Supautils extension. [1]: https://github.com/yugabyte/yugabyte-db/blob/master/src/postgres/src/backend/tcop/utility.c#L373-L376 https://github.com/yugabyte/yugabyte-db/blob/master/src/post...
  - rad_gruchalski 5y ago
    I’ll give it a shot over the weekend as I have a few use cases for similar functionality in Yugabyte. This is pretty exciting stuff at the right time!

You mean event triggers[1] right? For the problem mentioned in the blog post — protecting roles — it's not possible to use event triggers because PostgreSQL doesn't fire[2] a DDL event for `CREATE/ALTER/DROP ROLE`. If an event for role modification was added, then maybe the same could be accomplished with event triggers(perhaps in pure SQL). Though hooks will always be more strict, since you can enforce conditions by preloading the extension in C code, without custom SQL. Also, hooks can enforce a condition even in presence of superusers[3], something that event triggers can't because a superuser can drop them. [1]: https://www.postgresql.org/docs/current/sql-createeventtrigger.html https://www.postgresql.org/docs/current/sql-createeventtrigg... [2]: https://www.postgresql.org/docs/current/event-trigger-matrix.html https://www.postgresql.org/docs/current/event-trigger-matrix... (full list of events fired) [3]: https://paquier.xyz/postgresql-2/hooks-in-postgres-super-superuser-restrictions/ https://paquier.xyz/postgresql-2/hooks-in-postgres-super-sup... (example hook that does this)

rad_gruchalski 5y ago

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

steve-chavez 5y ago
> I wonder if this works with yugabyte It should work too! Check this line here[1]. That shows that Yugabyte also has the `ProcessUtility_hook`(same one used in the article) defined. It would be a matter of installing and loading the Supautils extension. [1]: https://github.com/yugabyte/yugabyte-db/blob/master/src/postgres/src/backend/tcop/utility.c#L373-L376 https://github.com/yugabyte/yugabyte-db/blob/master/src/post...
- rad_gruchalski 5y ago
  I’ll give it a shot over the weekend as I have a few use cases for similar functionality in Yugabyte. This is pretty exciting stuff at the right time!