What is the best way to store X509 certificate in PostgreSQL database? I'm working on web authenticate system where users will digitally sign random tokens and this will be checked with X509 certificates stored on the server.

Therefore I have to store several X509 certificates (PEM or DER format) in PostgreSQL database. Sounds easy, but I want to have possibility to search certificates with subject, issuer, notBefore, notAfter and similar criteria.

My idea is to have following columns in database: X509data, notAfter, notBefore, subject, issuer etc. Than I will create object (in SQL alchemy) representing X509 certificate with methods like add_new_X509(), find_X509(search criteria)  etc. So whenever I will add new certificate with method add_new_X509() it will automatically reads from certificate all data and fill up rest of the columns and put raw certificate into X509data column.

Unfortunately this solution have two disadvantages: 


I will store the same information twice (in X509 certificate itself and in separate columns for easy searching)
Whenever I want to read X509 certificate, my application will have to crosscheck notAfter, notBefore, subject, issuer with information stored in original certificate (this is for security reasons, in case someone would try to modify this fields).


So.. anybody have better idea, or suggestion? Maybe somebody see any other security issue that can arise with this solution?
Duplication isn't ideal, but in this case is probably the best choice. Set the table permissions so that the table owner is not the operational day to day database user your app runs as, and only GRANT your app the ability to write to the certificate data column, not the "cache" columns with expiry etc. Have a SECURITY DEFINER trigger function intercept writes to the certificate field and as a privileged user update the indexed cache columns by using an X.509 library to extract the fields from the certificate after verifying it.

Alternately, you could write a PL/Python, PL/Perl, or even a C SQL function that calls an X.509 certificate parser library to extract fields and return them. So you'd say extract_x509_field(cert, 'subject'). Or perhaps even a row-returning form like SELECT subject, issuerName FROM (SELECT extract_x509_fields(cert) FROM the_table) where extract_x509_fields returns a row of all relevant cert data. With this approach you could create functional indexes like CREATE INDEX cert_issuer ON certificate_table( extract_x509_field(cert,'issuer') ); that could be used to match WHERE expressions. You wouldn't need to have table columns for the extracted data at all. The downside is that this would likely be slower as the cert would get parsed multiple times during index creation, during index re-checks, etc.

Either way, it's vital that your application operate as a PostgreSQL user that is not the database owner, not a superuser, and not the owner of the tables and indexes of concern. It should be GRANTed the minimum rights necessary and no more. If you have quite separate tasks (say, read-only vs write-and-update) consider using different database users for them so that even if the "read" part of your app is tricked into trying to write a field/update a cert/etc, it doesn't have permission to.