View | Details | Raw Unified | Return to bug 31378
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_31378.pl (+91 lines)
Line 0 Link Here
1
use Modern::Perl;
2
use C4::Context;
3
4
return {
5
    bug_number  => "31378",
6
    description => "Add auth_provider and auth_provider_domains configuration tables",
7
    up => sub {
8
        my ($args) = @_;
9
        my ($dbh, $out) = @$args{qw(dbh out)};
10
11
        unless (TableExists('auth_providers')) {
12
            $dbh->do(q{
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`),
23
                UNIQUE KEY (`code`),
24
                KEY `protocol` (`protocol`)
25
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
26
            });
27
        }
28
29
        unless (TableExists('auth_provider_domains')) {
30
            $dbh->do(q{
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;
50
            });
51
        }
52
53
        if (C4::Context->preference('GoogleOpenIDConnect')) {
54
            # Print useful stuff here
55
            say $out "Setting google provider";
56
            $dbh->do(q{
57
                INSERT INTO `auth_providers` (name, protocol, config, mapping), auto_register, registration_config, interface)
58
                SELECT  'google' as name,
59
                        'OIDC' as protocol,
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
62
                FROM
63
                    (SELECT value FROM `systempreferences` where variable = 'GoogleOAuth2ClientID') k
64
                JOIN
65
                    (SELECT value FROM `systempreferences` where variable = 'GoogleOAuth2ClientSecret') s
66
            });
67
68
            $dbh->do(q{
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)
70
                        p.id as provider_id,
71
                        d.value as domain,
72
                        r.value as auto_register,
73
                        0 as update_on_auth,
74
                        b.value as default_branch,
75
                        c.value as default_category,
76
                        1 as allow_opac,
77
                        0 as allow_interface
78
                FROM
79
                    (SELECT id FROM `auth_provider` WHERE name = 'google') p
80
                JOIN
81
                    (SELECT CASE WHEN value = '' OR value IS NULL THEN NULL ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectDomain') d
82
                JOIN
83
                    (SELECT CASE WHEN value = '' OR value IS NULL THEN '0' ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectAutoRegister') r
84
                JOIN
85
                    (SELECT CASE WHEN value = '' OR value IS NULL THEN NULL ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectDefaultCategory') c
86
                JOIN
87
                    (SELECT CASE WHEN value = '' OR value IS NULL THEN NULL ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectDefaultBranch') b
88
            });
89
        }
90
    },
91
};
(-)a/installer/data/mysql/kohastructure.sql (-1 / +49 lines)
Lines 849-854 CREATE TABLE `auth_header` ( Link Here
849
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
849
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
850
/*!40101 SET character_set_client = @saved_cs_client */;
850
/*!40101 SET character_set_client = @saved_cs_client */;
851
851
852
--
853
-- Table structure for table `auth_provider`
854
--
855
856
DROP TABLE IF EXISTS `auth_providers`;
857
/*!40101 SET @saved_cs_client     = @@character_set_client */;
858
/*!40101 SET character_set_client = utf8 */;
859
CREATE TABLE `auth_providers` (
860
  `auth_provider_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key, used to identify the provider',
861
  `code` varchar(20) NOT NULL COMMENT 'Provider code',
862
  `description` varchar(255) NOT NULL COMMENT 'Description for the provider',
863
  `protocol` enum('OAuth', 'OIDC', 'LDAP', 'CAS') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Protocol provider speaks',
864
  `config` longtext NOT NULL DEFAULT '{}' COMMENT 'Configuration of the provider in JSON format',
865
  `mapping` longtext NOT NULL DEFAULT '{}' COMMENT 'Configuration to map provider data to Koha user',
866
  `matchpoint` enum('email','userid','cardnumber') NOT NULL COMMENT 'The patron attribute to be used as matchpoint',
867
  `icon_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Provider icon URL',
868
  PRIMARY KEY (`auth_provider_id`),
869
  UNIQUE KEY (`code`),
870
  KEY `protocol` (`protocol`)
871
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
872
/*!40101 SET character_set_client = @saved_cs_client */;
873
874
--
875
-- Table structure for table `auth_provider`
876
--
877
878
DROP TABLE IF EXISTS `auth_provider_domains`;
879
/*!40101 SET @saved_cs_client     = @@character_set_client */;
880
/*!40101 SET character_set_client = utf8 */;
881
CREATE TABLE `auth_provider_domains` (
882
  `auth_provider_domain_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key, used to identify providers domain',
883
  `auth_provider_id` int(11) NOT NULL COMMENT 'Reference to provider',
884
  `domain` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Domain name. If null means all domains',
885
  `auto_register` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Allow user auto register',
886
  `update_on_auth` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Update user data on auth login',
887
  `default_library_id` varchar(10) DEFAULT NULL COMMENT 'Default library to create user if auto register is enabled',
888
  `default_category_id` varchar(10) DEFAULT NULL COMMENT 'Default category to create user if auto register is enabled',
889
  `allow_opac` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Allow provider from opac interface',
890
  `allow_staff` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Allow provider from staff interface',
891
  PRIMARY KEY (`auth_provider_domain_id`),
892
  UNIQUE KEY (`auth_provider_id`, `domain`),
893
  KEY `domain` (`domain`),
894
  KEY `allow_opac` (`allow_opac`),
895
  KEY `allow_staff` (`allow_staff`),
896
  CONSTRAINT `auth_provider_domain_ibfk_1` FOREIGN KEY (`auth_provider_id`) REFERENCES `auth_providers` (`auth_provider_id`) ON DELETE CASCADE,
897
  CONSTRAINT `auth_provider_domain_ibfk_2` FOREIGN KEY (`default_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
898
  CONSTRAINT `auth_provider_domain_ibfk_3` FOREIGN KEY (`default_category_id`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE
899
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
900
852
--
901
--
853
-- Table structure for table `auth_subfield_structure`
902
-- Table structure for table `auth_subfield_structure`
854
--
903
--
855
- 

Return to bug 31378