1 /* eslint-disable security/detect-object-injection */
4 const pgpInitOptions
= {
8 const path
= require('path');
9 const pgp
= require('pg-promise')(pgpInitOptions
);
10 const { unappliedSchemaVersions
} = require('../schema-version-helper');
11 const Database
= require('../abstract');
12 const DBErrors
= require('../errors');
13 const common
= require('../../common');
14 const Enum
= require('../../enum');
16 const _fileScope
= common
.fileScope(__filename
);
18 const PGTypeIdINT8
= 20; // Type Id 20 == INT8 (BIGINT)
19 const PGTypeIdINT8Array
= 1016; //Type Id 1016 == INT8[] (BIGINT[])
20 pgp
.pg
.types
.setTypeParser(PGTypeIdINT8
, BigInt
); // Type Id 20 = INT8 (BIGINT)
21 const parseBigIntArray
= pgp
.pg
.types
.getTypeParser(PGTypeIdINT8Array
); // Type Id 1016 = INT8[] (BIGINT[])
22 pgp
.pg
.types
.setTypeParser(PGTypeIdINT8Array
, (a
) => parseBigIntArray(a
).map(BigInt
));
24 const schemaVersionsSupported
= {
37 class DatabasePostgres
extends Database
{
38 constructor(logger
, options
, _pgp
= pgp
) {
39 super(logger
, options
);
41 this.db
= _pgp(options
.db
.connectionString
);
42 this.schemaVersionsSupported
= schemaVersionsSupported
;
44 // Suppress QF warnings when running tests
45 this.noWarnings
= options
.db
.noWarnings
;
48 const queryLogLevel
= options
.db
.queryLogLevel
;
50 const queryScope
= _fileScope('pgp:query');
51 pgpInitOptions
.query
= (event
) => {
52 this.logger
[queryLogLevel
](queryScope
, '', { ...common
.pick(event
|| {}, ['query', 'params']) });
57 const errorScope
= _fileScope('pgp:error');
58 pgpInitOptions
.error
= (err
, event
) => {
59 this.logger
.error(errorScope
, '', { err
, event
});
62 // Deophidiate column names in-place, log results
63 pgpInitOptions
.receive
= ({ data
, result
, ctx: event
}) => {
64 const exemplaryRow
= data
[0];
65 for (const prop
in exemplaryRow
) {
66 const camel
= common
.camelfy(prop
);
67 if (!(camel
in exemplaryRow
)) {
68 for (const d
of data
) {
76 const resultLog
= common
.pick(result
|| {}, ['command', 'rowCount', 'duration']);
77 this.logger
[queryLogLevel
](_fileScope('pgp:result'), '', { query: event
.query
, ...resultLog
});
81 // Expose these for test coverage
82 this.pgpInitOptions
= pgpInitOptions
;
85 this._initStatements(_pgp
);
89 _queryFileHelper(_pgp
) {
91 const _scope
= _fileScope('_queryFile');
92 /* istanbul ignore next */
95 ...(this.noWarnings
&& { noWarnings: this.noWarnings
}),
97 const qf
= new _pgp
.QueryFile(file
, qfParams
);
99 this.logger
.error(_scope
, 'failed to create SQL statement', { error: qf
.error
, file
});
107 async
initialize(applyMigrations
= true) {
108 const _scope
= _fileScope('initialize');
109 this.logger
.debug(_scope
, 'called', { applyMigrations
});
110 if (applyMigrations
) {
111 await
this._initTables();
113 await
super.initialize();
115 await
this.listener
.start();
120 async
_initTables(_pgp
) {
121 const _scope
= _fileScope('_initTables');
122 this.logger
.debug(_scope
, 'called', {});
124 const _queryFile
= this._queryFileHelper(_pgp
|| this._pgp
);
126 // Migrations rely upon this table, ensure it exists.
127 const metaVersionTable
= '_meta_schema_version';
129 const tableExists
= async (name
) => this.db
.oneOrNone('SELECT table_name FROM information_schema.tables WHERE table_name=$(name)', { name
});
130 let metaExists
= await
tableExists(metaVersionTable
);
132 const fPath
= path
.join(__dirname
, 'sql', 'schema', 'init.sql');
133 const initSql
= _queryFile(fPath
);
134 const results
= await
this.db
.multiResult(initSql
);
135 this.logger
.debug(_scope
, 'executed init sql', { results
});
136 metaExists
= await
tableExists(metaVersionTable
);
137 /* istanbul ignore if */
139 throw new DBErrors
.UnexpectedResult(`did not create ${metaVersionTable} table`);
141 this.logger
.info(_scope
, 'created schema version table', { metaVersionTable
});
145 const currentSchema
= await
this._currentSchema();
146 const migrationsWanted
= unappliedSchemaVersions(__dirname
, currentSchema
, this.schemaVersionsSupported
);
147 this.logger
.debug(_scope
, 'schema migrations wanted', { migrationsWanted
});
148 for (const v
of migrationsWanted
) {
149 const fPath
= path
.join(__dirname
, 'sql', 'schema', v
, 'apply.sql');
150 const migrationSql
= _queryFile(fPath
);
151 const results
= await
this.db
.multiResult(migrationSql
);
152 this.logger
.debug(_scope
, 'executed migration sql', { version: v
, results
});
153 this.logger
.info(_scope
, 'applied migration', { version: v
});
158 _initStatements(_pgp
) {
159 const _scope
= _fileScope('_initStatements');
160 const _queryFile
= this._queryFileHelper(_pgp
);
161 this.statement
= _pgp
.utils
.enumSql(path
.join(__dirname
, 'sql'), {}, _queryFile
);
162 this.logger
.debug(_scope
, 'statements initialized', { statements: Object
.keys(this.statement
).length
});
166 async
healthCheck() {
167 const _scope
= _fileScope('healthCheck');
168 this.logger
.debug(_scope
, 'called', {});
169 const c
= await
this.db
.connect();
171 return { serverVersion: c
.client
.serverVersion
};
175 async
_currentSchema() {
176 return this.db
.one('SELECT major, minor, patch FROM _meta_schema_version ORDER BY major DESC, minor DESC, patch DESC LIMIT 1');
180 async
_closeConnection() {
181 const _scope
= _fileScope('_closeConnection');
184 await
this.listener
.stop();
186 await
this._pgp
.end();
188 this.logger
.error(_scope
, 'failed', { error: e
});
194 /* istanbul ignore next */
195 async
_purgeTables(really
= false) {
196 const _scope
= _fileScope('_purgeTables');
199 await
this.db
.tx(async (t
) => {
207 ].map(async (table
) => t
.query('TRUNCATE TABLE $(table:name) CASCADE', { table
})));
211 this.logger
.error(_scope
, 'failed', { error: e
});
218 return this.db
.task(async (t
) => fn(t
));
222 // eslint-disable-next-line class-methods-use-this
223 async
transaction(dbCtx
, fn
) {
224 return dbCtx
.txIf(async (t
) => fn(t
));
228 static _almanacErrorThrow() {
229 throw new DBErrors
.UnexpectedResult('did not update almanac');
233 async
almanacGetAll(dbCtx
) {
234 const _scope
= _fileScope('almanacGetAll');
235 this.logger
.debug(_scope
, 'called');
238 return await dbCtx
.manyOrNone(this.statement
.almanacGetAll
);
240 this.logger
.error(_scope
, 'failed', { error: e
});
246 async
almanacUpsert(dbCtx
, event
, date
) {
247 const _scope
= _fileScope('almanacUpsert');
248 this.logger
.debug(_scope
, 'called', { event
, date
});
251 const result
= await dbCtx
.result(this.statement
.almanacUpsert
, { event
, date: date
?? new Date() });
252 if (result
.rowCount
!= 1) {
253 this.constructor._almanacErrorThrow();
256 this.logger
.error(_scope
, 'failed', { error: e
, event
, date
});
262 async
authenticationGet(dbCtx
, identifier
) {
263 const _scope
= _fileScope('authenticationGet');
264 this.logger
.debug(_scope
, 'called', { identifier
});
267 return await dbCtx
.oneOrNone(this.statement
.authenticationGet
, { identifier
});
269 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
275 async
authenticationSuccess(dbCtx
, identifier
) {
276 const _scope
= _fileScope('authenticationSuccess');
277 this.logger
.debug(_scope
, 'called', { identifier
});
280 const result
= await dbCtx
.result(this.statement
.authenticationSuccess
, { identifier
});
281 if (result
.rowCount
!= 1) {
282 throw new DBErrors
.UnexpectedResult('did not update authentication success event');
285 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
291 async
authenticationUpsert(dbCtx
, identifier
, credential
) {
292 const _scope
= _fileScope('authenticationUpsert');
293 const scrubbedCredential
= '*'.repeat((credential
|| '').length
);
294 this.logger
.debug(_scope
, 'called', { identifier
, scrubbedCredential
});
297 const result
= await dbCtx
.result(this.statement
.authenticationUpsert
, { identifier
, credential
});
298 if (result
.rowCount
!= 1) {
299 throw new DBErrors
.UnexpectedResult('did not upsert authentication');
302 this.logger
.error(_scope
, 'failed', { error: e
, identifier
, scrubbedCredential
});
308 async
profileIdentifierInsert(dbCtx
, profile
, identifier
) {
309 const _scope
= _fileScope('profileIdentifierInsert');
310 this.logger
.debug(_scope
, 'called', { profile
, identifier
});
313 const result
= await dbCtx
.result(this.statement
.profileIdentifierInsert
, { profile
, identifier
});
314 if (result
.rowCount
!= 1) {
315 throw new DBErrors
.UnexpectedResult('did not insert identifier');
318 this.logger
.error(_scope
, 'failed', { error: e
, profile
, identifier
});
324 async
profileIsValid(dbCtx
, profile
) {
325 const _scope
= _fileScope('profileIsValid');
326 this.logger
.debug(_scope
, 'called', { profile
});
329 const profileResponse
= await dbCtx
.oneOrNone(this.statement
.profileGet
, { profile
});
330 return !!profileResponse
;
332 this.logger
.error(_scope
, 'failed', { error: e
, profile
});
338 async
profileScopeInsert(dbCtx
, profile
, scope
) {
339 const _scope
= _fileScope('profileScopeInsert');
340 this.logger
.debug(_scope
, 'called', { profile
, scope
});
343 const result
= await dbCtx
.result(this.statement
.profileScopeInsert
, { profile
, scope
});
344 // Duplicate inserts get ignored
345 if (result
.rowCount
!= 1 && result
.rowCount
!= 0) {
346 throw new DBErrors
.UnexpectedResult('did not insert profile scope');
349 this.logger
.error(_scope
, 'failed', { error: e
, profile
, scope
});
355 async
profileScopesSetAll(dbCtx
, profile
, scopes
) {
356 const _scope
= _fileScope('profileScopesSetAll');
357 this.logger
.debug(_scope
, 'called', { profile
, scopes
});
360 await
this.transaction(dbCtx
, async (txCtx
) => {
361 await txCtx
.result(this.statement
.profileScopesClear
, { profile
});
363 await txCtx
.result(this.statement
.profileScopesSetAll
, { profile
, scopes
});
367 this.logger
.error(_scope
, 'failed', { error: e
, profile
, scopes
});
373 async
profilesScopesByIdentifier(dbCtx
, identifier
) {
374 const _scope
= _fileScope('profilesScopesByIdentifier');
375 this.logger
.debug(_scope
, 'called', { identifier
});
378 const profileScopesRows
= await dbCtx
.manyOrNone(this.statement
.profilesScopesByIdentifier
, { identifier
});
379 return Database
._profilesScopesBuilder(profileScopesRows
);
381 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
387 async
redeemCode(dbCtx
, { codeId
, created
, isToken
, clientId
, profile
, identifier
, scopes
, lifespanSeconds
, refreshLifespanSeconds
, resource
, profileData
}) {
388 const _scope
= _fileScope('redeemCode');
389 this.logger
.debug(_scope
, 'called', { codeId
, created
, isToken
, clientId
, profile
, identifier
, scopes
, lifespanSeconds
, refreshLifespanSeconds
, resource
, profileData
});
391 let result
, ret
= false;
393 await
this.transaction(dbCtx
, async (txCtx
) => {
394 result
= await txCtx
.result(this.statement
.redeemCode
, { codeId
, created
, isToken
, clientId
, profile
, identifier
, lifespanSeconds
, refreshLifespanSeconds
, resource
, profileData
});
395 if (result
.rowCount
!= 1) {
396 this.logger
.error(_scope
, 'failed', { result
});
397 throw new DBErrors
.UnexpectedResult('did not redeem code');
399 // Abort and return false if redemption resulted in revocation.
400 if (result
.rows
[0].isRevoked
) {
403 this.logger
.debug(_scope
, 'code redeemed', { redeemed: result
.rows
[0] });
405 // Ensure there are entries for all scopes.
406 if (scopes
.length
!== 0) {
407 await txCtx
.result(this.statement
.scopesInsert
, { scopes
});
410 // Record accepted scopes for this token.
411 result
= await txCtx
.result(this.statement
.tokenScopesSet
, { codeId
, scopes
});
412 if (result
.rowCount
!= scopes
.length
) {
413 this.logger
.error(_scope
, 'token scope count mismatch', { codeId
, scopes
, result
});
414 throw new DBErrors
.UnexpectedResult('did not set all scopes on token');
419 this.logger
.error(_scope
, 'failed', { error: e
, codeId
, created
, isToken
, clientId
, profile
, identifier
, scopes
, lifespanSeconds
, refreshLifespanSeconds
, profileData
});
427 async
refreshCode(dbCtx
, codeId
, refreshed
, removeScopes
) {
428 const _scope
= _fileScope('refreshCode');
429 this.logger
.debug(_scope
, 'called', { codeId
, refreshed
, removeScopes
});
432 return await
this.transaction(dbCtx
, async (txCtx
) => {
433 const refreshedToken
= await txCtx
.oneOrNone(this.statement
.refreshCode
, { codeId
, refreshed
});
434 if (refreshedToken
) {
435 if (removeScopes
.length
) {
436 const removeResult
= await txCtx
.result(this.statement
.tokenScopesRemove
, { codeId
, removeScopes
});
437 if (removeResult
.rowCount
!= removeScopes
.length
) {
438 this.logger
.error(_scope
, 'failed to remove token scopes', { actual: removeResult
.rowCount
, expected: removeScopes
.length
});
439 throw new DBErrors
.UnexpectedResult('did not remove scopes from token');
442 delete refreshedToken
.scopes
; // Not updated, remove from response.
445 this.logger
.debug(_scope
, 'did not refresh token', {});
447 return refreshedToken
;
450 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
456 async
resourceGet(dbCtx
, resourceId
) {
457 const _scope
= _fileScope('resourceGet');
458 this.logger
.debug(_scope
, 'called', { resourceId
});
461 return await dbCtx
.oneOrNone(this.statement
.resourceGet
, { resourceId
});
463 this.logger
.error(_scope
, 'failed', { error: e
, resourceId
});
469 async
resourceUpsert(dbCtx
, resourceId
, secret
, description
) {
470 const _scope
= _fileScope('resourceUpsert');
471 const logSecret
= secret
?.length
&& common
.logTruncate('*'.repeat(secret
.length
), 3) || undefined;
472 this.logger
.debug(_scope
, 'called', { resourceId
, secret: logSecret
, description
});
475 const result
= await dbCtx
.result(this.statement
.resourceUpsert
, { resourceId
, secret
, description
});
476 if (result
.rowCount
!= 1) {
477 throw new DBErrors
.UnexpectedResult('did not upsert resource');
479 return result
.rows
[0];
481 this.logger
.error(_scope
, 'failed', { error: e
, resourceId
, secret: logSecret
, description
});
487 async
scopeCleanup(dbCtx
, atLeastMsSinceLast
) {
488 const _scope
= _fileScope('scopeCleanup');
489 this.logger
.debug(_scope
, 'called', { atLeastMsSinceLast
});
491 const almanacEvent
= Enum
.AlmanacEntry
.ScopeCleanup
;
493 return await
this.transaction(dbCtx
, async (txCtx
) => {
495 // Check that enough time has passed since last cleanup
496 const now
= new Date();
497 const cleanupNotAfter
= new Date(now
.getTime() - atLeastMsSinceLast
);
498 const { date: lastCleanupDate
} = await txCtx
.oneOrNone(this.statement
.almanacGet
, { event: almanacEvent
}) || { date: new Date(0) };
499 if (lastCleanupDate
>= cleanupNotAfter
) {
500 this.logger
.debug(_scope
, 'skipping token cleanup, too soon', { lastCleanupDate
, cleanupNotAfter
, atLeastMsSinceLast
});
505 const { rowCount: scopesRemoved
} = await txCtx
.result(this.statement
.scopeCleanup
);
507 // Update the last cleanup time
508 const result
= await txCtx
.result(this.statement
.almanacUpsert
, { event: almanacEvent
, date: now
});
509 if (result
.rowCount
!= 1) {
510 this.constructor._almanacErrorThrow();
513 this.logger
.debug(_scope
, 'completed', { scopesRemoved
, atLeastMsSinceLast
});
514 return scopesRemoved
;
518 this.logger
.error(_scope
, 'failed', { error: e
, atLeastMsSinceLast
});
524 async
scopeDelete(dbCtx
, scope
) {
525 const _scope
= _fileScope('scopeDelete');
526 this.logger
.debug(_scope
, 'called', { scope
});
529 return await
this.transaction(dbCtx
, async (txCtx
) => {
530 const { inUse
} = await txCtx
.one(this.statement
.scopeInUse
, { scope
});
532 this.logger
.debug(_scope
, 'not deleted, in use', { scope
});
535 const result
= await txCtx
.result(this.statement
.scopeDelete
, { scope
});
536 if (result
.rowCount
== 0) {
537 this.logger
.debug(_scope
, 'no such scope', { scope
});
539 this.logger
.debug(_scope
, 'deleted', { scope
});
544 this.logger
.error(_scope
, 'failed', { error: e
, scope
});
550 async
scopeUpsert(dbCtx
, scope
, application
, description
, manuallyAdded
= false) {
551 const _scope
= _fileScope('scopeUpsert');
552 this.logger
.debug(_scope
, 'called', { scope
, description
});
555 const result
= await dbCtx
.result(this.statement
.scopeUpsert
, { scope
, application
, description
, manuallyAdded
});
556 if (result
.rowCount
!= 1) {
557 throw new DBErrors
.UnexpectedResult('did not upsert scope');
560 this.logger
.error(_scope
, 'failed', { error: e
, scope
, application
, description
});
566 async
tokenCleanup(dbCtx
, codeLifespanSeconds
, atLeastMsSinceLast
) {
567 const _scope
= _fileScope('tokenCleanup');
568 this.logger
.debug(_scope
, 'called', { codeLifespanSeconds
, atLeastMsSinceLast
});
570 const almanacEvent
= Enum
.AlmanacEntry
.TokenCleanup
;
572 return await
this.transaction(dbCtx
, async (txCtx
) => {
574 // Check that enough time has passed since last cleanup
575 const now
= new Date();
576 const cleanupNotAfter
= new Date(now
.getTime() - atLeastMsSinceLast
);
577 const { date: lastCleanupDate
} = await txCtx
.oneOrNone(this.statement
.almanacGet
, { event: almanacEvent
}) || { date: new Date(0) };
578 if (lastCleanupDate
>= cleanupNotAfter
) {
579 this.logger
.debug(_scope
, 'skipping token cleanup, too soon', { lastCleanupDate
, cleanupNotAfter
, codeLifespanSeconds
, atLeastMsSinceLast
});
584 const { rowCount: tokensRemoved
} = await txCtx
.result(this.statement
.tokenCleanup
, { codeLifespanSeconds
});
586 // Update the last cleanup time
587 const result
= await txCtx
.result(this.statement
.almanacUpsert
, { event: almanacEvent
, date: now
});
588 if (result
.rowCount
!= 1) {
589 this.constructor._almanacErrorThrow();
592 this.logger
.debug(_scope
, 'completed', { tokensRemoved
, codeLifespanSeconds
, atLeastMsSinceLast
});
593 return tokensRemoved
;
597 this.logger
.error(_scope
, 'failed', { error: e
, atLeastMsSinceLast
});
603 async
tokenGetByCodeId(dbCtx
, codeId
) {
604 const _scope
= _fileScope('tokenGetByCodeId');
605 this.logger
.debug(_scope
, 'called', { codeId
});
608 return await dbCtx
.oneOrNone(this.statement
.tokenGetByCodeId
, { codeId
});
610 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
616 async
tokenRevokeByCodeId(dbCtx
, codeId
) {
617 const _scope
= _fileScope('tokenRevokeByCodeId');
618 this.logger
.debug(_scope
, 'called', { codeId
});
621 const result
= await dbCtx
.result(this.statement
.tokenRevokeByCodeId
, { codeId
});
622 if (result
.rowCount
!= 1) {
623 throw new DBErrors
.UnexpectedResult('did not revoke token');
626 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
632 async
tokenRefreshRevokeByCodeId(dbCtx
, codeId
) {
633 const _scope
= _fileScope('tokenRefreshRevokeByCodeId');
634 this.logger
.debug(_scope
, 'called', { codeId
});
637 const result
= await dbCtx
.result(this.statement
.tokenRefreshRevokeByCodeId
, { codeId
});
638 if (result
.rowCount
!= 1) {
639 throw new DBErrors
.UnexpectedResult('did not revoke token');
642 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
648 async
tokensGetByIdentifier(dbCtx
, identifier
) {
649 const _scope
= _fileScope('tokensGetByIdentifier');
650 this.logger
.debug(_scope
, 'called', { identifier
});
653 return await dbCtx
.manyOrNone(this.statement
.tokensGetByIdentifier
, { identifier
});
655 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
661 async
ticketRedeemed(dbCtx
, redeemedData
) {
662 const _scope
= _fileScope('ticketRedeemed');
663 this.logger
.debug(_scope
, 'called', { ...redeemedData
});
666 const result
= await dbCtx
.result(this.statement
.ticketRedeemed
, redeemedData
);
667 if (result
.rowCount
!= 1) {
668 throw new DBErrors
.UnexpectedResult('did not store redeemed ticket');
671 this.logger
.error(_scope
, 'failed', { error: e
, ...redeemedData
});
677 async
ticketTokenPublished(dbCtx
, redeemedData
) {
678 const _scope
= _fileScope('ticketRedeemed');
679 this.logger
.debug(_scope
, 'called', { ...redeemedData
});
681 const almanacEvent
= Enum
.AlmanacEntry
.TicketPublished
;
683 const result
= await dbCtx
.result(this.statement
.ticketTokenPublished
, redeemedData
);
684 if (result
.rowCount
!= 1) {
685 throw new DBErrors
.UnexpectedResult('did not store redeemed ticket');
687 const almanacResult
= await dbCtx
.result(this.statement
.almanacUpsert
, { event: almanacEvent
, date: new Date() });
688 if (almanacResult
.rowCount
!= 1) {
689 this.constructor._almanacErrorThrow();
692 this.logger
.error(_scope
, 'failed', { error: e
, ...redeemedData
});
697 async
ticketTokenGetUnpublished(dbCtx
) {
698 const _scope
= _fileScope('ticketTokenGetUnpublished');
699 this.logger
.debug(_scope
, 'called');
702 return await dbCtx
.manyOrNone(this.statement
.ticketTokenGetUnpublished
);
704 this.logger
.error(_scope
, 'failed', { error: e
});
711 module
.exports
= DatabasePostgres
;