Get Postgres SSL certificate
Hi, I am trying to find a way to get the generated SSL certificate for my Postgresql database. If I understood correctly, it is generated within the volume connected to the database container. Is there a way to access that file in order to get it?
I need it to enable SSL connection in Google's Data Studio (Looker Studio).
Thanks in advance.
Project: df430c8f-a68d-4b0d-844a-916d03633e59
45 Replies
Project ID:
df430c8f-a68d-4b0d-844a-916d03633e59You can find it here:
stored as
server.key and server.crt
source:
https://github.com/railwayapp-templates/postgres-ssl/blob/main/init-ssl.shThanks! How may I access that within the volume? I assume I can't just ssh on it, or can I?
that's a good question 🤔
railway doesn't support SSH so you can't do that
perhaps you could just log it to the console and get it from there
honestly I'm not entirely sure how to get to it, I think Postgres and your app runs in seperate containers
so your app won't be able to access the local files in the postgres container
maybe just slap an
echo at the end of the init-ssl.sh file and deploy postgres with that
It's possible I'm overcomplicating this, there might be an easier way, maybe we should wait for someone who knows moreThank you for your support, Fragly. 🙂
Indeed, I have the same feeling. Messing with the Postgresql dockerfile is one option, but perhaps we have a different and simpler solution for this. Can we tag someone here?
fragly is right, you can absolutely get the cert off of the volume, but it's a dance, do you wanna dance?
@Brody Sorry, I don't understand what you mean by that. Do we have a way to access those volume's files instead of outputting the cert to stdout in the Postgres's Dockerfile?
yeah of course, by dance I mean it's not a straight forward method but it does get you a way to download the files
it also involves a good amount of downtime for postgres, is that okay?
Ah okay. Yes, it’s fine. We’re preparing to migrate an application from Heroku, so it’s not a real production environment just yet. So we can have downtime, no problem. 🙂
is grabbing the cert something that you would need to do frequently?
Not really, only when the cert is rotated. Google's Looker Studio requires it for SSL connection. So, this is the only use case we have for having access to those files, for now.
what specific file types does it want?
It is asking for a x.509 certificate. I assume it is a .cert file
Just confirmed: yes, it is a .cert file
the image only generates an .crt file, is that the same?
Yes, that's the same
okay are you ready to walk through the process?
yes!
alright, can I see a screenshot of your railway project so I know where we stand?
sure, just a sec
here I am
I'm interested on that
metrics DB server certbig project, we will be taking that database offline for the duration of this walkthrough
you may also want to take a backup before we start because we will be messing around with its volume
alright! that's fine, that metrics db is being generated by the etl tasks, it's okay if we lose some data
I have another db in Heroku as a backup too
alright then we are proceeding with out a backup? neither me or railway is responsible for any data loss
yes, that's fine. terms accepted! 🙂
haha alright remove the active deployment for the database
removed ✅
unmount it's volume
done
deploy a template called filebrowser into the project, but make sure to set the use volume root variable to 1
the first one?
yes
done, deploying
once that's deployed, remove it's active deployment, and delete it's volume
done
and you set that use volume root variable to 1?
yes
should I attach that metrics-data volume to that deployment now?
yeah attach the postgres volume to filebrowser at
/dataokay, attached
should I deploy it?
yep, redeploy the removed deployment
done, it worked
thanks a lot Brody
I will mount the volume again to that db and remove this service
cool solution
the mount point on postgres is
/var/lib/postgresql/data
id be curious to know if google accepted those certsI'll try that on an instant to check if worked. WIll let you know!
it worked \o/
awsome!!