DB changes to support OIDC auth

6 files changed, 69 insertions, 16 deletions

diff --git a/bin/update-schema b/bin/update-schema
index 900e628e6..3f4b2bafe 100755
--- a/bin/update-schema
+++ b/bin/update-schema
@@ -212,6 +212,7 @@ else {
 # (assuming schema change files are never half-applied, which should be the case)
 sub get_db_version {
     return 'EMPTY' if ! table_exists('problem');
+    return '0068' if column_exists('users', 'oidc_ids');
     return '0067' if table_exists('roles');
     return '0066' if column_exists('users', 'area_ids');
     return '0065' if constraint_contains('admin_log_object_type_check', 'moderation');
diff --git a/db/downgrade_0068---0067.sql b/db/downgrade_0068---0067.sql
new file mode 100644
index 000000000..c12c0374b
--- /dev/null
+++ b/db/downgrade_0068---0067.sql
@@ -0,0 +1,3 @@
+begin;
+alter table users drop column oidc_ids;
+commit;
diff --git a/db/schema.sql b/db/schema.sql
index 93d73ab00..a211ef50d 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -35,6 +35,7 @@ create table users (
     title           text,
     twitter_id      bigint  unique,
     facebook_id     bigint  unique,
+    oidc_ids        text    ARRAY,
     area_ids        integer ARRAY,
     extra           text
 );
diff --git a/db/schema_0068-oidc_login.sql b/db/schema_0068-oidc_login.sql
new file mode 100644
index 000000000..aadbef4da
--- /dev/null
+++ b/db/schema_0068-oidc_login.sql
@@ -0,0 +1,3 @@
+begin;
+alter table users add column oidc_ids text ARRAY;
+commit;
diff --git a/perllib/FixMyStreet/DB/Result/User.pm b/perllib/FixMyStreet/DB/Result/User.pm
index fc651b4d1..4ea7524bb 100644
--- a/perllib/FixMyStreet/DB/Result/User.pm
+++ b/perllib/FixMyStreet/DB/Result/User.pm
@@ -24,22 +24,30 @@ __PACKAGE__->add_columns(
   },
   "email",
   { data_type => "text", is_nullable => 1 },
-  "email_verified",
-  { data_type => "boolean", default_value => \"false", is_nullable => 0 },
   "name",
   { data_type => "text", is_nullable => 1 },
   "phone",
   { data_type => "text", is_nullable => 1 },
-  "phone_verified",
-  { data_type => "boolean", default_value => \"false", is_nullable => 0 },
   "password",
   { data_type => "text", default_value => "", is_nullable => 0 },
-  "from_body",
-  { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
   "flagged",
   { data_type => "boolean", default_value => \"false", is_nullable => 0 },
+  "from_body",
+  { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
+  "title",
+  { data_type => "text", is_nullable => 1 },
+  "facebook_id",
+  { data_type => "bigint", is_nullable => 1 },
+  "twitter_id",
+  { data_type => "bigint", is_nullable => 1 },
   "is_superuser",
   { data_type => "boolean", default_value => \"false", is_nullable => 0 },
+  "extra",
+  { data_type => "text", is_nullable => 1 },
+  "email_verified",
+  { data_type => "boolean", default_value => \"false", is_nullable => 0 },
+  "phone_verified",
+  { data_type => "boolean", default_value => \"false", is_nullable => 0 },
   "created",
   {
     data_type     => "timestamp",
@@ -54,16 +62,10 @@ __PACKAGE__->add_columns(
     is_nullable   => 0,
     original      => { default_value => \"now()" },
   },
-  "title",
-  { data_type => "text", is_nullable => 1 },
-  "twitter_id",
-  { data_type => "bigint", is_nullable => 1 },
-  "facebook_id",
-  { data_type => "bigint", is_nullable => 1 },
-  "extra",
-  { data_type => "text", is_nullable => 1 },
   "area_ids",
   { data_type => "integer[]", is_nullable => 1 },
+  "oidc_ids",
+  { data_type => "text[]", is_nullable => 1 },
 );
 __PACKAGE__->set_primary_key("id");
 __PACKAGE__->add_unique_constraint("users_facebook_id_key", ["facebook_id"]);
@@ -129,8 +131,8 @@ __PACKAGE__->has_many(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07035 @ 2019-05-23 18:03:28
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:qtmzA7ywVkyQpjLh1ienNg
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2019-06-20 16:31:44
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Ryb6giJm/7N7svg/d+2GeA
 
 # These are not fully unique constraints (they only are when the *_verified
 # is true), but this is managed in ResultSet::User's find() wrapper.
@@ -142,6 +144,7 @@ __PACKAGE__->rabx_column('extra');
 
 use Moo;
 use Text::CSV;
+use List::MoreUtils 'uniq';
 use FixMyStreet::SMS;
 use mySociety::EmailUtil;
 use namespace::clean -except => [ 'meta' ];
@@ -534,6 +537,7 @@ sub anonymize_account {
         title => undef,
         twitter_id => undef,
         facebook_id => undef,
+        oidc_ids => undef,
     });
 }
 
@@ -654,4 +658,20 @@ sub in_role {
     return $self->roles_hash->{$role};
 }
 
+sub add_oidc_id {
+    my ($self, $oidc_id) = @_;
+
+    my $oidc_ids = $self->oidc_ids || [];
+    my @oidc_ids = uniq ( $oidc_id, @$oidc_ids );
+    $self->oidc_ids(\@oidc_ids);
+}
+
+sub remove_oidc_id {
+    my ($self, $oidc_id) = @_;
+
+    my $oidc_ids = $self->oidc_ids || [];
+    my @oidc_ids = grep { $_ ne $oidc_id } @$oidc_ids;
+    $self->oidc_ids(scalar @oidc_ids ? \@oidc_ids : undef);
+}
+
 1;
diff --git a/t/app/model/user.t b/t/app/model/user.t
index cbc0fe6cf..88b29ca84 100644
--- a/t/app/model/user.t
+++ b/t/app/model/user.t
@@ -75,6 +75,31 @@ subtest 'Check non-existent methods on user object die' => sub {
     );
 };
 
+subtest 'OIDC ids can be manipulated correctly' => sub {
+    my $user = $problem->user;
+
+    is $user->oidc_ids, undef, 'user starts with no OIDC ids';
+
+    $user->add_oidc_id("fixmystreet:1234:5678");
+    is_deeply $user->oidc_ids, ["fixmystreet:1234:5678"], 'OIDC id added correctly';
+
+    $user->add_oidc_id("mycobrand:0123:abcd");
+    is_deeply [ sort @{$user->oidc_ids} ], ["fixmystreet:1234:5678", "mycobrand:0123:abcd"], 'Second OIDC id added correctly';
+
+    $user->add_oidc_id("mycobrand:0123:abcd");
+    is_deeply [ sort @{$user->oidc_ids} ], ["fixmystreet:1234:5678", "mycobrand:0123:abcd"], 'Adding existing OIDC id does not add duplicate';
+
+    $user->remove_oidc_id("mycobrand:0123:abcd");
+    is_deeply $user->oidc_ids, ["fixmystreet:1234:5678"], 'OIDC id can be removed OK';
+
+    $user->remove_oidc_id("mycobrand:0123:abcd");
+    is_deeply $user->oidc_ids, ["fixmystreet:1234:5678"], 'Removing non-existent OIDC id has no effect';
+
+    $user->remove_oidc_id("fixmystreet:1234:5678");
+    is $user->oidc_ids, undef, 'Removing last OIDC id results in undef';
+
+};
+
 done_testing();
 
 sub create_update {