How can I build an SSL cert/chain for the Rails pg gem to fix PG::ConnectionBad (SSL SYSCALL error: Connection reset by peer (0x00002746/10054))

David Krider 0
Viewed 9

My company seems to have recently made some sort of change to the Cisco Endpoint Ark blah blah blah setup on our corporate Windows laptops, and the SSL configuration has killed the ability of one of my programs to talk to a Postgres server hosted at Azure. E.g.,

C:\Users\xxxxx\Projects\stuff_and_things>rails c
Loading production environment (Rails 6.0.3.4)
irb(main):001:0> User.first
Traceback (most recent call last):
        1: from (irb):1
PG::ConnectionBad (SSL SYSCALL error: Connection reset by peer (0x00002746/10054))

For 3 years, just specifying sslmode: require has been all I've needed for the pg gem to connect to my database server. I've played with the various sslmode and sslrootcert keywords in my database.yml file, but nothing I've tried has worked, and I'm not getting any hints or error messages about what the problem actually is. Rails can't get far enough in this process to even give me any messages in the log.

From other things I've read, I think I need to build a new, single cert that includes the whole chain of trust in one file, and specify that as root.crt, but I can't find any reference about how to do that. I know Rails is "old and busted" now, so finding any tips about this has been really difficult.

Does anyone have any clearer insight on how I could make the Rails pg gem happy again, or at least any way I could get better error messages from the process?

ruby-on-rails ssl postgresql
1 Answers

I recalled that I've had to work around this kind of SSL issue on our laptops before, to get bundler working. Thanks to that pointer to RubyInstaller's SSL FAQ, I got that to work after adding Cisco's Umbrella root and 2048 cert .pem's to the right place, and run a script that comes with RubyInstaller to rehash the cert stack. Notably, I was helped in this process through bundler's error messages, which told me that I needed those particular certs to satisfy the chain of trust.

Weighing in at 240KB, I finally realized that the .pem file that RubyInstaller's script had produced -- which is still keeping bundler happy -- was, in fact, the complete trust chain I needed to make the pg gem happy as well, so, in my database.yml, I've used:

sslmode: verify-full
sslrootcert: C:\Ruby25-x64\ssl\cert.pem

And this seems to have made Rails happy again. Now I have to figure out how I'm going to keep these settings separate between my Windows and Linux machines hitting the same production server...

David Krider0
Related