[mitreid-connect] OIDC Server with PostgreSQL

[Richer, Justin P.]

We'd be happy to include the postgresql table file in the next release. Please submit a pull request with that as well as the appropriate changes needed in the data-context.xml file (properly labeled and commented out).

Thanks!
 -- Justin

On Sep 5, 2014, at 11:09 AM, Marcin Krystek <mkrystek at man.poznan.pl> wrote:

> Hello,
> 
> I would like to share my last two days experience in playing with MITRE OpenId Connect Server. I hope it will be useful for somebody...
> 
> Default OIDC Server release supports two types of database backends: HSQL and MySQL and default configuration uses HSQL. It appears that after some changes in the sql scripts OIDC Server will work also with PostgreSQL.
> 
> In my work I have used mysql_database_tables.sql as a starting point. It contains some MySQL specific clauses, thankfully there are just few of them. All I had to do, was to change:
> MySQL AUTO_INCREMENT clasue to PostreSQL sequencers and triggers,
> MySQL LONGBLOB to PostgreSQL BYTEA type
> MySQL DEFAULT 1 to PostgreSQL true
> MySQL DEFAULT 0 to PostgreSQL false
> 
> Unfortunately, these changes are not enough. The OIDC Server will not be able to retrieve user access token because two attributes has incompatible types. If you look closer to the client_details table you will notice that client_details.id attribute is bigint. In the access_token table client_id attribute is character varying(256) and (based on its value) it refers to client_details.id. It works in HSQL, I didn't test MySQL, but in PostgreSQL it doesn't. When spring framework is trying to execute the following query:
> "select [...] from client_details where client_details.id=access_token.client_id PostgreSQL will throw an exception.
> 
> This can be solved by changing access_token.client_id attribute type to bigint. I didn't perform any sophisticated tests, however after this change I can successfully login to the both OIDC server and to the sample-web-app, and all links/information sites seems to be working properly.
> 
> The example sql script is available in the attachment.
> 
> I have two questions:
> Is this type inconsistency between client_details.id and access_token.client_id intentional or it's a bug and it works just because HSQL and (probably) MySQL do some not explicit type cast?
> Is it safe, from the OIDC Server code point of view, to change access_token.client_id attribute type from varchar to bigint? Maybe I have missed some dependency or I didn't reach some specific scenario when such change will cause problems.
> 
> Cheers,
> Marcin
> 
> 
> <postgres_database_tables.sql>_______________________________________________
> mitreid-connect mailing list
> mitreid-connect at mit.edu
> http://mailman.mit.edu/mailman/listinfo/mitreid-connect