[HN Gopher] Protecting reserved roles with PostgreSQL Hooks
___________________________________________________________________
Protecting reserved roles with PostgreSQL Hooks
Author : steve-chavez
Score : 79 points
Date : 2021-07-02 15:31 UTC (7 hours ago)
(HTM) web link (supabase.io)
(TXT) w3m dump (supabase.io)
| jzelinskie wrote:
| 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.
| kureikain wrote:
| 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) 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=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 wrote:
| > 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
|
| Note - not affiliated, just have used them for a while and
| they've been good. :)
| jjeaff wrote:
| 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.
| FractalHQ wrote:
| Supabase gives you free Postgres instances with full access
| iirc
| paulryanrogers wrote:
| 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.
| quicksnap wrote:
| 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!
| blondin wrote:
| how are these hooks different from triggers?
| steve-chavez wrote:
| 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_ , something that event triggers can't
| because a superuser can drop them.
|
| [1]: https://www.postgresql.org/docs/current/sql-
| createeventtrigg...
|
| [2]: https://www.postgresql.org/docs/current/event-trigger-
| matrix... (full list of events fired)
| rad_gruchalski wrote:
| 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...
___________________________________________________________________
(page generated 2021-07-02 23:00 UTC)