From 7342c7f0e19e15ab3c7ba2133a56393c15989f08 Mon Sep 17 00:00:00 2001 From: Janik Kleinhoff Date: Mon, 8 May 2017 02:06:38 +0000 Subject: Move users to DB Summary: This will automatically take care of migrating the users from users.json; you may delete that file. Note that this removes htpasswd support. We now store (hashed) user passwords in the database. See T19 for rationale. Test Plan: Run this on a testnet for a while, try to break it. Reviewers: ilbelkyr, #antispammeta Reviewed By: ilbelkyr, #antispammeta Tags: #antispammeta, #database Differential Revision: https://dev.antispammeta.net/D2 --- sql/_common/upgrade/3-4/002-convert_users.pl | 31 ++ sql/_source/deploy/4/001-auto-__VERSION.yml | 91 ++++++ sql/_source/deploy/4/001-auto.yml | 437 +++++++++++++++++++++++++++ 3 files changed, 559 insertions(+) create mode 100644 sql/_common/upgrade/3-4/002-convert_users.pl create mode 100644 sql/_source/deploy/4/001-auto-__VERSION.yml create mode 100644 sql/_source/deploy/4/001-auto.yml (limited to 'sql') diff --git a/sql/_common/upgrade/3-4/002-convert_users.pl b/sql/_common/upgrade/3-4/002-convert_users.pl new file mode 100644 index 0000000..9631345 --- /dev/null +++ b/sql/_common/upgrade/3-4/002-convert_users.pl @@ -0,0 +1,31 @@ +#!/usr/bin/env perl +use v5.20; +use warnings; + +use DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator::ScriptHelpers 'schema_from_schema_loader'; +use Authen::Passphrase::RejectAll; + +schema_from_schema_loader({ naming => 'v4' }, sub { + my ($schema, $versions) = @_; + + while (my ($name, $user) = each %{ $::users->{person} }) { + my %flags; + for my $flag (split(//, $user->{flags})) { + $flags{$flag} = 1; + } + $schema->resultset('Users')->create({ + name => $name, + # We don't have the manually adjusted schema, so we need to + # pass the raw value for the passphrase column + passphrase => Authen::Passphrase::RejectAll->new->as_rfc2307, + flag_secret => $flags{s} // 0, + flag_admin => $flags{a} // 0, + flag_hilights => $flags{h} // 0, + flag_debug => $flags{d} // 0, + flag_plugin => $flags{p} // 0, + }); + } + + say "NOTE: The data from users.json has been moved to the database.\n" + . "You may remove users.json now, although keeping a backup is strongly recommended."; + }) diff --git a/sql/_source/deploy/4/001-auto-__VERSION.yml b/sql/_source/deploy/4/001-auto-__VERSION.yml new file mode 100644 index 0000000..907f443 --- /dev/null +++ b/sql/_source/deploy/4/001-auto-__VERSION.yml @@ -0,0 +1,91 @@ +--- +schema: + procedures: {} + tables: + dbix_class_deploymenthandler_versions: + constraints: + - deferrable: 1 + expression: '' + fields: + - id + match_type: '' + name: '' + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: PRIMARY KEY + - deferrable: 1 + expression: '' + fields: + - version + match_type: '' + name: dbix_class_deploymenthandler_versions_version + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: UNIQUE + fields: + ddl: + data_type: text + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: ddl + order: 3 + size: + - 0 + id: + data_type: int + default_value: ~ + is_auto_increment: 1 + is_nullable: 0 + is_primary_key: 1 + is_unique: 0 + name: id + order: 1 + size: + - 0 + upgrade_sql: + data_type: text + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: upgrade_sql + order: 4 + size: + - 0 + version: + data_type: varchar + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 1 + name: version + order: 2 + size: + - 50 + indices: [] + name: dbix_class_deploymenthandler_versions + options: [] + order: 1 + triggers: {} + views: {} +translator: + add_drop_table: 0 + filename: ~ + no_comments: 0 + parser_args: + sources: + - __VERSION + parser_type: SQL::Translator::Parser::DBIx::Class + producer_args: {} + producer_type: SQL::Translator::Producer::YAML + show_warnings: 0 + trace: 0 + version: 0.11021 diff --git a/sql/_source/deploy/4/001-auto.yml b/sql/_source/deploy/4/001-auto.yml new file mode 100644 index 0000000..8ae9ddc --- /dev/null +++ b/sql/_source/deploy/4/001-auto.yml @@ -0,0 +1,437 @@ +--- +schema: + procedures: {} + tables: + actionlog: + constraints: + - deferrable: 1 + expression: '' + fields: + - index + match_type: '' + name: '' + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: PRIMARY KEY + fields: + account: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: account + order: 11 + size: + - 17 + action: + data_type: varchar + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: action + order: 3 + size: + - 20 + byaccount: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: byaccount + order: 16 + size: + - 17 + bygecos: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: bygecos + order: 15 + size: + - 512 + byhost: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: byhost + order: 14 + size: + - 64 + bynick: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: bynick + order: 12 + size: + - 17 + byuser: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: byuser + order: 13 + size: + - 11 + channel: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: channel + order: 5 + size: + - 51 + gecos: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: gecos + order: 10 + size: + - 512 + host: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: host + order: 8 + size: + - 64 + index: + data_type: bigint + default_value: ~ + is_auto_increment: 1 + is_nullable: 0 + is_primary_key: 1 + is_unique: 0 + name: index + order: 1 + size: + - 0 + ip: + data_type: integer + default_value: ~ + extra: + unsigned: 1 + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: ip + order: 9 + size: + - 0 + nick: + data_type: varchar + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: nick + order: 6 + size: + - 17 + reason: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: reason + order: 4 + size: + - 512 + time: + data_type: timestamp + default_value: !!perl/ref + =: current_timestamp + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: time + order: 2 + size: + - 0 + user: + data_type: varchar + default_value: ~ + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: user + order: 7 + size: + - 11 + indices: [] + name: actionlog + options: [] + order: 1 + alertlog: + constraints: + - deferrable: 1 + expression: '' + fields: + - sqlid + match_type: '' + name: '' + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: PRIMARY KEY + fields: + channel: + data_type: text + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: channel + order: 2 + size: + - 0 + gecos: + data_type: text + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: gecos + order: 6 + size: + - 0 + host: + data_type: text + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: host + order: 5 + size: + - 0 + id: + data_type: tinytext + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: id + order: 8 + size: + - 0 + level: + data_type: tinytext + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: level + order: 7 + size: + - 0 + nick: + data_type: text + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: nick + order: 3 + size: + - 0 + reason: + data_type: text + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: reason + order: 9 + size: + - 0 + sqlid: + data_type: bigint + default_value: ~ + is_auto_increment: 1 + is_nullable: 0 + is_primary_key: 1 + is_unique: 0 + name: sqlid + order: 10 + size: + - 0 + time: + data_type: timestamp + default_value: !!perl/ref + =: current_timestamp + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: time + order: 1 + size: + - 0 + user: + data_type: text + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: user + order: 4 + size: + - 0 + indices: [] + name: alertlog + options: [] + order: 2 + users: + constraints: + - deferrable: 1 + expression: '' + fields: + - id + match_type: '' + name: '' + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: PRIMARY KEY + - deferrable: 1 + expression: '' + fields: + - name + match_type: '' + name: uniq_user_name + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: UNIQUE + fields: + flag_admin: + data_type: boolean + default_value: 0 + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: flag_admin + order: 6 + size: + - 0 + flag_debug: + data_type: boolean + default_value: 0 + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: flag_debug + order: 8 + size: + - 0 + flag_hilights: + data_type: boolean + default_value: 0 + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: flag_hilights + order: 5 + size: + - 0 + flag_plugin: + data_type: boolean + default_value: 0 + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: flag_plugin + order: 7 + size: + - 0 + flag_secret: + data_type: boolean + default_value: 0 + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: flag_secret + order: 4 + size: + - 0 + id: + data_type: bigint + default_value: ~ + is_auto_increment: 1 + is_nullable: 0 + is_primary_key: 1 + is_unique: 0 + name: id + order: 1 + size: + - 0 + name: + data_type: varchar + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 1 + name: name + order: 2 + size: + - 20 + passphrase: + data_type: text + default_value: ~ + is_nullable: 0 + is_primary_key: 0 + is_unique: 0 + name: passphrase + order: 3 + size: + - 0 + indices: [] + name: users + options: [] + order: 3 + triggers: {} + views: {} +translator: + add_drop_table: 0 + filename: ~ + no_comments: 0 + parser_args: + sources: + - Actionlog + - Alertlog + - User + parser_type: SQL::Translator::Parser::DBIx::Class + producer_args: {} + producer_type: SQL::Translator::Producer::YAML + show_warnings: 0 + trace: 0 + version: 0.11021 -- cgit v1.2.3