Using ssl cert references in DATABASE_URL leads to pgBoss requiring custom cert env data
Im providing a custom ssl cert to the wasp app for postgre connections, which works fine with prisma. The issue arise when pgBoss needs to connect with custom ssl cert, as it is not configurable via the DATABASE_URL like prisma, but instead requires the environment variable PG_BOSS_NEW_OPTIONS to be set with a specific json structure containing binary cert data. This is not secure, and frankly unmanagable.
How can this be worked around professionally (without having to manually edit
pgBoss.ts file in production)?7 Replies
Hi @linus625. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:
----------------------
You're right about the challenge with pg-boss and SSL certificates. When using custom SSL certificates with PostgreSQL in a Wasp app, there's a difference in how Prisma and pg-boss handle the connection.
According to the documentation, pg-boss requires special configuration when using SSL connections. For Heroku deployments, Wasp recommends setting the
PG_BOSS_NEW_OPTIONS environment variable with a specific JSON structure as mentioned in the deployment docs.
The same approach applies to your custom certificate scenario. The pg-boss documentation indicates that setting PG_BOSS_NEW_OPTIONS is the official way to customize the pg-boss instance initialization parameters, including SSL options. As noted in the Wasp documentation:
If you need to customize the creation of the pg-boss instance, you can set an environment variable called PG_BOSS_NEW_OPTIONS to a stringified JSON object containing these initialization parameters. NOTE: Setting this overwrites all Wasp defaults, so you must include database connection information as well.
Recurring Jobs documentation
While storing binary certificate data in an environment variable isn't ideal from a security perspective, there are a few professional approaches you could take:
1. Store your certificate in a secure location and reference it by path in the PG_BOSS_NEW_OPTIONS JSON (if pg-boss supports file paths for certificates)
2. Use a secret management service like AWS Secrets Manager, HashiCorp Vault, or similar to inject the certificate data securely at runtime
3. Consider using a connection pooler like PgBouncer that handles the SSL termination, allowing both Prisma and pg-boss to connect to it without SSL
4. If you're in a cloud environment, check if there are managed certificate solutions that might simplify this process
The knowledge sources don't provide a direct solution to avoid using PG_BOSS_NEW_OPTIONS, as this appears to be the intended configuration method for pg-boss in Wasp. Editing the generated code is indeed not recommended as it would be overwritten on subsequent builds.
I'd recommend raising this as a feature request in the Wasp GitHub repository, as there's an existing issue (#1357) discussing Prisma's directUrl option, which shows the team is interested in improving database connection configuration.
Is there a specific aspect of your setup that makes using PG_BOSS_NEW_OPTIONS particularly problematic?
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
Sorry @linus625, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist:
Perhaps try again in 15mins?Pg boss expects base64 encoded cert data in the variables ssl.ca, ssl.cert and ssl.key in the json structure that must be set in the environment variable PG_BOSS_NEW_OPTIONS, (including the connectionString set to DATABASE_URL without the prisma specific ssl arguments).
This is not secure or preferable from a workflow perspective.
What would be preferable is to be able to provide paths to the cert files and settings to other configurable parameters of pgBoss, just like prisma expects.
I get it to connect properly with this code in
pgBoss.ts
let pgBossNewOptions = {
connectionString: config.databaseUrl.split('?').at(0),
ssl: (env.PG_ROOTCERT && env.PG_CLIENTCERT && env.PG_CLIENTKEY) ? {
rejectUnauthorized: true,
ca: fs.readFileSync(env.PG_ROOTCERT),
cert: fs.readFileSync(env.PG_CLIENTCERT),
key: fs.readFileSync(env.PG_CLIENTKEY)
} : false,
max: 20, // number of allowed simultaneous connections
application_name: 'wasp-pgboss'
};
In combination with working certs in the paths (with 600/644 permissions and user access) provided via the new environment variables.Sorry to hear that this was cumbersome! What would be the ideal scenario for you? e.g. what kind of env vars would you expect to set?
Ideally we would setup db connection properties generally via environment variables and let wasp handle
DATABASE_URL internally (if needed). It could be backwards compatible by not defining DATABASE_URL and that would tell wasp to look for other environment vars.
As one way of making this less obscure, because I just now realized some of the DATABASE_URL arguments are not picked up by prisma, but some are which is obviously very confusing.
For example, the prisma-specific-argument sslaccept is picked up in dev, but blocks prisma from finding prisma-specific-arguments sslcert and sslidentity in production.
pgboss on the other hand, want a root certificate (if not defined by node or system) a client certificate and a client key to be provided to it (see above).
So making that smoother from the wasp interface would be nice.I've encoded this in an issue that we'll work on in the future: https://github.com/wasp-lang/wasp/issues/2677
One of the things on our roadmap is upgrading PgBoss to a newer version - so we might redesign the interface between PgBoss and Wasp (e.g. exposing the config in a nicer way would be on the table)
GitHub
Improve database SSL certificate support for PgBoss · Issue #2677 ...
We had a user using SSL certificates for his database connection and while it worked fine with Prisma, they had some issues with PgBoss. Discord Thread Pg boss expects base64 encoded cert data in t...
Nice!
Wohooo @linus625, you just became a Waspeteer level 3!