1 /* eslint-disable security/detect-object-injection */
4 const pgpInitOptions
= {
8 const path
= require('path');
9 const pgp
= require('pg-promise')(pgpInitOptions
);
10 const svh
= require('../schema-version-helper');
11 const Database
= require('../abstract');
12 const DBErrors
= require('../errors');
13 const common
= require('../../common');
15 const _fileScope
= common
.fileScope(__filename
);
17 const PGTypeIdINT8
= 20; // Type Id 20 == INT8 (BIGINT)
18 const PGTYpeIdINT8Array
= 1016; //Type Id 1016 == INT8[] (BIGINT[])
19 pgp
.pg
.types
.setTypeParser(PGTypeIdINT8
, BigInt
); // Type Id 20 = INT8 (BIGINT)
20 const parseBigIntArray
= pgp
.pg
.types
.getTypeParser(PGTYpeIdINT8Array
); // Type Id 1016 = INT8[] (BIGINT[])
21 pgp
.pg
.types
.setTypeParser(PGTYpeIdINT8Array
, (a
) => parseBigIntArray(a
).map(BigInt
));
23 const schemaVersionsSupported
= {
36 class DatabasePostgres
extends Database
{
37 constructor(logger
, options
, _pgp
= pgp
) {
38 super(logger
, options
);
40 this.db
= _pgp(options
.db
.connectionString
);
41 this.schemaVersionsSupported
= schemaVersionsSupported
;
43 // Suppress QF warnings when running tests
44 this.noWarnings
= options
.db
.noWarnings
;
47 const queryLogLevel
= options
.db
.queryLogLevel
;
49 const queryScope
= _fileScope('pgp:query');
50 pgpInitOptions
.query
= (event
) => {
51 this.logger
[queryLogLevel
](queryScope
, '', { ...common
.pick(event
|| {}, ['query', 'params']) });
56 const errorScope
= _fileScope('pgp:error');
57 pgpInitOptions
.error
= (err
, event
) => {
58 this.logger
.error(errorScope
, '', { err
, event
});
61 // Deophidiate column names in-place, log results
62 pgpInitOptions
.receive
= ({ data
, result
, ctx: event
}) => {
63 const exemplaryRow
= data
[0];
64 for (const prop
in exemplaryRow
) {
65 const camel
= common
.camelfy(prop
);
66 if (!(camel
in exemplaryRow
)) {
67 for (const d
of data
) {
75 const resultLog
= common
.pick(result
|| {}, ['command', 'rowCount', 'duration']);
76 this.logger
[queryLogLevel
](_fileScope('pgp:result'), '', { query: event
.query
, ...resultLog
});
80 // Expose these for test coverage
81 this.pgpInitOptions
= pgpInitOptions
;
84 this._initStatements(_pgp
);
88 _queryFileHelper(_pgp
) {
90 const _scope
= _fileScope('_queryFile');
91 /* istanbul ignore next */
94 ...(this.noWarnings
&& { noWarnings: this.noWarnings
}),
96 const qf
= new _pgp
.QueryFile(file
, qfParams
);
98 this.logger
.error(_scope
, 'failed to create SQL statement', { error: qf
.error
, file
});
106 async
initialize(applyMigrations
= true) {
107 const _scope
= _fileScope('initialize');
108 this.logger
.debug(_scope
, 'called', { applyMigrations
});
109 if (applyMigrations
) {
110 await
this._initTables();
112 await
super.initialize();
114 await
this.listener
.start();
119 async
_initTables(_pgp
) {
120 const _scope
= _fileScope('_initTables');
121 this.logger
.debug(_scope
, 'called', {});
123 const _queryFile
= this._queryFileHelper(_pgp
|| this._pgp
);
125 // Migrations rely upon this table, ensure it exists.
126 const metaVersionTable
= '_meta_schema_version';
128 const tableExists
= async (name
) => this.db
.oneOrNone('SELECT table_name FROM information_schema.tables WHERE table_name=$(name)', { name
});
129 let metaExists
= await
tableExists(metaVersionTable
);
131 const fPath
= path
.join(__dirname
, 'sql', 'schema', 'init.sql');
132 const initSql
= _queryFile(fPath
);
133 const results
= await
this.db
.multiResult(initSql
);
134 this.logger
.debug(_scope
, 'executed init sql', { results
});
135 metaExists
= await
tableExists(metaVersionTable
);
136 /* istanbul ignore if */
138 throw new DBErrors
.UnexpectedResult(`did not create ${metaVersionTable} table`);
140 this.logger
.info(_scope
, 'created schema version table', { metaVersionTable
});
144 const currentSchema
= await
this._currentSchema();
145 const migrationsWanted
= svh
.unappliedSchemaVersions(__dirname
, currentSchema
, this.schemaVersionsSupported
);
146 this.logger
.debug(_scope
, 'schema migrations wanted', { migrationsWanted
});
147 for (const v
of migrationsWanted
) {
148 const fPath
= path
.join(__dirname
, 'sql', 'schema', v
, 'apply.sql');
149 const migrationSql
= _queryFile(fPath
);
150 const results
= await
this.db
.multiResult(migrationSql
);
151 this.logger
.debug(_scope
, 'executed migration sql', { version: v
, results
});
152 this.logger
.info(_scope
, 'applied migration', { version: v
});
157 _initStatements(_pgp
) {
158 const _scope
= _fileScope('_initStatements');
159 const _queryFile
= this._queryFileHelper(_pgp
);
160 this.statement
= _pgp
.utils
.enumSql(path
.join(__dirname
, 'sql'), {}, _queryFile
);
161 this.logger
.debug(_scope
, 'statements initialized', { statements: Object
.keys(this.statement
).length
});
165 async
healthCheck() {
166 const _scope
= _fileScope('healthCheck');
167 this.logger
.debug(_scope
, 'called', {});
168 const c
= await
this.db
.connect();
170 return { serverVersion: c
.client
.serverVersion
};
174 async
_currentSchema() {
175 return this.db
.one('SELECT major, minor, patch FROM _meta_schema_version ORDER BY major DESC, minor DESC, patch DESC LIMIT 1');
179 async
_closeConnection() {
180 const _scope
= _fileScope('_closeConnection');
183 await
this.listener
.stop();
185 await
this._pgp
.end();
187 this.logger
.error(_scope
, 'failed', { error: e
});
193 /* istanbul ignore next */
194 async
_purgeTables(really
= false) {
195 const _scope
= _fileScope('_purgeTables');
198 await
this.db
.tx(async (t
) => {
204 ].map(async (table
) => t
.query('TRUNCATE TABLE $(table:name) CASCADE', { table
})));
208 this.logger
.error(_scope
, 'failed', { error: e
});
215 return this.db
.task(async (t
) => fn(t
));
219 // eslint-disable-next-line class-methods-use-this
220 async
transaction(dbCtx
, fn
) {
221 return dbCtx
.txIf(async (t
) => fn(t
));
225 async
almanacGetAll(dbCtx
) {
226 const _scope
= _fileScope('almanacGetAll');
227 this.logger
.debug(_scope
, 'called');
230 return await dbCtx
.manyOrNone(this.statement
.almanacGetAll
);
232 this.logger
.error(_scope
, 'failed', { error: e
});
238 async
authenticationGet(dbCtx
, identifier
) {
239 const _scope
= _fileScope('authenticationGet');
240 this.logger
.debug(_scope
, 'called', { identifier
});
243 return await dbCtx
.oneOrNone(this.statement
.authenticationGet
, { identifier
});
245 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
251 async
authenticationSuccess(dbCtx
, identifier
) {
252 const _scope
= _fileScope('authenticationSuccess');
253 this.logger
.debug(_scope
, 'called', { identifier
});
256 const result
= await dbCtx
.result(this.statement
.authenticationSuccess
, { identifier
});
257 if (result
.rowCount
!= 1) {
258 throw new DBErrors
.UnexpectedResult('did not update authentication success event');
261 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
267 async
authenticationUpsert(dbCtx
, identifier
, credential
) {
268 const _scope
= _fileScope('authenticationUpsert');
269 const scrubbedCredential
= '*'.repeat((credential
|| '').length
);
270 this.logger
.debug(_scope
, 'called', { identifier
, scrubbedCredential
});
273 const result
= await dbCtx
.result(this.statement
.authenticationUpsert
, { identifier
, credential
});
274 if (result
.rowCount
!= 1) {
275 throw new DBErrors
.UnexpectedResult('did not upsert authentication');
278 this.logger
.error(_scope
, 'failed', { error: e
, identifier
, scrubbedCredential
});
284 async
profileIdentifierInsert(dbCtx
, profile
, identifier
) {
285 const _scope
= _fileScope('profileIdentifierInsert');
286 this.logger
.debug(_scope
, 'called', { profile
, identifier
});
289 const result
= await dbCtx
.result(this.statement
.profileIdentifierInsert
, { profile
, identifier
});
290 if (result
.rowCount
!= 1) {
291 throw new DBErrors
.UnexpectedResult('did not insert identifier');
294 this.logger
.error(_scope
, 'failed', { error: e
, profile
, identifier
});
300 async
profileIsValid(dbCtx
, profile
) {
301 const _scope
= _fileScope('profileIsValid');
302 this.logger
.debug(_scope
, 'called', { profile
});
305 const profileResponse
= await dbCtx
.oneOrNone(this.statement
.profileGet
, { profile
});
306 return !!profileResponse
;
308 this.logger
.error(_scope
, 'failed', { error: e
, profile
});
314 async
profileScopeInsert(dbCtx
, profile
, scope
) {
315 const _scope
= _fileScope('profileScopeInsert');
316 this.logger
.debug(_scope
, 'called', { profile
, scope
});
319 const result
= await dbCtx
.result(this.statement
.profileScopeInsert
, { profile
, scope
});
320 // Duplicate inserts get ignored
321 if (result
.rowCount
!= 1 && result
.rowCount
!= 0) {
322 throw new DBErrors
.UnexpectedResult('did not insert profile scope');
325 this.logger
.error(_scope
, 'failed', { error: e
, profile
, scope
});
331 async
profileScopesSetAll(dbCtx
, profile
, scopes
) {
332 const _scope
= _fileScope('profileScopesSetAll');
333 this.logger
.debug(_scope
, 'called', { profile
, scopes
});
336 await
this.transaction(dbCtx
, async (txCtx
) => {
337 await txCtx
.result(this.statement
.profileScopesClear
, { profile
});
339 await txCtx
.result(this.statement
.profileScopesSetAll
, { profile
, scopes
});
343 this.logger
.error(_scope
, 'failed', { error: e
, profile
, scopes
});
349 async
profilesScopesByIdentifier(dbCtx
, identifier
) {
350 const _scope
= _fileScope('profilesScopesByIdentifier');
351 this.logger
.debug(_scope
, 'called', { identifier
});
354 const profileScopesRows
= await dbCtx
.manyOrNone(this.statement
.profilesScopesByIdentifier
, { identifier
});
355 return Database
._profilesScopesBuilder(profileScopesRows
);
357 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
363 async
redeemCode(dbCtx
, { codeId
, created
, isToken
, clientId
, profile
, identifier
, scopes
, lifespanSeconds
, refreshLifespanSeconds
, resource
, profileData
}) {
364 const _scope
= _fileScope('redeemCode');
365 this.logger
.debug(_scope
, 'called', { codeId
, created
, isToken
, clientId
, profile
, identifier
, scopes
, lifespanSeconds
, refreshLifespanSeconds
, resource
, profileData
});
367 let result
, ret
= false;
369 await
this.transaction(dbCtx
, async (txCtx
) => {
370 result
= await txCtx
.result(this.statement
.redeemCode
, { codeId
, created
, isToken
, clientId
, profile
, identifier
, lifespanSeconds
, refreshLifespanSeconds
, resource
, profileData
});
371 if (result
.rowCount
!= 1) {
372 this.logger
.error(_scope
, 'failed', { result
});
373 throw new DBErrors
.UnexpectedResult('did not redeem code');
375 // Abort and return false if redemption resulted in revocation.
376 if (result
.rows
[0].isRevoked
) {
379 this.logger
.debug(_scope
, 'code redeemed', { redeemed: result
.rows
[0] });
381 // Ensure there are entries for all scopes.
382 if (scopes
.length
!== 0) {
383 await txCtx
.result(this.statement
.scopesInsert
, { scopes
});
386 // Record accepted scopes for this token.
387 result
= await txCtx
.result(this.statement
.tokenScopesSet
, { codeId
, scopes
});
388 if (result
.rowCount
!= scopes
.length
) {
389 this.logger
.error(_scope
, 'token scope count mismatch', { codeId
, scopes
, result
});
390 throw new DBErrors
.UnexpectedResult('did not set all scopes on token');
395 this.logger
.error(_scope
, 'failed', { error: e
, codeId
, created
, isToken
, clientId
, profile
, identifier
, scopes
, lifespanSeconds
, refreshLifespanSeconds
, profileData
});
403 async
refreshCode(dbCtx
, codeId
, refreshed
, removeScopes
) {
404 const _scope
= _fileScope('refreshCode');
405 this.logger
.debug(_scope
, 'called', { codeId
, refreshed
, removeScopes
});
408 return await
this.transaction(dbCtx
, async (txCtx
) => {
409 const refreshedToken
= await txCtx
.oneOrNone(this.statement
.refreshCode
, { codeId
, refreshed
});
410 if (refreshedToken
) {
411 if (removeScopes
.length
) {
412 const removeResult
= await txCtx
.result(this.statement
.tokenScopesRemove
, { codeId
, removeScopes
});
413 if (removeResult
.rowCount
!= removeScopes
.length
) {
414 this.logger
.error(_scope
, 'failed to remove token scopes', { actual: removeResult
.rowCount
, expected: removeScopes
.length
});
415 throw new DBErrors
.UnexpectedResult('did not remove scopes from token');
418 delete refreshedToken
.scopes
; // Not updated, remove from response.
421 this.logger
.debug(_scope
, 'did not refresh token', {});
423 return refreshedToken
;
426 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
432 async
resourceGet(dbCtx
, resourceId
) {
433 const _scope
= _fileScope('resourceGet');
434 this.logger
.debug(_scope
, 'called', { resourceId
});
437 return await dbCtx
.oneOrNone(this.statement
.resourceGet
, { resourceId
});
439 this.logger
.error(_scope
, 'failed', { error: e
, resourceId
});
445 async
resourceUpsert(dbCtx
, resourceId
, secret
, description
) {
446 const _scope
= _fileScope('resourceUpsert');
447 const logSecret
= secret
?.length
&& common
.logTruncate('*'.repeat(secret
.length
), 3) || undefined;
448 this.logger
.debug(_scope
, 'called', { resourceId
, secret: logSecret
, description
});
451 const result
= await dbCtx
.result(this.statement
.resourceUpsert
, { resourceId
, secret
, description
});
452 if (result
.rowCount
!= 1) {
453 throw new DBErrors
.UnexpectedResult('did not upsert resource');
455 return result
.rows
[0];
457 this.logger
.error(_scope
, 'failed', { error: e
, resourceId
, secret: logSecret
, description
});
463 async
scopeCleanup(dbCtx
, atLeastMsSinceLast
) {
464 const _scope
= _fileScope('scopeCleanup');
465 this.logger
.debug(_scope
, 'called', { atLeastMsSinceLast
});
467 const almanacEvent
= 'scopeCleanup';
469 return await
this.transaction(dbCtx
, async (txCtx
) => {
471 // Check that enough time has passed since last cleanup
472 const now
= new Date();
473 const cleanupNotAfter
= new Date(now
.getTime() - atLeastMsSinceLast
);
474 const { date: lastCleanupDate
} = await txCtx
.oneOrNone(this.statement
.almanacGet
, { event: almanacEvent
}) || { date: new Date(0) };
475 if (lastCleanupDate
>= cleanupNotAfter
) {
476 this.logger
.debug(_scope
, 'skipping token cleanup, too soon', { lastCleanupDate
, cleanupNotAfter
, atLeastMsSinceLast
});
481 const { rowCount: scopesRemoved
} = await txCtx
.result(this.statement
.scopeCleanup
);
483 // Update the last cleanup time
484 const result
= await txCtx
.result(this.statement
.almanacUpsert
, { event: almanacEvent
, date: now
});
485 if (result
.rowCount
!= 1) {
486 throw new DBErrors
.UnexpectedResult('did not update almanac');
489 this.logger
.debug(_scope
, 'completed', { scopesRemoved
, atLeastMsSinceLast
});
490 return scopesRemoved
;
494 this.logger
.error(_scope
, 'failed', { error: e
, atLeastMsSinceLast
});
500 async
scopeDelete(dbCtx
, scope
) {
501 const _scope
= _fileScope('scopeDelete');
502 this.logger
.debug(_scope
, 'called', { scope
});
505 return await
this.transaction(dbCtx
, async (txCtx
) => {
506 const { inUse
} = await txCtx
.one(this.statement
.scopeInUse
, { scope
});
508 this.logger
.debug(_scope
, 'not deleted, in use', { scope
});
511 const result
= await txCtx
.result(this.statement
.scopeDelete
, { scope
});
512 if (result
.rowCount
== 0) {
513 this.logger
.debug(_scope
, 'no such scope', { scope
});
515 this.logger
.debug(_scope
, 'deleted', { scope
});
520 this.logger
.error(_scope
, 'failed', { error: e
, scope
});
526 async
scopeUpsert(dbCtx
, scope
, application
, description
, manuallyAdded
= false) {
527 const _scope
= _fileScope('scopeUpsert');
528 this.logger
.debug(_scope
, 'called', { scope
, description
});
531 const result
= await dbCtx
.result(this.statement
.scopeUpsert
, { scope
, application
, description
, manuallyAdded
});
532 if (result
.rowCount
!= 1) {
533 throw new DBErrors
.UnexpectedResult('did not upsert scope');
536 this.logger
.error(_scope
, 'failed', { error: e
, scope
, application
, description
});
542 async
tokenCleanup(dbCtx
, codeLifespanSeconds
, atLeastMsSinceLast
) {
543 const _scope
= _fileScope('tokenCleanup');
544 this.logger
.debug(_scope
, 'called', { codeLifespanSeconds
, atLeastMsSinceLast
});
546 const almanacEvent
= 'tokenCleanup';
548 return await
this.transaction(dbCtx
, async (txCtx
) => {
550 // Check that enough time has passed since last cleanup
551 const now
= new Date();
552 const cleanupNotAfter
= new Date(now
.getTime() - atLeastMsSinceLast
);
553 const { date: lastCleanupDate
} = await txCtx
.oneOrNone(this.statement
.almanacGet
, { event: almanacEvent
}) || { date: new Date(0) };
554 if (lastCleanupDate
>= cleanupNotAfter
) {
555 this.logger
.debug(_scope
, 'skipping token cleanup, too soon', { lastCleanupDate
, cleanupNotAfter
, codeLifespanSeconds
, atLeastMsSinceLast
});
560 const { rowCount: tokensRemoved
} = await txCtx
.result(this.statement
.tokenCleanup
, { codeLifespanSeconds
});
562 // Update the last cleanup time
563 const result
= await txCtx
.result(this.statement
.almanacUpsert
, { event: almanacEvent
, date: now
});
564 if (result
.rowCount
!= 1) {
565 throw new DBErrors
.UnexpectedResult('did not update almanac');
568 this.logger
.debug(_scope
, 'completed', { tokensRemoved
, codeLifespanSeconds
, atLeastMsSinceLast
});
569 return tokensRemoved
;
573 this.logger
.error(_scope
, 'failed', { error: e
, atLeastMsSinceLast
});
579 async
tokenGetByCodeId(dbCtx
, codeId
) {
580 const _scope
= _fileScope('tokenGetByCodeId');
581 this.logger
.debug(_scope
, 'called', { codeId
});
584 return await dbCtx
.oneOrNone(this.statement
.tokenGetByCodeId
, { codeId
});
586 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
592 async
tokenRevokeByCodeId(dbCtx
, codeId
) {
593 const _scope
= _fileScope('tokenRevokeByCodeId');
594 this.logger
.debug(_scope
, 'called', { codeId
});
597 const result
= await dbCtx
.result(this.statement
.tokenRevokeByCodeId
, { codeId
});
598 if (result
.rowCount
!= 1) {
599 throw new DBErrors
.UnexpectedResult('did not revoke token');
602 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
608 async
tokenRefreshRevokeByCodeId(dbCtx
, codeId
) {
609 const _scope
= _fileScope('tokenRefreshRevokeByCodeId');
610 this.logger
.debug(_scope
, 'called', { codeId
});
613 const result
= await dbCtx
.result(this.statement
.tokenRefreshRevokeByCodeId
, { codeId
});
614 if (result
.rowCount
!= 1) {
615 throw new DBErrors
.UnexpectedResult('did not revoke token');
618 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
624 async
tokensGetByIdentifier(dbCtx
, identifier
) {
625 const _scope
= _fileScope('tokensGetByIdentifier');
626 this.logger
.debug(_scope
, 'called', { identifier
});
629 return await dbCtx
.manyOrNone(this.statement
.tokensGetByIdentifier
, { identifier
});
631 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
638 module
.exports
= DatabasePostgres
;