7 ms·

Difference between running Postgres for yourself and for others

188 points by pwmtr 2y ago

zhengiszen 2y ago
In Red Hat ecosystem there is an Ansible role to that end : https://github.com/linux-system-roles/postgresql https://github.com/linux-system-roles/postgresql I don't know if it will help everyone but it could be a good way to standardize and maintain an instance configuration
- wg0 2y ago
  Is there something similar for MySQL that covers backup and restore too?
  - zhengiszen 2y ago
    Not that I'm aware of. Here the project homepage with list of supported software: https://linux-system-roles.github.io/#toggleMenu https://linux-system-roles.github.io/#toggleMenu
superice 2y ago
I'm a little confused about the point-in-time restore functionality, I'm pretty sure there must be a way to not have to force those one minute WAL boundaries. DigitalOceans managed PostgreSQL for instance just allows you to specify a timestamp and restore, and when looking into the PostgreSQL docs I remember seeing an option to specify a timestamp as well.
- pwmtr 2y ago
  You can still restore to a given minute even without one minute WAL boundaries (most of the time). Consider the case where you have a very low write activity and you would be able to fill up one WAL file (16MB) in 1 hour. That WAL file won't be archived until it is full and if you lose your database for some reason, you won't have last 1 hour's data in your backups. That means you cannot to restore any minute in that one hour window. Shorter WAL boundaries reduces your exposure. If you set archive_timeout to 1 minute, then you can restore any minute in the past with the exception of the last minute (in practice, it is possible to lose last few minutes because their WAL file might not be archived yet, but still the exposure would be much less) DigitalOcean uses 5 minutes as archive_timeout, which is also a reasonable value. In our experience, we saw that most of our customers prefer less exposure and we settled on 1 minute as archive_timeout value. Roughly archive_timeout defines your RPO(recovery point objective).
tehlike 2y ago
I feel like cloudnative-pg takes away majority of pain using well known kubernetes operator pattern.
- pwmtr 2y ago
  At another thread in this page, I wrote more about this, but in summary; we also like k8s-based managed Postgres solutions. They are quite useful if you are running Postgres for yourself. In managed Postgres services offered by hyperscalers or companies like Crunchy though, it is not used very commonly.
  - JohnMakin 2y ago
    > it is not used very commonly. Is this a problem of multi-tenancy in k8s specifically or something else?
    - pwmtr 2y ago
      At k8s, isolation is at the container level, thus properly isolating (for security purposes) system calls is quite difficult. This wouldn't be a concern if you are running Postgres for yourself. Also for us, one reason was operational simplicity. You can write a control plane for managed Postgres in 20K lines of code, including unit tests. This way, if anything breaks at scale, you can quickly figure out the issue without having to dive into dependencies.
  - tehlike 2y ago
    I always assumed crunchy was using their own operator for their managed offering. Is that not the case? https://github.com/CrunchyData/postgres-operator https://github.com/CrunchyData/postgres-operator
    - pwmtr 2y ago
      They have a Ruby control plane; https://www.crunchydata.com/blog/crunchy-bridges-ruby-backend-sorbet-tapioca-and-parlour-generated-type-stubs https://www.crunchydata.com/blog/crunchy-bridges-ruby-backen...
thyrsus 2y ago
Thanks for the article; it's an important checklist. I would think you'd want at most one certificate authority per customer rather than per database. Why am I wrong?
- pwmtr 2y ago
  You are not wrong. There are benefits for sharing certificate authority per customer. We actually considered using one authority per organization. However in our case, there is no organization entity in our data model. There is projects, which is similar but not exactly same. So we were not entirely sure where should we put the boundary and decided to go with more isolated approach. It is likely that we would add a organization-like entity in the future to our data model and at that time sharing certificate authority would make more sense.
wolfhumble 2y ago
From the article: "The problem with the server certificate is that someone needs to sign it. Usually you would want that certificate to be signed by someone who is trusted globally like DigiCert. But that means you need to send an external request; and the provider at some point (usually in minutes but sometimes it can be hours) signs your certificate and returns it back to you. This time lag is unfortunately not acceptable for users. So most of the time you would sign the certificate yourself. This means you need to create a certificate authority (CA) and share it with the users so they can validate the certificate chain. Ideally, you would also create a different CA for each database." Couldn't you automate this with Let's Encrypt instead? Thanks!
- pwmtr 2y ago
  Sure you can, but Let's Encrypt, just like DigiCert, is a 3rd party provider and they don't guarantee that you would get a signed certificate in few minutes. If they have an outage, it could take hours to get a certificate and you wouldn't be able to provision any database servers during that time. In our previous gig at Microsoft, we had multiple DigiCert outages which blocked the provisionings.
  - wolfhumble 2y ago
    I personally, anecdotally, haven't had any problems with this the last years, and it doesn't seem like this is a big issue based on the information from the incident forum posts: https://community.letsencrypt.org/c/incidents/16/l/top https://community.letsencrypt.org/c/incidents/16/l/top Self signing probably causes quite a few other issues, even though you have more control of the process, doesn't it? Thanks!
    - pwmtr 2y ago
      I cannot comment on Let's Encrypt's reliability. Maybe I had just too many bad experiences from DigiCert outages and I'm bit pessimistic. However, their status page does not give much confidence https://letsencrypt.status.io/pages/history/55957a99e800baa4470002da https://letsencrypt.status.io/pages/history/55957a99e800baa4... I think if you need to generate a certificate once in a while, using Let's Encrypt or DigiCert is OK. Even if they are down, you can wait for few hours. If you need to generate a certificate every few minutes, few hours of downtime means hundreds of failed provisionings. Hence, we opted for self-signing. In terms of reliability, it is great, because we control everything. It is also quite fast; it takes few seconds to generate and sign a certificate. The biggest drawback is that you need to distribute the certificate for CA as well. Historically, this was fine, because you need to pass CA cert to PostgreSQL as a parameter anyway, so the additional friction for users that we introduced due to CA cert distribution was low. However with PG16, now there is an option sslrootcert=system, which automatically uses OS trusted CA roots certs. Now the alternative is much seamless and requires almost no action from user, which tilted the balance in favor of globally trusted CAs, but still it doesn't give me enough reason for the switch. I have few ideas around simultaneously self signing a cert and also requesting certificate from Let's Encrypt. The database can start with the self signed certificate at the beginning and we can switch to Let's Encrypt certificate when it is ready. Maybe I'd implement something like that in the future.
olsbkj 2y ago
[dead]
ramonverse 2y ago
> Security: Did you know that with one simple trick you can drop to the OS from PostgreSQL and managed service providers hate that? The trick is COPY table_name from COMMAND I certainly did not know that.
- amluto 2y ago
  If anyone actually needs the extra performance from avoiding streaming over the Postgres protocol, this could have been done with some dignity using pipes and splice or using SCM_RIGHTS. The latter technology has been around for a long time.
  - kragen 2y ago
    only on localhost
    - theamk 2y ago
      That's kinda the point - there is an argument that one should not be baking arbitrary shell command execution into database server at all. Such execution will lack critical security features - setting right user, process tracking, cleanup, etc.. If you need to execute commands on database server for admin work, use something designed for this (such as ssh) - this will keep right management and logging simple, only one source of shell command execution. If you need to execute commands periodically, use some sort of task scheduler, running as a dedicated user. To avoid 2nd connection, you may use use postgres-controllable job queues. Either way, limit to allowed commands only, so that even if postgres credentials are leaked no arbitrary commands can be executed. Inboth approaches, this would have allow high speed, localhost-specific transports instead of local shell.. if postgresql would have supported them.
      - kragen 2y ago
        actually i think what i said was wrong, because i guess the shell command generating the data to import runs on the database server, not the client, so there's no reason the database server couldn't be using pipes or file descriptor passing for this already. in fact i'm not clear why amluto thinks it isn't
pwmtr 2y ago
Hey, author for the blog post is here. If you have any questions or comments, please let me know! It's also worth calling out the first diagram shows dependencies between features for Ubicloud's managed Postgres. AWS, Azure, and GCP's managed Postgres service would have a different diagram. That's because we at Ubicloud treat write-ahead logs (WAL) as a first class citizen.
- aae42 2y ago
  surprised the article doesn't mention use of pgbouncer, do you not use a proxy in front of your pg instances?
  - pwmtr 2y ago
    There is a "Connection Pooling" box at the first diagram. Though, the article does not talk about all boxes. It would simply be very long wall of text if we had mention all components. Instead we picked some of the more interesting parts and focused on those.
- candiddevmike 2y ago
  Your blog doesn't really mention any of the turn key PostgreSQL deployment options out there. These days, especially on Kubernetes, it has never been easier to run a SaaS-equivalent PostgreSQL stack. I think you may benefit from researching the ecosystem some more.
  - metadat 2y ago
    Being critical without posting actual better solutions isn't so helpful. If you have concrete knowledge, please share it and don't be cryptic!
  - sakjur 2y ago
    The author seems to have spent the better part of a decade working professionally with Postgres. I think editorial choice, rather than ignorance, might be why they’re not mentioning more prior art.

188 points by pwmtr 2y ago

In Red Hat ecosystem there is an Ansible role to that end : https://github.com/linux-system-roles/postgresql https://github.com/linux-system-roles/postgresql I don't know if it will help everyone but it could be a good way to standardize and maintain an instance configuration

wg0 2y ago
Is there something similar for MySQL that covers backup and restore too?
- zhengiszen 2y ago
  Not that I'm aware of. Here the project homepage with list of supported software: https://linux-system-roles.github.io/#toggleMenu https://linux-system-roles.github.io/#toggleMenu

superice 2y ago

I'm a little confused about the point-in-time restore functionality, I'm pretty sure there must be a way to not have to force those one minute WAL boundaries. DigitalOceans managed PostgreSQL for instance just allows you to specify a timestamp and restore, and when looking into the PostgreSQL docs I remember seeing an option to specify a timestamp as well.

pwmtr 2y ago
You can still restore to a given minute even without one minute WAL boundaries (most of the time). Consider the case where you have a very low write activity and you would be able to fill up one WAL file (16MB) in 1 hour. That WAL file won't be archived until it is full and if you lose your database for some reason, you won't have last 1 hour's data in your backups. That means you cannot to restore any minute in that one hour window. Shorter WAL boundaries reduces your exposure. If you set archive_timeout to 1 minute, then you can restore any minute in the past with the exception of the last minute (in practice, it is possible to lose last few minutes because their WAL file might not be archived yet, but still the exposure would be much less) DigitalOcean uses 5 minutes as archive_timeout, which is also a reasonable value. In our experience, we saw that most of our customers prefer less exposure and we settled on 1 minute as archive_timeout value. Roughly archive_timeout defines your RPO(recovery point objective).

tehlike 2y ago

I feel like cloudnative-pg takes away majority of pain using well known kubernetes operator pattern.

pwmtr 2y ago
At another thread in this page, I wrote more about this, but in summary; we also like k8s-based managed Postgres solutions. They are quite useful if you are running Postgres for yourself. In managed Postgres services offered by hyperscalers or companies like Crunchy though, it is not used very commonly.
- JohnMakin 2y ago
  > it is not used very commonly. Is this a problem of multi-tenancy in k8s specifically or something else?
  - pwmtr 2y ago
    At k8s, isolation is at the container level, thus properly isolating (for security purposes) system calls is quite difficult. This wouldn't be a concern if you are running Postgres for yourself. Also for us, one reason was operational simplicity. You can write a control plane for managed Postgres in 20K lines of code, including unit tests. This way, if anything breaks at scale, you can quickly figure out the issue without having to dive into dependencies.
- tehlike 2y ago
  I always assumed crunchy was using their own operator for their managed offering. Is that not the case? https://github.com/CrunchyData/postgres-operator https://github.com/CrunchyData/postgres-operator
  - pwmtr 2y ago
    They have a Ruby control plane; https://www.crunchydata.com/blog/crunchy-bridges-ruby-backend-sorbet-tapioca-and-parlour-generated-type-stubs https://www.crunchydata.com/blog/crunchy-bridges-ruby-backen...

thyrsus 2y ago

Thanks for the article; it's an important checklist. I would think you'd want at most one certificate authority per customer rather than per database. Why am I wrong?

pwmtr 2y ago
You are not wrong. There are benefits for sharing certificate authority per customer. We actually considered using one authority per organization. However in our case, there is no organization entity in our data model. There is projects, which is similar but not exactly same. So we were not entirely sure where should we put the boundary and decided to go with more isolated approach. It is likely that we would add a organization-like entity in the future to our data model and at that time sharing certificate authority would make more sense.

wolfhumble 2y ago

From the article: "The problem with the server certificate is that someone needs to sign it. Usually you would want that certificate to be signed by someone who is trusted globally like DigiCert. But that means you need to send an external request; and the provider at some point (usually in minutes but sometimes it can be hours) signs your certificate and returns it back to you. This time lag is unfortunately not acceptable for users. So most of the time you would sign the certificate yourself. This means you need to create a certificate authority (CA) and share it with the users so they can validate the certificate chain. Ideally, you would also create a different CA for each database." Couldn't you automate this with Let's Encrypt instead? Thanks!

pwmtr 2y ago
Sure you can, but Let's Encrypt, just like DigiCert, is a 3rd party provider and they don't guarantee that you would get a signed certificate in few minutes. If they have an outage, it could take hours to get a certificate and you wouldn't be able to provision any database servers during that time. In our previous gig at Microsoft, we had multiple DigiCert outages which blocked the provisionings.
- wolfhumble 2y ago
  I personally, anecdotally, haven't had any problems with this the last years, and it doesn't seem like this is a big issue based on the information from the incident forum posts: https://community.letsencrypt.org/c/incidents/16/l/top https://community.letsencrypt.org/c/incidents/16/l/top Self signing probably causes quite a few other issues, even though you have more control of the process, doesn't it? Thanks!
  - pwmtr 2y ago
    I cannot comment on Let's Encrypt's reliability. Maybe I had just too many bad experiences from DigiCert outages and I'm bit pessimistic. However, their status page does not give much confidence https://letsencrypt.status.io/pages/history/55957a99e800baa4470002da https://letsencrypt.status.io/pages/history/55957a99e800baa4... I think if you need to generate a certificate once in a while, using Let's Encrypt or DigiCert is OK. Even if they are down, you can wait for few hours. If you need to generate a certificate every few minutes, few hours of downtime means hundreds of failed provisionings. Hence, we opted for self-signing. In terms of reliability, it is great, because we control everything. It is also quite fast; it takes few seconds to generate and sign a certificate. The biggest drawback is that you need to distribute the certificate for CA as well. Historically, this was fine, because you need to pass CA cert to PostgreSQL as a parameter anyway, so the additional friction for users that we introduced due to CA cert distribution was low. However with PG16, now there is an option sslrootcert=system, which automatically uses OS trusted CA roots certs. Now the alternative is much seamless and requires almost no action from user, which tilted the balance in favor of globally trusted CAs, but still it doesn't give me enough reason for the switch. I have few ideas around simultaneously self signing a cert and also requesting certificate from Let's Encrypt. The database can start with the self signed certificate at the beginning and we can switch to Let's Encrypt certificate when it is ready. Maybe I'd implement something like that in the future.

olsbkj 2y ago

[dead]

ramonverse 2y ago

> Security: Did you know that with one simple trick you can drop to the OS from PostgreSQL and managed service providers hate that? The trick is COPY table_name from COMMAND I certainly did not know that.

amluto 2y ago
If anyone actually needs the extra performance from avoiding streaming over the Postgres protocol, this could have been done with some dignity using pipes and splice or using SCM_RIGHTS. The latter technology has been around for a long time.
- kragen 2y ago
  only on localhost
  - theamk 2y ago
    That's kinda the point - there is an argument that one should not be baking arbitrary shell command execution into database server at all. Such execution will lack critical security features - setting right user, process tracking, cleanup, etc.. If you need to execute commands on database server for admin work, use something designed for this (such as ssh) - this will keep right management and logging simple, only one source of shell command execution. If you need to execute commands periodically, use some sort of task scheduler, running as a dedicated user. To avoid 2nd connection, you may use use postgres-controllable job queues. Either way, limit to allowed commands only, so that even if postgres credentials are leaked no arbitrary commands can be executed. Inboth approaches, this would have allow high speed, localhost-specific transports instead of local shell.. if postgresql would have supported them.
    - kragen 2y ago
      actually i think what i said was wrong, because i guess the shell command generating the data to import runs on the database server, not the client, so there's no reason the database server couldn't be using pipes or file descriptor passing for this already. in fact i'm not clear why amluto thinks it isn't

pwmtr 2y ago

Hey, author for the blog post is here. If you have any questions or comments, please let me know! It's also worth calling out the first diagram shows dependencies between features for Ubicloud's managed Postgres. AWS, Azure, and GCP's managed Postgres service would have a different diagram. That's because we at Ubicloud treat write-ahead logs (WAL) as a first class citizen.

aae42 2y ago
surprised the article doesn't mention use of pgbouncer, do you not use a proxy in front of your pg instances?
- pwmtr 2y ago
  There is a "Connection Pooling" box at the first diagram. Though, the article does not talk about all boxes. It would simply be very long wall of text if we had mention all components. Instead we picked some of the more interesting parts and focused on those.
candiddevmike 2y ago
Your blog doesn't really mention any of the turn key PostgreSQL deployment options out there. These days, especially on Kubernetes, it has never been easier to run a SaaS-equivalent PostgreSQL stack. I think you may benefit from researching the ecosystem some more.
- metadat 2y ago
  Being critical without posting actual better solutions isn't so helpful. If you have concrete knowledge, please share it and don't be cryptic!
- sakjur 2y ago
  The author seems to have spent the better part of a decade working professionally with Postgres. I think editorial choice, rather than ignorance, might be why they’re not mentioning more prior art.