4 ms·

This seems like it fits the narrative. Still, WTF? A quick google tells me that PostgreSQL supports SSL cert-based authN. The prod environment (and possibly an

This seems like it fits the narrative. Still, WTF? A quick google tells me that PostgreSQL supports SSL cert-based authN. The prod environment (and possibly an Ops host) should be the only ones with the right certs to connect to the prod DB.

dalore 8y ago
Creating an ssl connection to the postgres server might add extra connection time (Yes could be prevented by persistent connections but that's not always feasible, especially in a cloud based environment). So it's not the best solution. Postgres pg_hba.conf does support access by ip address easily enough. For access to the production databases the ip addresses should be limited to those that need access. Or if not in postgres, a firewall could limit it. So if a developer accidentally tried to wipe it, they wouldn't be allowed to connect. If they actually needed to connect, they would first have to add their ip address (or use a bastion host).
- lvh 8y ago
  The GP was presumably talking about TLS with mutual auth. There’s no excuse for not having TLS at all enabled, but mTLS is a great extra security feature. The cost of mTLS (performance wise) is negligible, and if you can’t keep a persistent database connection up (because cloud?!) but also can’t do TLS because perf then I don’t know what to tell you. The only performance issue TLS has is that it isn’t used enough.
  - foobarbazetc 8y ago
    As someone who has helped many, many companies with Postgres I can tell you that nobody runs PG over TLS in production if everything’s talking over private links. There’s zero reason to do this.
- benmmurphy 8y ago
  if you are running PG like a KV store (simple lookups/simple updates/inserts) where most of the dataset fits in memory then it quickly becomes CPU bound and changes in PG configuration/access patterns can have large effects. for example i've seen running pgbouncer on the same host drop performance by about 33%, and switching from simple protocol to extended protocol single use prepared statements drop performance by 33%.

dalore 8y ago

Creating an ssl connection to the postgres server might add extra connection time (Yes could be prevented by persistent connections but that's not always feasible, especially in a cloud based environment). So it's not the best solution. Postgres pg_hba.conf does support access by ip address easily enough. For access to the production databases the ip addresses should be limited to those that need access. Or if not in postgres, a firewall could limit it. So if a developer accidentally tried to wipe it, they wouldn't be allowed to connect. If they actually needed to connect, they would first have to add their ip address (or use a bastion host).

lvh 8y ago
The GP was presumably talking about TLS with mutual auth. There’s no excuse for not having TLS at all enabled, but mTLS is a great extra security feature. The cost of mTLS (performance wise) is negligible, and if you can’t keep a persistent database connection up (because cloud?!) but also can’t do TLS because perf then I don’t know what to tell you. The only performance issue TLS has is that it isn’t used enough.
- foobarbazetc 8y ago
  As someone who has helped many, many companies with Postgres I can tell you that nobody runs PG over TLS in production if everything’s talking over private links. There’s zero reason to do this.
benmmurphy 8y ago
if you are running PG like a KV store (simple lookups/simple updates/inserts) where most of the dataset fits in memory then it quickly becomes CPU bound and changes in PG configuration/access patterns can have large effects. for example i've seen running pgbouncer on the same host drop performance by about 33%, and switching from simple protocol to extended protocol single use prepared statements drop performance by 33%.