5 ms·

Protecting reserved roles with PostgreSQL Hooks

100 points by steve-chavez 5y ago

quicksnap 5y ago
Just going to toss out some unsolicited love at Supabase. Working with it has been extremely pleasant, and the team is amazing and helpful. Keep up the good work!
jzelinskie 5y ago
This is really cool stuff and I've been considering extending Postgres or MySQL similarly, but too many people use managed database offerings that don't allow for modifications. I'd love to hear from anyone that has found a compromise in terms of portability.
- FractalHQ 5y ago
  Supabase gives you free Postgres instances with full access iirc
- kureikain 5y ago
  I use to swear on RDS being an AWS certified person. AWS taugh us managed is the best and always put warning when you want to self hosted. Then recently at my small company(an email forwarding app that process 500K email daily https://hanami.run https://hanami.run) I want to cut cost and go with dedicated server for our Postgres database. At that moment, so many cool thing appear. You suddenly give super power to tune yoru database however you like. On the other hand, RDS and multi-az deployment is just DNS failure at the end of day. You still have to handle stuff like restarting the app to make sure it got the right DNS after RDS failover. So at the end of day, pick a hosting provider with reliable uptime(look at their history status page about outage in term of network/power). Tune postgres use tool like https://www.pgconfig.org/#/?max_connections=100&pg_version=13&environment_name=WEB&total_ram=4&cpus=2&drive_type=SSD&arch=x86-64&os_type=linux https://www.pgconfig.org/#/?max_connections=100&pg_version=1... Add 2 read replicas(it takes like 10 commands to do that in modern postgres) for streaming replication. Practice failover(promote read replica to master) once per month. Then you will be good.
  - justinclift 5y ago
    > So at the end of day, pick a hosting provider with reliable uptime ... On that note, Hetzner has a pretty good reputation and pricing: https://www.hetzner.com/dedicated-rootserver/matrix-ax https://www.hetzner.com/dedicated-rootserver/matrix-ax Note - not affiliated, just have used them for a while and they've been good. :)
    - greeklish 5y ago
      We use a VPS from them for the mail server (mailcow) at our company. Pretty solid and cheap.
- jjeaff 5y ago
  Speaking for myself, I tried managed solutions at the major cloud providers. Way overpriced and not that performant. I then decided to go back to running my own database but this time in a docker container on k3s on bare metal. A few hours fiddling with the config upon launch and that's it. Rock solid, way more performant because I can tweak the config to my specific needs. In the 2 years I have been running a MySQL server on k3s, I have about 60-90 seconds of down time. Caused by 3 separate instances where the db became unresponsive and k3s rebooted it. Most likely just some tweaks i need to make with the health check timeouts. It has been more set it and forget than a managed solution and about 20% the cost.
  - nextaccountic 5y ago
    How many dedicated servers do you have? Do you manage the servers themselves or use a cloud offering like Hetzer?
    - jjeaff 5y ago
      I use a single dedicated server that I rent from a hosting provider like Hetzner. If I needed to use multiple servers it would admittedly get a bit more complicated. But only if i needed to replicate persistent data like the database.
paulryanrogers 5y ago
Always interesting to see products that push down users or roles into DB users / roles. Seems like it could be a lot more secure if grants were also carefully managed. IME though there is rarely time for such pleasantries and they often get in the way of connection pooling.
blondin 5y ago
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...

100 points by steve-chavez 5y ago

Just going to toss out some unsolicited love at Supabase. Working with it has been extremely pleasant, and the team is amazing and helpful. Keep up the good work!

jzelinskie 5y ago

This is really cool stuff and I've been considering extending Postgres or MySQL similarly, but too many people use managed database offerings that don't allow for modifications. I'd love to hear from anyone that has found a compromise in terms of portability.

FractalHQ 5y ago
Supabase gives you free Postgres instances with full access iirc
kureikain 5y ago
I use to swear on RDS being an AWS certified person. AWS taugh us managed is the best and always put warning when you want to self hosted. Then recently at my small company(an email forwarding app that process 500K email daily https://hanami.run https://hanami.run) I want to cut cost and go with dedicated server for our Postgres database. At that moment, so many cool thing appear. You suddenly give super power to tune yoru database however you like. On the other hand, RDS and multi-az deployment is just DNS failure at the end of day. You still have to handle stuff like restarting the app to make sure it got the right DNS after RDS failover. So at the end of day, pick a hosting provider with reliable uptime(look at their history status page about outage in term of network/power). Tune postgres use tool like https://www.pgconfig.org/#/?max_connections=100&pg_version=13&environment_name=WEB&total_ram=4&cpus=2&drive_type=SSD&arch=x86-64&os_type=linux https://www.pgconfig.org/#/?max_connections=100&pg_version=1... Add 2 read replicas(it takes like 10 commands to do that in modern postgres) for streaming replication. Practice failover(promote read replica to master) once per month. Then you will be good.
- justinclift 5y ago
  > So at the end of day, pick a hosting provider with reliable uptime ... On that note, Hetzner has a pretty good reputation and pricing: https://www.hetzner.com/dedicated-rootserver/matrix-ax https://www.hetzner.com/dedicated-rootserver/matrix-ax Note - not affiliated, just have used them for a while and they've been good. :)
  - greeklish 5y ago
    We use a VPS from them for the mail server (mailcow) at our company. Pretty solid and cheap.
jjeaff 5y ago
Speaking for myself, I tried managed solutions at the major cloud providers. Way overpriced and not that performant. I then decided to go back to running my own database but this time in a docker container on k3s on bare metal. A few hours fiddling with the config upon launch and that's it. Rock solid, way more performant because I can tweak the config to my specific needs. In the 2 years I have been running a MySQL server on k3s, I have about 60-90 seconds of down time. Caused by 3 separate instances where the db became unresponsive and k3s rebooted it. Most likely just some tweaks i need to make with the health check timeouts. It has been more set it and forget than a managed solution and about 20% the cost.
- nextaccountic 5y ago
  How many dedicated servers do you have? Do you manage the servers themselves or use a cloud offering like Hetzer?
  - jjeaff 5y ago
    I use a single dedicated server that I rent from a hosting provider like Hetzner. If I needed to use multiple servers it would admittedly get a bit more complicated. But only if i needed to replicate persistent data like the database.

paulryanrogers 5y ago

Always interesting to see products that push down users or roles into DB users / roles. Seems like it could be a lot more secure if grants were also carefully managed. IME though there is rarely time for such pleasantries and they often get in the way of connection pooling.

blondin 5y ago

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