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
|
// SPDX-FileCopyrightText: 2026 Progmasoft <support@progmasoft.com>
// SPDX-License-Identifier: AGPL-3.0-or-later WITH AdditionRef-Progmasoft-Patent-Grant-1.1
using Npgsql;
namespace Progmasoft.OAuth.Storage;
/// <summary>The tables of <see cref="OAuthDbContext"/> and the steps that bring a database to them.</summary>
/// <remarks>
/// <para>
/// The same numbered steps as the account tables, in a version table of their own: a database records the steps it
/// has taken in <c>oauth_schema_versions</c>, and <see cref="ApplyAsync"/> takes the missing ones in one
/// transaction. A step is never edited after it has been released.
/// </para>
/// <para>
/// Step 1 is the script Entity Framework generates for <see cref="OAuthDbContext"/>, so that the tables are exactly
/// what OpenIddict's stores read and write; a test compares the two. The steps run after the account steps, since
/// an application owner refers to an account.
/// </para>
/// </remarks>
internal static class OAuthSchema
{
/// <summary>Key of the advisory lock that lets one process at a time change these tables.</summary>
private const long MigrationLockKey = 0x50726F4F41757468;
/// <summary>The statements Entity Framework generates for the model, as step 1 holds them.</summary>
internal const string ModelScript =
"""
CREATE TABLE oauth_applications (
"Id" text NOT NULL,
"ApplicationType" character varying(50),
"ClientId" character varying(100),
"ClientSecret" text,
"ClientType" character varying(50),
"ConcurrencyToken" character varying(50),
"ConsentType" character varying(50),
"DisplayName" text,
"DisplayNames" text,
"JsonWebKeySet" text,
"Permissions" text,
"PostLogoutRedirectUris" text,
"Properties" text,
"RedirectUris" text,
"Requirements" text,
"Settings" text,
CONSTRAINT "PK_oauth_applications" PRIMARY KEY ("Id")
);
CREATE TABLE oauth_scopes (
"Id" text NOT NULL,
"ConcurrencyToken" character varying(50),
"Description" text,
"Descriptions" text,
"DisplayName" text,
"DisplayNames" text,
"Name" character varying(200),
"Properties" text,
"Resources" text,
CONSTRAINT "PK_oauth_scopes" PRIMARY KEY ("Id")
);
CREATE TABLE oauth_application_owners (
"ApplicationId" text NOT NULL,
"AccountId" uuid NOT NULL,
"CreatedAt" timestamp with time zone NOT NULL,
CONSTRAINT "PK_oauth_application_owners" PRIMARY KEY ("ApplicationId"),
CONSTRAINT "FK_oauth_application_owners_oauth_applications_ApplicationId" FOREIGN KEY ("ApplicationId") REFERENCES oauth_applications ("Id") ON DELETE CASCADE
);
CREATE TABLE oauth_authorizations (
"Id" text NOT NULL,
"ApplicationId" text,
"ConcurrencyToken" character varying(50),
"CreationDate" timestamp with time zone,
"Properties" text,
"Scopes" text,
"Status" character varying(50),
"Subject" character varying(400),
"Type" character varying(50),
CONSTRAINT "PK_oauth_authorizations" PRIMARY KEY ("Id"),
CONSTRAINT "FK_oauth_authorizations_oauth_applications_ApplicationId" FOREIGN KEY ("ApplicationId") REFERENCES oauth_applications ("Id")
);
CREATE TABLE oauth_tokens (
"Id" text NOT NULL,
"ApplicationId" text,
"AuthorizationId" text,
"ConcurrencyToken" character varying(50),
"CreationDate" timestamp with time zone,
"ExpirationDate" timestamp with time zone,
"Payload" text,
"Properties" text,
"RedemptionDate" timestamp with time zone,
"ReferenceId" character varying(100),
"Status" character varying(50),
"Subject" character varying(400),
"Type" character varying(150),
CONSTRAINT "PK_oauth_tokens" PRIMARY KEY ("Id"),
CONSTRAINT "FK_oauth_tokens_oauth_applications_ApplicationId" FOREIGN KEY ("ApplicationId") REFERENCES oauth_applications ("Id"),
CONSTRAINT "FK_oauth_tokens_oauth_authorizations_AuthorizationId" FOREIGN KEY ("AuthorizationId") REFERENCES oauth_authorizations ("Id")
);
CREATE INDEX "IX_oauth_application_owners_AccountId" ON oauth_application_owners ("AccountId");
CREATE UNIQUE INDEX "IX_oauth_applications_ClientId" ON oauth_applications ("ClientId");
CREATE INDEX "IX_oauth_authorizations_ApplicationId_Status_Subject_Type" ON oauth_authorizations ("ApplicationId", "Status", "Subject", "Type");
CREATE UNIQUE INDEX "IX_oauth_scopes_Name" ON oauth_scopes ("Name");
CREATE INDEX "IX_oauth_tokens_ApplicationId_Status_Subject_Type" ON oauth_tokens ("ApplicationId", "Status", "Subject", "Type");
CREATE INDEX "IX_oauth_tokens_AuthorizationId" ON oauth_tokens ("AuthorizationId");
CREATE UNIQUE INDEX "IX_oauth_tokens_ReferenceId" ON oauth_tokens ("ReferenceId");
""";
/// <summary>The steps, in order. The version of a step is its position, counted from one.</summary>
private static readonly string[] Steps =
[
// 1: OpenIddict's applications, authorizations, scopes and tokens, and the owners of applications. Deleting
// an account deletes its ownership rows; the applications themselves are deleted through the portal.
ModelScript + """
ALTER TABLE oauth_application_owners
ADD CONSTRAINT oauth_application_owners_account_fk
FOREIGN KEY ("AccountId") REFERENCES accounts (id) ON DELETE CASCADE;
"""
];
/// <summary>The version a database has after every step.</summary>
public static int CurrentVersion => Steps.Length;
/// <summary>Takes the steps a database has not taken yet.</summary>
/// <remarks>
/// Processes that start together wait for one another on an advisory lock, so each step is taken once. A
/// database that is newer than this program is refused.
/// </remarks>
/// <param name="dataSource">Connections to the database.</param>
/// <param name="cancellationToken">Cancels the operation.</param>
/// <returns>The number of steps that were taken; zero when the database was current.</returns>
/// <exception cref="InvalidOperationException">The database has taken steps this program does not know.</exception>
public static async Task<int> ApplyAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
{
await using NpgsqlConnection connection = await dataSource.OpenConnectionAsync(cancellationToken);
await using NpgsqlTransaction transaction = await connection.BeginTransactionAsync(cancellationToken);
await ExecuteAsync(connection, transaction, $"SELECT pg_advisory_xact_lock({MigrationLockKey})", cancellationToken);
await ExecuteAsync(
connection,
transaction,
"""
CREATE TABLE IF NOT EXISTS oauth_schema_versions (
version integer PRIMARY KEY,
applied_at timestamptz NOT NULL DEFAULT now()
)
""",
cancellationToken);
int version;
await using (NpgsqlCommand read = new(
"SELECT coalesce(max(version), 0) FROM oauth_schema_versions", connection, transaction))
{
version = (int)(await read.ExecuteScalarAsync(cancellationToken))!;
}
if (version > Steps.Length)
{
throw new InvalidOperationException(
$"The OAuth tables are at schema version {version}, and this program knows version {Steps.Length}. " +
"Run the release that created them, or a newer one.");
}
int taken = 0;
for (int step = version; step < Steps.Length; step++)
{
await ExecuteAsync(connection, transaction, Steps[step], cancellationToken);
await using NpgsqlCommand record = new(
"INSERT INTO oauth_schema_versions (version) VALUES ($1)", connection, transaction);
record.Parameters.AddWithValue(step + 1);
await record.ExecuteNonQueryAsync(cancellationToken);
taken++;
}
await transaction.CommitAsync(cancellationToken);
return taken;
}
/// <summary>Runs statements that return nothing.</summary>
/// <param name="connection">The connection to run them on.</param>
/// <param name="transaction">The transaction they belong to.</param>
/// <param name="sql">The statements.</param>
/// <param name="cancellationToken">Cancels the operation.</param>
/// <returns>A task that completes when the statements have run.</returns>
private static async Task ExecuteAsync(
NpgsqlConnection connection,
NpgsqlTransaction transaction,
string sql,
CancellationToken cancellationToken)
{
await using NpgsqlCommand command = new(sql, connection, transaction);
await command.ExecuteNonQueryAsync(cancellationToken);
}
}
|