Want to use cert generated by Caddy for my postgres ssl connection

Jim_Jones · March 26, 2025, 1:52am

1. The problem I’m having:

I wish to use the cert generated by Caddy for my website also for my postgres ssl connection. Is this OK? According to postgres, the user running postgres must be the owner of the file. But Caddy is the owner, and if I change it I think Caddy will stop functioning correcting for this url? Please let me know what should I do?

2. Error messages and/or full log output:

2025-03-26 01:34:20.549 UTC [19132] FATAL:  private key file "/var/lib/caddy/.local/share/caddy/certificates/acme-v02.api.letsencrypt.org-directory/site-name/site-name.key" must be owned by the database user or root

3. Caddy version:

v2.9.1

4. How I installed and ran Caddy:

a. System environment:

Ubuntu

b. Command:

PASTE OVER THIS, BETWEEN THE ``` LINES.
Please use the preview pane to ensure it looks nice.

c. Service/unit/compose file:

PASTE OVER THIS, BETWEEN THE ``` LINES.
Please use the preview pane to ensure it looks nice.

d. My complete Caddy config:

PASTE OVER THIS, BETWEEN THE ``` LINES.
Please use the preview pane to ensure it looks nice.

Mohammed90 · March 26, 2025, 2:10am

It’s definitely okay. Are you planning on using the Let’s Encrypt certificates? If yes, I’d recommend against that. For database connectivity, I’d put my root of trust in my own CA, vhich Caddy manages. You can have Caddy generate the root and leaf certificate for the various use cases.

In regards to the file ownership, if the sole user of the certificate is PostgreSQL, are there risks in running Caddy with the same user as Postgres? I can’t think of any, especially if you’re using Caddy’s internal PKI, so the ACME challenges aren’t needed. Another solution is to rely on Caddy events system and use the exec event handler no copy the certificate elsewhere and chown it to a different user. You might need to edit the sudoer file to allow Caddy user to chmod files.

timelordx · March 26, 2025, 3:06am

One possible solution could be creating a cron job script to check the certificate in Caddy’s certificate folder and compare it with the one used by Postgres.

The script would compare their hashes, and if they differ, it would copy the new certificate from Caddy’s folder to Postgres’ folder, update the key/certificate file ownership, and reload the Postgres service.

There’s some relevant information here:

github.com/timelordx/synology-cert-deploy

synology-cert-deploy.sh

main


      
          ## Check if new and default Synology certificates differ and deploy if needed
          grep -q "$syno_cert_md5" <<< "$cert_pem_md5"
          deploy_default=$?
          [[ $force -eq 1 ]] && deploy_default=1
          if [[ $deploy_default -eq 1 ]]; then
            echo "[default] deploying certificate to ${syno_cert_default_name}"
            if [[ $dry_run -ne 1 ]]; then
              echo "$privkey_pem"   > "${syno_cert_default_dir}/privkey.pem"
              echo "$cert_pem"      > "${syno_cert_default_dir}/cert.pem"
              echo "$fullchain_pem" > "${syno_cert_default_dir}/fullchain.pem"
              echo "$chain_pem"     > "${syno_cert_default_dir}/chain.pem"
            fi
          fi

github.com/timelordx/synology-cert-deploy

synology-helper-example.sh

main


      
          ## Find the directory containing the most recent file matching your search filter and search location
          search_result="$( find "$search_location" -type f -name "$search_filter" -exec ls -t {} +  2>/dev/null | head -n 1 )"
          [[ -z "$search_result" ]] && fatal_error "empty search resut for the provided search filter and search location!"
          search_result_dir="$( dirname "$search_result" )"

Jim_Jones · March 26, 2025, 3:07am

Thanks for the response Mohammed. So you think it’s better to generate my own cert (is this what you mean by “my own CA”)? Would this present any connectivity issues compared to going with an official CA like LetsEncrypt?

In regards to the exec handler, you mean it allows to copy the cert every time it’s updated by Caddy?

TheRettom · March 26, 2025, 3:22pm

I believe @Mohammed90 means having Caddy handle it with a specific configuration, which means adjusting PKI. This is one solution for file ownership by utilizing a shared user for both PostgreSQL and Caddy.

This is the second option he specified. Yeah, it looks like it is capable of reloading a configuration for a daemon when it detects a certificate change, or in other words, you can configure it to copy the certificate somewhere and chown it to the PostgreSQL user. Doing so would likely need Caddy to have elevated privileges, so you would probably need to add the Caddy user to the sudoer file.

It looks like @timelordx’s solution is a different way of doing what the caddy-events-exec would accomplish. Either way will work.

Jim_Jones · March 27, 2025, 12:32am

Thank you all, very helpful!

Jim_Jones · March 27, 2025, 1:51am

Quick question. If I did this, I think there is a possibility it could cause downtime. I.e. there could be some lag between the new cert being issued and the cron running. Or does Caddy renew certs some time before the current one expires? In such a case a daily cron would work.

timelordx · March 27, 2025, 3:18am

Yes, I don’t remember exactly how many days in advance it renews, but it’s well ahead of time, so a daily cron job should work just fine. That’s how it’s been running on my Synology for quite some time now, renewing the wildcard certificate I use there.

Jim_Jones · March 28, 2025, 12:02am

I’m a little confused regarding the root, leaf and CA certificates. Is this something I have to manage if I follow your cron job suggestion? Say for example if I want to connect to the db from my desktop.

timelordx · March 28, 2025, 1:08am

Caddy provides everything - both the key and the full chain. If you need to separate the certificate from the chain, you can easily do so. One way to do it is shown here:

github.com/timelordx/synology-cert-deploy

synology-cert-deploy.sh

main


      
          ## Extract privkey, cert, chain and fullchain from new_key and new_fullchain
          privkey_pem="$( openssl pkey -in "$new_key" -out - 2>/dev/null )"
          cert_pem="$( openssl x509 -in "$new_fullchain" -outform PEM -out - 2>/dev/null  )"
          fullchain_pem="$( grep -v -E '^\s*$' "$new_fullchain" 2>/dev/null )"
          chain_pem="$( diff <( echo "$cert_pem" ) <( echo "$fullchain_pem" ) 2>/dev/null | grep -E '^> ' | sed 's/^> //g' )"

If you’re using Caddy to obtain a properly trusted certificate and have configured Postgres to present both the certificate and the chain correctly, your desktop clients should have no issues connecting to the database. The trusted root CAs that Caddy uses to issue third-party certificates are already included in the trusted stores of all modern OSes and browsers.

Jim_Jones · March 28, 2025, 1:38am

Thank you very much. I think I have the basic setup working now. Should I enforce client auth?

ssl_ca_file = '/path/to/ca.crt'  # Optional, for client certificate verification

I see this is optional. If it’s recommended, how should I go about doing it? I’m sorry for the basic questions.

timelordx · March 28, 2025, 2:08am

It’s up to you. Client-side certificate authentication is generally more secure than just a login and password, but unless you’re prepared to run your own Certificate Authority and issue certificates for all your clients, I’d steer clear of it. It really comes down to how much security you need. In most cases, SSL/TLS encryption combined with login and password authentication should be sufficient.

Jim_Jones · March 28, 2025, 2:54am

Thank you sir. Do you have a lightning address? I will tip you.

timelordx · March 28, 2025, 2:58am

Nah Glad I could help. Cheers!

system · April 27, 2025, 2:58am

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.