the stock postgres-ssl template does not currently provide a complete verify-full setup for an external client connecting through the Railway public TCP proxy hostname.

The certificate generation script currently adds DNS:localhost and, when available, the Railway private domain to the certificate SAN. It does not add the public TCP proxy hostname. Therefore a connection to *.proxy.rlwy.net with sslmode=verify-full will correctly fail hostname validation.

For production, I would recommend using a stable DNS name that you control instead of binding the certificate directly to Railway's generated proxy hostname.

For example:

db.example.com -> CNAME -> <your Railway TCP proxy domain>

Continue using Railway's assigned TCP proxy port, but configure Npgsql and pg_dump with Host=db.example.com.

Then issue the PostgreSQL server certificate with at least:

subjectAltName =
  DNS:db.example.com
  DNS:localhost
  DNS:<service>.railway.internal

This keeps the TLS identity stable even if the Railway TCP proxy hostname changes later; only the CNAME would need to be updated.

There is another important issue with the current template if you require stable CA trust.

The template stores root.crt, root.key, server.crt, and server.key inside the PostgreSQL volume, so they survive normal restarts and image redeployments. However, the current renewal path can regenerate both the server certificate and the root CA. If an external client has pinned that root.crt, regenerating the CA will break trust.

For a production verify-full setup, I would therefore fork/customize the template so that:

• the existing CA is retained;
• only the leaf/server certificate is renewed;
• the configured public DNS name is always included in the SAN;
• the server certificate is re-signed by the same CA;
• CA rotation is handled as an explicit migration rather than as part of normal certificate renewal.

For the existing database, you do not need to recreate the database or volume. You can keep the existing CA and issue a new server certificate signed by the existing root.key, with your public hostname added to its SAN. Replace only the server certificate/key files and reload PostgreSQL's configuration. PostgreSQL can reread its TLS certificate files on configuration reload.

For example, after installing the new certificate files:

SELECT pg_reload_conf();

Then the external clients can use full verification.

For pg_dump/libpq:

host=db.example.com
port=<railway-tcp-port>
sslmode=verify-full
sslrootcert=/secure/path/railway-postgres-root.crt

For Npgsql:

Host=db.example.com;
Port=<railway-tcp-port>;
SSL Mode=VerifyFull;
Root Certificate=/secure/path/railway-postgres-root.crt;

Only distribute root.crt to clients. Never distribute root.key.

Also note that the root.crt generated by this template is a private/self-signed CA, not a publicly trusted CA. That is perfectly compatible with verify-full as long as Npgsql/libpq explicitly trust that CA.

If your requirement is specifically a publicly trusted certificate, I would instead use a custom domain you control and automate certificate issuance/renewal with a public CA such as an ACME-based CA. You generally cannot obtain a public certificate yourself for Railway's *.proxy.rlwy.net hostname because you do not control that domain.

So the cleanest architecture is:

VPS -> db.yourdomain.com:<Railway port> -> Railway TCP Proxy -> PostgreSQL TLS

with the PostgreSQL certificate containing db.yourdomain.com in its SAN and either a stable private CA pinned by the clients or a certificate issued by a public CA.

That gives you real verify-full hostname and CA validation without disabling or bypassing TLS verification, while leaving the existing PostgreSQL data volume intact.