Postgresql – How to find SSL root cert that made connection to the database in PostgreSQL

It's not really about it being a bad idea in general, more that there are very few instances where you really need to expose your database to a public interface.

Much of security is about reducing the number of attack surfaces you expose. If your application does not absolutely have to expose your database to the public then you can hide it away behind a firewall and have another level of protection from any potential exploit that might appear in the future.

I would not consider "remote administration" a good enough reason to expose postgres. As you say; you already need SSH access to your box so you could easily keep postgres behind a firewall and gain access via that without any problems.

RE: SSL connections. This would be a must unless you were happy with third-parties just reading your data while you send it over the wire. The considerations here are the same as any traffic over the public internet.

According to Books Online, the certificate used for encrypting connections must meet the following requirements:

1. The certificate must be issued for Server Authentication
2. The name (common name or CN) on the certificate must match the full-qualified domain name used by clients to connect.

If those two conditions are not met, the certificate will not show in the drop-down listing. Additionally, the service account for SQL Server must have read access to the private key of the certificate. Without that right, you will be able to select the certificate, but the service will not start.

Since this is a test system you intend to use as proof-of-concept, a self-signed certificate should be adequate. I am not sure what language you are using for your client application, but this link provides options available for connection strings in .Net. You should review at least the following two options.

• Encrypt - Tells the client application to force encryption.
• TrustServerCertificate - Tells the client application to skip validating the certificate chain. This is important with a self-signed certificate as the chain likely will not be trusted.