Add SSL support to Databases

[sharukhkhanajm]

Description

I have created a Postgres db in Coolify now I am trying to deploy my application in production mode so I am getting this error in production mode.

I am using go lang with sslmode=require

panic: failed to connect to `host=24.199.98.149 user=postgres database=postgres`: tls error (server refused TLS connection)

Minimal Reproduction (if possible, example repository)

N/A

Exception or Error

No response

Version

v4.0.0-beta.239

[aritraroy]

I am also seeing the same issue with a MongoDB database deployed using Coolify. Any help on this will be really appreciated.

[aiherrera]

I'm on v4.0.0-beta.306 still don't have SSL activated

[vikyw89]

following

[jonathanbell]

Looks like TLS is not turned on by default. Anyone has instructions on how to do this?

[ro-bit-admin]

Any update on this?

[rouuuge]

You can use that

[wottpal]

Do i need to create server.crt & server.key myself? Would be great to have SSL-mode as a flag in Coolify/Postgres itself, @andrasbacsai :)

[FrSenpai]

following this, need a feature to auto generate ssl certificates :(

[aritraroy]

Given that some many people are looking for this feature, it would be great if you can prioritize this @andrasbacsai

[maietta]

This is an open source project. To prioritize features, one should consider putting a financial reward on that feature request.

The higher the dollar amount, the better chances of this feature getting added sooner.

[rouuuge]

Do i need to create server.crt & server.key myself? Would be great to have SSL-mode as a flag in Coolify/Postgres itself, @andrasbacsai :)

guys i guess it's already working. at least for me. only create the server.crt and key by yourself and mount the directory like:

[nebiyuelias1]

Yes, I created one myself and I added the directory to be mounted. But I was getting the above error.

[rouuuge]

it might be a permission issue, i created a script for generating the keys:

#!/bin/bash

# Set the directory for the new certificates
CERT_DIR="/data/coolify/postgresql/certs"

# Ensure the directory exists
mkdir -p $CERT_DIR
cd $CERT_DIR

# Generate a new private key
openssl genrsa -out server.key 2048

# Create a configuration file
cat > server.cnf <<EOF
[req]
distinguished_name = req_distinguished_name
x509_extensions = v3_req
prompt = no
[req_distinguished_name]
CN = <your_domain>
[v3_req]
keyUsage = critical, digitalSignature, keyEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
[alt_names]
DNS.1 = <your_domain>
EOF

# Generate CSR and self-signed certificate
openssl req -new -key server.key -out server.csr -config server.cnf
openssl x509 -req -in server.csr -signkey server.key -out server.crt -days 365 -
extfile server.cnf -extensions v3_req

# Set appropriate permissions
chmod 600 server.key
chmod 644 server.crt

# Set ownership to 70:70 (postgres user and group)
chown 70:70 server.key server.crt

# Clean up
rm server.cnf server.csr

echo "New SSL certificate and key have been generated in $CERT_DIR"
echo "server.crt and server.key are ready to use"
echo "Ownership set to 70:70 and permissions set to 600 for key, 644 for cert"

just replace the <your_domain> with your url

[nebiyuelias1]

@rouuuge thanks! It worked for me but I had to change the user id and group id. Not 70 in my case.

[GraemeFulton]

thank you this worked for me – I was trying to get ssl for postgres in Supabase. Here's how i did that for anyone searching:

After running the script above (with the right user id and group id), it created the certificates on my server, but I realised the certificates have to be copied into the docker container for Supabase to use them.
To get that working, I had to add these lines into the Docker Compose:

In the services->supabase-db->command bit, add these 3:

      - '-c'
      - ssl=on
      - '-c'
      - ssl_cert_file=/etc/postgresql/certs/server.crt
      - '-c'
      - ssl_key_file=/etc/postgresql/certs/server.key
      - '-c'

and in services->supabse-db->volumes:
- '/data/coolify/postgresql/certs:/etc/postgresql/certs:ro'

All together:

services:
  supabase-db:
    image: 'supabase/postgres:15.1.1.78'
    # ... other existing configurations ...
    command:
      - postgres
      - '-c'
      - config_file=/etc/postgresql/postgresql.conf
      - '-c'
      - ssl=on
      - '-c'
      - ssl_cert_file=/etc/postgresql/certs/server.crt
      - '-c'
      - ssl_key_file=/etc/postgresql/certs/server.key
    volumes:
      - 'supabase-db-data:/var/lib/postgresql/data'
      - '/path/on/host/postgresql/certs:/etc/postgresql/certs:ro'
      # ... other existing volume mounts ...

Docker compose is found in this bit:

[krisbitney]

This works until I try to connect to the database.

Connection failed: Error: self-signed certificate
at /Users/kris/WebstormProjects/zenchain-protocol/faucet/node_modules/pg-pool/index.js:45:11
at processTicksAndRejections (node:internal/process/task_queues:95:5)
at async checkConnectionHealth (/Users/kris/WebstormProjects/zenchain-protocol/faucet/scripts/check_pg_health.ts:15:14) {
code: 'DEPTH_ZERO_SELF_SIGNED_CERT'
}

[wottpal]

Nice workaround, thanks! Though, as noted above, Coolify needs a "single-click solution" for this (and might even consider enabling it by default).

[peaklabs-dev]

• Checkbox to require SSL mode sslmode=require
• Also we should add a dropdown where the user can even select the ssl mode like "PREFERRED", "DISABLED", "ALLOW", "VERIFY-CA", "VERIFY-FULL"...
• And the SSL certificate will be generated automatically in the background
• We also need a domain setting for a public URL, to use a custom domain in the URL instead of the IP adress

[mrpajzl]

Is there some plan when this could see the light? If there's something I could help with let me know. But RN I'm impatiantly waiting 😆

[gurgunday]

I agree, this would be an awesome feature

Otherwise the databases are just not usable from the public internet as they are insecure by default

[ghost]

@peaklabs-dev is there any eta for this? its quite a blocker for me

[gurgunday]

@ComposeVisualEditorDev

#3976 (reply in thread)

[ghost]

ohh didnt see that comment, thanks!

[aritraroy]

This checklist is a good start. Wanted to know if SSL mode is being considered only for Postgres or also for MongoDB? I have been facing the same limitation with mongodb as well.

[peaklabs-dev]

It is planned for all DBs

[tb-b]

@peaklabs-dev do you have approx. ETA?

[peaklabs-dev]

No ETA but probably after v4 stable.

[rouuuge]

Just a hint for those who use a postgres service: currently you loose your volume if you map the cert directory:

See my comment here

[zachlatta]

I am happy to add a $500 bounty to this if shipped in a version deployed on app.coolify.io before the end of January. This is a crucial feature for us.

Criteria would be that SSL is enabled by default on all “default” DBs that support it in Coolify (meaning those listed when you create a new project and look at resources to add). Postgres is the most important to us.

[andrasbacsai]

Oh, this one was unnoticed by us. Let me figure out the next step.

[zaaakher]

I started this PR #4884 for this feature a couple days ago but got absolutely stuck 😅. @andrasbacsai would you kindly continue where I left off?

[peaklabs-dev]

@zachlatta can you please DM me on discord so we can discuss the next steps.

[zachlatta]

@peaklabs-dev DMed!

[peaklabs-dev]

@zachlatta Thank you. Answered on discord, I know you said email but it is only 1 message and thats it.

[sandros94]

Could SSL be also added to KV stores like redis, keydb and dragonfly?