Ssl – Use Let’s Encrypt certificates with Postgresql

lets-encryptpostgresqlsslssl-certificate

I am trying to setup a remote database connection to a server running postgresql. I am already running nginx on the remote server with a LE certificate. My question is if the postgresql certificate needs to be signed by a CA like let's encrypt or if a self signed certificate would suffice. I am trying to understand what the security risks are when using a self signed cert for my database connection.

Best Answer

A self-signed cert is perfectly fine if you control both ends of the connection. You can provide the client with the CA certificate to verify against.

Public CA certificates exist so that clients who don't already have trusted communication with your server still go "OK, that looks like a legitimate certificate signed by somebody I trust".

The important thing is to use at least sslmode=verify-ca and supply the CA cert as root.crt to the client so it can check the server against it and make sure it's taking to the server it expects. verify-full mode is not necessary if you're using a self-signed cert that only one host has anyway, it's protection against MiTM attacks using legitimate but stolen certificates signed by a trusted CA.

See the manual.

Manual plugin

You can either perform a manual verification - with the manual plugin.

certbot -d bristol3.pki.enigmabridge.com --manual --preferred-challenges dns certonly

Certbot will then provide you instructions to manually update a TXT record for the domain in order to proceed with the validation.

Please deploy a DNS TXT record under the name
_acme-challenge.bristol3.pki.enigmabridge.com with the following value:

667drNmQL3vX6bu8YZlgy0wKNBlCny8yrjF1lSaUndc

Once this is deployed,
Press ENTER to continue

Once you have updated the DNS record, press Enter, certbot will continue and if the LetsEncrypt CA verifies the challenge, the certificate is issued as normally.

You may also use a command with more options to minimize interactivity and answering certbot questions. Note that the manual plugin does not yet support non-interactive mode.

certbot --text --agree-tos --email you@example.com -d bristol3.pki.enigmabridge.com --manual --preferred-challenges dns --expand --renew-by-default  --manual-public-ip-logging-ok certonly

Renewal does not work with the manual plugin as it runs in non-interactive mode. More info in the official certbot documentation.

Update: manual hooks

In the new certbot version you can use hooks, e.g., --manual-auth-hook, --manual-cleanup-hook. The hooks are external scripts executed by certbot to perform the task.

Information is passed in environment variables - e.g., domain to validate, challenge token. Vars: CERTBOT_DOMAIN, CERTBOT_VALIDATION, CERTBOT_TOKEN.

certbot certonly --manual --preferred-challenges=dns --manual-auth-hook /path/to/dns/authenticator.sh --manual-cleanup-hook /path/to/dns/cleanup.sh -d secure.example.com

You can write your own handler or use already existing ones. There are many available, e.g., for Cloudflare DNS.

More info on official certbot hooks documentation.

Automation, Renewal, Scripting

~~If you would like to automate DNS challenge validation it is not currently possible with vanilla certbot.~~ Update: some automation is possible with the certbot hooks.

We thus created a simple plugin that supports scripting with DNS automation. It's available as certbot-external-auth.

pip install certbot-external-auth

It supports the DNS, HTTP, TLS-SNI validation methods. You can either use it in handler mode or in JSON output mode.

Handler mode

In handler mode, the certbot + plugin calls external hooks (a program, shell script, Python, ...) to perform the validation and installation. In practice you write a simple handler/shell script which gets the input arguments - domain, token and makes the change in DNS. When the handler finishes, certbot proceeds with validation as usual.

This gives you extra flexibility, renewal is also possible.

Handler mode is also compatible with Dehydrated DNS hooks (former letsencrypt.sh). There are already many DNS hooks for common providers (e.g., CloudFlare, GoDaddy, AWS). In the repository there is a README with extensive examples and example handlers.

Example with Dehydrated DNS hook:

certbot \
    --text --agree-tos --email you@example.com \
    --expand --renew-by-default \
    --configurator certbot-external-auth:out \
    --certbot-external-auth:out-public-ip-logging-ok \
    -d "bristol3.pki.enigmabridge.com" \
    --preferred-challenges dns \
    --certbot-external-auth:out-handler ./dehydrated-example.sh \
    --certbot-external-auth:out-dehydrated-dns \
    run

JSON mode

Another plugin mode is JSON mode. It produces one JSON object per line. This enables a more complicated integration - e.g., when Ansible or some deployment manager is calling certbot. Communication is performed via STDOUT and STDIN. Cerbot produces JSON objects with data to perform the validation, for example:

certbot \
    --text --agree-tos --email you@example.com \
    --expand --renew-by-default \
    --configurator certbot-external-auth:out \
    --certbot-external-auth:out-public-ip-logging-ok \
    -d "bristol3.pki.enigmabridge.com" \
    --preferred-challenges dns \
    certonly 2>/dev/null

{"cmd": "perform_challenge", "type": "dns-01", "domain": "bs3.pki.enigmabridge.com", "token": "3gJ87yANDpmuuKVL2ktfQ0_qURQ3mN0IfqgbTU_AGS4", "validation": "ejEDZXYEeYHUxqBAiX4csh8GKkeVX7utK6BBOBshZ1Y", "txt_domain": "_acme-challenge.bs3.pki.enigmabridge.com", "key_auth": "3gJ87yANDpmuuKVL2ktfQ0_qURQ3mN0IfqgbTU_AGS4.tRQM98JsABZRm5-NiotcgD212RAUPPbyeDP30Ob_7-0"}

Once DNS is updated, the caller sends the new-line character to STDIN of certbot to signal it can continue with validation.

This enables automation and certificate management from the central management server. For installation you can deploy certificates over SSH.

For more info please refer to the readme and examples on certbot-external-auth GitHub.

EDIT: There is also a new blog post describing the DNS validation problem and the plugin usage.

EDIT: We currently work on Ansible 2-step validation, will be soon off.

Let’s Encrypt SSL Certificate File Not Found Error – Troubleshooting

In my case the files and permissions where not the issue. I was trying to restart the server with apachectl restart or test the config (apachectl -t or apachectl configtest). The user running the command (me) simply didn't have the proper permissions to access the certificates. I just had to prefix the commands with sudo to run them as root! No more errors, the config test returns "Syntax OK" and I can restart the server. (OK I'm a bit embarrassed it took me so long to figure that one out...)