FAQ
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, because
I 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 connection
function 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 into database
encoding. I think it is safe to assume that database can
represent characters used in it's user's names.

fieldname would be something like 'commonName' or
'emailAddress' and looked up using OpenSSL object names API.
function ssl_cert_issuer(VARCHAR fieldname) returns VARCHAR;
- does the same for certificate issuer.

function ssl_cert_serial() returns NUMERIC;
- returns certificate serial number. NUMERIC type is used here
instead of 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

--

Search Discussions

  • Martijn van Oosterhout at May 19, 2006 at 8:02 am

    On Fri, May 19, 2006 at 10:33:52AM +0400, Victor B. Wagner wrote:
    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?
    You should be able to have these in a contrib module, Postgres exports
    all symbols.
    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?
    Look up the docs for encodings, but there are functions to convert
    strings.
    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.
    numeric_in converts a text string to a numeric. Don't know about 128
    bit strings though.
    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.
    TEXT is VARCHAR minus the length check.
    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.
    There is a coding style somewhere, I think... Have you read the
    developers FAQ?

    --
    Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
    From each according to his ability. To each according to his ability to litigate.
  • Victor B. Wagner at May 22, 2006 at 6:56 am

    On 2006.05.19 at 10:02:34 +0200, Martijn van Oosterhout wrote:
    On Fri, May 19, 2006 at 10:33:52AM +0400, Victor B. Wagner wrote:
    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?
    You should be able to have these in a contrib module, Postgres exports
    all symbols.
    Thanks, it really works this way.

    BTW, what should I do if I want to submit entirely new directory into
    contrib - shall I make a patch against current source tree or just
    archive of this directory would do?
    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.
    numeric_in converts a text string to a numeric. Don't know about 128
    bit strings though.
    This works. But I was unable to look up information about this function
    in the docs, and have to resort to looking for its usage in the backend
    sources.

    I've also unable to find useful description of DirectFunctionCallN
    family of functions, which seems to be neccessary to call one 'C'
    language PostgreSQL function from another.

    It is clear enough from examples in the sources, but...
  • Martijn van Oosterhout at May 22, 2006 at 7:05 am

    On Mon, May 22, 2006 at 10:55:59AM +0400, Victor B. Wagner wrote:
    BTW, what should I do if I want to submit entirely new directory into
    contrib - shall I make a patch against current source tree or just
    archive of this directory would do?
    Diffs are better, unless it really is a lot of code.
    numeric_in converts a text string to a numeric. Don't know about 128
    bit strings though.
    This works. But I was unable to look up information about this function
    in the docs, and have to resort to looking for its usage in the backend
    sources.
    Every type has conversion functions for cstring-to-type and
    type-to-cstring, which used when parsing queries. You can use these
    functions in normal code also. timestamp_in/out, data_in/out, etc...
    I've also unable to find useful description of DirectFunctionCallN
    family of functions, which seems to be neccessary to call one 'C'
    language PostgreSQL function from another.

    It is clear enough from examples in the sources, but...
    Hmm, documentation patches welcome...

    Have a nice day,
    --
    Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
    From each according to his ability. To each according to his ability to litigate.
  • Tom Lane at May 22, 2006 at 2:41 pm

    Martijn van Oosterhout writes:
    On Mon, May 22, 2006 at 10:55:59AM +0400, Victor B. Wagner wrote:
    I've also unable to find useful description of DirectFunctionCallN
    family of functions, which seems to be neccessary to call one 'C'
    language PostgreSQL function from another.

    It is clear enough from examples in the sources, but...
    Hmm, documentation patches welcome...
    The documentation exists: see src/backend/utils/fmgr/README and the
    source-code comments in fmgr.c.

    Something that's been on the TODO list for a long time is to recast
    this and other README files into part of the main SGML docs. There
    will never be any substitute for reading the code though ...

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 19, '06 at 6:56a
activeMay 22, '06 at 2:41p
posts5
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase