author | Michael Krelin <hacker@klever.net> | 2013-11-27 17:11:26 (UTC) |
---|---|---|
committer | Michael Krelin <hacker@klever.net> | 2013-11-27 17:11:26 (UTC) |
commit | 0f1cc2ac41835ee8fa5dded1593fa95092b54bbe (patch) (unidiff) | |
tree | 8563cd1578aad126c803be452dedd3b9dd9e0921 /backend | |
parent | b59defff1efe85e43850243910007dd1fe3a4ef2 (diff) | |
download | clipperz-0f1cc2ac41835ee8fa5dded1593fa95092b54bbe.zip clipperz-0f1cc2ac41835ee8fa5dded1593fa95092b54bbe.tar.gz clipperz-0f1cc2ac41835ee8fa5dded1593fa95092b54bbe.tar.bz2 |
switched postgresql schema from json type to plaintext
-rw-r--r-- | backend/node/src/clipperz.js | 20 | ||||
-rw-r--r-- | backend/node/src/clipperz.schema.sql | 4 |
2 files changed, 13 insertions, 11 deletions
diff --git a/backend/node/src/clipperz.js b/backend/node/src/clipperz.js index b98c00e..b8b4d3e 100644 --- a/backend/node/src/clipperz.js +++ b/backend/node/src/clipperz.js | |||
@@ -5,42 +5,44 @@ var ASYNC = require('async'); | |||
5 | 5 | ||
6 | var express_store = require('express').session.Store; | 6 | var express_store = require('express').session.Store; |
7 | 7 | ||
8 | function clipperz_hash(v) { | 8 | function clipperz_hash(v) { |
9 | return CRYPTO.createHash('sha256').update( | 9 | return CRYPTO.createHash('sha256').update( |
10 | CRYPTO.createHash('sha256').update(v).digest('binary') | 10 | CRYPTO.createHash('sha256').update(v).digest('binary') |
11 | ).digest('hex'); | 11 | ).digest('hex'); |
12 | }; | 12 | }; |
13 | function clipperz_random() { | 13 | function clipperz_random() { |
14 | for(var r = '';r.length<64;r+=''+BIGNUM(Math.floor(Math.random()*1e18)).toString(16)); | 14 | for(var r = '';r.length<64;r+=''+BIGNUM(Math.floor(Math.random()*1e18)).toString(16)); |
15 | return r.substr(0,64); | 15 | return r.substr(0,64); |
16 | }; | 16 | }; |
17 | function clipperz_store(PG) { | 17 | function clipperz_store(PG) { |
18 | var rv = function(o) { express_store.call(this,o); } | 18 | var rv = function(o) { express_store.call(this,o); } |
19 | rv.prototype.get = function(sid,cb) { PG.Q( | 19 | rv.prototype.get = function(sid,cb) { PG.Q( |
20 | "SELECT s_data FROM clipperz.thesession WHERE s_id=$1",[sid], | 20 | "SELECT s_data FROM clipperz.thesession WHERE s_id=$1",[sid], |
21 | function(e,r) { cb(e,(e||!r.rowCount)?null:r.rows[0].s_data); } | 21 | function(e,r) { cb(e,(e||!r.rowCount)?null:JSON.parse(r.rows[0].s_data)); } |
22 | ) }; | 22 | ) }; |
23 | rv.prototype.set = function(sid,data,cb) { PG.Q( | 23 | rv.prototype.set = function(sid,data,cb) { |
24 | "UPDATE clipperz.thesession SET s_data=$1, s_mtime=current_timestamp" | 24 | var d = JSON.stringify(data); |
25 | +" WHERE s_id=$2",[data,sid], function(e,r) { | 25 | PG.Q( |
26 | "UPDATE clipperz.thesession SET s_data=$1, s_mtime=current_timestamp" | ||
27 | +" WHERE s_id=$2",[d,sid], function(e,r) { | ||
26 | if(e) return cb(e); | 28 | if(e) return cb(e); |
27 | if(r.rowCount) return cb(); | 29 | if(r.rowCount) return cb(); |
28 | PG.Q("INSERT INTO clipperz.thesession (s_id,s_data) VALUES ($1,$2)",[sid,data],cb); | 30 | PG.Q("INSERT INTO clipperz.thesession (s_id,s_data) VALUES ($1,$2)",[sid,d],cb); |
29 | } | 31 | }); |
30 | ) }; | 32 | }; |
31 | rv.prototype.destroy = function(sid,cb) { PG.Q( | 33 | rv.prototype.destroy = function(sid,cb) { PG.Q( |
32 | "DELETE FROM clipperz.thesession WHERE s_id=$1",[sid],cb | 34 | "DELETE FROM clipperz.thesession WHERE s_id=$1",[sid],cb |
33 | ) }; | 35 | ) }; |
34 | rv.prototype.length = function(cb) { PG.Q( | 36 | rv.prototype.length = function(cb) { PG.Q( |
35 | "SELECT count(*) AS c FROM clipperz.thesession", function(e,r) { | 37 | "SELECT count(*) AS c FROM clipperz.thesession", function(e,r) { |
36 | cb(e,e?null:r.rows[0].c); | 38 | cb(e,e?null:r.rows[0].c); |
37 | } | 39 | } |
38 | ) }; | 40 | ) }; |
39 | rv.prototype.length = function(cb) { PQ.Q( | 41 | rv.prototype.length = function(cb) { PQ.Q( |
40 | "DELETE FROM clipperz.thesession", cb | 42 | "DELETE FROM clipperz.thesession", cb |
41 | ) }; | 43 | ) }; |
42 | rv.prototype.__proto__ = express_store.prototype; | 44 | rv.prototype.__proto__ = express_store.prototype; |
43 | return rv; | 45 | return rv; |
44 | } | 46 | } |
45 | 47 | ||
46 | var srp_g = BIGNUM(2); | 48 | var srp_g = BIGNUM(2); |
@@ -213,33 +215,33 @@ var CLIPPERZ = module.exports = function(CONFIG) { | |||
213 | if(r.otp_key_checksum!=ppp.oneTimePasswordKeyChecksum) | 215 | if(r.otp_key_checksum!=ppp.oneTimePasswordKeyChecksum) |
214 | return cb(new Error('OTP was disabled because of checksum mismatch')); | 216 | return cb(new Error('OTP was disabled because of checksum mismatch')); |
215 | if(r.otps_code!='ACTIVE') | 217 | if(r.otps_code!='ACTIVE') |
216 | return cb(new Error("OTP wasn't active, sorry")); | 218 | return cb(new Error("OTP wasn't active, sorry")); |
217 | req.session.u=r.u_id; req.session.otp=r.otp_id; | 219 | req.session.u=r.u_id; req.session.otp=r.otp_id; |
218 | res.res({data:r.otp_data,version:r.otp_version}); | 220 | res.res({data:r.otp_data,version:r.otp_version}); |
219 | }); | 221 | }); |
220 | } | 222 | } |
221 | break; | 223 | break; |
222 | 224 | ||
223 | case 'message': | 225 | case 'message': |
224 | if(!req.session.K) return res.res({result:'EXCEPTION',message:"effectively, we're missing a aconnection"}); | 226 | if(!req.session.K) return res.res({result:'EXCEPTION',message:"effectively, we're missing a aconnection"}); |
225 | if(req.session.K!=pp.srpSharedSecret) return res.res({error:'Wrong shared secret!'}); | 227 | if(req.session.K!=pp.srpSharedSecret) return res.res({error:'Wrong shared secret!'}); |
226 | switch(message) { | 228 | switch(message) { |
227 | case 'getUserDetails': return ASYNC.parallel({ | 229 | case 'getUserDetails': return ASYNC.parallel({ |
228 | u: function(cb) { | 230 | u: function(cb) { |
229 | PG.Q("SELECT u_header::varchar,u_statistics,u_version FROM clipperz.theuser WHERE u_id=$1", | 231 | PG.Q("SELECT u_header,u_statistics,u_version FROM clipperz.theuser WHERE u_id=$1", |
230 | [req.session.u],function(e,r) { | 232 | [req.session.u],function(e,r) { |
231 | if(e) return cb(e); | 233 | if(e) return cb(e); |
232 | if(!r.rowCount) return cb(new Error("user's gone AWOL")); | 234 | if(!r.rowCount) return cb(new Error("user's gone AWOL")); |
233 | cb(null,r.rows[0]); | 235 | cb(null,r.rows[0]); |
234 | }); | 236 | }); |
235 | }, | 237 | }, |
236 | stats: function(cb) { | 238 | stats: function(cb) { |
237 | PG.Q("SELECT r_ref,r_mtime FROM clipperz.therecord WHERE u_id=$1", | 239 | PG.Q("SELECT r_ref,r_mtime FROM clipperz.therecord WHERE u_id=$1", |
238 | [req.session.u],function(e,r) { | 240 | [req.session.u],function(e,r) { |
239 | if(e) return cb(e); | 241 | if(e) return cb(e); |
240 | cb(null,r.rows.reduce(function(p,r){p[r.r_ref]={updateDate:r.r_mtime};return p},{})); | 242 | cb(null,r.rows.reduce(function(p,r){p[r.r_ref]={updateDate:r.r_mtime};return p},{})); |
241 | }); | 243 | }); |
242 | } | 244 | } |
243 | },function(e,r) { | 245 | },function(e,r) { |
244 | if(e) return cb(e); | 246 | if(e) return cb(e); |
245 | res.res({header:r.u.u_header,statistics:r.u.u_statistics,version:r.u.u_version,recordsStats:r.stats}); | 247 | res.res({header:r.u.u_header,statistics:r.u.u_statistics,version:r.u.u_version,recordsStats:r.stats}); |
@@ -489,33 +491,33 @@ var CLIPPERZ = module.exports = function(CONFIG) { | |||
489 | case 'echo': return res.res({result:ppp}); | 491 | case 'echo': return res.res({result:ppp}); |
490 | case 'getOneTimePasswordsDetails': return res.res({}); | 492 | case 'getOneTimePasswordsDetails': return res.res({}); |
491 | case 'getLoginHistory': return res.res({result:[]}); | 493 | case 'getLoginHistory': return res.res({result:[]}); |
492 | } | 494 | } |
493 | break; | 495 | break; |
494 | case 'logout': return req.session.destroy(function(e){res.res({})}); | 496 | case 'logout': return req.session.destroy(function(e){res.res({})}); |
495 | } | 497 | } |
496 | cb(); | 498 | cb(); |
497 | }, | 499 | }, |
498 | 500 | ||
499 | dump: function(req,res,cb) { | 501 | dump: function(req,res,cb) { |
500 | if(!req.session.u) return cb(new Error('logging in helps')); | 502 | if(!req.session.u) return cb(new Error('logging in helps')); |
501 | return ASYNC.parallel({ | 503 | return ASYNC.parallel({ |
502 | u: function(cb) { | 504 | u: function(cb) { |
503 | PG.Q( | 505 | PG.Q( |
504 | "SELECT" | 506 | "SELECT" |
505 | +" u_name, u_srp_s, u_srp_v, u_authversion, u_header::varchar, u_statistics, u_version" | 507 | +" u_name, u_srp_s, u_srp_v, u_authversion, u_header, u_statistics, u_version" |
506 | +" FROM clipperz.theuser WHERE u_id=$1",[req.session.u],function(e,r) { | 508 | +" FROM clipperz.theuser WHERE u_id=$1",[req.session.u],function(e,r) { |
507 | if(e) return cb(e); | 509 | if(e) return cb(e); |
508 | if(!r.rowCount) return cb(new Error("user's gone AWOL")); | 510 | if(!r.rowCount) return cb(new Error("user's gone AWOL")); |
509 | r = r.rows[0]; | 511 | r = r.rows[0]; |
510 | return cb(null,{u:r.u_name,d:{s:r.u_srp_s,v:r.u_srp_v, version:r.u_authversion, | 512 | return cb(null,{u:r.u_name,d:{s:r.u_srp_s,v:r.u_srp_v, version:r.u_authversion, |
511 | maxNumberOfRecords: '100', userDetails: r.u_header, | 513 | maxNumberOfRecords: '100', userDetails: r.u_header, |
512 | statistics: r.u_statistics, userDetailsVersion: r.u_version | 514 | statistics: r.u_statistics, userDetailsVersion: r.u_version |
513 | }}); | 515 | }}); |
514 | }); | 516 | }); |
515 | }, | 517 | }, |
516 | records: function(cb) { | 518 | records: function(cb) { |
517 | PG.Q( | 519 | PG.Q( |
518 | "SELECT" | 520 | "SELECT" |
519 | +" r.r_id, r.r_ref, r_data, r_version, r_ctime, r_mtime, r_atime," | 521 | +" r.r_id, r.r_ref, r_data, r_version, r_ctime, r_mtime, r_atime," |
520 | +" rv.rv_id, rv.rv_ref AS rv_ref, rv_header, rv_data, rv_version, rv_ctime, rv_mtime, rv_atime" | 522 | +" rv.rv_id, rv.rv_ref AS rv_ref, rv_header, rv_data, rv_version, rv_ctime, rv_mtime, rv_atime" |
521 | +" FROM" | 523 | +" FROM" |
diff --git a/backend/node/src/clipperz.schema.sql b/backend/node/src/clipperz.schema.sql index 1c2305c..591828a 100644 --- a/backend/node/src/clipperz.schema.sql +++ b/backend/node/src/clipperz.schema.sql | |||
@@ -1,24 +1,24 @@ | |||
1 | CREATE SCHEMA clipperz; | 1 | CREATE SCHEMA clipperz; |
2 | 2 | ||
3 | CREATE TABLE clipperz.theuser ( | 3 | CREATE TABLE clipperz.theuser ( |
4 | u_id serial PRIMARY KEY, | 4 | u_id serial PRIMARY KEY, |
5 | u_name varchar NOT NULL UNIQUE, | 5 | u_name varchar NOT NULL UNIQUE, |
6 | u_srp_s varchar NOT NULL, | 6 | u_srp_s varchar NOT NULL, |
7 | u_srp_v varchar NOT NULL, | 7 | u_srp_v varchar NOT NULL, |
8 | u_header json NOT NULL, | 8 | u_header varchar NOT NULL, |
9 | u_statistics varchar NOT NULL, | 9 | u_statistics varchar NOT NULL, |
10 | u_authversion varchar NOT NULL, | 10 | u_authversion varchar NOT NULL, |
11 | u_version varchar NOT NULL, | 11 | u_version varchar NOT NULL, |
12 | u_lock varchar NOT NULL | 12 | u_lock varchar NOT NULL |
13 | ); | 13 | ); |
14 | 14 | ||
15 | CREATE TABLE clipperz.therecord ( | 15 | CREATE TABLE clipperz.therecord ( |
16 | r_id serial PRIMARY KEY, | 16 | r_id serial PRIMARY KEY, |
17 | u_id integer NOT NULL REFERENCES clipperz.theuser(u_id) ON UPDATE CASCADE ON DELETE CASCADE, | 17 | u_id integer NOT NULL REFERENCES clipperz.theuser(u_id) ON UPDATE CASCADE ON DELETE CASCADE, |
18 | r_ref varchar NOT NULL UNIQUE, | 18 | r_ref varchar NOT NULL UNIQUE, |
19 | r_data varchar NOT NULL, | 19 | r_data varchar NOT NULL, |
20 | r_version varchar NOT NULL, | 20 | r_version varchar NOT NULL, |
21 | r_ctime timestamp NOT NULL DEFAULT current_timestamp, | 21 | r_ctime timestamp NOT NULL DEFAULT current_timestamp, |
22 | r_mtime timestamp NOT NULL DEFAULT current_timestamp, | 22 | r_mtime timestamp NOT NULL DEFAULT current_timestamp, |
23 | r_atime timestamp NOT NULL DEFAULT current_timestamp | 23 | r_atime timestamp NOT NULL DEFAULT current_timestamp |
24 | ); | 24 | ); |
@@ -48,20 +48,20 @@ CREATE TABLE clipperz.otpstatus ( | |||
48 | CREATE TABLE clipperz.theotp ( | 48 | CREATE TABLE clipperz.theotp ( |
49 | otp_id serial PRIMARY KEY, | 49 | otp_id serial PRIMARY KEY, |
50 | u_id integer REFERENCES clipperz.theuser (u_id) ON UPDATE CASCADE ON DELETE CASCADE, | 50 | u_id integer REFERENCES clipperz.theuser (u_id) ON UPDATE CASCADE ON DELETE CASCADE, |
51 | otps_id integer REFERENCES clipperz.otpstatus (otps_id) ON UPDATE CASCADE ON DELETE CASCADE, | 51 | otps_id integer REFERENCES clipperz.otpstatus (otps_id) ON UPDATE CASCADE ON DELETE CASCADE, |
52 | otp_ref varchar NOT NULL UNIQUE, | 52 | otp_ref varchar NOT NULL UNIQUE, |
53 | otp_key varchar NOT NULL UNIQUE, | 53 | otp_key varchar NOT NULL UNIQUE, |
54 | otp_key_checksum varchar NOT NULL, | 54 | otp_key_checksum varchar NOT NULL, |
55 | otp_data varchar NOT NULL, | 55 | otp_data varchar NOT NULL, |
56 | otp_version varchar NOT NULL, | 56 | otp_version varchar NOT NULL, |
57 | otp_ctime timestamp NOT NULL DEFAULT current_timestamp, | 57 | otp_ctime timestamp NOT NULL DEFAULT current_timestamp, |
58 | otp_rtime timestamp NOT NULL DEFAULT current_timestamp, | 58 | otp_rtime timestamp NOT NULL DEFAULT current_timestamp, |
59 | otp_utime timestamp NOT NULL DEFAULT current_timestamp | 59 | otp_utime timestamp NOT NULL DEFAULT current_timestamp |
60 | ); | 60 | ); |
61 | 61 | ||
62 | CREATE TABLE clipperz.thesession ( | 62 | CREATE TABLE clipperz.thesession ( |
63 | s_id varchar PRIMARY KEY, | 63 | s_id varchar PRIMARY KEY, |
64 | s_data json, | 64 | s_data varchar, |
65 | s_ctime timestamp DEFAULT current_timestamp, | 65 | s_ctime timestamp DEFAULT current_timestamp, |
66 | s_mtime timestamp DEFAULT current_timestamp | 66 | s_mtime timestamp DEFAULT current_timestamp |
67 | ); | 67 | ); |