Ghi 6

[bapril]

I have no clue how this should work, so I'm going to start with a pull request.

Issue #6

[vixie]

ben, since this has a schema change, can you post a shell snippet showing you testing it?

[bapril]

Posted in the issue, not the merge, sorry:

ops-trust=# -- Starting Version 7 ops-trust=# BEGIN; BEGIN ops-trust=# --GHI_6 Omnibus DB update #7 ops-trust=# ops-trust=# --GHI_4 LockDown Member Name. ops-trust=# ALTER TABLE member ADD ident_changed BOOLEAN NOT NULL DEFAULT false; perly. --Update Version. UPDATE schema_metadata SET value = 8 WHERE value = 7 AND key = 'portal_schema_version'; ALTER TABLE ops-trust=# --GHI_5 Sync passowrds and ssh keys for people accounts to the DB. ops-trust=# ALTER TABLE member ADD ssh_key TEXT; ALTER TABLE ops-trust=# ops-trust=# --GHI_8 Replace UUID in second_factors table with SEQUENCE. ops-trust=# ALTER TABLE second_factors RENAME TO second_factors_old; ALTER TABLE ops-trust=# CREATE TABLE second_factors ( ops-trust(# id SERIAL, ops-trust(# member TEXT NOT NULL REFERENCES member(ident) ops-trust(# ON UPDATE CASCADE ops-trust(# ON DELETE CASCADE, ops-trust(# type TEXT NOT NULL REFERENCES second_factor_types(type) ops-trust(# ON UPDATE CASCADE ops-trust(# ON DELETE CASCADE, ops-trust(# entered TIMESTAMP NOT NULL DEFAULT NOW()::TIMESTAMP, ops-trust(# active BOOLEAN NOT NULL DEFAULT false, ops-trust(# counter INTEGER DEFAULT 0, ops-trust(# key TEXT, ops-trust(# descr TEXT ops-trust(# ); NOTICE: CREATE TABLE will create implicit sequence "second_factors_id_seq" for serial column "second_factors.id" CREATE TABLE ops-trust=# GRANT ALL ON TABLE second_factors TO www,sysadmin, "www-data"; GRANT ops-trust=# ops-trust=# INSERT INTO second_factors (member,type,entered,active,counter,key,descr) ops-trust-# SELECT member,type,entered,active,counter,key,descr FROM second_factors_old; INSERT 0 254 ops-trust=# ops-trust=# DROP TABLE second_factors_old; DROP TABLE ops-trust=# ops-trust=# -- Set the db version properly. ops-trust=# --Update Version. ops-trust=# UPDATE schema_metadata ops-trust-# SET value = 8 ops-trust-# WHERE value = 7 ops-trust-# AND key = 'portal_schema_version'; UPDATE 1 ops-trust=# ops-trust=# ops-trust=# ops-trust=# SELECT * FROM second_factors; ops-trust=# SELECT * FROM second_factors limit 5; id | member | type | entered | active | counter | key | descr

----+--------------------+------+----------------------------+--------+---------+------------------------------------------------------------------+------------------------ 1 | kyle | TOTP | 2014-03-13 14:01:39.716941 | t | 0 | opYs/ouJyb | kcwp 2 | terrymacdonald7375 | TOTP | 2014-03-13 02:57:19.078248 | t | 0 | dhAIbuacJx | Samsung 3 | fergie | TOTP | 2014-03-13 02:59:08.538228 | t | 0 | gdlMDz0LFO | Fergie's Android Phone 4 | fergie | TOTP | 2014-03-13 03:04:08.620724 | f | 0 | icqFLkPu3g | Fergie's Android Phone 5 | fordmerrill6641 | SOTP | 2014-05-31 03:35:48.042378 | t | 0 | 085aee92b001324814097083782018e8918be187cb2df8981330fd8fa32ced2e | recovery 1 (5 rows)

ops-trust=# \d member

tel_info | text | not null default ''::text post_info | text | not null default ''::text bio_info | text | not null default ''::text entered | timestamp without time zone | not null default (now())::timestamp without time zone tz_info | text | not null default ''::text sms_info | text | ident | text | not null airport | text | no_email | boolean | not null default false affiliation | text | not null default ''::text hide_email | boolean | not null default false change_pw | boolean | not null default false furlough | boolean | not null default false activity | timestamp without time zone | not null default (now())::timestamp without time zone uuid | uuid | not null sysadmin | boolean | not null default false passwd_chat | text | login_attempts | integer | not null default 0 login_try_begin | timestamp without time zone | image | text | recover_email | text | recover_password | text | recover_password_set_at | timestamp without time zone | last_notice | integer | ident_changed | boolean | not null default false ssh_key | text | Indexes: "member_pkey" PRIMARY KEY, btree (ident) "member_uuid_key" UNIQUE CONSTRAINT, btree (uuid) "member_wikiname_unique" UNIQUE CONSTRAINT, btree (ident) Check constraints: "member_airport_check" CHECK (length(airport) = 3) "member_ident_check1" CHECK (ident_ok(ident)) "member_recover_email_check" CHECK (email_ok(recover_email)) "member_sms_info_check" CHECK (pstn_ok(sms_info)) Foreign-key constraints: "member_ident_fkey" FOREIGN KEY (ident, recover_email) REFERENCES member_email(member, email) ON UPDATE CASCADE "member_last_notice_fkey" FOREIGN KEY (last_notice) REFERENCES notices(id) Referenced by: TABLE "audit_history" CONSTRAINT "audit_history_member_fkey" FOREIGN KEY (member) REFERENCES member(ident) ON UPDATE CASCADE ON DELETE SET NULL TABLE "member_details" CONSTRAINT "member_details_member_fkey" FOREIGN KEY (member) REFERENCES member(ident) ON UPDATE CASCADE ON DELETE CASCADE TABLE "member_email" CONSTRAINT "member_email_member_fkey" FOREIGN KEY (member) REFERENCES member(ident) ON UPDATE CASCADE ON DELETE CASCADE TABLE "member_language_skill" CONSTRAINT "member_language_skill_member_fkey1" FOREIGN KEY (member) REFERENCES member(ident) ON UPDATE CASCADE ON DELETE CASCADE TABLE "member_mailinglist" CONSTRAINT "mm2_member_fkey" FOREIGN KEY (member) REFERENCES member(ident) ON UPDATE CASCADE ON DELETE CASCADE TABLE "member_trustgroup" CONSTRAINT "mt2_member_fkey" FOREIGN KEY (member) REFERENCES member(ident) ON UPDATE CASCADE ON DELETE CASCADE TABLE "member_vouch" CONSTRAINT "mv2_vouchee_fkey" FOREIGN KEY (vouchee) REFERENCES member(ident) ON UPDATE CASCADE ON DELETE CASCADE TABLE "member_vouch" CONSTRAINT "mv2_vouchor_fkey" FOREIGN KEY (vouchor) REFERENCES member(ident) ON UPDATE CASCADE ON DELETE CASCADE TABLE "second_factors" CONSTRAINT "second_factors_member_fkey1" FOREIGN KEY (member) REFERENCES member(ident) ON UPDATE CASCADE ON DELETE CASCADE