From f271977a024011948b147a6f70d58d07a62d1559 Mon Sep 17 00:00:00 2001 From: Jacob O'Mara Date: Tue, 4 Nov 2025 15:22:15 +0000 Subject: [PATCH] Bug 39224: add database schema for shibboleth configuration Add shibboleth_config and shibboleth_field_mappings tables to store Shibboleth authentication configuration in the database. Includes atomic update to migrate existing XML-based configurations. --- .../bug_39224_add_shibboleth_tables.pl | 152 ++++++++++++++++++ installer/data/mysql/kohastructure.sql | 37 +++++ 2 files changed, 189 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_39224_add_shibboleth_tables.pl diff --git a/installer/data/mysql/atomicupdate/bug_39224_add_shibboleth_tables.pl b/installer/data/mysql/atomicupdate/bug_39224_add_shibboleth_tables.pl new file mode 100755 index 00000000000..b6843641508 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_39224_add_shibboleth_tables.pl @@ -0,0 +1,152 @@ +use Modern::Perl; +use Koha::Installer::Output qw(say_warning say_success say_info); +use XML::Simple; + +return { + bug_number => "39224", + description => "Add tables for storing Shibboleth configs,mappings and migration to db based storage", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + my $xml_config; + my $koha_conf = $ENV{KOHA_CONF}; + if ( $koha_conf && -f $koha_conf ) { + $xml_config = XML::Simple->new->XMLin($koha_conf); + } + + unless ($xml_config) { + say_warning( $out, "Could not load XML config from KOHA_CONF. XML settings will not be migrated." ); + } + + unless ( TableExists('shibboleth_field_mappings') ) { + $dbh->do( + q{ + CREATE TABLE IF NOT EXISTS shibboleth_field_mappings ( + mapping_id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', + idp_field varchar(255) COMMENT 'Field name from the identity provider', + koha_field varchar(255) NOT NULL COMMENT 'Corresponding field in Koha borrowers table', + is_matchpoint tinyint(1) NOT NULL DEFAULT 0 COMMENT 'If this field is used to match existing users', + default_content varchar(255) DEFAULT NULL COMMENT 'Default content for this field if not provided by the IdP', + PRIMARY KEY (mapping_id), + UNIQUE KEY koha_field_idx (koha_field), + KEY idp_field_idx (idp_field) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + } + ); + say_success( $out, "Added new table 'shibboleth_field_mappings'" ); + } + + unless ( TableExists('shibboleth_config') ) { + $dbh->do( + q{ + CREATE TABLE IF NOT EXISTS shibboleth_config ( + shibboleth_config_id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', + force_opac_sso tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Force Shibboleth SSO for OPAC', + force_staff_sso tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Force Shibboleth SSO for staff interface', + autocreate tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Automatically create new patrons', + sync tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Sync patron attributes on login', + welcome tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Send welcome email to new patrons', + PRIMARY KEY (shibboleth_config_id) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + } + ); + say_success( $out, "Added new table 'shibboleth_config'" ); + } + + my $use_shibboleth = 0; + if ( $xml_config && $xml_config->{config} && defined $xml_config->{config}->{useshibboleth} ) { + $use_shibboleth = $xml_config->{config}->{useshibboleth}; + } + + $dbh->do( + q{ + INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) + VALUES ('ShibbolethAuthentication',?,'Enable or disable Shibboleth authentication integration','0|1','YesNo') + }, undef, $use_shibboleth + ); + + my $sth = $dbh->prepare( + "SELECT variable, value FROM systempreferences WHERE variable IN ('staffShibOnly', 'OPACShibOnly')"); + $sth->execute(); + my $old_prefs = {}; + while ( my $row = $sth->fetchrow_hashref ) { + $old_prefs->{ $row->{variable} } = $row->{value}; + } + + if (%$old_prefs) { + my ($config_exists) = $dbh->selectrow_array("SELECT COUNT(*) FROM shibboleth_config"); + + if ( !$config_exists ) { + $dbh->do( + q{ + INSERT INTO shibboleth_config + (force_opac_sso, force_staff_sso) VALUES (?,?) + }, undef, + ( $old_prefs->{OPACShibOnly} || 0 ), + ( $old_prefs->{staffShibOnly} || 0 ) + ); + } + + $dbh->do( + q{ + DELETE FROM systempreferences + WHERE variable IN ('staffShibOnly','OPACShibOnly') + } + ); + say_success( $out, "Migrated and removed deprecated Shibboleth system preferences" ); + } + + if ( $xml_config && $xml_config->{config} && $xml_config->{config}->{shibboleth} ) { + my ($config_exists) = $dbh->selectrow_array("SELECT COUNT(*) FROM shibboleth_config"); + + if ( !$config_exists ) { + $dbh->do( + q{ + INSERT INTO shibboleth_config + (autocreate, sync, welcome) VALUES (?,?,?) + }, undef, + ( $xml_config->{config}->{shibboleth}->{autocreate} || 0 ), + ( $xml_config->{config}->{shibboleth}->{sync} || 0 ), + ( $xml_config->{config}->{shibboleth}->{welcome} || 0 ) + ); + } else { + $dbh->do( + q{ + UPDATE shibboleth_config SET + autocreate = ?, + sync = ?, + welcome = ? + }, undef, + ( $xml_config->{config}->{shibboleth}->{autocreate} || 0 ), + ( $xml_config->{config}->{shibboleth}->{sync} || 0 ), + ( $xml_config->{config}->{shibboleth}->{welcome} || 0 ) + ); + } + + my $mapping = $xml_config->{config}->{shibboleth}->{mapping}; + foreach my $koha_field ( keys %$mapping ) { + my $idp_field = $mapping->{$koha_field}->{is} || undef; + my $default = $mapping->{$koha_field}->{content} || undef; + my $is_matchpoint = ( $koha_field eq $xml_config->{config}->{shibboleth}->{matchpoint} ) ? 1 : 0; + + next unless ( $idp_field || $default ); + + $dbh->do( + q{ + INSERT IGNORE INTO shibboleth_field_mappings + (koha_field, idp_field, default_content, is_matchpoint) + VALUES (?,?,?,?) + }, undef, + $koha_field, + $idp_field, + $default, + $is_matchpoint + ); + } + say_success( $out, "Migrated Shibboleth XML configuration" ); + } + + return 1; + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 998007871e2..36a9df7ff77 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -6026,6 +6026,43 @@ CREATE TABLE `sessions` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `shibboleth_config` +-- + +DROP TABLE IF EXISTS `shibboleth_config`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `shibboleth_config` ( + `shibboleth_config_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', + `force_opac_sso` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Force Shibboleth SSO for OPAC', + `force_staff_sso` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Force Shibboleth SSO for staff interface', + `autocreate` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Automatically create new patrons', + `sync` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Sync patron attributes on login', + `welcome` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Send welcome email to new patrons', + PRIMARY KEY (`shibboleth_config_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `shibboleth_field_mappings` +-- + +DROP TABLE IF EXISTS `shibboleth_field_mappings`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `shibboleth_field_mappings` ( + `mapping_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', + `idp_field` varchar(255) DEFAULT NULL COMMENT 'Field name from the identity provider', + `koha_field` varchar(255) NOT NULL COMMENT 'Corresponding field in Koha borrowers table', + `is_matchpoint` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'If this field is used to match existing users', + `default_content` varchar(255) DEFAULT NULL COMMENT 'Default content for this field if not provided by the IdP', + PRIMARY KEY (`mapping_id`), + UNIQUE KEY `koha_field_idx` (`koha_field`), + KEY `idp_field_idx` (`idp_field`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `sms_providers` -- -- 2.39.5