|
Line 0
Link Here
|
|
|
1 |
use Modern::Perl; |
| 2 |
use Koha::Installer::Output qw(say_warning say_success say_info); |
| 3 |
use JSON; |
| 4 |
|
| 5 |
return { |
| 6 |
bug_number => "39224", |
| 7 |
description => |
| 8 |
"Unified identity providers: normalized mappings, hostname-based selection, SAML2/Shibboleth migration", |
| 9 |
up => sub { |
| 10 |
my ($args) = @_; |
| 11 |
my ( $dbh, $out ) = @$args{qw(dbh out)}; |
| 12 |
|
| 13 |
# ── 1. Update identity_providers.protocol enum ───────────────────────────── |
| 14 |
# Remove CAS and LDAP (unsupported via this interface), add SAML2. |
| 15 |
|
| 16 |
if ( column_exists( 'identity_providers', 'protocol' ) ) { |
| 17 |
my ($current_type) = $dbh->selectrow_array( |
| 18 |
"SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS |
| 19 |
WHERE TABLE_SCHEMA = DATABASE() |
| 20 |
AND TABLE_NAME = 'identity_providers' |
| 21 |
AND COLUMN_NAME = 'protocol'" |
| 22 |
); |
| 23 |
|
| 24 |
unless ( $current_type && $current_type eq "enum('OAuth','OIDC','SAML2')" ) { |
| 25 |
|
| 26 |
# Warn about and remove any providers using protocols no longer in the enum. |
| 27 |
for my $protocol (qw(CAS LDAP)) { |
| 28 |
my ($count) = $dbh->selectrow_array( |
| 29 |
"SELECT COUNT(*) FROM identity_providers WHERE protocol = ?", |
| 30 |
undef, $protocol |
| 31 |
); |
| 32 |
if ($count) { |
| 33 |
say_warning( |
| 34 |
$out, |
| 35 |
"Removing $count identity provider(s) with protocol '$protocol'" |
| 36 |
. " (no longer supported via this interface)" |
| 37 |
); |
| 38 |
$dbh->do( |
| 39 |
"DELETE FROM identity_providers WHERE protocol = ?", |
| 40 |
undef, $protocol |
| 41 |
); |
| 42 |
} |
| 43 |
} |
| 44 |
|
| 45 |
$dbh->do( |
| 46 |
q{ |
| 47 |
ALTER TABLE identity_providers |
| 48 |
MODIFY COLUMN `protocol` |
| 49 |
enum('OAuth','OIDC','SAML2') |
| 50 |
COLLATE utf8mb4_unicode_ci NOT NULL |
| 51 |
COMMENT 'Protocol provider speaks' |
| 52 |
} |
| 53 |
); |
| 54 |
say_success( |
| 55 |
$out, |
| 56 |
"Updated identity_providers.protocol enum to ('OAuth','OIDC','SAML2')" |
| 57 |
); |
| 58 |
} |
| 59 |
} |
| 60 |
|
| 61 |
# ── 2. Add enabled column to identity_providers ──────────────────────────── |
| 62 |
|
| 63 |
unless ( column_exists( 'identity_providers', 'enabled' ) ) { |
| 64 |
$dbh->do( |
| 65 |
q{ |
| 66 |
ALTER TABLE identity_providers |
| 67 |
ADD COLUMN `enabled` tinyint(1) NOT NULL DEFAULT 1 |
| 68 |
COMMENT 'Whether this provider is active' |
| 69 |
AFTER `config` |
| 70 |
} |
| 71 |
); |
| 72 |
say_success( $out, "Added column 'identity_providers.enabled'" ); |
| 73 |
} |
| 74 |
|
| 75 |
# ── 3. Drop matchpoint from identity_providers (moved to identity_provider_hostnames) ── |
| 76 |
|
| 77 |
if ( column_exists( 'identity_providers', 'matchpoint' ) ) { |
| 78 |
$dbh->do("ALTER TABLE identity_providers DROP COLUMN `matchpoint`"); |
| 79 |
say_success( $out, "Dropped column 'identity_providers.matchpoint' (moved to hostname level)" ); |
| 80 |
} |
| 81 |
|
| 82 |
# ── 4. Create identity_provider_mappings table ───────────────────────────── |
| 83 |
|
| 84 |
unless ( TableExists('identity_provider_mappings') ) { |
| 85 |
$dbh->do( |
| 86 |
q{ |
| 87 |
CREATE TABLE `identity_provider_mappings` ( |
| 88 |
`mapping_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', |
| 89 |
`identity_provider_id` int(11) NOT NULL COMMENT 'Reference to identity provider', |
| 90 |
`provider_field` varchar(255) DEFAULT NULL COMMENT 'Attribute name from the identity provider', |
| 91 |
`koha_field` varchar(255) NOT NULL COMMENT 'Corresponding field in Koha borrowers table', |
| 92 |
`default_content` varchar(255) DEFAULT NULL COMMENT 'Default value if provider does not supply this field', |
| 93 |
PRIMARY KEY (`mapping_id`), |
| 94 |
UNIQUE KEY `provider_koha_field` (`identity_provider_id`, `koha_field`), |
| 95 |
KEY `provider_field_idx` (`provider_field`), |
| 96 |
CONSTRAINT `idp_mapping_ibfk_1` FOREIGN KEY (`identity_provider_id`) |
| 97 |
REFERENCES `identity_providers` (`identity_provider_id`) ON DELETE CASCADE |
| 98 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
| 99 |
} |
| 100 |
); |
| 101 |
say_success( $out, "Added new table 'identity_provider_mappings'" ); |
| 102 |
} |
| 103 |
|
| 104 |
# ── 5. Migrate OAuth/OIDC JSON mappings to normalized rows ───────────────── |
| 105 |
# The old identity_providers table stored mappings as a JSON blob and the |
| 106 |
# matchpoint as a simple column value; both are preserved in the new schema. |
| 107 |
|
| 108 |
if ( column_exists( 'identity_providers', 'mapping' ) ) { |
| 109 |
my $providers = $dbh->selectall_arrayref( |
| 110 |
"SELECT identity_provider_id, mapping FROM identity_providers |
| 111 |
WHERE mapping IS NOT NULL AND mapping != '' AND mapping != '{}'", |
| 112 |
{ Slice => {} } |
| 113 |
); |
| 114 |
|
| 115 |
my $migrated = 0; |
| 116 |
for my $provider (@$providers) { |
| 117 |
my $mapping_json = $provider->{mapping}; |
| 118 |
my $provider_id = $provider->{identity_provider_id}; |
| 119 |
|
| 120 |
my $mapping; |
| 121 |
eval { $mapping = decode_json($mapping_json) }; |
| 122 |
next unless $mapping && ref $mapping eq 'HASH'; |
| 123 |
|
| 124 |
for my $koha_field ( keys %$mapping ) { |
| 125 |
my $provider_field = $mapping->{$koha_field}; |
| 126 |
|
| 127 |
$dbh->do( |
| 128 |
q{ |
| 129 |
INSERT IGNORE INTO identity_provider_mappings |
| 130 |
(identity_provider_id, provider_field, koha_field) |
| 131 |
VALUES (?, ?, ?) |
| 132 |
}, undef, |
| 133 |
$provider_id, $provider_field, $koha_field |
| 134 |
); |
| 135 |
$migrated++; |
| 136 |
} |
| 137 |
} |
| 138 |
|
| 139 |
say_success( |
| 140 |
$out, |
| 141 |
"Migrated $migrated OAuth/OIDC field mapping(s) to identity_provider_mappings" |
| 142 |
); |
| 143 |
|
| 144 |
$dbh->do("ALTER TABLE identity_providers DROP COLUMN `mapping`"); |
| 145 |
say_success( $out, "Dropped column 'identity_providers.mapping'" ); |
| 146 |
} |
| 147 |
|
| 148 |
# ── 5. Add send_welcome_email to identity_provider_domains ──────────────── |
| 149 |
|
| 150 |
unless ( column_exists( 'identity_provider_domains', 'send_welcome_email' ) ) { |
| 151 |
$dbh->do( |
| 152 |
q{ |
| 153 |
ALTER TABLE identity_provider_domains |
| 154 |
ADD COLUMN `send_welcome_email` tinyint(1) NOT NULL DEFAULT 0 |
| 155 |
COMMENT 'Send welcome email to patron on first login' |
| 156 |
AFTER `auto_register_staff` |
| 157 |
} |
| 158 |
); |
| 159 |
say_success( $out, "Added column 'identity_provider_domains.send_welcome_email'" ); |
| 160 |
} |
| 161 |
|
| 162 |
# ── 6. Create canonical hostnames table ──────────────────────────────────── |
| 163 |
|
| 164 |
unless ( TableExists('hostnames') ) { |
| 165 |
$dbh->do( |
| 166 |
q{ |
| 167 |
CREATE TABLE `hostnames` ( |
| 168 |
`hostname_id` int(11) NOT NULL AUTO_INCREMENT |
| 169 |
COMMENT 'Unique identifier for this hostname', |
| 170 |
`hostname` varchar(255) NOT NULL |
| 171 |
COMMENT 'Server hostname string', |
| 172 |
PRIMARY KEY (`hostname_id`), |
| 173 |
UNIQUE KEY `hostname` (`hostname`) |
| 174 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
| 175 |
COMMENT='Canonical hostname registry for identity provider selection' |
| 176 |
} |
| 177 |
); |
| 178 |
say_success( $out, "Added new table 'hostnames'" ); |
| 179 |
|
| 180 |
# Seed reserved rows: hostname_id=1 for OPACBaseURL, hostname_id=2 for staffClientBaseURL. |
| 181 |
for my $info ( [ 'OPACBaseURL', 1 ], [ 'staffClientBaseURL', 2 ] ) { |
| 182 |
my ( $pref_name, $id ) = @$info; |
| 183 |
my ($url) = $dbh->selectrow_array( |
| 184 |
"SELECT value FROM systempreferences WHERE LOWER(variable) = LOWER(?)", |
| 185 |
undef, $pref_name |
| 186 |
); |
| 187 |
my ($hostname) = ( $url // '' ) =~ m{^https?://([^/:?#]+)} or next; |
| 188 |
$dbh->do( |
| 189 |
"INSERT IGNORE INTO hostnames (hostname_id, hostname) VALUES (?, ?)", |
| 190 |
undef, $id, $hostname |
| 191 |
); |
| 192 |
say_success( $out, "Seeded hostname_id=$id from $pref_name ($hostname)" ); |
| 193 |
} |
| 194 |
} |
| 195 |
|
| 196 |
# ── 7. Create/upgrade identity_provider_hostnames bridge table ───────────── |
| 197 |
|
| 198 |
unless ( TableExists('identity_provider_hostnames') ) { |
| 199 |
$dbh->do( |
| 200 |
q{ |
| 201 |
CREATE TABLE `identity_provider_hostnames` ( |
| 202 |
`identity_provider_hostname_id` int(11) NOT NULL AUTO_INCREMENT |
| 203 |
COMMENT 'unique key, used to identify the hostname entry', |
| 204 |
`hostname_id` int(11) NOT NULL |
| 205 |
COMMENT 'FK to hostnames table', |
| 206 |
`identity_provider_id` int(11) NOT NULL |
| 207 |
COMMENT 'Identity provider associated with this hostname', |
| 208 |
`is_enabled` tinyint(1) NOT NULL DEFAULT 1 |
| 209 |
COMMENT 'Whether this hostname is active for this provider', |
| 210 |
`force_sso` tinyint(1) NOT NULL DEFAULT 0 |
| 211 |
COMMENT 'Force SSO redirect for users on this hostname', |
| 212 |
`matchpoint` varchar(255) DEFAULT NULL |
| 213 |
COMMENT 'Koha field used to match incoming users against existing patrons', |
| 214 |
PRIMARY KEY (`identity_provider_hostname_id`), |
| 215 |
UNIQUE KEY `hostname_id_provider` (`hostname_id`, `identity_provider_id`), |
| 216 |
KEY `idp_hostname_provider_idx` (`identity_provider_id`), |
| 217 |
CONSTRAINT `fk_iph_hostname` FOREIGN KEY (`hostname_id`) |
| 218 |
REFERENCES `hostnames` (`hostname_id`) |
| 219 |
ON DELETE CASCADE ON UPDATE RESTRICT, |
| 220 |
CONSTRAINT `idp_hostname_ibfk_1` FOREIGN KEY (`identity_provider_id`) |
| 221 |
REFERENCES `identity_providers` (`identity_provider_id`) |
| 222 |
ON DELETE CASCADE ON UPDATE RESTRICT |
| 223 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
| 224 |
COMMENT='Maps server hostnames to identity providers (many-to-many).' |
| 225 |
} |
| 226 |
); |
| 227 |
say_success( $out, "Added new table 'identity_provider_hostnames'" ); |
| 228 |
} |
| 229 |
|
| 230 |
unless ( column_exists( 'identity_provider_hostnames', 'matchpoint' ) ) { |
| 231 |
$dbh->do( |
| 232 |
q{ |
| 233 |
ALTER TABLE identity_provider_hostnames |
| 234 |
ADD COLUMN `matchpoint` varchar(255) DEFAULT NULL |
| 235 |
COMMENT 'Koha field used to match incoming users against existing patrons' |
| 236 |
} |
| 237 |
); |
| 238 |
say_success( $out, "Added column 'identity_provider_hostnames.matchpoint'" ); |
| 239 |
} |
| 240 |
|
| 241 |
# ── 8. Migrate Shibboleth config from kha-conf.xml to identity_providers ────────────────────── |
| 242 |
# Reads useshibboleth from koha-conf.xml and migrates it to a DB syspref. |
| 243 |
# Also migrates the <shibboleth> mapping config to a SAML2 identity provider. |
| 244 |
|
| 245 |
{ |
| 246 |
require C4::Context; |
| 247 |
my $use_shib = C4::Context->config('useshibboleth') // 0; |
| 248 |
|
| 249 |
if ($use_shib) { |
| 250 |
my $shib_cfg = C4::Context->config('shibboleth'); |
| 251 |
unless ($shib_cfg) { |
| 252 |
say_warning( |
| 253 |
$out, |
| 254 |
"useshibboleth=1 in koha-conf.xml but no <shibboleth> section found; " |
| 255 |
. "please configure the SAML2 identity provider manually" |
| 256 |
); |
| 257 |
} else { |
| 258 |
|
| 259 |
# Check if a SAML2 provider already exists (idempotent) |
| 260 |
my ($saml2_id) = $dbh->selectrow_array( |
| 261 |
"SELECT identity_provider_id FROM identity_providers |
| 262 |
WHERE protocol = 'SAML2' LIMIT 1" |
| 263 |
); |
| 264 |
|
| 265 |
unless ($saml2_id) { |
| 266 |
my $config = encode_json( |
| 267 |
{ |
| 268 |
autocreate => $shib_cfg->{autocreate} ? 1 : 0, |
| 269 |
sync => $shib_cfg->{sync} ? 1 : 0, |
| 270 |
welcome => $shib_cfg->{welcome} ? 1 : 0, |
| 271 |
} |
| 272 |
); |
| 273 |
|
| 274 |
$dbh->do( |
| 275 |
q{ |
| 276 |
INSERT INTO identity_providers |
| 277 |
(code, description, protocol, config, enabled) |
| 278 |
VALUES ('shibboleth', 'Shibboleth (migrated from koha-conf.xml)', 'SAML2', ?, 1) |
| 279 |
}, undef, $config |
| 280 |
); |
| 281 |
$saml2_id = $dbh->last_insert_id( undef, undef, 'identity_providers', undef ); |
| 282 |
say_success( |
| 283 |
$out, |
| 284 |
"Migrated Shibboleth config to identity_providers (id=$saml2_id)" |
| 285 |
); |
| 286 |
|
| 287 |
# Create a default wildcard domain entry for the Shibboleth provider |
| 288 |
$dbh->do( |
| 289 |
q{ |
| 290 |
INSERT IGNORE INTO identity_provider_domains |
| 291 |
(identity_provider_id, domain, allow_opac, allow_staff, |
| 292 |
auto_register_opac, auto_register_staff, update_on_auth, send_welcome_email) |
| 293 |
VALUES (?, NULL, 1, 1, ?, 0, ?, ?) |
| 294 |
}, undef, |
| 295 |
$saml2_id, |
| 296 |
$shib_cfg->{autocreate} ? 1 : 0, |
| 297 |
$shib_cfg->{sync} ? 1 : 0, |
| 298 |
$shib_cfg->{welcome} ? 1 : 0, |
| 299 |
); |
| 300 |
say_success( |
| 301 |
$out, |
| 302 |
"Created default domain entry for Shibboleth provider" |
| 303 |
); |
| 304 |
} |
| 305 |
|
| 306 |
# Migrate field mappings from the <shibboleth><mapping> section |
| 307 |
if ( $saml2_id && $shib_cfg->{mapping} && ref $shib_cfg->{mapping} eq 'HASH' ) { |
| 308 |
my $mapped = 0; |
| 309 |
|
| 310 |
for my $koha_field ( keys %{ $shib_cfg->{mapping} } ) { |
| 311 |
my $entry = $shib_cfg->{mapping}{$koha_field}; |
| 312 |
|
| 313 |
# Entry may be a hashref with {is => 'attr'} or just a string |
| 314 |
my $provider_field = ref $entry eq 'HASH' ? $entry->{is} : $entry; |
| 315 |
|
| 316 |
$dbh->do( |
| 317 |
q{ |
| 318 |
INSERT IGNORE INTO identity_provider_mappings |
| 319 |
(identity_provider_id, provider_field, koha_field) |
| 320 |
VALUES (?, ?, ?) |
| 321 |
}, undef, |
| 322 |
$saml2_id, $provider_field, $koha_field |
| 323 |
); |
| 324 |
$mapped++; |
| 325 |
} |
| 326 |
|
| 327 |
# Store the matchpoint on hostname entries (hostname_id=1=OPACBaseURL, hostname_id=2=staffClientBaseURL) |
| 328 |
if ( my $matchpoint = $shib_cfg->{matchpoint} ) { |
| 329 |
for my $hostname_id ( 1, 2 ) { |
| 330 |
$dbh->do( |
| 331 |
q{ |
| 332 |
INSERT IGNORE INTO identity_provider_hostnames |
| 333 |
(hostname_id, identity_provider_id, is_enabled, force_sso, matchpoint) |
| 334 |
VALUES (?, ?, 1, 0, ?) |
| 335 |
}, undef, $hostname_id, $saml2_id, $matchpoint |
| 336 |
); |
| 337 |
} |
| 338 |
say_success( |
| 339 |
$out, |
| 340 |
"Migrated Shibboleth matchpoint '$matchpoint' to hostname entries" |
| 341 |
); |
| 342 |
} |
| 343 |
|
| 344 |
say_success( |
| 345 |
$out, |
| 346 |
"Migrated $mapped Shibboleth field mapping(s) to identity_provider_mappings" |
| 347 |
); |
| 348 |
} |
| 349 |
} |
| 350 |
} |
| 351 |
} |
| 352 |
|
| 353 |
# ── 9. Migrate OPACShibOnly / staffShibOnly to hostname-based force_sso ─── |
| 354 |
# For each shibOnly syspref that is ON, create a force_sso=1 entry in the |
| 355 |
# identity_provider_hostnames table using the corresponding base URL syspref |
| 356 |
# to determine the hostname. |
| 357 |
|
| 358 |
my %shib_only_map = ( |
| 359 |
OPACShibOnly => 'OPACBaseURL', |
| 360 |
staffShibOnly => 'staffClientBaseURL', |
| 361 |
); |
| 362 |
|
| 363 |
for my $pref_name ( sort keys %shib_only_map ) { |
| 364 |
my ($shib_only) = $dbh->selectrow_array( |
| 365 |
"SELECT value FROM systempreferences WHERE variable = ?", |
| 366 |
undef, $pref_name |
| 367 |
); |
| 368 |
next unless $shib_only; |
| 369 |
|
| 370 |
my ($saml2_id) = $dbh->selectrow_array( |
| 371 |
"SELECT identity_provider_id FROM identity_providers |
| 372 |
WHERE protocol = 'SAML2' LIMIT 1" |
| 373 |
); |
| 374 |
unless ($saml2_id) { |
| 375 |
say_warning( |
| 376 |
$out, |
| 377 |
"'$pref_name' is enabled but no SAML2 provider found; " |
| 378 |
. "please configure force_sso manually after adding a SAML2 provider" |
| 379 |
); |
| 380 |
next; |
| 381 |
} |
| 382 |
|
| 383 |
my $url_pref = $shib_only_map{$pref_name}; |
| 384 |
my ($url) = $dbh->selectrow_array( |
| 385 |
"SELECT value FROM systempreferences WHERE variable = ?", |
| 386 |
undef, $url_pref |
| 387 |
); |
| 388 |
unless ( $url && $url =~ m{^https?://([^/:]+)} ) { |
| 389 |
say_warning( |
| 390 |
$out, |
| 391 |
"'$pref_name' is enabled but '$url_pref' is not set or not a valid URL; " |
| 392 |
. "please configure force_sso manually" |
| 393 |
); |
| 394 |
next; |
| 395 |
} |
| 396 |
my $hostname = $1; |
| 397 |
|
| 398 |
$dbh->do( |
| 399 |
"INSERT IGNORE INTO hostnames (hostname) VALUES (?)", |
| 400 |
undef, $hostname |
| 401 |
); |
| 402 |
my ($hostname_id) = $dbh->selectrow_array( |
| 403 |
"SELECT hostname_id FROM hostnames WHERE hostname = ?", |
| 404 |
undef, $hostname |
| 405 |
); |
| 406 |
|
| 407 |
$dbh->do( |
| 408 |
q{ |
| 409 |
INSERT INTO identity_provider_hostnames |
| 410 |
(hostname_id, identity_provider_id, is_enabled, force_sso) |
| 411 |
VALUES (?, ?, 1, 1) |
| 412 |
ON DUPLICATE KEY UPDATE force_sso = 1 |
| 413 |
}, undef, $hostname_id, $saml2_id |
| 414 |
); |
| 415 |
say_success( |
| 416 |
$out, |
| 417 |
"Enabled force_sso for hostname '$hostname' (migrated from $pref_name)" |
| 418 |
); |
| 419 |
} |
| 420 |
|
| 421 |
$dbh->do("DELETE FROM systempreferences WHERE variable IN ('OPACShibOnly', 'staffShibOnly')"); |
| 422 |
say_success( |
| 423 |
$out, |
| 424 |
"Removed system preferences 'OPACShibOnly' and 'staffShibOnly'" |
| 425 |
); |
| 426 |
|
| 427 |
return 1; |
| 428 |
}, |
| 429 |
}; |