date:20060518

[HACKERS] SSL certificate info on SQL level and HSM support for libpq

2006-05-18 Thread Victor B. Wagner

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.
engi

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

2006-05-18 Thread Thomas Hallgren


Christopher Kings-Lynne wrote:
If you want to get users to swtich to your software from your 
competitors, you have to eliminate barriers, and a big one for any 
database is getting locked into a specific one.  People aren't going 
to take the time to try switching to postgresql if they can't easily 
make it back to thier former database. It's one of the reasons why 
PostgreSQL's standards compliance is so important; if you want to 
swtich to a new database, your best bet is to give PostgreSQL a shot, 
because even if you don't like it, we're not going to try and trap you 
into our software with bunches of non-standard knobs. Low barrier to 
exit == low barrier to entry. 


Another reason why a tool to export from pgsql to mysql is just as 
important as the vice versa...


If that's really true, then let's create a bidirectional compatibility layer as a joint 
venture with people from the MySQL camp. Should be a win-win situation. I somehow doubt that 
is the case. Important yes. But "just as important"? No way.


We would loose big time on the export side since the vendor lock-in aspect is seriously out 
balanced by current levels of standards compliance. On the other hand, we'd win by order of 
magnitude on the import side. I bet the MySQL people would be utterly uninterested in such a 
venture. I think that if anything should be done, we should concentrate on import and let 
the MySQL people worry about going the other way. Once it becomes "just as imporant", they will.


Regards,
Thomas Hallgren



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Toward A Positive Marketing Approach.

2006-05-18 Thread Greg Stark

Michael Dean <[EMAIL PROTECTED]> writes:

> Greetings Guys
> 
> As a newbie person moving away from my technical background to marketing, I
> think a refreshed course for pg is needed!  So far I have read all 5000 or so
> of this month's emails and want to make a few remarks IMHO:
> 
> 1.  We should treat all marketing efforts by hackers/programmers as social
> bugs.  Get some marketing pros (debuggers) in on this, or the popularity of
> postgresql will continue to pale in the real world.

And this is a problem why? You seem to have mistaken this as some sort of
commercial project that needs to return a profit or some sort of evangelical
movement. It is neither. The contributors get the benefit of a good database
which for whatever varied reasons satisfies their needs.

> 8. Stop mentioning mysql in every breath.  It serves them, not pg.  After all,
> mysql must be better, or why would these folks at pg be so specifically,
> vociferously and universally concerned! talk only about pg, make comparisons 
> to
> the whole field of db's, don't single anyone out!

I do agree with this point (though not the rationale). 

Bashing mysql (or Oracle) doesn't really accomplish much to improve Postgres
or help Postgres users. There is some room for "let's avoid the mistakes
others have made" or "learn from what others have done well", but there's an
awful lot of contentless "mysql sucks" threads too. Personally I have "mysql"
killfiled in these lists.

-- 
greg

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

2006-05-18 Thread Thomas Hallgren

Chris Browne wrote:

[EMAIL PROTECTED] ("Marc G. Fournier") writes:

To give someone a running chance at migrating it to PostgreSQL, a
'MySQL compatibility module' would allow them to just plug the
existing DB in, and then work at improving sections of the code over
time ...

Hell, if done well, the module should be able to dump appropriately
'clean' PgSQL schemas ... as in your example elow about the domains ...

You can't have that because you essentially need to throw out four
aspects of fairly vital "data validation" functionality:

1. Dates cease to be validatable.

2. NULL and 0 and '' are all roughly equivalent, even though they
aren't.

3. Foreign key constraints have to be ignored.

4. You have to fabricate a locale offering a case-insensitive sort
order.

I suppose #4 isn't "vital data validation"...

But after you "gut" the PostgreSQL-based system of those four aspects
of data integrity, I'm not sure there's any remaining benefit to
having PostgreSQL in play...

Assuming the objective with a transition would be to improve on things, an alternative
approach could be to offer a three step migration path:

1. A dump/restore utility that dumps a MySQL database and restores it into a PostgreSQL
database. This utility must have plugin capabilities where logic can be added that deals
with cases #1, #2, and #3 above. It might be as simple as just logging incorrect records to
a file and skip them. A pre-defined set of generic plugins could be supplied that did just
that. A user would have the chance to replace them with customized plugins to cover for
special cases in his own app. Perl or PHP would probably be good candidates for plugin language.

2. Provide an add-on to the PostgreSQL parser that would make it accept MySQL syntax. The
database would still run untainted underneath so from this point on, no more invalid dates
or foreign keys can be entered. Some other add-ons are needed as well to cater for some sane
but non-standard MySQL behavior that PostgreSQL is lacking.

3. A good user guide that helps the user to, over time, move away from the non standard
MySQL specific expressions. The objective being to at some point skip the MySQL syntax layer
altogether.

92 matches

Mail list logo