6 description => "Add auth_provider and auth_provider_domains configuration tables",
9 my ($dbh, $out) = @$args{qw(dbh out)};
11 unless (TableExists('auth_providers')) {
13 CREATE TABLE `auth_providers` (
14 `auth_provider_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key, used to identify the provider',
15 `code` varchar(20) NOT NULL COMMENT 'Provider code',
16 `description` varchar(255) NOT NULL COMMENT 'Description for the provider',
17 `protocol` enum('OAuth', 'OIDC', 'LDAP', 'CAS') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Protocol provider speaks',
18 `config` longtext NOT NULL DEFAULT '{}' COMMENT 'Configuration of the provider in JSON format',
19 `mapping` longtext NOT NULL DEFAULT '{}' COMMENT 'Configuration to map provider data to Koha user',
20 `matchpoint` enum('email','userid','cardnumber') NOT NULL COMMENT 'The patron attribute to be used as matchpoint',
21 `icon_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Provider icon URL',
22 PRIMARY KEY (`auth_provider_id`),
24 KEY `protocol` (`protocol`)
25 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
29 unless (TableExists('auth_provider_domains')) {
31 CREATE TABLE `auth_provider_domains` (
32 `auth_provider_domain_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key, used to identify providers domain',
33 `auth_provider_id` int(11) NOT NULL COMMENT 'Reference to provider',
34 `domain` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Domain name. If null means all domains',
35 `auto_register` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Allow user auto register',
36 `update_on_auth` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Update user data on auth login',
37 `default_library_id` varchar(10) DEFAULT NULL COMMENT 'Default library to create user if auto register is enabled',
38 `default_category_id` varchar(10) DEFAULT NULL COMMENT 'Default category to create user if auto register is enabled',
39 `allow_opac` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Allow provider from opac interface',
40 `allow_staff` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Allow provider from staff interface',
41 PRIMARY KEY (`auth_provider_domain_id`),
42 UNIQUE KEY (`auth_provider_id`, `domain`),
43 KEY `domain` (`domain`),
44 KEY `allow_opac` (`allow_opac`),
45 KEY `allow_staff` (`allow_staff`),
46 CONSTRAINT `auth_provider_domain_ibfk_1` FOREIGN KEY (`auth_provider_id`) REFERENCES `auth_providers` (`auth_provider_id`) ON DELETE CASCADE,
47 CONSTRAINT `auth_provider_domain_ibfk_2` FOREIGN KEY (`default_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
48 CONSTRAINT `auth_provider_domain_ibfk_3` FOREIGN KEY (`default_category_id`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE
49 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
53 if (C4::Context->preference('GoogleOpenIDConnect')) {
54 # Print useful stuff here
55 say $out "Setting google provider";
57 INSERT INTO `auth_providers` (name, protocol, config, mapping), auto_register, registration_config, interface)
58 SELECT 'google' as name,
60 JSON_OBJECT("key", k.value, "secret", s.value, "well_known_url", "https://accounts.google.com/.well-known/openid-configuration", "scope", "openid email profile") as config,
61 JSON_OBJECT("email", "email", "firstname", "given_name", "surname", "family_name", "_key", "email") as mapping
63 (SELECT value FROM `systempreferences` where variable = 'GoogleOAuth2ClientID') k
65 (SELECT value FROM `systempreferences` where variable = 'GoogleOAuth2ClientSecret') s
69 INSERT INTO `auth_provider_domains` (auth_provider_id, domain, auto_register, update_on_auth, default_library_id, default_category_id, allow_opac, allow_staff)
72 r.value as auto_register,
74 b.value as default_branch,
75 c.value as default_category,
79 (SELECT id FROM `auth_provider` WHERE name = 'google') p
81 (SELECT CASE WHEN value = '' OR value IS NULL THEN NULL ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectDomain') d
83 (SELECT CASE WHEN value = '' OR value IS NULL THEN '0' ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectAutoRegister') r
85 (SELECT CASE WHEN value = '' OR value IS NULL THEN NULL ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectDefaultCategory') c
87 (SELECT CASE WHEN value = '' OR value IS NULL THEN NULL ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectDefaultBranch') b