This repository has been archived by the owner on Mar 17, 2018. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
db_api.sql
1052 lines (942 loc) · 33.2 KB
/
db_api.sql
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
-- Regano database function definitions
--
-- Uses PostgreSQL extensions.
--
-- These functions are intended to be called from the Web UI or other frontend.
--
-- Regano is a domain registration system for OpenNIC TLDs written in
-- Perl. This file is part of Regano.
--
-- Regano may be distributed under the same terms as Perl itself. Of
-- particular importance, note that while regano is distributed in the
-- hope that it will be useful, there is NO WARRANTY OF ANY KIND
-- WHATSOEVER WHETHER EXPLICIT OR IMPLIED.
-- The type definitions in db_types.sql must already be installed.
-- The table definitions in db_tables.sql must already be installed.
-- The function definitions in db_functions.sql must already be installed.
-- The configuration in db_config.sql must be loaded for these to actually work.
-- Inquire about the status of a domain.
CREATE OR REPLACE FUNCTION regano_api.domain_status
(regano.dns_fqdn)
RETURNS regano.domain_status AS $$
DECLARE
name ALIAS FOR $1;
max_expired_age CONSTANT interval NOT NULL
:= (regano.config_get('domain/grace_period')).interval;
max_pending_age CONSTANT interval NOT NULL
:= (regano.config_get('domain/pend_term')).interval;
active_domain regano.domains%ROWTYPE;
primary_label regano.dns_label;
tail regano.dns_fqdn;
BEGIN
PERFORM *
FROM regano.bailiwicks
WHERE lower(domain_tail) = lower(name)
OR lower(domain_tail) = '.'||lower(name);
IF FOUND THEN
RETURN 'BAILIWICK';
END IF;
primary_label := substring(name from '^([^.]+)[.]');
tail := substring(name from '^[^.]+([.].+[.])$');
PERFORM * FROM regano.bailiwicks WHERE lower(domain_tail) = lower(tail);
IF NOT FOUND THEN
RETURN 'ELSEWHERE';
END IF;
PERFORM * FROM regano.reserved_domains
WHERE domain_name = lower(primary_label);
IF FOUND THEN
RETURN 'RESERVED';
END IF;
-- clean up pending domains, then check if the requested domain is pending
DELETE FROM regano.pending_domains WHERE start < (now() - max_pending_age);
PERFORM * FROM regano.pending_domains
WHERE lower(domain_name) = lower(primary_label)
AND lower(domain_tail) = lower(tail);
IF FOUND THEN
RETURN 'PENDING';
END IF;
-- clean up expired domains, then check if the requested domain is active
DELETE FROM regano.domains WHERE expiration < (now() - max_expired_age);
SELECT * INTO active_domain
FROM regano.domains
WHERE (lower(primary_label) = lower(domain_name))
AND (lower(tail) = lower(domain_tail));
IF FOUND THEN
IF now() < active_domain.expiration THEN
RETURN 'REGISTERED';
ELSE
RETURN 'EXPIRED';
END IF;
END IF;
RETURN 'AVAILABLE';
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.domain_status (regano.dns_fqdn)
OWNER TO regano;
-- Inquire why a domain is reserved.
CREATE OR REPLACE FUNCTION regano_api.domain_reservation_reason
(regano.dns_fqdn)
RETURNS text AS $$
SELECT CASE WHEN regano_api.domain_status($1) <> 'RESERVED' THEN NULL
ELSE reason END
FROM regano.reserved_domains
WHERE domain_name = substring($1 from '^([^.]+)[.]')
$$ LANGUAGE SQL STABLE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.domain_reservation_reason (regano.dns_fqdn)
OWNER TO regano;
-- Inquire how a domain is handled.
CREATE OR REPLACE FUNCTION regano_api.domain_mode
(regano.dns_fqdn)
RETURNS regano.domain_mode AS $$
DECLARE
name ALIAS FOR $1;
active_domain regano.domains%ROWTYPE;
BEGIN
SELECT * INTO active_domain
FROM regano.domains
WHERE lower(name) = lower(domain_name||domain_tail);
IF NOT FOUND OR now() > active_domain.expiration THEN
RETURN NULL;
END IF;
PERFORM * FROM regano.domain_records
WHERE domain_id = active_domain.id AND seq_no = 0 AND type = 'SOA';
IF FOUND THEN
RETURN 'HOSTED';
END IF;
PERFORM * FROM regano.domain_records r
WHERE domain_id = active_domain.id AND r.name = '@' AND type = 'NS';
IF FOUND THEN
RETURN 'DELEGATED';
END IF;
RETURN 'INLINE';
END;
$$ LANGUAGE plpgsql STABLE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.domain_mode (regano.dns_fqdn)
OWNER TO regano;
-- Create a new user account.
CREATE OR REPLACE FUNCTION regano_api.user_register
(text, regano.password, text, text)
RETURNS void AS $$
DECLARE
username_ ALIAS FOR $1;
password_ ALIAS FOR $2;
contact_name ALIAS FOR $3;
contact_email ALIAS FOR $4;
crypt_alg CONSTANT text NOT NULL
:= (regano.config_get('auth/crypt')).text;
crypt_iter CONSTANT integer NOT NULL
:= (regano.config_get('auth/crypt')).number;
new_user_id bigint; -- row ID of new user record
BEGIN
INSERT INTO users (username, password, contact_id)
VALUES (username_, ROW(password_.xdigest, password_.xsalt,
crypt(password_.digest,
gen_salt(crypt_alg, crypt_iter))), 1)
RETURNING id INTO STRICT new_user_id;
INSERT INTO contacts (owner_id, id, name, email)
VALUES (new_user_id, 1, contact_name, contact_email);
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.user_register (text, regano.password, text, text)
OWNER TO regano;
-- Get the external digest algorithm and salt for a user.
CREATE OR REPLACE FUNCTION regano_api.user_get_salt_info (text)
RETURNS regano.password AS $$
DECLARE
username_ ALIAS FOR $1;
password_ regano.password;
BEGIN
SELECT (password).xdigest, (password).xsalt INTO password_
FROM regano.users WHERE (username = username_);
IF NOT FOUND THEN
-- return an unspecified record to impede timing attacks
SELECT (password).xdigest, (password).xsalt INTO password_
FROM regano.users FETCH FIRST 1 ROW ONLY;
END IF;
RETURN password_;
END;
$$ LANGUAGE plpgsql STABLE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.user_get_salt_info (text)
OWNER TO regano;
-- Begin a session for a user.
CREATE OR REPLACE FUNCTION regano_api.user_login
(text, regano.password)
RETURNS uuid AS $$
<<var>>
DECLARE
username ALIAS FOR $1;
password ALIAS FOR $2;
crypt_alg CONSTANT text NOT NULL
:= (regano.config_get('auth/crypt')).text;
crypt_iter CONSTANT integer NOT NULL
:= (regano.config_get('auth/crypt')).number;
max_age CONSTANT interval NOT NULL
:= (regano.config_get('session/max_age')).interval;
user_id bigint; -- row ID of user record
stored_pw text; -- password hash from database
session_id uuid; -- session ID
BEGIN
SELECT id, (regano.users.password).digest INTO user_id, stored_pw
FROM regano.users WHERE (regano.users.username = var.username);
IF NOT FOUND THEN
-- fake a stored password to impede timing attacks
stored_pw := gen_salt(crypt_alg, crypt_iter);
END IF;
-- clean up expired sessions
DELETE FROM regano.sessions WHERE start < (CURRENT_TIMESTAMP - max_age);
-- verify password; note that a bare salt cannot match any hash
IF crypt(password.digest, stored_pw) = stored_pw THEN
-- login successful
INSERT INTO regano.sessions (id, user_id)
VALUES (gen_random_uuid(), user_id)
RETURNING id INTO STRICT session_id;
RETURN session_id;
ELSE
-- login failed
RETURN NULL;
END IF;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.user_login (text, regano.password)
OWNER TO regano;
-- Change a logged-in user's password.
CREATE OR REPLACE FUNCTION regano_api.user_change_password
(uuid, regano.password, regano.password)
RETURNS boolean AS $$
DECLARE
session_id ALIAS FOR $1;
old_pw ALIAS FOR $2;
new_pw ALIAS FOR $3;
crypt_alg CONSTANT text NOT NULL
:= (regano.config_get('auth/crypt')).text;
crypt_iter CONSTANT integer NOT NULL
:= (regano.config_get('auth/crypt')).number;
user_id bigint; -- row ID of user record
BEGIN
SELECT regano.sessions.user_id INTO user_id
FROM regano.sessions WHERE id = session_id;
IF NOT FOUND THEN
RETURN FALSE;
END IF;
new_pw.digest := crypt(new_pw.digest, gen_salt(crypt_alg, crypt_iter));
UPDATE regano.users
SET password = new_pw
WHERE ((id = user_id) AND
(crypt(old_pw.digest, (regano.users.password).digest) =
(regano.users.password).digest));
RETURN FOUND;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.user_change_password
(uuid, regano.password, regano.password)
OWNER TO regano;
-- End a session.
CREATE OR REPLACE FUNCTION regano_api.session_logout (uuid) RETURNS void AS $$
DELETE FROM regano.sessions WHERE id = $1
$$ LANGUAGE SQL VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.session_logout (uuid)
OWNER TO regano;
-- Retrieve username for a session, update session activity timestamp, and
-- perform auto-logout if the session has expired.
CREATE OR REPLACE FUNCTION regano_api.session_check (uuid)
RETURNS text AS $$
<<var>>
DECLARE
id ALIAS FOR $1;
max_age CONSTANT interval NOT NULL
:= (regano.config_get('session/max_age')).interval;
max_idle CONSTANT interval NOT NULL
:= (regano.config_get('session/max_idle')).interval;
session regano.sessions%ROWTYPE;
BEGIN
SELECT * INTO session
FROM regano.sessions WHERE regano.sessions.id = var.id;
IF FOUND THEN
IF ((CURRENT_TIMESTAMP - session.activity) > max_idle) OR
((CURRENT_TIMESTAMP - session.start) > max_age) THEN
-- session is expired
PERFORM regano_api.session_logout(session.id);
RETURN NULL;
ELSE
-- update activity timestamp
UPDATE regano.sessions
SET activity = CURRENT_TIMESTAMP
WHERE regano.sessions.id = var.id;
END IF;
ELSE
-- no such session exists
RETURN NULL;
END IF;
RETURN regano.username(session);
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.session_check (uuid)
OWNER TO regano;
-- Retrieve the current user's user record, sans password.
CREATE OR REPLACE FUNCTION regano_api.user_info
(session_id uuid)
RETURNS regano.users AS $$
DECLARE
user regano.users%ROWTYPE;
BEGIN
SELECT * INTO STRICT user
FROM regano.users WHERE id = regano.session_user_id(session_id);
user.password := NULL;
RETURN user;
END;
$$ LANGUAGE plpgsql STABLE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.user_info (uuid)
OWNER TO regano;
-- Retrieve the ID of the current user's primary contact record.
CREATE OR REPLACE FUNCTION regano_api.contact_primary_id
(session_id uuid)
RETURNS integer AS $$
SELECT contact_id FROM regano.users WHERE id = regano.session_user_id($1)
$$ LANGUAGE SQL STABLE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.contact_primary_id (uuid)
OWNER TO regano;
-- Change the primary contact for the current user.
CREATE OR REPLACE FUNCTION regano_api.user_set_primary_contact
(session_id uuid, contact_id integer)
RETURNS void AS $$
DECLARE
contact regano.contacts%ROWTYPE;
session regano.sessions%ROWTYPE;
BEGIN
SELECT * INTO STRICT session FROM regano.sessions WHERE id = session_id;
SELECT * INTO STRICT contact FROM regano.contacts
WHERE owner_id = session.user_id AND id = contact_id;
IF NOT contact.email_verified THEN
RAISE EXCEPTION
'Only a verified email address may be set as primary contact.';
END IF;
UPDATE regano.users
SET contact_id = contact.id
WHERE id = session.user_id;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.user_set_primary_contact (uuid, integer)
OWNER TO regano;
-- Retrieve all contact records belonging to the current user.
CREATE OR REPLACE FUNCTION regano_api.contact_list
(session_id uuid)
RETURNS SETOF regano.contacts AS $$
SELECT * FROM regano.contacts WHERE owner_id = regano.session_user_id($1)
$$ LANGUAGE SQL STABLE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.contact_list (uuid)
OWNER TO regano;
-- Add a contact record for the current user.
CREATE OR REPLACE FUNCTION regano_api.contact_add
(session_id uuid, name text, email text)
RETURNS integer AS $$
INSERT INTO regano.contacts (owner_id, id, name, email)
VALUES (regano.session_user_id($1),
regano.contact_next_id(regano.session_user_id($1)), $2, $3)
RETURNING id;
$$ LANGUAGE SQL VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.contact_add (uuid, text, text)
OWNER TO regano;
-- Remove a contact record for the current user.
CREATE OR REPLACE FUNCTION regano_api.contact_remove
(session_id uuid, contact_id integer)
RETURNS void AS $$
DECLARE
user_id CONSTANT bigint NOT NULL
:= regano.session_user_id(session_id);
renumbering CURSOR (user_id bigint)
FOR SELECT id
FROM regano.contacts
WHERE owner_id = user_id
ORDER BY id
FOR UPDATE;
i integer := 0;
BEGIN
DELETE
FROM regano.contacts
WHERE owner_id = user_id AND id = contact_id;
FOR contact IN renumbering (user_id) LOOP
i := i + 1;
UPDATE regano.contacts
SET id = i
WHERE CURRENT OF renumbering;
END LOOP;
END
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.contact_remove (uuid, integer)
OWNER TO regano;
-- Update the name field of a contact record.
CREATE OR REPLACE FUNCTION regano_api.contact_update_name
(session_id uuid, contact_id integer, value text)
RETURNS void AS $$
DECLARE
contact regano.contacts%ROWTYPE;
session regano.sessions%ROWTYPE;
BEGIN
SELECT * INTO STRICT session FROM regano.sessions WHERE id = session_id;
SELECT * INTO STRICT contact FROM regano.contacts
WHERE owner_id = session.user_id AND id = contact_id
FOR UPDATE;
UPDATE regano.contacts
SET name = value
WHERE owner_id = session.user_id AND id = contact_id;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.contact_update_name (uuid, integer, text)
OWNER TO regano;
-- Update the email address field of a contact record.
CREATE OR REPLACE FUNCTION regano_api.contact_update_email
(session_id uuid, contact_id integer, value text)
RETURNS void AS $$
DECLARE
contact regano.contacts%ROWTYPE;
session regano.sessions%ROWTYPE;
primary_contact_id integer;
BEGIN
SELECT * INTO STRICT session FROM regano.sessions WHERE id = session_id;
SELECT users.contact_id INTO STRICT primary_contact_id
FROM regano.users WHERE id = session.user_id;
SELECT * INTO STRICT contact FROM regano.contacts
WHERE owner_id = session.user_id AND id = contact_id
FOR UPDATE;
IF contact_id = primary_contact_id AND contact.email_verified THEN
RAISE EXCEPTION
'Verified email address (%) for primary contact (%) may not be changed.',
contact.email, contact_id;
END IF;
-- cancel any in-progress address verification
DELETE FROM regano.contact_verifications
WHERE contact_verifications.user_id = session.user_id
AND contact_verifications.contact_id = contact.id;
-- change the stored email address
UPDATE regano.contacts
SET email_verified = FALSE, email = value
WHERE owner_id = session.user_id AND id = contact_id;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.contact_update_email (uuid, integer, text)
OWNER TO regano;
-- Begin the process of verifying a contact record.
CREATE OR REPLACE FUNCTION regano_api.contact_verify_begin
(session_id uuid, contact_id integer)
RETURNS void AS $$
DECLARE
contact regano.contacts%ROWTYPE;
session regano.sessions%ROWTYPE;
BEGIN
SELECT * INTO STRICT session FROM regano.sessions WHERE id = session_id;
SELECT * INTO STRICT contact FROM regano.contacts
WHERE owner_id = session.user_id AND id = contact_id;
DELETE FROM regano.contact_verifications
WHERE contact_verifications.contact_id = contact_verify_begin.contact_id
AND contact_verifications.user_id = session.user_id;
INSERT INTO regano.contact_verifications (id, key, user_id, contact_id)
VALUES (gen_random_uuid(), gen_random_uuid(),
session.user_id, contact_id);
NOTIFY regano__contact_verifications;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.contact_verify_begin (uuid, integer)
OWNER TO regano;
-- Complete verification of a contact record.
CREATE OR REPLACE FUNCTION regano_api.contact_verify_complete
(verification_id uuid, key uuid)
RETURNS boolean AS $$
<<var>>
DECLARE
domain_term CONSTANT interval NOT NULL
:= (regano.config_get('domain/term')).interval;
max_age CONSTANT interval NOT NULL
:= (regano.config_get('verify/max_age')).interval;
pending_domain regano.pending_domains%ROWTYPE;
verification regano.contact_verifications%ROWTYPE;
is_primary_contact boolean;
BEGIN
-- clean up expired verifications
DELETE
FROM regano.contact_verifications
WHERE start < (CURRENT_TIMESTAMP - max_age);
-- look up the provided verification ID
SELECT * INTO verification
FROM regano.contact_verifications
WHERE (id = verification_id) AND
(contact_verifications.key = contact_verify_complete.key);
IF NOT FOUND THEN
RETURN FALSE;
END IF;
-- mark email address as verified
UPDATE regano.contacts
SET email_verified = TRUE
WHERE owner_id = verification.user_id AND id = verification.contact_id;
-- check if a primary contact was verified
SELECT users.contact_id = verification.contact_id
INTO STRICT is_primary_contact
FROM regano.users
WHERE id = verification.user_id;
-- check for a pending domain
SELECT * INTO pending_domain
FROM regano.pending_domains
WHERE pending_domains.user_id = verification.user_id;
IF FOUND AND is_primary_contact THEN
-- register the pending domain
DELETE
FROM regano.pending_domains
WHERE domain_name = pending_domain.domain_name
AND domain_tail = pending_domain.domain_tail;
INSERT INTO regano.domains
(domain_name, domain_tail, owner_id, expiration)
VALUES (pending_domain.domain_name, pending_domain.domain_tail,
verification.user_id, now() + domain_term);
END IF;
-- clean up the successful verification
DELETE
FROM regano.contact_verifications
WHERE id = verification_id;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.contact_verify_complete (uuid, uuid)
OWNER TO regano;
-- Retrieve information about a pending domain belonging to the current user.
CREATE OR REPLACE FUNCTION regano_api.domain_check_pending
(uuid)
RETURNS regano.pending_domain AS $$
SELECT domain_name||domain_tail AS name,
start, start + regano.config_get('domain/pend_term') AS expire
FROM regano.pending_domains WHERE user_id = regano.session_user_id($1)
$$ LANGUAGE SQL STABLE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.domain_check_pending (uuid)
OWNER TO regano;
-- Retrieve all domains belonging to the current user.
-- The domain table is public; this is for the account overview page.
CREATE OR REPLACE FUNCTION regano_api.domain_list
(uuid)
RETURNS SETOF regano.domain AS $$
SELECT domain_name||domain_tail AS name, registered, expiration, last_update,
CASE WHEN now() < expiration
THEN 'REGISTERED'::regano.domain_status
ELSE 'EXPIRED'::regano.domain_status
END AS status
FROM regano.domains WHERE owner_id = regano.session_user_id($1)
$$ LANGUAGE SQL STABLE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.domain_list (uuid)
OWNER TO regano;
-- Register an available domain.
CREATE OR REPLACE FUNCTION regano_api.domain_register
(uuid, regano.dns_fqdn)
RETURNS regano.domain_status AS $$
<<var>>
DECLARE
session_id ALIAS FOR $1;
name ALIAS FOR $2;
domain_term CONSTANT interval NOT NULL
:= (regano.config_get('domain/term')).interval;
user_id CONSTANT bigint NOT NULL
:= regano.session_user_id(session_id);
verified boolean; -- verified email address on file?
primary_label regano.dns_label;
tail regano.dns_fqdn;
BEGIN
primary_label := substring(name from '^([^.]+)[.]');
tail := substring(name from '^[^.]+([.].+[.])$');
IF regano_api.domain_status(name) <> 'AVAILABLE' THEN
RETURN regano_api.domain_status(name);
END IF;
SELECT email_verified INTO STRICT verified
FROM regano.users JOIN regano.contacts
ON owner_id = user_id AND contact_id = contacts.id
WHERE regano.users.id = user_id;
IF verified THEN
-- user has a verified email address; register the domain now
INSERT INTO regano.domains
(domain_name, domain_tail, owner_id, expiration)
VALUES (primary_label, tail, user_id, now() + domain_term);
RETURN 'REGISTERED';
ELSE
-- no verified email address on file; registration will be pending
INSERT INTO regano.pending_domains
(domain_name, domain_tail, user_id)
VALUES (primary_label, tail, user_id);
RETURN 'PENDING';
END IF;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.domain_register (uuid, regano.dns_fqdn)
OWNER TO regano;
-- Renew a domain
CREATE OR REPLACE FUNCTION regano_api.domain_renew
(uuid, regano.dns_fqdn)
RETURNS timestamp with time zone AS $$
DECLARE
session_id ALIAS FOR $1;
name ALIAS FOR $2;
domain_term CONSTANT interval NOT NULL
:= (regano.config_get('domain/term')).interval;
user_id CONSTANT bigint NOT NULL
:= regano.session_user_id(session_id);
primary_label regano.dns_label;
tail regano.dns_fqdn;
domain regano.domains%ROWTYPE;
result timestamp with time zone;
BEGIN
primary_label := substring(name from '^([^.]+)[.]');
tail := substring(name from '^[^.]+([.].+[.])$');
SELECT * INTO STRICT domain
FROM regano.domains
WHERE (lower(primary_label) = lower(domain_name))
AND (lower(tail) = lower(domain_tail))
FOR UPDATE;
IF user_id <> domain.owner_id THEN
RAISE EXCEPTION
'attempt made to renew domain (%) not belonging to current user (%)',
name, regano.username(session_id);
END IF;
UPDATE regano.domains
SET expiration = now() + domain_term,
last_update = now()
WHERE id = domain.id
RETURNING expiration INTO STRICT result;
RETURN result;
END
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.domain_renew (uuid, regano.dns_fqdn)
OWNER TO regano;
-- Immediately expire a domain
CREATE OR REPLACE FUNCTION regano_api.domain_release
(uuid, regano.dns_fqdn)
RETURNS void AS $$
DECLARE
session_id ALIAS FOR $1;
name ALIAS FOR $2;
user_id CONSTANT bigint NOT NULL
:= regano.session_user_id(session_id);
primary_label regano.dns_label;
tail regano.dns_fqdn;
domain regano.domains%ROWTYPE;
BEGIN
primary_label := substring(name from '^([^.]+)[.]');
tail := substring(name from '^[^.]+([.].+[.])$');
SELECT * INTO STRICT domain
FROM regano.domains
WHERE (lower(primary_label) = lower(domain_name))
AND (lower(tail) = lower(domain_tail))
FOR UPDATE;
IF user_id <> domain.owner_id THEN
RAISE EXCEPTION
'attempt made to release domain (%) not belonging to current user (%)',
name, regano.username(session_id);
END IF;
UPDATE regano.domains
SET expiration = now(),
last_update = now()
WHERE id = domain.id;
END
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.domain_release (uuid, regano.dns_fqdn)
OWNER TO regano;
-- Set default TTL for records in a domain
CREATE OR REPLACE FUNCTION regano_api.domain_set_default_ttl
(uuid, regano.dns_fqdn, regano.dns_interval)
RETURNS void AS $$
DECLARE
session_id ALIAS FOR $1;
name ALIAS FOR $2;
new_ttl ALIAS FOR $3;
user_id CONSTANT bigint NOT NULL
:= regano.session_user_id(session_id);
primary_label regano.dns_label;
tail regano.dns_fqdn;
domain regano.domains%ROWTYPE;
BEGIN
primary_label := substring(name from '^([^.]+)[.]');
tail := substring(name from '^[^.]+([.].+[.])$');
SELECT * INTO STRICT domain
FROM regano.domains
WHERE (lower(primary_label) = lower(domain_name))
AND (lower(tail) = lower(domain_tail))
FOR UPDATE;
IF user_id <> domain.owner_id THEN
RAISE EXCEPTION
'attempt made to set TTL for domain (%) not belonging to current user (%)',
name, regano.username(session_id);
END IF;
UPDATE regano.domains
SET default_ttl = new_ttl,
last_update = now()
WHERE id = domain.id;
END
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.domain_set_default_ttl
(uuid, regano.dns_fqdn, regano.dns_interval)
OWNER TO regano;
-- Updating domain records is done in multiple steps, all in a single
-- database transaction. First, the existing records for the domain are
-- removed. Second, new records are inserted in order. Third, the
-- database transaction is committed.
-- Remove existing records for a domain
CREATE OR REPLACE FUNCTION regano_api.zone_clear
(session_id uuid,
zone_name regano.dns_fqdn)
RETURNS void AS $$
DECLARE
domain regano.domains%ROWTYPE;
BEGIN
domain := regano.zone_verify_access(session_id, zone_name, 'clear zone');
DELETE
FROM regano.domain_records
WHERE domain_id = domain.id;
UPDATE regano.domains
SET last_update = now()
WHERE id = domain.id;
END
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION regano_api.zone_clear (uuid, regano.dns_fqdn)
OWNER TO regano;
-- Add an SOA record for a domain
-- NOTE: A domain may only have one SOA record, at the domain root, with
-- sequence number zero.
CREATE OR REPLACE FUNCTION regano_api.zone_add_SOA
(session_id uuid,
zone_name regano.dns_fqdn,
rec_ttl regano.dns_interval,
SOA_mbox regano.dns_email,
SOA_refresh regano.dns_interval,
SOA_retry regano.dns_interval,
SOA_expire regano.dns_interval,
SOA_minimum regano.dns_interval)
RETURNS void AS $$
DECLARE
domain regano.domains%ROWTYPE;
BEGIN
domain := regano.zone_verify_access(session_id, zone_name, 'add SOA');
INSERT INTO regano.domain_records
(domain_id, seq_no, type, name, ttl, data_RR_SOA)
VALUES (domain.id, 0, 'SOA', '@', rec_ttl,
ROW(zone_name, SOA_mbox, SOA_refresh, SOA_retry,
SOA_expire, SOA_minimum));
UPDATE regano.domains
SET last_update = now()
WHERE id = domain.id;
END
$$ LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
ALTER FUNCTION regano_api.zone_add_SOA
(uuid, regano.dns_fqdn, regano.dns_interval, regano.dns_email,
regano.dns_interval, regano.dns_interval, regano.dns_interval,
regano.dns_interval)
OWNER TO regano;
-- Add a record that stores another DNS name
CREATE OR REPLACE FUNCTION regano_api.zone_add_name
(session_id uuid,
zone_name regano.dns_fqdn,
rec_ttl regano.dns_interval,
rec_name regano.dns_name,
rec_type regano.dns_record_type,
rec_data regano.dns_name)
RETURNS void AS $$
DECLARE
domain regano.domains%ROWTYPE;
new_seq_no bigint;
rec_name_c CONSTANT regano.dns_name NOT NULL
:= regano.canonicalize_record_name(rec_name,
zone_name);
BEGIN
domain := regano.zone_verify_access(session_id, zone_name, 'add name');
new_seq_no := regano.zone_next_seq_no(domain.id);
INSERT INTO regano.domain_records
(domain_id, seq_no, type, name, ttl, data_name)
VALUES (domain.id, new_seq_no, rec_type, rec_name_c, rec_ttl, rec_data);
UPDATE regano.domains
SET last_update = now()
WHERE id = domain.id;
END
$$ LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
ALTER FUNCTION regano_api.zone_add_name
(uuid, regano.dns_fqdn, regano.dns_interval, regano.dns_name,
regano.dns_record_type, regano.dns_name)
OWNER TO regano;
-- Add a record that stores free-form text
CREATE OR REPLACE FUNCTION regano_api.zone_add_text
(session_id uuid,
zone_name regano.dns_fqdn,
rec_ttl regano.dns_interval,
rec_name regano.dns_name,
rec_type regano.dns_record_type,
rec_data text)
RETURNS void AS $$
DECLARE
domain regano.domains%ROWTYPE;
new_seq_no bigint;
rec_name_c CONSTANT regano.dns_name NOT NULL
:= regano.canonicalize_record_name(rec_name,
zone_name);
BEGIN
domain := regano.zone_verify_access(session_id, zone_name, 'add text');
new_seq_no := regano.zone_next_seq_no(domain.id);
INSERT INTO regano.domain_records
(domain_id, seq_no, type, name, ttl, data_text)
VALUES (domain.id, new_seq_no, rec_type, rec_name_c, rec_ttl, rec_data);
UPDATE regano.domains
SET last_update = now()
WHERE id = domain.id;
END
$$ LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
ALTER FUNCTION regano_api.zone_add_text
(uuid, regano.dns_fqdn, regano.dns_interval, regano.dns_name,
regano.dns_record_type, text)
OWNER TO regano;
-- Add an A record
CREATE OR REPLACE FUNCTION regano_api.zone_add_A
(session_id uuid,
zone_name regano.dns_fqdn,
rec_ttl regano.dns_interval,
rec_name regano.dns_name,
rec_data regano.dns_RR_A)
RETURNS void AS $$
DECLARE
domain regano.domains%ROWTYPE;
new_seq_no bigint;
rec_name_c CONSTANT regano.dns_name NOT NULL
:= regano.canonicalize_record_name(rec_name,
zone_name);
BEGIN
domain := regano.zone_verify_access(session_id, zone_name, 'add A');
new_seq_no := regano.zone_next_seq_no(domain.id);
INSERT INTO regano.domain_records
(domain_id, seq_no, type, name, ttl, data_RR_A)
VALUES (domain.id, new_seq_no, 'A', rec_name_c, rec_ttl, rec_data);
UPDATE regano.domains
SET last_update = now()
WHERE id = domain.id;
END
$$ LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
ALTER FUNCTION regano_api.zone_add_A
(uuid, regano.dns_fqdn, regano.dns_interval, regano.dns_name,
regano.dns_RR_A)
OWNER TO regano;
-- Add an AAAA record
CREATE OR REPLACE FUNCTION regano_api.zone_add_AAAA
(session_id uuid,
zone_name regano.dns_fqdn,
rec_ttl regano.dns_interval,
rec_name regano.dns_name,
rec_data regano.dns_RR_AAAA)
RETURNS void AS $$
DECLARE
domain regano.domains%ROWTYPE;
new_seq_no bigint;
rec_name_c CONSTANT regano.dns_name NOT NULL
:= regano.canonicalize_record_name(rec_name,
zone_name);
BEGIN
domain := regano.zone_verify_access(session_id, zone_name, 'add AAAA');
new_seq_no := regano.zone_next_seq_no(domain.id);
INSERT INTO regano.domain_records
(domain_id, seq_no, type, name, ttl, data_RR_AAAA)
VALUES (domain.id, new_seq_no, 'AAAA', rec_name_c, rec_ttl, rec_data);
UPDATE regano.domains
SET last_update = now()
WHERE id = domain.id;
END
$$ LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
ALTER FUNCTION regano_api.zone_add_AAAA
(uuid, regano.dns_fqdn, regano.dns_interval, regano.dns_name,
regano.dns_RR_AAAA)
OWNER TO regano;
-- Add a DS record
CREATE OR REPLACE FUNCTION regano_api.zone_add_DS
(session_id uuid,
zone_name regano.dns_fqdn,
rec_ttl regano.dns_interval,
rec_name regano.dns_name,
DS_key_tag regano.uint16bit,
DS_algorithm regano.uint8bit,
DS_digest_type regano.uint8bit,
DS_digest regano.hexstring)
RETURNS void AS $$
DECLARE
domain regano.domains%ROWTYPE;
new_seq_no bigint;
rec_name_c CONSTANT regano.dns_name NOT NULL
:= regano.canonicalize_record_name(rec_name,
zone_name);
BEGIN
domain := regano.zone_verify_access(session_id, zone_name, 'add DS');
new_seq_no := regano.zone_next_seq_no(domain.id);
INSERT INTO regano.domain_records
(domain_id, seq_no, type, name, ttl, data_RR_DS)
VALUES (domain.id, new_seq_no, 'DS', rec_name_c, rec_ttl,
ROW(DS_key_tag, DS_algorithm, DS_digest_type, DS_digest));
UPDATE regano.domains
SET last_update = now()
WHERE id = domain.id;
END
$$ LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
ALTER FUNCTION regano_api.zone_add_DS
(uuid, regano.dns_fqdn, regano.dns_interval, regano.dns_name,
regano.uint16bit, regano.uint8bit, regano.uint8bit,
regano.hexstring)
OWNER TO regano;
-- Add an MX record
CREATE OR REPLACE FUNCTION regano_api.zone_add_MX
(session_id uuid,
zone_name regano.dns_fqdn,
rec_ttl regano.dns_interval,
rec_name regano.dns_name,
MX_preference regano.uint16bit,
MX_exchange regano.dns_name)
RETURNS void AS $$
DECLARE
domain regano.domains%ROWTYPE;
new_seq_no bigint;
rec_name_c CONSTANT regano.dns_name NOT NULL
:= regano.canonicalize_record_name(rec_name,
zone_name);
BEGIN