postgREST v11.1.0 connection failure after RDS CA (certificate authority) rotation

[MisterOctober]

Howdy gang! I ran into an issue that I'm hoping to get some advice on :

• Background: postgREST 11.1.0 running on Amazon Linux 2023, connecting to an RDS Postgres 15.5 instance. This setup has been running successfully (with various version updates) for over five years.

• Problem: The time has come to rotate RDS CA (certificate authority) certs. This is a simple process as follows:

• 1. Download new cert bundle from AWS;
• 2. Update client trust stores (replacing old certificate bundle with the new one, in application config and in this case, in ~/.postgresql/root.crt on the postgres client bastion host running postgREST (and psql), reload postgREST config with sudo killall -SIGUSR2 postgrest;
• 3. Update RDS config to reflect new CA.

This process is straightforward and worked as expected for other app connections and for the local psql connection on the bastion host (which is running from the same Postgres install that postgREST uses locally).
However, postgREST balked after cert update with the following error:
"error": {
"code": "PGRST000",
"details": "connection to server at "$URI"\ $PORT failed: SSL error: certificate verify failed>
"hint": null,
"message": "Database connection error. Retrying the connection.",
"statusCode": 503
}

I rolled back the RDS-side cert update, but left the new cert bundle (which includes old and new certs) in place, and the connection restored.
So clearly, the cert<->CA config on the RDS side is valid and simpatico, but I'm missing something in the postgREST config. I don't recall, and I didn't see in the docs, anything about specific postgREST config steps for cert / CA updates (that is, my understanding is that as long as the local Postgres client root.crt is valid with respect to the PG server, postgREST should be able to connect . However, I think probably I'm missing or forgetting some key postgREST config step.

Question: Given that the RDS CA <-> cert bundle appears to be valid when used in other apps, how can I correct this cert-validation connection error and successfully connect to the CA-updated RDS instance with postgREST?

All guidance is much appreciated!

[steve-chavez]

This setup has been running successfully (with various version updates) for over five years.

Hey Nicholas! That's awesome to hear!

"error": {
"code": "PGRST000",
"details": "connection to server at "$URI"\ $PORT failed: SSL error: certificate verify failed>
"hint": null,
"message": "Database connection error. Retrying the connection.",
"statusCode": 503
}

but I'm missing something in the postgREST config.

The above is purely a libpq error, can you connect with psql on the same instance PostgREST is running? If so, try and compare the psql connection string you use and the one that is set on PostgREST's db-uri.

[MisterOctober]

The connection strings for psql and postgREST db-uri contain the same server address, port, and database name;
they differ in user (I believe this is common, as the postgREST user is only used for the postgREST connection),
and also in psql usually I don't have the explicit sslmode=verify-full as exists in the postgREST db-uri because SSL is enforced on the server (confirmed by ssl_is_used() which returns true) and my understanding is that psql defaults to verify if there is a cert in ~/.postgresql/root.crt -- however, just to double check, I connected with pql prepending PGSSLMODE=verify-full and the result was as expected.

I've done a fair amount of digging around for reasons that might cause the observed problem (i.e., two connections from the same client using the same root cert, but one fails and the other succeeds), however I haven't come up with any likely hypotheses so far

[MisterOctober]

@steve-chavez : as far as the postgREST connection to the Postgres server is concerned, when rotating root certificates, is there any action that needs to, or ought to, be taken beyond replacing ~/postgresql/root.crt on the client machine from which postgREST connects?

[wolfgangwalther]

reload postgREST config with sudo killall -SIGUSR2 postgrest;

Did you only try reloading the config so far? I could imagine that the new root certificate only takes effect after actually restarting PostgREST.

[MisterOctober]

AH! Great thought, I haven't done an actual hard (non-signal) restart of PostgREST, will try that as soon as possible and report back -- thanks!

[MisterOctober]

@wolfgangwalther : I just tried rotating the Postgres server certificate authority and restarting PostgREST with sudo pkill postgrest (confirmed new process ID after restart), but the PostgREST connection once again failed (while other apps were able to connect with the new cert)

Are there any other things I should try or check? The old server CA expires on 2024-08-22 (i.e., this Thursday) so I'm pretty eager to get the connection working with the new CA

[wolfgangwalther]

It's unclear to me how you're running PostgREST. Is that via systemd? As root or some other user? Are you sure this will actually look in ~/.postgresql/ for the certificate, especially with ~ being the same ~ you are looking at with psql?

Could it be that you had copied the root certificate in this folder initially, but then had to copy it into some other place in the system, i.e. somewhere in /etc/.. or so, too? Maybe you need to update it in a different place?

[MisterOctober]

@wolfgangwalther : your guess is correct, PostgREST is running via systemd. It's running not as root, but as a different user (but the same user from which psql is invoked, so I think the ~/ references should be the same for each case.

Your suggestion of checking for other places the cert might need to be updated makes sense -- it's been a few years since the CA was rotated and so it's possible that my internal documentation is missing a step and / or it's a user-level issue -- I'll try to determine if there's any other place the root cert needs to be updated, and report back. Thanks for all your help so far!

[wolfgangwalther]

Also, which binary are you running? The static executable or a dynamically linked one?

[MisterOctober]

Closing the loop here:

Following @wolfgangwalther's suggestion, I re-checked the systemd service config and found that, contrary to my recollection, PostgREST had indeed been changed to run under a different user than the bastion host psql user last year and that change had not been adequately documented.

I updated that user's ~/.postgresql/root.crt , rotated the server-side certificate authority, tested, and the PostgREST connection was stable as expected.

Huge thanks to the PostgREST team not only for helping with this question, but for all their hard work over the years providing user guidance and maintaining the best postgres API in the biz.