-
Notifications
You must be signed in to change notification settings - Fork 220
/
Copy pathpostgres-server-queries.coffee
2536 lines (2324 loc) · 102 KB
/
postgres-server-queries.coffee
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#########################################################################
# This file is part of CoCalc: Copyright © 2020 Sagemath, Inc.
# License: MS-RSL – see LICENSE.md for details
#########################################################################
###
PostgreSQL -- implementation of all the queries needed for the backend servers
These are all the non-reactive non-push queries, e.g., adding entries to logs,
checking on cookies, creating accounts and projects, etc.
COPYRIGHT : (c) 2017 SageMath, Inc.
LICENSE : MS-RSL
###
# limit for async.map or async.paralleLimit, esp. to avoid high concurrency when querying in parallel
MAP_LIMIT = 5
async = require('async')
random_key = require("random-key")
misc_node = require('@cocalc/backend/misc_node')
misc2_node = require('@cocalc/backend/misc')
{defaults} = misc = require('@cocalc/util/misc')
required = defaults.required
# IDK why, but if that import line is down below, where the other "./postgres/*" imports are, building manage
# fails with: remember-me.ts(15,31): error TS2307: Cannot find module 'async-await-utils/hof' or its corresponding type declarations.
{get_remember_me} = require('./postgres/remember-me')
{SCHEMA, DEFAULT_QUOTAS, PROJECT_UPGRADES, COMPUTE_STATES, RECENT_TIMES, RECENT_TIMES_KEY, site_settings_conf} = require('@cocalc/util/schema')
{ quota } = require("@cocalc/util/upgrades/quota")
PROJECT_GROUPS = misc.PROJECT_GROUPS
read = require('read')
{PROJECT_COLUMNS, one_result, all_results, count_result, expire_time} = require('./postgres-base')
{syncdoc_history} = require('./postgres/syncdoc-history')
# TODO is set_account_info_if_possible used here?!
{is_paying_customer, set_account_info_if_possible} = require('./postgres/account-queries')
{getStripeCustomerId, syncCustomer} = require('./postgres/stripe')
{site_license_usage_stats, projects_using_site_license, number_of_projects_using_site_license} = require('./postgres/site-license/analytics')
{update_site_license_usage_log} = require('./postgres/site-license/usage-log')
{site_license_public_info} = require('./postgres/site-license/public')
{site_license_manager_set} = require('./postgres/site-license/manager')
{matching_site_licenses, manager_site_licenses} = require('./postgres/site-license/search')
{project_datastore_set, project_datastore_get, project_datastore_del} = require('./postgres/project-queries')
{permanently_unlink_all_deleted_projects_of_user, unlink_old_deleted_projects, cleanup_old_projects_data} = require('./postgres/delete-projects')
{get_all_public_paths, unlist_all_public_paths} = require('./postgres/public-paths')
{get_personal_user} = require('./postgres/personal')
{set_passport_settings, get_passport_settings, get_all_passport_settings, get_all_passport_settings_cached, create_passport, passport_exists, update_account_and_passport, _passport_key} = require('./postgres/passport')
{projects_that_need_to_be_started} = require('./postgres/always-running');
{calc_stats} = require('./postgres/stats')
{getServerSettings, resetServerSettingsCache, getPassportsCached, setPassportsCached} = require('@cocalc/database/settings/server-settings');
{pii_expire} = require("./postgres/pii")
passwordHash = require("@cocalc/backend/auth/password-hash").default;
registrationTokens = require('./postgres/registration-tokens').default;
{updateUnreadMessageCount} = require('./postgres/messages');
centralLog = require('./postgres/central-log').default;
stripe_name = require('@cocalc/util/stripe/name').default;
exports.extend_PostgreSQL = (ext) -> class PostgreSQL extends ext
# write an event to the central_log table
log: (opts) =>
opts = defaults opts,
event : required # string
value : required # object
cb : undefined
try
await centralLog(opts)
opts.cb?()
catch err
opts.cb?(err)
uncaught_exception: (err) =>
# call when things go to hell in some unexpected way; at least
# we attempt to record this in the database...
try
@log
event : 'uncaught_exception'
value : {error:"#{err}", stack:"#{err.stack}", host:require('os').hostname()}
catch e
# IT IS CRITICAL THAT uncaught_exception not raise an exception, since if it
# did then we would hit a horrible infinite loop!
# dump a range of data from the central_log table
get_log: (opts) =>
opts = defaults opts,
start : undefined # if not given start at beginning of time
end : undefined # if not given include everything until now
log : 'central_log' # which table to query
event : undefined
where : undefined # if given, restrict to records with the given json
# containment, e.g., {account_id:'...'}, only returns
# entries whose value has the given account_id.
cb : required
@_query
query : "SELECT * FROM #{opts.log}"
where :
'time >= $::TIMESTAMP' : opts.start
'time <= $::TIMESTAMP' : opts.end
'event = $::TEXT' : opts.event
'value @> $::JSONB' : opts.where
cb : all_results(opts.cb)
# Return every entry x in central_log in the given period of time for
# which x.event==event and x.value.account_id == account_id.
get_user_log: (opts) =>
opts = defaults opts,
start : undefined
end : undefined # if not given include everything until now
event : 'successful_sign_in'
account_id : required
cb : required
@get_log
start : opts.start
end : opts.end
event : opts.event
where : {account_id: opts.account_id}
cb : opts.cb
log_client_error: (opts) =>
opts = defaults opts,
event : 'event'
error : 'error'
account_id : undefined
cb : undefined
# get rid of the entry in 30 days
expire = misc.expire_time(30 * 24 * 60 * 60)
@_query
query : 'INSERT INTO client_error_log'
values :
'id :: UUID' : misc.uuid()
'event :: TEXT' : opts.event
'error :: TEXT' : opts.error
'account_id :: UUID' : opts.account_id
'time :: TIMESTAMP' : 'NOW()'
'expire :: TIMESTAMP' : expire
cb : opts.cb
webapp_error: (opts) =>
opts = defaults opts,
account_id : undefined
name : undefined
message : undefined
comment : undefined
stacktrace : undefined
file : undefined
path : undefined
lineNumber : undefined
columnNumber : undefined
severity : undefined
browser : undefined
mobile : undefined
responsive : undefined
user_agent : undefined
smc_version : undefined
build_date : undefined
smc_git_rev : undefined
uptime : undefined
start_time : undefined
id : undefined # ignored
cb : undefined
# get rid of the entry in 30 days
expire = misc.expire_time(30 * 24 * 60 * 60)
@_query
query : 'INSERT INTO webapp_errors'
values :
'id :: UUID' : misc.uuid()
'account_id :: UUID' : opts.account_id
'name :: TEXT' : opts.name
'message :: TEXT' : opts.message
'comment :: TEXT' : opts.comment
'stacktrace :: TEXT' : opts.stacktrace
'file :: TEXT' : opts.file
'path :: TEXT' : opts.path
'lineNumber :: INTEGER' : opts.lineNumber
'columnNumber :: INTEGER' : opts.columnNumber
'severity :: TEXT' : opts.severity
'browser :: TEXT' : opts.browser
'mobile :: BOOLEAN' : opts.mobile
'responsive :: BOOLEAN' : opts.responsive
'user_agent :: TEXT' : opts.user_agent
'smc_version :: TEXT' : opts.smc_version
'build_date :: TEXT' : opts.build_date
'smc_git_rev :: TEXT' : opts.smc_git_rev
'uptime :: TEXT' : opts.uptime
'start_time :: TIMESTAMP' : opts.start_time
'time :: TIMESTAMP' : 'NOW()'
'expire :: TIMESTAMP' : expire
cb : opts.cb
get_client_error_log: (opts) =>
opts = defaults opts,
start : undefined # if not given start at beginning of time
end : undefined # if not given include everything until now
event : undefined
cb : required
opts.log = 'client_error_log'
@get_log(opts)
set_server_setting: (opts) =>
opts = defaults opts,
name : required
value : required
readonly : undefined # boolean. if yes, that value is not controlled via any UI
cb : required
async.series([
(cb) =>
values =
'name::TEXT' : opts.name
'value::TEXT' : opts.value
if opts.readonly?
values.readonly = !!opts.readonly
@_query
query : 'INSERT INTO server_settings'
values : values
conflict : 'name'
cb : cb
# also set a timestamp
(cb) =>
@_query
query : 'INSERT INTO server_settings'
values :
'name::TEXT' : '_last_update'
'value::TEXT' : (new Date()).toISOString()
conflict : 'name'
cb : cb
], (err) =>
# clear the cache no matter what (e.g., server_settings might have partly changed then errored)
@reset_server_settings_cache()
opts.cb(err)
)
reset_server_settings_cache: =>
resetServerSettingsCache()
get_server_setting: (opts) =>
opts = defaults opts,
name : required
cb : required
@_query
query : 'SELECT value FROM server_settings'
where :
"name = $::TEXT" : opts.name
cb : one_result('value', opts.cb)
get_server_settings_cached: (opts) =>
opts = defaults opts,
cb: required
try
opts.cb(undefined, await getServerSettings())
catch err
opts.cb(err)
get_site_settings: (opts) =>
opts = defaults opts,
cb : required # (err, settings)
@_query
query : 'SELECT name, value FROM server_settings'
cache : true
where :
"name = ANY($)" : misc.keys(site_settings_conf)
cb : (err, result) =>
if err
opts.cb(err)
else
x = {}
for k in result.rows
if k.name == 'commercial' and k.value in ['true', 'false'] # backward compatibility
k.value = eval(k.value)
x[k.name] = k.value
opts.cb(undefined, x)
server_settings_synctable: (opts={}) =>
opts.table = 'server_settings'
return @synctable(opts)
set_passport_settings: (opts) =>
opts = defaults opts,
strategy : required
conf : required
info : undefined
cb : required
return await set_passport_settings(@, opts)
get_passport_settings: (opts) =>
opts = defaults opts,
strategy : required
return await get_passport_settings(@, opts)
get_all_passport_settings: () =>
return await get_all_passport_settings(@)
get_all_passport_settings_cached: () =>
return await get_all_passport_settings_cached(@)
create_passport: (opts) =>
return await create_passport(@, opts)
passport_exists: (opts) =>
return await passport_exists(@, opts)
update_account_and_passport: (opts) =>
return await update_account_and_passport(@, opts)
###
Creating an account using SSO only.
This needs to be rewritten in @cocalc/server like
all the other account creation. This is horrible
because
###
create_sso_account: (opts={}) =>
opts = defaults opts,
first_name : undefined
last_name : undefined
created_by : undefined # ip address of computer creating this account
email_address : undefined
password_hash : undefined
lti_id : undefined # 2-tuple <string[]>[iss, user_id]
passport_strategy : undefined
passport_id : undefined
passport_profile : undefined
usage_intent : undefined
cb : required # cb(err, account_id)
dbg = @_dbg("create_sso_account(#{opts.first_name}, #{opts.last_name}, #{opts.lti_id}, #{opts.email_address}, #{opts.passport_strategy}, #{opts.passport_id}), #{opts.usage_intent}")
dbg()
for name in ['first_name', 'last_name']
if opts[name]
test = misc2_node.is_valid_username(opts[name])
if test?
opts.cb("#{name} not valid: #{test}")
return
if opts.email_address # canonicalize the email address, if given
opts.email_address = misc.lower_email_address(opts.email_address)
account_id = misc.uuid()
passport_key = undefined
if opts.passport_strategy?
# This is to make it impossible to accidentally create two accounts with the same passport
# due to calling create_account twice at once. See TODO below about changing schema.
# This should be enough for now since a given user only makes their account through a single
# server via the persistent websocket...
@_create_account_passport_keys ?= {}
passport_key = _passport_key(strategy:opts.passport_strategy, id:opts.passport_id)
last = @_create_account_passport_keys[passport_key]
if last? and new Date() - last <= 60*1000
opts.cb("recent attempt to make account with this passport strategy")
return
@_create_account_passport_keys[passport_key] = new Date()
async.series([
(cb) =>
if not opts.passport_strategy?
cb(); return
dbg("verify that no account with passport (strategy='#{opts.passport_strategy}', id='#{opts.passport_id}') already exists")
# **TODO:** need to make it so insertion into the table still would yield an error due to
# unique constraint; this will require probably moving the passports
# object to a separate table. This is important, since this is exactly the place where
# a race condition might cause trouble!
@passport_exists
strategy : opts.passport_strategy
id : opts.passport_id
cb : (err, account_id) ->
if err
cb(err)
else if account_id
cb("account with email passport strategy '#{opts.passport_strategy}' and id '#{opts.passport_id}' already exists")
else
cb()
(cb) =>
dbg("create the actual account")
@_query
query : "INSERT INTO accounts"
values :
'account_id :: UUID' : account_id
'first_name :: TEXT' : opts.first_name
'last_name :: TEXT' : opts.last_name
'lti_id :: TEXT[]' : opts.lti_id
'created :: TIMESTAMP' : new Date()
'created_by :: INET' : opts.created_by
'password_hash :: CHAR(173)' : opts.password_hash
'email_address :: TEXT' : opts.email_address
'sign_up_usage_intent :: TEXT': opts.usage_intent
cb : cb
(cb) =>
if opts.passport_strategy?
dbg("add passport authentication strategy")
@create_passport
account_id : account_id
strategy : opts.passport_strategy
id : opts.passport_id
profile : opts.passport_profile
cb : cb
else
cb()
], (err) =>
if err
dbg("error creating account -- #{err}")
opts.cb(err)
else
dbg("successfully created account")
opts.cb(undefined, account_id)
)
is_admin: (opts) =>
opts = defaults opts,
account_id : required
cb : required
@_query
query : "SELECT groups FROM accounts"
where : 'account_id = $::UUID':opts.account_id
cache : true
cb : one_result 'groups', (err, groups) =>
opts.cb(err, groups? and 'admin' in groups)
user_is_in_group: (opts) =>
opts = defaults opts,
account_id : required
group : required
cb : required
@_query
query : "SELECT groups FROM accounts"
where : 'account_id = $::UUID':opts.account_id
cache : true
cb : one_result 'groups', (err, groups) =>
opts.cb(err, groups? and opts.group in groups)
make_user_admin: (opts) =>
opts = defaults opts,
account_id : undefined
email_address : undefined
cb : required
if not opts.account_id? and not opts.email_address?
opts.cb?("account_id or email_address must be given")
return
async.series([
(cb) =>
if opts.account_id?
cb()
else
@get_account
email_address : opts.email_address
columns : ['account_id']
cb : (err, x) =>
if err
cb(err)
else if not x?
cb("no such email address")
else
opts.account_id = x.account_id
cb()
(cb) =>
@clear_cache() # caching is mostly for permissions so this is exactly when it would be nice to clear it.
@_query
query : "UPDATE accounts"
where : 'account_id = $::UUID':opts.account_id
set :
groups : ['admin']
cb : cb
], opts.cb)
count_accounts_created_by: (opts) =>
opts = defaults opts,
ip_address : required
age_s : required
cb : required
@_count
table : 'accounts'
where :
"created_by = $::INET" : opts.ip_address
"created >= $::TIMESTAMP" : misc.seconds_ago(opts.age_s)
cb : opts.cb
# Completely delete the given account from the database. This doesn't
# do any sort of cleanup of things associated with the account! There
# is no reason to ever use this, except for testing purposes.
delete_account: (opts) =>
opts = defaults opts,
account_id : required
cb : required
if not @_validate_opts(opts) then return
@_query
query : "DELETE FROM accounts"
where : "account_id = $::UUID" : opts.account_id
cb : opts.cb
# Mark the account as deleted, thus freeing up the email
# address for use by another account, etc. The actual
# account entry remains in the database, since it may be
# referred to by many other things (projects, logs, etc.).
# However, the deleted field is set to true, so the account
# is excluded from user search.
# TODO: rewritten in packages/server/accounts/delete.ts
mark_account_deleted: (opts) =>
opts = defaults opts,
account_id : undefined
email_address : undefined
cb : required
if not opts.account_id? and not opts.email_address?
opts.cb("one of email address or account_id must be specified -- make sure you are signed in")
return
query = undefined
email_address = undefined
async.series([
(cb) =>
if opts.account_id?
cb()
else
@account_exists
email_address : opts.email_address
cb : (err, account_id) =>
if err
cb(err)
else if not account_id
cb("no such email address known")
else
opts.account_id = account_id
cb()
(cb) =>
@_query
query : "SELECT email_address FROM accounts"
where : "account_id = $::UUID" : opts.account_id
cb : one_result 'email_address', (err, x) =>
email_address = x; cb(err)
(cb) =>
@_query
query : "UPDATE accounts"
set :
"deleted::BOOLEAN" : true
"email_address_before_delete::TEXT" : email_address
"email_address" : null
"passports" : null
where : "account_id = $::UUID" : opts.account_id
cb : cb
], opts.cb)
account_exists: (opts) =>
opts = defaults opts,
email_address : required
cb : required # cb(err, account_id or undefined) -- actual account_id if it exists; err = problem with db connection...
@_query
query : 'SELECT account_id FROM accounts'
where : "email_address = $::TEXT" : opts.email_address
cb : one_result('account_id', opts.cb)
# set an account creation action, or return all of them for the given email address
account_creation_actions: (opts) =>
opts = defaults opts,
email_address : required
action : undefined # if given, adds this action; if not, returns all non-expired actions
ttl : 60*60*24*14 # add action with this ttl in seconds (default: 2 weeks)
cb : required # if ttl not given cb(err, [array of actions])
if opts.action?
# add action
@_query
query : 'INSERT INTO account_creation_actions'
values :
'id :: UUID' : misc.uuid()
'email_address :: TEXT' : opts.email_address
'action :: JSONB' : opts.action
'expire :: TIMESTAMP' : expire_time(opts.ttl)
cb : opts.cb
else
# query for actions
@_query
query : 'SELECT action FROM account_creation_actions'
where :
'email_address = $::TEXT' : opts.email_address
'expire >= $::TIMESTAMP' : new Date()
cb : all_results('action', opts.cb)
account_creation_actions_success: (opts) =>
opts = defaults opts,
account_id : required
cb : required
@_query
query : 'UPDATE accounts'
set :
'creation_actions_done::BOOLEAN' : true
where :
'account_id = $::UUID' : opts.account_id
cb : opts.cb
# DEPRECATED: use import accountCreationActions from "@cocalc/server/accounts/account-creation-actions"; instead!!!!
do_account_creation_actions: (opts) =>
opts = defaults opts,
email_address : required
account_id : required
cb : required
dbg = @_dbg("do_account_creation_actions(email_address='#{opts.email_address}')")
dbg("**DEPRECATED!** This will miss doing important things, e.g., creating initial project.")
@account_creation_actions
email_address : opts.email_address
cb : (err, actions) =>
if err
opts.cb(err); return
f = (action, cb) =>
dbg("account_creation_actions: action = #{misc.to_json(action)}")
if action.action == 'add_to_project'
@add_user_to_project
project_id : action.project_id
account_id : opts.account_id
group : action.group
cb : (err) =>
if err
dbg("Error adding user to project: #{err}")
cb(err)
else
dbg("ERROR: skipping unknown action -- #{action.action}")
# also store in database so we can look into this later.
@log
event : 'unknown_action'
value :
error : "unknown_action"
action : action
account_id : opts.account_id
host : require('os').hostname()
cb()
async.map actions, f, (err) =>
if not err
@account_creation_actions_success
account_id : opts.account_id
cb : opts.cb
else
opts.cb(err)
verify_email_create_token: (opts) => # has been rewritten in backend/email/verify.ts
opts = defaults opts,
account_id : required
cb : undefined
locals =
email_address : undefined
token : undefined
old_challenge : undefined
async.series([
(cb) =>
@_query
query : "SELECT email_address, email_address_challenge FROM accounts"
where : "account_id = $::UUID" : opts.account_id
cb : one_result (err, x) =>
locals.email_address = x?.email_address
locals.old_challenge = x?.email_address_challenge
cb(err)
(cb) =>
# TODO maybe expire tokens after some time
if locals.old_challenge?
old = locals.old_challenge
# return the same token if there is one for the same email
if old.token? and old.email == locals.email_address
locals.token = locals.old_challenge.token
cb()
return
{generate} = require("random-key")
locals.token = generate(16).toLowerCase()
data =
email : locals.email_address
token : locals.token
time : new Date()
@_query
query : "UPDATE accounts"
set :
'email_address_challenge::JSONB' : data
where :
"account_id = $::UUID" : opts.account_id
cb : cb
], (err) ->
opts.cb?(err, locals)
)
verify_email_check_token: (opts) => # rewritten in server/auth/redeem-verify-email.ts
opts = defaults opts,
email_address : required
token : required
cb : undefined
locals =
account_id : undefined
email_address_challenge : undefined
async.series([
(cb) =>
@get_account
email_address : opts.email_address
columns : ['account_id', 'email_address_challenge']
cb : (err, x) =>
if err
cb(err)
else if not x?
cb("no such email address")
else
locals.account_id = x.account_id
locals.email_address_challenge = x.email_address_challenge
cb()
(cb) =>
if not locals.email_address_challenge?
@is_verified_email
email_address : opts.email_address
cb : (err, verified) ->
if not err and verified
cb("This email address is already verified.")
else
cb("For this email address no account verification is setup.")
else if locals.email_address_challenge.email != opts.email_address
cb("The account's email address does not match the token's email address.")
else if locals.email_address_challenge.time < misc.hours_ago(24)
cb("The account verification token is no longer valid. Get a new one!")
else
if locals.email_address_challenge.token == opts.token
cb()
else
cb("Provided token does not match.")
(cb) =>
# we're good, save it
@_query
query : "UPDATE accounts"
jsonb_set :
email_address_verified:
"#{opts.email_address}" : new Date()
where : "account_id = $::UUID" : locals.account_id
cb : cb
(cb) =>
# now delete the token
@_query
query : 'UPDATE accounts'
set :
'email_address_challenge::JSONB' : null
where :
"account_id = $::UUID" : locals.account_id
cb : cb
], opts.cb)
# returns the email address and whether or not it is verified
verify_email_get: (opts) =>
opts = defaults opts,
account_id : required
cb : undefined
@_query
query : "SELECT email_address, email_address_verified FROM accounts"
where : "account_id = $::UUID" : opts.account_id
cb : one_result (err, x) ->
opts.cb?(err, x)
# answers the question as cb(null, [true or false])
is_verified_email: (opts) => # rewritten in server/auth/redeem-verify-email.ts
opts = defaults opts,
email_address : required
cb : required
@get_account
email_address : opts.email_address
columns : ['email_address_verified']
cb : (err, x) =>
if err
opts.cb(err)
else if not x?
opts.cb("no such email address")
else
verified = !!x.email_address_verified?[opts.email_address]
opts.cb(undefined, verified)
###
Auxiliary billing related queries
###
get_coupon_history: (opts) =>
opts = defaults opts,
account_id : required
cb : undefined
@_dbg("Getting coupon history")
@_query
query : "SELECT coupon_history FROM accounts"
where : 'account_id = $::UUID' : opts.account_id
cb : one_result("coupon_history", opts.cb)
update_coupon_history: (opts) =>
opts = defaults opts,
account_id : required
coupon_history : required
cb : undefined
@_dbg("Setting to #{opts.coupon_history}")
@_query
query : 'UPDATE accounts'
set : 'coupon_history::JSONB' : opts.coupon_history
where : 'account_id = $::UUID' : opts.account_id
cb : opts.cb
###
Querying for searchable information about accounts.
###
account_ids_to_usernames: (opts) =>
opts = defaults opts,
account_ids : required
cb : required # (err, mapping {account_id:{first_name:?, last_name:?}})
if not @_validate_opts(opts) then return
if opts.account_ids.length == 0 # easy special case -- don't waste time on a db query
opts.cb(undefined, [])
return
@_query
query : 'SELECT account_id, first_name, last_name FROM accounts'
where : 'account_id = ANY($::UUID[])' : opts.account_ids
cb : (err, result) =>
if err
opts.cb(err)
else
v = misc.dict(([r.account_id, {first_name:r.first_name, last_name:r.last_name}] for r in result.rows))
# fill in unknown users (should never be hit...)
for id in opts.account_ids
if not v[id]?
v[id] = {first_name:undefined, last_name:undefined}
opts.cb(err, v)
_account_where: (opts) =>
# account_id > email_address > lti_id
if opts.account_id
return {"account_id = $::UUID" : opts.account_id}
else if opts.email_address
return {"email_address = $::TEXT" : opts.email_address}
else if opts.lti_id
return {"lti_id = $::TEXT[]" : opts.lti_id}
else
throw Error("postgres-server-queries::_account_where neither account_id, nor email_address, nor lti_id specified and nontrivial")
get_account: (opts) =>
opts = defaults opts,
email_address : undefined # provide one of email, account_id, or lti_id (pref is account_id, then email_address, then lti_id)
account_id : undefined
lti_id : undefined
columns : ['account_id',
'password_hash',
'password_is_set', # true or false, depending on whether a password is set (since don't send password_hash to user!)
'first_name',
'last_name',
'email_address',
'evaluate_key',
'autosave',
'terminal',
'editor_settings',
'other_settings',
'groups',
'passports'
]
cb : required
if not @_validate_opts(opts) then return
columns = misc.copy(opts.columns)
if 'password_is_set' in columns
if 'password_hash' not in columns
remove_password_hash = true
columns.push('password_hash')
misc.remove(columns, 'password_is_set')
password_is_set = true
@_query
query : "SELECT #{columns.join(',')} FROM accounts"
where : @_account_where(opts)
cb : one_result (err, z) =>
if err
opts.cb(err)
else if not z?
opts.cb("no such account")
else
if password_is_set
z.password_is_set = !!z.password_hash
if remove_password_hash
delete z.password_hash
for c in columns
if not z[c]? # for same semantics as rethinkdb... (for now)
delete z[c]
opts.cb(undefined, z)
# check whether or not a user is banned
is_banned_user: (opts) =>
opts = defaults opts,
email_address : undefined
account_id : undefined
cb : required # cb(err, true if banned; false if not banned)
if not @_validate_opts(opts) then return
@_query
query : 'SELECT banned FROM accounts'
where : @_account_where(opts)
cb : one_result('banned', (err, banned) => opts.cb(err, !!banned))
_touch_account: (account_id, cb) =>
if @_throttle('_touch_account', 120, account_id)
cb()
return
@_query
query : 'UPDATE accounts'
set : {last_active: 'NOW()'}
where : "account_id = $::UUID" : account_id
cb : cb
_touch_project: (project_id, account_id, cb) =>
if @_throttle('_user_touch_project', 60, project_id, account_id)
cb()
return
NOW = new Date()
@_query
query : "UPDATE projects"
set : {last_edited : NOW}
jsonb_merge : {last_active:{"#{account_id}":NOW}}
where : "project_id = $::UUID" : project_id
cb : cb
# Indicate activity by a user, possibly on a specific project, and
# then possibly on a specific path in that project.
touch: (opts) =>
opts = defaults opts,
account_id : required
project_id : undefined
path : undefined
action : 'edit'
ttl_s : 50 # min activity interval; calling this function with same input again within this interval is ignored
cb : undefined
if opts.ttl_s
if @_throttle('touch', opts.ttl_s, opts.account_id, opts.project_id, opts.path, opts.action)
opts.cb?()
return
now = new Date()
async.parallel([
(cb) =>
@_touch_account(opts.account_id, cb)
(cb) =>
if not opts.project_id?
cb(); return
@_touch_project(opts.project_id, opts.account_id, cb)
(cb) =>
if not opts.path? or not opts.project_id?
cb(); return
@record_file_use(project_id:opts.project_id, path:opts.path, action:opts.action, account_id:opts.account_id, cb:cb)
], (err)->opts.cb?(err))
# Invalidate all outstanding remember me cookies for the given account by
# deleting them from the remember_me key:value store.
invalidate_all_remember_me: (opts) =>
opts = defaults opts,
account_id : undefined
email_address : undefined
cb : undefined
if not @_validate_opts(opts) then return
@_query
query : 'DELETE FROM remember_me'
where : @_account_where(opts)
cb : opts.cb
# Get remember me cookie with given hash. If it has expired,
# **get back undefined instead**. (Actually deleting expired).
# We use retry_until_success, since an intermittent database
# reconnect can result in a cb error that will very soon
# work fine, and we don't to flat out sign the client out
# just because of this.
get_remember_me: (opts) =>
opts = defaults opts,
hash : required
cache : true
cb : required # cb(err, signed_in_message | undefined)
account_id = undefined
try
account_id = await get_remember_me(@, opts.hash, opts.cache)
catch err
opts.cb(err)
return
if account_id
opts.cb(undefined, {event:"signed_in", account_id:account_id})
else
opts.cb()
delete_remember_me: (opts) =>
opts = defaults opts,
hash : required
cb : undefined
@_query
query : 'DELETE FROM remember_me'
where :
'hash = $::TEXT' : opts.hash.slice(0,127)
cb : opts.cb