[mitreid-connect] OIDC Server with PostgreSQL

Fri Sep 5 11:09:14 EDT 2014

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

-------------- next part --------------
--
-- Tables for OIDC Server functionality, PostgreSQL
--

CREATE TABLE IF NOT EXISTS access_token (
	id BIGINT NOT NULL,
	token_value VARCHAR(4096),
	expiration TIMESTAMP NULL,
	token_type VARCHAR(256),
	refresh_token_id BIGINT,
	client_id BIGINT,
	auth_holder_id BIGINT,
	id_token_id BIGINT,
	approved_site_id BIGINT
);

CREATE SEQUENCE access_token_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1;

ALTER TABLE access_token ALTER COLUMN id SET DEFAULT nextval('access_token_id_seq');

CREATE TABLE IF NOT EXISTS address (
	id BIGINT NOT NULL,
	formatted VARCHAR(256),
	street_address VARCHAR(256),
	locality VARCHAR(256),
	region VARCHAR(256),
	postal_code VARCHAR(256),
	country VARCHAR(256)
);

CREATE SEQUENCE address_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE address ALTER COLUMN id SET DEFAULT nextval('address_id_seq');

CREATE TABLE IF NOT EXISTS approved_site (
	id BIGINT NOT NULL,
	user_id VARCHAR(4096),
	client_id VARCHAR(4096),
	creation_date TIMESTAMP NULL,
	access_date TIMESTAMP NULL,
	timeout_date TIMESTAMP NULL,
	whitelisted_site_id VARCHAR(256)
);

CREATE SEQUENCE approved_site_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE approved_site ALTER COLUMN id SET DEFAULT nextval('approved_site_id_seq');

CREATE TABLE IF NOT EXISTS approved_site_scope (
	owner_id BIGINT,
	scope VARCHAR(256)
);

CREATE TABLE IF NOT EXISTS authentication_holder (
	id BIGINT NOT NULL,
	owner_id BIGINT,
	authentication BYTEA
);

CREATE SEQUENCE authentication_holder_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE authentication_holder ALTER COLUMN id SET DEFAULT nextval('authentication_holder_id_seq');

CREATE TABLE IF NOT EXISTS client_authority (
	owner_id BIGINT,
	authority BYTEA
);

CREATE TABLE IF NOT EXISTS authorization_code (
	id BIGINT NOT NULL,
	code VARCHAR(256),
	authentication BYTEA
);

CREATE SEQUENCE authorization_code_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE authorization_code ALTER COLUMN id SET DEFAULT nextval('authorization_code_id_seq');

CREATE TABLE IF NOT EXISTS client_grant_type (
	owner_id BIGINT,
	grant_type VARCHAR(2000)
);

CREATE TABLE IF NOT EXISTS client_response_type (
	owner_id BIGINT,
	response_type VARCHAR(2000)
);

CREATE TABLE IF NOT EXISTS blacklisted_site (
	id BIGINT NOT NULL,
	uri VARCHAR(2048)
);

CREATE SEQUENCE blacklisted_site_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE blacklisted_site ALTER COLUMN id SET DEFAULT nextval('blacklisted_site_id_seq');

CREATE TABLE IF NOT EXISTS client_details (
	id BIGINT NOT NULL,

	client_description VARCHAR(1024),
	reuse_refresh_tokens BOOLEAN NOT NULL DEFAULT true,
	dynamically_registered BOOLEAN NOT NULL DEFAULT false,
	allow_introspection BOOLEAN NOT NULL DEFAULT false,
	id_token_validity_seconds BIGINT NOT NULL DEFAULT 600,

	client_id VARCHAR(256),
	client_secret VARCHAR(2048),
	access_token_validity_seconds BIGINT,
	refresh_token_validity_seconds BIGINT,

	application_type VARCHAR(256),
	client_name VARCHAR(256),
	token_endpoint_auth_method VARCHAR(256),
	subject_type VARCHAR(256),

	logo_uri VARCHAR(2048),
	policy_uri VARCHAR(2048),
	client_uri VARCHAR(2048),
	tos_uri VARCHAR(2048),

	jwks_uri VARCHAR(2048),
	sector_identifier_uri VARCHAR(2048),

	request_object_signing_alg VARCHAR(256),

	user_info_signed_response_alg VARCHAR(256),
	user_info_encrypted_response_alg VARCHAR(256),
	user_info_encrypted_response_enc VARCHAR(256),

	id_token_signed_response_alg VARCHAR(256),
	id_token_encrypted_response_alg VARCHAR(256),
	id_token_encrypted_response_enc VARCHAR(256),

	token_endpoint_auth_signing_alg VARCHAR(256),

	default_max_age BIGINT,
	require_auth_time BOOLEAN,
	created_at TIMESTAMP NULL,
	initiate_login_uri VARCHAR(2048),
	post_logout_redirect_uri VARCHAR(2048),
	unique(client_id)
);

CREATE SEQUENCE client_details_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE client_details ALTER COLUMN id SET DEFAULT nextval('client_details_id_seq');

CREATE TABLE IF NOT EXISTS client_request_uri (
	owner_id BIGINT,
	request_uri VARCHAR(2000)
);

CREATE TABLE IF NOT EXISTS client_default_acr_value (
	owner_id BIGINT,
	default_acr_value VARCHAR(2000)
);

CREATE TABLE IF NOT EXISTS client_contact (
	owner_id BIGINT,
	contact VARCHAR(256)
);

CREATE TABLE IF NOT EXISTS client_redirect_uri (
	owner_id BIGINT, 
	redirect_uri VARCHAR(2048) 
);

CREATE TABLE IF NOT EXISTS refresh_token (
	id BIGINT NOT NULL,
	token_value VARCHAR(4096),
	expiration TIMESTAMP NULL,
	auth_holder_id BIGINT,
	client_id VARCHAR(256)
);

CREATE SEQUENCE refresh_token_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE refresh_token ALTER COLUMN id SET DEFAULT nextval('refresh_token_id_seq');

CREATE TABLE IF NOT EXISTS client_resource (
	owner_id BIGINT, 
	resource_id VARCHAR(256) 
);

CREATE TABLE IF NOT EXISTS client_scope (
	owner_id BIGINT,
	scope VARCHAR(2048)
);

CREATE TABLE IF NOT EXISTS token_scope (
	owner_id BIGINT,
	scope VARCHAR(2048)
);

CREATE TABLE IF NOT EXISTS system_scope (
	id BIGINT NOT NULL,
	scope VARCHAR(256) NOT NULL,
	description VARCHAR(4096),
	icon VARCHAR(256),
	allow_dyn_reg BOOLEAN NOT NULL DEFAULT false,
	default_scope BOOLEAN NOT NULL DEFAULT false,
	structured BOOLEAN NOT NULL DEFAULT false,
	structured_param_description VARCHAR(256),
	unique(scope)
);

CREATE SEQUENCE system_scope_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE system_scope ALTER COLUMN id SET DEFAULT nextval('system_scope_id_seq');

CREATE TABLE IF NOT EXISTS user_info (
	id BIGINT NOT NULL,
	sub VARCHAR(256),
	preferred_username VARCHAR(256),
	name VARCHAR(256),
	given_name VARCHAR(256),
	family_name VARCHAR(256),
	middle_name VARCHAR(256),
	nickname VARCHAR(256),
	profile VARCHAR(256),
	picture VARCHAR(256),
	website VARCHAR(256),
	email VARCHAR(256),
	email_verified BOOLEAN,
	gender VARCHAR(256),
	zone_info VARCHAR(256),
	locale VARCHAR(256),
	phone_number VARCHAR(256),
	phone_number_verified BOOLEAN,
	address_id VARCHAR(256),
	updated_time VARCHAR(256),
	birthdate VARCHAR(256)
);

CREATE SEQUENCE user_info_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE user_info ALTER COLUMN id SET DEFAULT nextval('user_info_id_seq');

CREATE TABLE IF NOT EXISTS whitelisted_site (
	id BIGINT NOT NULL,
	creator_user_id VARCHAR(256),
	client_id VARCHAR(256)
);

CREATE SEQUENCE whitelisted_site_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE whitelisted_site ALTER COLUMN id SET DEFAULT nextval('whitelisted_site_id_seq');

CREATE TABLE IF NOT EXISTS whitelisted_site_scope (
	owner_id BIGINT,
	scope VARCHAR(256)
);

CREATE TABLE IF NOT EXISTS pairwise_identifier (
	id BIGINT NOT NULL,
	identifier VARCHAR(256),
	sub VARCHAR(256),
	sector_identifier VARCHAR(2048)
);

CREATE SEQUENCE pairwise_identifier_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

ALTER TABLE pairwise_identifier ALTER COLUMN id SET DEFAULT nextval('pairwise_identifier_id_seq');

ALTER TABLE ONLY access_token
    ADD CONSTRAINT access_token_pk PRIMARY KEY (id);

ALTER TABLE ONLY address
    ADD CONSTRAINT address_pk PRIMARY KEY (id);

ALTER TABLE ONLY approved_site
    ADD CONSTRAINT approved_site_pk PRIMARY KEY (id);

ALTER TABLE ONLY authentication_holder
    ADD CONSTRAINT authentication_holder_pk PRIMARY KEY (id);

ALTER TABLE ONLY authorization_code
    ADD CONSTRAINT authorization_code_pk PRIMARY KEY (id);

ALTER TABLE ONLY blacklisted_site
    ADD CONSTRAINT blacklisted_site_pk PRIMARY KEY (id);

ALTER TABLE ONLY client_details
    ADD CONSTRAINT client_details_pk PRIMARY KEY (id);

ALTER TABLE ONLY refresh_token
    ADD CONSTRAINT refresh_token_pk PRIMARY KEY (id);

ALTER TABLE ONLY system_scope
    ADD CONSTRAINT system_scope_pk PRIMARY KEY (id);

ALTER TABLE ONLY user_info
    ADD CONSTRAINT user_info_pk PRIMARY KEY (id);

ALTER TABLE ONLY whitelisted_site
    ADD CONSTRAINT whitelisted_site_pk PRIMARY KEY (id);

ALTER TABLE ONLY pairwise_identifier
    ADD CONSTRAINT pairwise_identifier_pk PRIMARY KEY (id);