# softhsm with postgres



## Alarc'h (Oct 14, 2018)

Hi,
I would like to know if anyone knows if it is possible to use softhsm2 to store encryption keys for data in a postgresql database. Postgres cryptography does not seem very robust (no salting for encryption, only for "digest"), but especially does not plan to keep private and public keys in the database in a secure way as Oracle or MS SQL server do, so I was wondering if it was possible, in the absence of a hardware HMS, to improve things a little with a software security module.


----------



## Alarc'h (Oct 14, 2018)

I answer to myself, I hadn't seen this thread, but it seems that Postgres doesn't know how to do what I would like him to do : 


> The problem of secure key storage is part of what PKCS#11 was invented for. It provides a generic interface for applications and crypto providers to talk to anything that can provide certain signing and decryption services _without ever revealing its key_. The usual, but not only, use is with hardware based crypto like smart cards and hardware crypto modules. Such devices can be told to sign or decrypt data passed to them, and can do so without ever revealing the key. If possible, consider using a smartcard or HSM. As far as I know PgCrypto cannot use PKCS#11 or other HSMs/smartcards.
> 
> If you can't do that, you can still probably use a key management agent, where you load your key into a key management program manually when the server boots, and the key management program provides a PKCS#11 (or some other) interface for signing and decryption via a socket. That way your web app never needs to know the key at all. gpg-agent may qualify for this purpose. Again, as far as I know PgCrypto cannot use a key management agent, though it'd be a great feature to add.


----------



## ShelLuser (Oct 15, 2018)

Alarc'h said:


> I answer to myself, I hadn't seen this thread, but it seems that Postgres doesn't know how to do what I would like him to do :


I would claim that this conclusion is wrong. After all: all which Postgres would have to do is store data, at worst it would be a blob which is perfectly doable. This issue isn't about Postgres.

But to answer the actual question:



Alarc'h said:


> Hi,
> I would like to know if anyone knows if it is possible to use softhsm2 to store encryption keys for data in a postgresql database.


If you check the options of security/softhsm2 (so: running `make config` in the port directory) you'll notice that the only storage method mentioned is SQLite. You can further verify this for yourself by extracting the original source code (run `make extract`) and then check that. In this case the classic configure script is used so: `./configure --help | less`. That too wil show you that the only storage method provided by SoftHSM2 is SQLite.

Ergo: SoftHSM2 doesn't support a backend SQL storage, so I seriously doubt that you'll be able to "just" make it use PostgreSQL. But that has nothing to do with Postgres but more so with SoftHSM.


----------



## Alarc'h (Oct 15, 2018)

Thanks for your reply,

in this case sqlite is only an alternative to flat file for internal purpose of softhsm (it's store is data in a database or in files). softhsm interact with other apps using PKCS #11 api. It seems that postgres doesn't know about PKCS #11. 

I agree that a database is design for storing data... ;D

SQL server (I don't use, I can't confirm) and Oracle (that's sure) can manage DH keys and encryption using HSM device or software emulating a HSM (less secure of course). Private keys and passphrases are totally isolated. 

And I maintain that this is indeed a question about postgresql. PG does not know how to do what Oracle does in terms of security, which is a huge limitation for professional use. This is by no means an attack on PG, which, moreover, is an excellent tool that I use a lot. 

That's why I would have preferred to be able to use a free tool rather than an Oracle instance, proprietary and expensive, to create a very simple database whose only constraint is the encryption of certain data.


----------



## ShelLuser (Oct 15, 2018)

Alarc'h said:


> softhsm interact with other apps using PKCS #11 api. It seems that postgres doesn't know about PKCS #11.


That's incorrect.



Alarc'h said:


> SQL server (I don't use, I can't confirm) and Oracle (that's sure) can manage DH keys and encryption using HSM device or software emulating a HSM (less secure of course). Private keys and passphrases are totally isolated.


PostgreSQL supports many authentication methods, including certificate based authentication through use of OpenSSL. See the link for more information.

Quite frankly I think you're severely mixing up your facts here. There's a huge difference between authentication and data storage. Your initial question evolved around PSQL as a storage backend for an authentication method yet now you're suddenly addressing the authentication methods of PSQL itself, which seems rather odd considering that the original question was about SoftHSM.

Bottom line though: don't focus on 3rd party software if you want to know what PostgreSQL does or doesn't support, check its manual instead. You're making it sound that you concluded that PSQL doesn't support certificate based authentication just because SoftHSM doesn't support it, which would be plain out ridiculous.


----------



## xtaz (Oct 15, 2018)

This doesn't look to me as if he's mixing up facts. I've been looking into this exact thing for work to satisfy the EU GDPR regulations. Our requirement is to do transparent data encryption where the application talks to the database using the same SQL that it always has, but the database itself then encrypts this data as it stores it into the storage backend. The second requirement is that the private keys used for this encryption must be stored securely within a hardware security module that is separate to the database server itself. And as the OP said, the usual way for an application to securely access keys in this way is using a PKCS#11 API.

To be honest I haven't looked at PostgreSQL other than a cursory glance. But I don't think PostgreSQL supports this. We've been looking at replacing our MySQL databases with MariaDB instead which uses something called Eperi Gateway as a software security module. MySQL has a feature called TDE but it's more expensive.


----------



## Alarc'h (Oct 15, 2018)

I know that PKCS is normally used for certificate management and authentication purpose, but what I need is cryptography and I need to be able to activate a private key for decryption without having to place the passphrase in plain text in an SQL query, which is mandatory with pg_crypto from the documentation. 
The PKCS #11 API allows you to access keys stored in an HSM without the application having to see the passphrases, which I need. 

I'm afraid my poor English has caused confusion, I'm sorry.


----------



## Alarc'h (Oct 15, 2018)

Hi xtaz, 

I was composing my message when yours arrived, and indeed my problem is exactly the one you describe. My problem is not directly related to the GDPR, but even more precisely to the French law concerning the computer storage of personal health data, which imposes drastic constraints on data security.


----------



## ShelLuser (Oct 15, 2018)

xtaz said:


> I've been looking into this exact thing for work to satisfy the EU GDPR regulations.


Then I have a news flash for you: if you follow the GDPR to the letter then you'll soon realize that no database can ever become GDRP compliant.

See, they demand that personal / user information is kept separately from other information. Basically it should not be possible to retrieve and combine personal information by accessing the storage facility. Yet what do you think a forum such as Xenforo does? If I go to my profile to check the information I added then it's the single entity (Xenforo) which retrieves and displays all of my data. All at once. It retrieves & combines everything again, thus linking personal data with my account which violates the GDRP.

Still, this thread's topic seems to change every time. First it was about Softhsm accessing Postgres, then it seemed to shift towards Postgres authentication and now we're suddenly addressing GDRP. Too many topic changes for my liking.


----------



## Alarc'h (Oct 15, 2018)

I have never talked about authentication but always about a way to encrypt and protect decryption keys. 

That said, you are not wrong, the requirements of the GDPR are quite contradictory and everyone in Europe is pulling their hair out to comply with them, and in fact no one is sure that they are absolutely in compliance with this text produced by the Brussels paper pushers.


----------

