Versions Compared

Old Version 4

changes.mady.by.user Niels van Dijk

Saved on

compared with

New Version Current

changes.mady.by.user Héder Mihály

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

For scalability we want Comanage to be decoupled from the AAs, so we can scale the AA separately (see technical design). We will use a MySQL database in master-slave mode to achieve this. The master database will be connected to CoManage, whereas the slaves (1-n) will be on the VMs that provide the AA capabilities. To avoid possible issue with the MySQL data model of the Comanage application, we will decouple the data model from CoManage from the data model we will use for the AAs. In addition this allows us to tune and tweak these databases specifically for their tasks (read/write vs write once, read many). To transfer teh the data from the CoManage database to the AA database we will use a standard feature of Comanage called the provisioning plugins. A provisioning plugin may be defined for a VO to deliver in real time changes to the data that is being managed via CoManage into a so called "provisioning target". We will define the same plugin for all VOs, which will provision data to the AA database which lives on the Master MySQL database, which will act as our provisioning target. The mysql master-slave mechanism will then peculate the changes towards the slave nodes.

...

Because we cannot beforehand know how many attribute values we need to store for a given user (multiple emails, group memberships, entitlements) we will store the AA data in a flat table that mimics a key value store.

Database CO_AA

Table attribute holds a lookup table of the attributes we support.

Code Block
CREATE TABLE `attribute` (
  `a_id` int(11) NOT NULL,
  `a_name` varchar(256) DEFAULT NULL,
  `a_oid` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`a_id`)
)
Code Block
INSERT INTO `a_type` VALUES (1,'eduPersonAffiliation','urn:oid:1.3.6.1.4.1.5923.1.1.1.1'),(2,'eduPersonEntitlement','urn:oid:1.3.6.1.4.1.5923.1.1.1.7'),(3,'eduPersonPrincipalName','urn:oid:1.3.6.1.4.1.5923.1.1.1.6'),(4,'eduPersonScopedAffiliation','urn:oid:1.3.6.1.4.1.5923.1.1.1.9'),(5,'eduPersonUniqueId','urn:oid:1.3.6.1.4.1.5923.1.1.1.13'),(6,'eduPersonOrcid','urn:oid:1.3.6.1.4.1.5923.1.1.1.16'),(7,'cn','urn:oid:2.5.4.3'),(8,'description','urn:oid:2.5.4.13'),(9,'displayName','urn:oid:2.16.840.1.113730.3.1.241'),(10,'facsimileTelephoneNumber','urn:oid:2.5.4.23'),(11,'givenName','urn:oid:2.5.4.42'),(12,'l','urn:oid:2.5.4.7'),(13,'labeledURI','urn:oid:1.3.6.1.4.1.250.1.57'),(14,'mail','urn:oid:0.9.2342.19200300.100.1.3'),(15,'mobile','urn:oid:0.9.2342.19200300.100.1.41'),(16,'o','urn:oid:2.5.4.10'),(17,'ou','urn:oid:2.5.4.11'),(18,'postalAddress','urn:oid:2.5.4.16'),(19,'postalCode','urn:oid:2.5.4.17'),(20,'sn','urn:oid:2.5.4.4'),(21,'st','urn:oid:2.5.4.8'),(22,'street','urn:oid:2.5.4.9'),(23,'telephoneNumber','urn:oid:2.5.4.20'),(24,'title','urn:oid:2.5.4.12'),(25,'uid','urn:oid:0.9.2342.19200300.100.1.1'),(26,'isMemberOf','urn:oid:1.3.6.1.4.1.5923.1.5.1.1');

 

Table user is a lookup table for the identifier of the users we have data for

Code Block
CREATE TABLE `user` (
  `u_id` int(11) NOT NULL,
  `u_identifier` varchar(256) NOT NULL,
  PRIMARY KEY (`uid`),
  KEY `u_identifier` (`u_identifier`)
)

 

The table vo is a lookup table that holds the collaborative organisations that we have data for

Code Block
CREATE TABLE `vo` (
  `vo_id` int(11) NOT NULL,
  `vo_identifier` varchar(256) NOT NULL,
  PRIMARY KEY (`vid`)
)

 

The attr provides a key - value pair based table of the attributes we have per user and per VO

Code Block
CREATE TABLE `attr` (
  `attr_values_id` int(11) NOT NULL AUTO_INCREMENT,
  `v`vo_id` int(11) NOT NULL,
  `u_id` int(11) NOT NULL,
  `a_id` int(11) NOT NULL,
  `a_val` varchar(2048) DEFAULT NULL,
  PRIMARY KEY (`attr_values_id`),
  KEY `v_id` (`v_id`,`u_id`)
)

For the AA we need a per user, per VO representation of the attributes. To this end we create a view user_attributes that will be queried by the aa.

Code Block
CREATE VIEW `user_attributes` AS select `u`.`user_identifier` AS `user_identifier`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.1'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.1`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.7'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.7`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.6'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.6`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.9'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.9`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.13'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.13`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.16'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.16`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.3'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.3`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.13'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.13`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.16.840.1.113730.3.1.241'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.16.840.1.113730.3.1.241`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.23'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.23`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.42'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.42`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.7'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.7`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.250.1.57'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.250.1.57`,group_concat(if((`atype`.`a_oid` = 'urn:oid:0.9.2342.19200300.100.1.3'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:0.9.2342.19200300.100.1.3`,group_concat(if((`atype`.`a_oid` = 'urn:oid:0.9.2342.19200300.100.1.41'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:0.9.2342.19200300.100.1.41`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.10'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.10`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.11'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.11`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.16'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.16`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.17'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.17`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.4'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.4`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.8'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.8`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.9'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.9`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.20'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.20`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.12'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.12`,group_concat(if((`atype`.`a_oid` = 'urn:oid:0.9.2342.19200300.100.1.1'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:0.9.2342.19200300.100.1.1`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.5.1.1'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.5.1.1` from (((`attr` `a` left join `a_type` `atype` on((`a`.`a_id` = `atype`.`a_id`))) left join `user` `u` on((`a`.`u_id` = `u`.`uid`))) left join `vo` `v` on((`a`.`v_id` = `v`.`vid`))) order by `atype`.`a_oid`

Implementation of AA with SimpleSAMLphp (plan)

We can start out with a stock simpleSAMLphp. Currently (20 June, 2016) this is version 1.14.4. : https://simplesamlphp.org/

The "Attribute Authority" module has to be added to this installation: https://github.com/NIIF/simplesamlphp-module-aa

This provides the functionality for handling Attribute Queries.

After that, we need to create a new Authentication Processing Filter by extending SimpleSAML_Auth_ProcessingFilter. The best way to implement this is to create a new module called "comanage" or similar. 

The important part of the code should look like this:

Code Block
class sspmod_comanage_Auth_Process_Comanage extends SimpleSAML_Auth_ProcessingFilter
{
	public function __construct($config, $reserved) {
		//fetch config
	}

    

...

 public function process(&$state) {
        $nameId = $state['Attributes'][(configured nameId)][0];
		
		$spid = $state['Destination']['entityid'];
        // database lookup comes here
        $state['Attributes'] = //set the attributes to return
     }
}

Then ssp has to be configured to use the newly created module in config/config.php

Code Block
   authproc.aa = array(
       ...
       '60' => array(
            'class' => 'comanage:Comanage',
            'nameId_attribute_name' =>  'subject_nameid', // look at the aa authsource config
            'comanage_db_host' =>          '()',
			'comanage_db_port' =>          '()',
			'comanage_db_dbname' =>          '()',
			'comanage_db_user' =>          '()',
			'comanage_db_password' =>          '()'
       ),

Implementation of AA with SimpleSAMLphp v0.1 (based on plan above)

The database scheme in use is as follows:

Table and view definitions:

Code Block
CREATE TABLE `attr` (
  `attr_values_id` int(11) NOT NULL,
  `vo_id` int(11) NOT NULL,
  `u_id` int(11) NOT NULL,
  `a_id` int(11) NOT NULL,
  `a_val` varchar(2048) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `attribute_defs` (
  `a_id` int(11) NOT NULL,
  `a_name` varchar(256) DEFAULT NULL,
  `a_oid` varchar(256) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sp` (
  `sp_id` int(11) NOT NULL,
  `entity_id` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `spvo` (
  `spvo_id` int(11) NOT NULL,
  `sp_id` int(11) NOT NULL,
  `vo_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `u_id` int(11) NOT NULL,
  `u_identifier` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `vo` (
  `vo_id` int(11) NOT NULL,
  `vo_identifier` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The view that makes it possible to query everything by one SELECT statement

Code Block
DROP TABLE IF EXISTS `attributeValuesView`;
CREATE ALGORITHM=UNDEFINED DEFINER=`vopaas`@`localhost` SQL SECURITY DEFINER VIEW `attributeValuesView`  AS  select `attribute_defs`.`a_name` AS `attrName`,`attribute_defs`.`a_oid` AS `oid`,`attr`.`a_val` AS `value`,`user`.`u_identifier` AS `userNameId`,`sp`.`entity_id` AS `spEntityId`,`vo`.`vo_identifier` AS `vo` from (((((`attr` join `attribute_defs` on((`attr`.`a_id` = `attribute_defs`.`a_id`))) join `vo` on((`attr`.`vo_id` = `vo`.`vo_id`))) join `user` on((`attr`.`u_id` = `user`.`u_id`))) join `spvo` on((`vo`.`vo_id` = `spvo`.`vo_id`))) join `sp` on((`spvo`.`sp_id` = `sp`.`sp_id`))) ;

Indexes and auto_increment id declarations:

Code Block
ALTER TABLE `attr`
  ADD PRIMARY KEY (`attr_values_id`),
  ADD KEY `v_id` (`vo_id`,`u_id`);

ALTER TABLE `attribute_defs`
  ADD PRIMARY KEY (`a_id`);

ALTER TABLE `sp`
  ADD PRIMARY KEY (`sp_id`),
  ADD KEY `entity_id` (`entity_id`);

ALTER TABLE `spvo`
  ADD PRIMARY KEY (`spvo_id`);

ALTER TABLE `user`
  ADD PRIMARY KEY (`u_id`),
  ADD KEY `u_identifier` (`u_identifier`);

ALTER TABLE `vo`
  ADD PRIMARY KEY (`vo_id`);

ALTER TABLE `attr`
  MODIFY `attr_values_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
ALTER TABLE `sp`
  MODIFY `sp_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `spvo`
  MODIFY `spvo_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `user`
  MODIFY `u_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `vo`
  MODIFY `vo_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

How it is selected:

Code Block
SELECT oid,value FROM attributeValuesView WHERE userNameId=? AND spEntityId=?

GitLab link to the php code: https://dev.niif.hu/vopaas/vopaas-ssp-module

Ansible script for deployment:

Code Block
---
# Deploys the vopaas simplesamlphp module that is handling the database connection to vopaas database
# requires a simplesamlphp installation + niif simplesamlphp aa module
- hosts: vopaas-aa

  tasks:
  - name: download vopaas-module files
    unarchive: src=vopaas-ssp-module.tar.gz dest={{ssp_path}}/modules

  - name: enable vopaas-module in configuration
    blockinfile:
      dest: "{{ssp_path}}/config/config.php"
      # this line is chosen because it is before the authproc.idp segment
      insertafter: "shib13.signresponse"
      block: |
            'authproc.aa' => array(
                '60' => array(
                     'class' => 'vopaas:Vopaas',
                     'nameId_attribute_name' =>  'eduPersonPrincipalName',
                     'vopaas_db_host' => '{{vopaas_db_host}}',
                     'vopaas_db_port' => '{{vopaas_db_port}}',
                     'vopaas_db_dbname' => '{{vopaas_db_dbname}}',
                     'vopaas_db_user' => '{{vopaas_db_user}}',
                     'vopaas_db_password' => '{{vopaas_db_password}}'
                ),
                90 => 'core:LanguageAdaptor',
            ),

...