4 ms·

Excellent post. The post doesn't detail a problem with Postgres per se, rather the author uses plain language to explain Postgres' TLS connection mechanisms and

Excellent post. The post doesn't detail a problem with Postgres per se, rather the author uses plain language to explain Postgres' TLS connection mechanisms and how they are applied in AWS and Heroku RDBMS services. When quickly iterating on software, I must admit I don't always consider configuring a root cert for the db. This nuanced look at Postgres' security options gives me much to consider for real production work. tl;dr: configure a root cert for your Postgres db and use verify-full in your connection string.

thusoy 10y ago
Thanks, glad you liked it!
tajen 10y ago
> configure a root cert and use verify-full Edit: The interpretation below is incorrect, see the answers to understand why. I don't understand why the post insists on using self signed certificates and ?sslmode=verify-*. It's MitM-prone by design. Just use ?sslmode=require and a CA-issued certificate. It's even easier than with a webserver and clients can identify that you are the domain you pretend to be. Obviously Postgres did it wrong by being to lazy to deploy normal certificates for all dbs. "Verify" is only with client certs, which are difficult to issue and install, and allows the server to identify the client. Am I correct?
- thusoy 10y ago
  Yes, self-signed certificates without a CA is MitM-prone, but that's the only thing Heroku enables. You're only given a username and password and have to do with whatever configuration they've done, thus the problems.
- agwa 10y ago
  > Just use ?sslmode=require and a CA-issued certificate. This is not secure. See Table 31-1 here: https://www.postgresql.org/docs/9.5/static/libpq-ssl.html#LIBPQ-SSL-SSLMODE-STATEMENTS https://www.postgresql.org/docs/9.5/static/libpq-ssl.html#LI... As you can see, "require" provides no MitM protection. The only option among the six options that provides both eavesdropping and MitM protection under all scenarios is "verify-full". Your misunderstanding is reasonable, and shows what can happen when software is insecure by default and has too many different security-sensitive options. I'll bet that very few Postgres users fully understand the nuances between the various sslmode options.
  - merb 10y ago
    he is not incorrect. if you are on a secure network you can stick with the default i.e. a aws network could be secure.
    - agwa 10y ago
      tajen advocated a CA-issued certificate along with the "require" option, which makes sense under no common scenario. If your network really is secure, you don't need TLS at all, so you can use "disable" and not bother with a cert. If your network isn't secure, then you need "verify-full" to actually have security.
      - merb 10y ago
        actually i just wanted to point that out. actually on aws you actually connect with ssl when you have an rds instance and using psql on a ec2 instance. however it will run with "prefer". which isn't really needed there.
    - tinus_hn 10y ago
      If you consider the Internet a secure network you can. Note that unless you connect to IP addresses you still need to do host name resolution which typically involves the Internet and if the Internet is considered insecure, may return answers controlled by an attacker.. I'd suggest avoiding shortcuts and using certificates.

thusoy 10y ago

Thanks, glad you liked it!

tajen 10y ago

> configure a root cert and use verify-full Edit: The interpretation below is incorrect, see the answers to understand why. I don't understand why the post insists on using self signed certificates and ?sslmode=verify-*. It's MitM-prone by design. Just use ?sslmode=require and a CA-issued certificate. It's even easier than with a webserver and clients can identify that you are the domain you pretend to be. Obviously Postgres did it wrong by being to lazy to deploy normal certificates for all dbs. "Verify" is only with client certs, which are difficult to issue and install, and allows the server to identify the client. Am I correct?

thusoy 10y ago
Yes, self-signed certificates without a CA is MitM-prone, but that's the only thing Heroku enables. You're only given a username and password and have to do with whatever configuration they've done, thus the problems.
agwa 10y ago
> Just use ?sslmode=require and a CA-issued certificate. This is not secure. See Table 31-1 here: https://www.postgresql.org/docs/9.5/static/libpq-ssl.html#LIBPQ-SSL-SSLMODE-STATEMENTS https://www.postgresql.org/docs/9.5/static/libpq-ssl.html#LI... As you can see, "require" provides no MitM protection. The only option among the six options that provides both eavesdropping and MitM protection under all scenarios is "verify-full". Your misunderstanding is reasonable, and shows what can happen when software is insecure by default and has too many different security-sensitive options. I'll bet that very few Postgres users fully understand the nuances between the various sslmode options.
- merb 10y ago
  he is not incorrect. if you are on a secure network you can stick with the default i.e. a aws network could be secure.
  - agwa 10y ago
    tajen advocated a CA-issued certificate along with the "require" option, which makes sense under no common scenario. If your network really is secure, you don't need TLS at all, so you can use "disable" and not bother with a cert. If your network isn't secure, then you need "verify-full" to actually have security.
    - merb 10y ago
      actually i just wanted to point that out. actually on aws you actually connect with ssl when you have an rds instance and using psql on a ec2 instance. however it will run with "prefer". which isn't really needed there.
  - tinus_hn 10y ago
    If you consider the Internet a secure network you can. Note that unless you connect to IP addresses you still need to do host name resolution which typically involves the Internet and if the Internet is considered insecure, may return answers controlled by an attacker.. I'd suggest avoiding shortcuts and using certificates.