SSL Issue: PowerSync + Postgres on Railway
Project ID:
0696700c-6952-4780-8a3d-01600989ab48
I'm trying to enable Powersync (https://www.powersync.com/), and use it to connect to my postgres DB on railway. However, when trying to connect using the TCP credentials, I get the following error: server certificate not trusted: SELF_SIGNED_CERT_IN_CHAIN.
When connecting to Postgres, Powersync only has two possible modes, verify-full and verify-ca. I'm using verify-full, is there any way to get around this? I don't think verify-ca and uploading certificates is the right way to go here.57 Replies
Project ID:
0696700c-6952-4780-8a3d-01600989ab48I asked the Powersync team, and they told me that my only option is likely to download a CA cert, like you can do on superbase. is there any way to do this on Railway?
there isnt
you would have to roll your own database with your own certs
https://docs.railway.app/databases/bring-your-own-database
Hey I'm from the PowerSync team and just got everything working using the stock Railway services, didn't have to roll own database
Busy bundling it all up as a template
(also converted myself into a Railway fan in the process... Railway is really nice)
ah yes the postgres databases railway provides do come with their own self signed certs, but there isn't a way to download them like pool noodle wanted to, glad it works without having to do that!
If there is uptake from users we'll make the process of downloading the cert simpler in our dashboard, but for now users have two workarounds available to them which I'll document in the integration guide
Here's one of them:
echo | openssl s_client -showcerts -starttls postgres -connect monorail.proxy.rlwy.net:37067 -servername monorail.proxy.rlwy.net 2>/dev/null | sed -n '/BEGIN CERTIFICATE/,/END CERTIFICATE/p' | awk '/BEGIN/{i++}i==2' > railway.pem
not the prettiest nor the most robust, but works for now until we streamline it on our endwould be even nicer if railway had some kind of file browser for the volumes so users could just go there and click download on the certs
I get roasted when I use "just" like that π€£ but yes that would also work nicely π
yeah it's a bad habit π€£
but what route are you planning on taking with your template? will the template come with a postgres database, or just (I think I'm using it correctly?) the powersync service that will be setup to automatically reference an existing postgres database that would exist in a users project
PowerSync integrates with existing backends and we have demo Django and NodeJS backends showing the extensions required to wire PowerSync up to your backend, so for the template it will be Postgres + NodeJS HTTP
We're still working on making the PowerSync service itself self-hostable (ETA early next year) and then we could update or re-publish so that the entire stack runs on Railway
very cool, a full starter project
yeah pretty much - it will be end-to-end Flutter app that works offline syncing to Railway Postgres
awesome! if you need any help making the template feel free to ask, I'd also be happy to give you any feedback on the template itself once done
I think I'd like some feedback yes, thank you.
of course, just open a #βο½help thread for that and I'll be around
Is it OK if I send questions about the template itself here for now and then open the help thread for the final review? Question: is this valid syntax for template env vars?
for the nodejs app in the template to reference the postgres database in the template
yep that's valid
Is there a way to set a Template Environment Variable to a system function that has the value of an HTTP NodeJS "Public Networking" hostname? To paraphrase - is there a way to get the public networking hostname from an environment variable?
yes of course https://docs.railway.app/reference/variables#railway-provided-variables
and this is a helpful little guide with examples
https://docs.railway.app/guides/variables#reference-variables
Thx!
If I want to run some once-off "post up" commands against Postgres, is the recommended way to do that still with a "temp" NodeJS service called "run once then delete me" ? example
GitHub
pulse-railway-pg-config/config-db.ts at 69d12d4b42d715edab7c1f0fdfd...
A railway.app project template. Contains a Pulse ready postgres database and set up service. Read more about pulse - prisma/pulse-railway-pg-config
it depends, that specific service you are talking about was just changing the wal_level, but now since databases are just services with a volume, you can provide a database with a modified start command that sets the wal_level through a command flag, so for that specific use case an init service is not needed. but what did you need to run once on the database?
1) set wal_level to logical
2) create a DB role "with replication" (recommended to use a separate SQL user for production, so could possibly skip this step for demo purposes)
3)
GRANT SELECT to all tables for the user created in step 2 (could consider skipping as above)
4) Run DDL script to create 2x demo tables
5) CREATE PUBLICATION powersync FOR ALL TABLES;
Interesting @ command flag - that will eliminate 1) at minimum.
If we skip 2 and 3 and just re-use default username/password, that just leaves 4) and 5)
I also need to decide whether I want the template to be a starter from which to build from, or more of a demo that is a full, working project to serve as a referencecould you do something like this but put it in the start command?
https://github.com/railwayapp-templates/postgres-ssl/blob/main/Dockerfile.latest#L7
there's also the option of maintaining your own postgres image that sets everything up perfectly to work with powersync, but thats probably not worth it
Thanks - I'm going to go with "a starter project from which you can build on" rather than end-to-end demo project, so that completely removes steps 2 through 5, so I'm good no more questions π
awesome !
struggling a bit with the start command syntax, if I wanted to override the start command for the default postgres service from
postgres --port=5432 to postgres --port=5433 (hypothetical example) what would I put into the "Custom Start Command" field under the Service Settings in the Dashboard?GitHub
postgres-ssl/Dockerfile.latest at 0187d4fba19a15ed8062499dc2a9fdde0...
Contribute to railwayapp-templates/postgres-ssl development by creating an account on GitHub.
wrapper.sh postgres --port=5433What's wrong with this Postgres service in my template?
When I try to use it, the Postgres fails to launch with this error:
youre trying to deploy from a repo instead of the docker image
is there a docker image for postgres-ssl?
of course, thats the sole purpose of that repo https://github.com/railwayapp-templates/postgres-ssl/pkgs/container/postgres-ssl
Ah I see now, missed that. Thanks!
wrong start command too, you want
wrapper.sh postgres --port=5433 -c wal_level=logical
missing a volume mount on /var/lib/postgresql/data
probably should have shown you this sooner, but you can fork the postgres template, modify the start command and then add your example app https://railway.app/button?code=postgresnope... will kick more tires tomorrow. Forking the template works, but adding that start command I'm unable to connect to Postgres externally.
any logs?
nothing particularly exciting
you changed the port postgres starts on, you need to also change the tcp port
i should have caught that earlier, my bad
didn't change anything other than the start command, wonder if it's a bug with the form
im saying you need to change the tcp port too
grrr
yeh
thx
thought I was still on 5432, that 5433 was just an example to figure out the start command syntax
oh, i thought you had some reason for running it on 5433 so i didnt question you
Heh serves me right. Anyway everything's working now, thanks for your help. We shipped a small update on our end too today to support the certs nicely π
awesome, let me know when you've published that template and I'll take a look!
When publishing what are good dimensions for the
Image URL ?
good question, I don't know lol. stick your logo in and see how it looks on the preview
haha ok cool
also (and getting into nice to haves here) is it possible to configure the template so that when someone uses it, the
Private repository checkbox is checked by default?
there isn't, that would be up to the user
Any recommendations for Avatar dimensions? Shows up in Template page
I think mine is 1000x1000 and looks fine to me
nice