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:

  1. I will store the same information twice (in X509 certificate itself and in separate columns for easy searching)
  2. 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.


I addressed a similar issue in my blog Storing X.509 Digital Certificates (And Other Messy Things) and some earlier comments. (It's too long to cut & paste here.) Many of the points made here are much easier if you can create a user-defined function that extracts the fields you need to cache.

Addressing one other point above - it is possible to write a trigger that uses SPI to verify that the cert's issuer is present in the database. You would need to include exceptions for self-signed root certs and 'trusted' certs that are provided by others. This, plus some other sanity checks (e.g., does the issuer have the 'basic' capacity? Are there DN restrictions?) would give you a much stronger repository.

Is it wise to do that? I keep going back and forth on that. My current thoughts are that it would work if you issue and manage your own certs but it will be a massive headache if you incorporate third-party certs. The problem is that there are a lot of junky certs out there. If you're strict you're going to exclude a lot of certs in use and if you're lax then why bother with the extra logic?


I didn't quite get why you want to store the various subject/issuer fields separately unless you are going to query for the information from the database; especially as you will have to read the certificate to verify its details (your second item on the list).

If you were storing this to automatically expire/invalidate certificates, you can run a separate task that does this.

Also, since postgresql allows you to use python as a procedure language; you could write a trigger or view that will return back the "parsed" information for your application - if you really wanted to offload it from your app.

In addition I would do the following since you are storing certificates:

  1. Make sure the connection between the clients and server is encrypted (you should do this anyway). See section 17.9 in the documentation.

  2. Make sure the table/column is encrypted. See the pycrypto module documentation.

  • 2
    I want to keep separate subject/issuer fields (or its SHA256 hash) for easy search of the issuer certificate. Proper X509 validation require that application must check certificate signature with issuer certificate.... and issuer certificate with its issuer certificate...up to the trusted CA certificate. If any of this steps fail... whole authorization will be invalidated and user will not be authorized. – Marek Mar 11 '13 at 12:05

Adding to Craig's excellent reply, that once you start pulling information from an X509 library, you can do some other interesting things with it. I don't know if SQLAlchemy always uses table aliases or if you can force it to do so, but if you can, then you can avoid duplicating information by calling the parsing routines on the X509 certs directly, and you can even index the output of those functions so you don't have to call them on select time (i.e. the functions get run on insert/update time) as he mentions.

One thing I would point out is that you can then create table methods that, if you can get your ORM to always use table aliases, can then take the place of columns. For example:

CREATE OR REPLACE FUNCTION issuer(x509table) returns text 
    select issuer_cn FROM extract_x509info($1.x509data);

This can then be found by:

SELECT x.issuer FROM x509table x;  --works

Note that the following is not valid and hence my comments about the ORM:

SELECT issuer FROM x509table; --does not work

The reason is that if there is no issuer column the parser will convert the first statement to:

SELECT issuer(x) from x509table x;

Note that indexing a lot of columns there will shift a lot of computation time from read to write (making inserts/updates slower, but read operations faster).

In the end it is hard to come up with a full recommendation without knowing exactly what you are doing, what your expected workloads are, etc. The advantage of duplication is it separates the data from the extraction functions for reading and writing, allowing more optimizations to be possible. The advantage of keeping everything functional (assuming your ORM supports this) is that it provides additional guarantees of data integrity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.