Storecert - a java program that stores and retrieves certificates in postgresql

[jeffemandel]

I needed a way to move certificates around to the various machines inside my NATed domain, so I wrote this java program, available at https://github.com/jeffemandel/storecert. The intended purpose is to be able to have all of the certificates acquired by the main server for the domain (the one the NAT points at), which then forward the https to the internal servers, but also have the certificates on those servers so that internal https works too.

The program can be called with either --store (which saves the certificates in the database) or --load (which retrieves them). Certificates are stored encrypted, and the user is advised to use pg_ctl to block external access.

Enjoy,
Jeff E Mandel MD MS
Perelman School of Medicine at the University of Pennsylvania

[_az]

The concept of centralised cert. storage is certainly interesting.

I had an idea previously that within an organisation there could be a “certificate broker” which would handle the request, completion of challenges and storage of certificates from Let’s Encrypt.

Then, client machines could connect to the broker occasionally and retrieve an updated certificate for their own use.

This would save you from having to configure acme clients on internal machines (who may be on NAT/no internet access).

Gotta say the usability of this specific project is a bit questionable but the contribution is cool anyway, thanks!

[jeffemandel]

I have no question it can be done better, but I've spent a lot of time recently writing a Tomcat servlet that accesses Postgresql, so it was the easiest approach for me. If someone wants to take a better approach, the core concept is that there is a master directory:

/etc/letsencrypt/live
|
|__www.example.com
|__calendar.example.com
|__addressbook.example.com

Each of these house cert,privkey,chain,fullchain for their named server. www.example.com has Apache2 (or equivalent) with virtual hosts redirecting the relevant ports:

<VirtualHost *:443>
	ServerName calendar.example.com
	SSLEngine on
	SSLCertificateFile /etc/letsencrypt/live/calendar.example.com/cert.pem
	SSLCertificateChainFile /etc/letsencrypt/live/calendar.example.com/chain.pem
	SSLCertificateKeyFile /etc/letsencrypt/live/calendar.example.com/privkey.pem
	Include /etc/letsencrypt/options-ssl-apache.conf
	SSLProxyEngine On
	ProxyRequests Off
	SSLProxyCheckPeerCN off
	SSLProxyCheckPeerExpire off
	SSLInsecureRenegotiation on
	SSLProxyVerify none
	SSLVerifyClient none
	SSLProxyCheckPeerName off
	<Location />
		ProxyPass https://calendar.example.com:443/
		ProxyPassReverse https://calendar.example.com:443/
	</Location>
</VirtualHost>

Now on calendar.example.com, we have a simpler directory structure:

/etc/letsencrypt/live
|
|__calendar.example.com

storecert just makes sure that this directory structure is kept in sync. You could probably do this with svn or such, but postgresql has some advantages in being able to make sure only www.example.com can write to the database (and only via localhost). I threw in the encryption to increase security further, but this may be overkill.

The remaining problems are:

1. Triggering storecert on the client machines when needed (so we don't restart services every day)
2. Handling idiosyncratic clients like OS X Server, Synology Diskstation, etc.

I can imagine 2 approaches to the first issue. The first might be:

curl https://calendar.example.com/storecert&function=load

Where storecert is an Apache Tomcat servlet. Clean, but it presumes you have Tomcat (or something) listening on some port.

The second approach would be to add a field (newcerts) to the database that holds TRUE until cleared. The client then polls the database:

PreparedStatement st = conn.prepareStatement("SELECT newcerts from certstore where server=?");
st.setString(1, server);
ResultSet rs = st.executeQuery();
if (rs.next() && rs.getBoolean("newcerts")) {
...
}

Of course, no reason you couldn't do both.

The second problem seems to be similar (or identical) to the problem of writing a renewal hook - there's a new certificate set in /etc/letsencrypt/live, so take the appropriate action.

Hopefully this will stimulate people to think about the proper way to do all of this, while I get back to my web app that needs proper security before I submit it for FDA approval. Try reading that fine manual.