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
|
#
# Log table to hold all OAuth request when you enabled logging
#
CREATE TABLE oauth_log (
olg_id serial primary key,
olg_osr_consumer_key varchar(64),
olg_ost_token varchar(64),
olg_ocr_consumer_key varchar(64),
olg_oct_token varchar(64),
olg_usa_id_ref text,
olg_received text not null,
olg_sent text not null,
olg_base_string text not null,
olg_notes text not null,
olg_timestamp timestamp not null default current_timestamp,
olg_remote_ip inet not null
);
COMMENT ON TABLE oauth_log IS 'Log table to hold all OAuth request when you enabled logging';
#
# /////////////////// CONSUMER SIDE ///////////////////
#
# This is a registry of all consumer codes we got from other servers
# The consumer_key/secret is obtained from the server
# We also register the server uri, so that we can find the consumer key and secret
# for a certain server. From that server we can check if we have a token for a
# particular user.
CREATE TABLE oauth_consumer_registry (
ocr_id serial primary key,
ocr_usa_id_ref text,
ocr_consumer_key varchar(128) not null,
ocr_consumer_secret varchar(128) not null,
ocr_signature_methods varchar(255) not null default 'HMAC-SHA1,PLAINTEXT',
ocr_server_uri varchar(255) not null,
ocr_server_uri_host varchar(128) not null,
ocr_server_uri_path varchar(128) not null,
ocr_request_token_uri varchar(255) not null,
ocr_authorize_uri varchar(255) not null,
ocr_access_token_uri varchar(255) not null,
ocr_timestamp timestamp not null default current_timestamp,
unique (ocr_consumer_key, ocr_usa_id_ref, ocr_server_uri)
);
COMMENT ON TABLE oauth_consumer_registry IS 'This is a registry of all consumer codes we got from other servers';
# Table used to sign requests for sending to a server by the consumer
# The key is defined for a particular user. Only one single named
# key is allowed per user/server combination
-- Create enum type token_type
CREATE TYPE consumer_token_type AS ENUM (
'request',
'authorized',
'access'
);
CREATE TABLE oauth_consumer_token (
oct_id serial primary key,
oct_ocr_id_ref integer not null,
oct_usa_id_ref text not null,
oct_name varchar(64) not null default '',
oct_token varchar(64) not null,
oct_token_secret varchar(64) not null,
oct_token_type consumer_token_type,
oct_token_ttl timestamp not null default timestamp '9999-12-31',
oct_timestamp timestamp not null default current_timestamp,
unique (oct_ocr_id_ref, oct_token),
unique (oct_usa_id_ref, oct_ocr_id_ref, oct_token_type, oct_name),
foreign key (oct_ocr_id_ref) references oauth_consumer_registry (ocr_id)
on update cascade
on delete cascade
);
COMMENT ON TABLE oauth_consumer_token IS 'Table used to sign requests for sending to a server by the consumer';
#
# ////////////////// SERVER SIDE /////////////////
#
# Table holding consumer key/secret combos an user issued to consumers.
# Used for verification of incoming requests.
CREATE TABLE oauth_server_registry (
osr_id serial primary key,
osr_usa_id_ref text,
osr_consumer_key varchar(64) not null,
osr_consumer_secret varchar(64) not null,
osr_enabled boolean not null default true,
osr_status varchar(16) not null,
osr_requester_name varchar(64) not null,
osr_requester_email varchar(64) not null,
osr_callback_uri varchar(255) not null,
osr_application_uri varchar(255) not null,
osr_application_title varchar(80) not null,
osr_application_descr text not null,
osr_application_notes text not null,
osr_application_type varchar(20) not null,
osr_application_commercial boolean not null default false,
osr_issue_date timestamp not null,
osr_timestamp timestamp not null default current_timestamp,
unique (osr_consumer_key)
);
COMMENT ON TABLE oauth_server_registry IS 'Table holding consumer key/secret combos an user issued to consumers';
# Nonce used by a certain consumer, every used nonce should be unique, this prevents
# replaying attacks. We need to store all timestamp/nonce combinations for the
# maximum timestamp received.
CREATE TABLE oauth_server_nonce (
osn_id serial primary key,
osn_consumer_key varchar(64) not null,
osn_token varchar(64) not null,
osn_timestamp bigint not null,
osn_nonce varchar(80) not null,
unique (osn_consumer_key, osn_token, osn_timestamp, osn_nonce)
);
COMMENT ON TABLE oauth_server_nonce IS 'Nonce used by a certain consumer, every used nonce should be unique, this prevents replaying attacks';
# Table used to verify signed requests sent to a server by the consumer
# When the verification is succesful then the associated user id is returned.
-- Create enum type token_type
CREATE TYPE server_token_type AS ENUM (
'request',
'access'
);
CREATE TABLE oauth_server_token (
ost_id serial primary key,
ost_osr_id_ref integer not null,
ost_usa_id_ref text not null,
ost_token varchar(64) not null,
ost_token_secret varchar(64) not null,
ost_token_type server_token_type,
ost_authorized boolean not null default false,
ost_referrer_host varchar(128) not null default '',
ost_token_ttl timestamp not null default timestamp '9999-12-31',
ost_timestamp timestamp not null default current_timestamp,
ost_verifier char(10),
ost_callback_url varchar(512),
unique (ost_token),
foreign key (ost_osr_id_ref) references oauth_server_registry (osr_id)
on update cascade
on delete cascade
);
COMMENT ON TABLE oauth_server_token IS 'Table used to verify signed requests sent to a server by the consumer';
|
