SSL certificate info on SQL level and HSM support for libpq - Mailing list pgsql-hackers

Hi,

I've project where I need to log information about database user, based
on hardware security tokens. These tokens are supported by OpenSSL.

So, I need two modification in the PostgreSQL core

1. Access to SSL certificate information on SQL level. It seems that this can be done using INTERNAL functions only,
becauseI cannot see how loadable module would get access to MyProcPort global variable.
 

2. Ability to use openssl OpenSSL hardware support modules (engines) in libpq.

I suppose that I'm not only person in the world who need this
functionality, so I'm asking here in hope that I would be able to made
patch, acceptable into PostgreSQL 8.2.

First problem
-------------

I've already tried to write internal function which just returns peer_dn 
field from the Port structure.
Unfortunately, it doesn't solve my problem because PostgreSQL uses
function X509_NAME_oneline to fill this field.

This function is marked deprecated in OpenSSL 0.9.8b docs for good
reason - it doesn't support non-ASCII characters in certificate subject
fields - it escapes them to something unreadable. And in my case
(potential customer base is in Russia) everything in certificate subject
except country code would be non-ASCII.

Moreover, certificate subject is not guaranteed to be unique. Only
combination of certificate issuer and serial number is guaranteed.

So, I need more complicated API which access peer field of Port
structure directly.

I propose following API:
------------------------
function ssl_on() returns bool; - true if current session is established            via SSL connectionfunction
ssl_client_authenticated()returns bool; - true if client            certificate was provided
 
These functions should be defined even if PostgreSQL is compiled without
SSL support and always return false in this case.
   function ssl_cert_subject(VARCHAR fieldname) returns VARCHAR;    - returns field of certificate subject, converted
intodatabase      encoding. I think it is safe to assume that database can      represent characters used in it's
user'snames.            fieldname would be something like 'commonName' or      'emailAddress' and looked up using
OpenSSLobject names API.   function ssl_cert_issuer(VARCHAR fieldname) returns VARCHAR;     - does the same for
certificateissuer.
 
function ssl_cert_serial() returns NUMERIC;     - returns certificate serial number. NUMERIC type is used here
insteadof int4 or int8 because certificate serial numbers       can be really BIG.
 

These functions return NULL if ssl_client_authenticated() returns false.

Function to return certificate extensions such as subjectAltName or
extendedKeyUsage may also be useful, but I don't need it right now, so I
don't promise to write it. It has additional complication that these
extensions are typically multivalued, so function have to return array
of values.

My questions here:
-----------------

1. Am I correct that these function have to be INTERNAL? Or it is
possible to get access to MyProcPort variable (on Windows platform too)
from dynamically loadable object?

2. I can make OpenSSL to return UTF-8 string for certificate name
fields. How to convert them into current database encoding. Where to
look up information of PostgreSQL backend encoding conversion functions
and how to determine database encoding from C function short of
connecting to SPI manager and issue SQL query?

3. Almost same question about arbitrary precession integer values.
Where to look up C functions to convert decimal/hexadecimal/binary
string representing integer of 128 bits or so into Postgres NUMERIC
type.

4. Is it good idea to use (ANSI standard) VARCHAR arguments or return
values? I see that PostgreSQL specific TEXT type is used more often by
internal PostgreSQL functions.

5. Is there some coding-style guidelines? I'm planning to submit this
patch to pgsql-patches next week, and I want to make it as easily
acceptable as possible.


Second problem
--------------

First of all, application which uses loadable OpenSSL engines have to
read openssl configuration file (or have its own configuration file) to
configure engine properly. This is one-line patch - just add call to 
OPENSSL_config(NULL) before SSL_library_init() and config (either
compiled-in default or specified by OPENSSL_CONF configuration variable) 
would be read and engine loaded and initialized.

Second, there should be some way to specify that private key shouldn't
be loaded from the engine (it might not be actually loaded - most
hardware security modules wouldn't give away private keys, but OpenSSL
would know how to deal with it), not from the file.

I'm planning to use yet another environment variable 
PGSSLKEY=engine_name:key_id

along same lines as PGCLIENTENCODING and OPENSSL_CONF variables are used.
engine_name here is name of hardware support engine used by openssl and
key_id - key identifier only engine knows how to interpret.

Thus using of hardware key storage would be absolutely transparent for
all applications which use libpq.

Main question here is - is an environment variable a good way to pass
such information, or there is better way?

I also thinking about adding same engine support into backend too.
While it is not very probably that somebody would spend money on crypto
hardware just for storage of database server SSL key, development
version of OpenSSL already have support for adding new cryptographic
algorithms via loadable engines, and use of some national cryptography
standard (such as Russian GOST R 34.10-2001) might require using of
loadable engine in the backend.

                        Sincerely yours, Victor Wagner

--