fiware-idm
fiware-idm copied to clipboard
Published
20 hours ago •
ging
ging
migrate_db for PostgreSQL fails.
When using PostgreSQL as a back-end database for Keyrock 8.0.0 or 8.1.0, the migrate_db fails.
== 20210603073911-hashed-access-tokens: migrating =======
ERROR: [object Object]
The followings are detailed logs.
- create_db
bash-5.0$ npm run-script create_db
> [email protected] create_db /opt/fiware-idm
> sequelize --env database db:create
Sequelize CLI [Node: 12.22.3, CLI: 4.1.1, ORM: 4.44.4]
idm:config *********************************************** +0ms
idm:config WARNING: The current encryption keys match the defaults found in the plaintext +0ms
idm:config template file - please update for a production instance +0ms
idm:config *********************************************** +0ms
Loaded configuration file "lib/sequelize.js".
Using environment "database".
sequelize deprecated String based operators are now deprecated. Please use Symbol based operators for better security, read more at http://docs.sequelizejs.com/manual/tutorial/querying.html#operators node_modules/sequelize/lib/sequelize.js:245:13
Database idm created.
- migrate_db
bash-5.0$ npm run-script migrate_db
> [email protected] migrate_db /opt/fiware-idm
> sequelize --env database db:migrate
Sequelize CLI [Node: 12.22.3, CLI: 4.1.1, ORM: 4.44.4]
idm:config *********************************************** +0ms
idm:config WARNING: The current encryption keys match the defaults found in the plaintext +0ms
idm:config template file - please update for a production instance +0ms
idm:config *********************************************** +0ms
Loaded configuration file "lib/sequelize.js".
Using environment "database".
sequelize deprecated String based operators are now deprecated. Please use Symbol based operators for better security, read more at http://docs.sequelizejs.com/manual/tutorial/querying.html#operators node_modules/sequelize/lib/sequelize.js:245:13
== 201802190000-CreateUserTable: migrating =======
== 201802190000-CreateUserTable: migrated (0.013s)
== 201802190003-CreateUserRegistrationProfileTable: migrating =======
== 201802190003-CreateUserRegistrationProfileTable: migrated (0.008s)
== 201802190005-CreateOrganizationTable: migrating =======
== 201802190005-CreateOrganizationTable: migrated (0.007s)
== 201802190008-CreateOAuthClientTable: migrating =======
== 201802190008-CreateOAuthClientTable: migrated (0.007s)
== 201802190009-CreateUserAuthorizedApplicationTable: migrating =======
== 201802190009-CreateUserAuthorizedApplicationTable: migrated (0.006s)
== 201802190010-CreateRoleTable: migrating =======
== 201802190010-CreateRoleTable: migrated (0.006s)
== 201802190015-CreatePermissionTable: migrating =======
== 201802190015-CreatePermissionTable: migrated (0.008s)
== 201802190020-CreateRoleAssignmentTable: migrating =======
== 201802190020-CreateRoleAssignmentTable: migrated (0.007s)
== 201802190025-CreateRolePermissionTable: migrating =======
== 201802190025-CreateRolePermissionTable: migrated (0.006s)
== 201802190030-CreateUserOrganizationTable: migrating =======
== 201802190030-CreateUserOrganizationTable: migrated (0.006s)
== 201802190035-CreateIotTable: migrating =======
== 201802190035-CreateIotTable: migrated (0.005s)
== 201802190040-CreatePepProxyTable: migrating =======
== 201802190040-CreatePepProxyTable: migrated (0.006s)
== 201802190045-CreateAuthZForceTable: migrating =======
== 201802190045-CreateAuthZForceTable: migrated (0.006s)
== 201802190050-CreateAuthTokenTable: migrating =======
== 201802190050-CreateAuthTokenTable: migrated (0.007s)
== 201802190060-CreateOAuthAuthorizationCodeTable: migrating =======
== 201802190060-CreateOAuthAuthorizationCodeTable: migrated (0.007s)
== 201802190065-CreateOAuthAccessTokenTable: migrating =======
== 201802190065-CreateOAuthAccessTokenTable: migrated (0.008s)
== 201802190070-CreateOAuthRefreshTokenTable: migrating =======
== 201802190070-CreateOAuthRefreshTokenTable: migrated (0.008s)
== 201802190075-CreateOAuthScopeTable: migrating =======
== 201802190075-CreateOAuthScopeTable: migrated (0.006s)
== 20180405125424-CreateUserTourAttribute: migrating =======
== 20180405125424-CreateUserTourAttribute: migrated (0.005s)
== 20180612134640-CreateEidasTable: migrating =======
== 20180612134640-CreateEidasTable: migrated (0.009s)
== 20180727101745-CreateUserEidasIdAttribute: migrating =======
== 20180727101745-CreateUserEidasIdAttribute: migrated (0.004s)
== 20180730094347-CreateTrustedApplicationsTable: migrating =======
== 20180730094347-CreateTrustedApplicationsTable: migrated (0.006s)
== 20180828133454-CreatePasswordSalt: migrating =======
== 20180828133454-CreatePasswordSalt: migrated (0.013s)
== 20180921104653-CreateEidasNifColumn: migrating =======
== 20180921104653-CreateEidasNifColumn: migrated (0.004s)
== 20180922140934-CreateOauthTokenType: migrating =======
== 20180922140934-CreateOauthTokenType: migrated (0.005s)
== 20181022103002-CreateEidasTypeAndAttributes: migrating =======
== 20181022103002-CreateEidasTypeAndAttributes: migrated (0.005s)
== 20181108144720-RevokeToken: migrating =======
== 20181108144720-RevokeToken: migrated (0.020s)
== 20181113121450-FixExtraAndScopeAttribute: migrating =======
== 20181113121450-FixExtraAndScopeAttribute: migrated (0.026s)
== 20181203120316-FixTokenTypesLength: migrating =======
== 20181203120316-FixTokenTypesLength: migrated (0.004s)
== 20190116101526-CreateSignOutUrl: migrating =======
== 20190116101526-CreateSignOutUrl: migrated (0.004s)
== 20190316203230-CreatePermissionIsRegex: migrating =======
== 20190316203230-CreatePermissionIsRegex: migrated (0.005s)
== 20190429164755-CreateUsagePolicyTable: migrating =======
== 20190429164755-CreateUsagePolicyTable: migrated (0.010s)
== 20190507112246-CreateRoleUsagePolicyTable: migrating =======
== 20190507112246-CreateRoleUsagePolicyTable: migrated (0.005s)
== 20190507112259-CreatePtpTable: migrating =======
== 20190507112259-CreatePtpTable: migrated (0.005s)
== 20191019153205-UpdateUserAuthorizedApplicationTable: migrating =======
== 20191019153205-UpdateUserAuthorizedApplicationTable: migrated (0.004s)
== 20200107102154-CreatePermissionFiwareService: migrating =======
== 20200107102154-CreatePermissionFiwareService: migrated (0.004s)
== 20200107102154-CreatePermissionUseFiwareService: migrating =======
== 20200107102154-CreatePermissionUseFiwareService: migrated (0.005s)
== 20200928134556-AddDisable2faKey: migrating =======
== 20200928134556-AddDisable2faKey: migrated (0.004s)
== 20210422214057-init-visible_attributes: migrating =======
== 20210422214057-init-visible_attributes: migrated (0.004s)
== 20210423161823-AddOidcNonce.js: migrating =======
== 20210423161823-AddOidcNonce.js: migrated (0.004s)
== 20210603073911-hashed-access-tokens: migrating =======
ERROR: [object Object]
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] migrate_db: `sequelize --env database db:migrate`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] migrate_db script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
npm ERR! A complete log of this run can be found in:
npm ERR! /home/node/.npm/_logs/2021-09-20T03_31_08_559Z-debug.log
- debug.log
bash-5.0$ cat /home/node/.npm/_logs/2021-09-20T03_31_08_559Z-debug.log
0 info it worked if it ends with ok
1 verbose cli [
1 verbose cli '/usr/local/bin/node',
1 verbose cli '/usr/local/bin/npm',
1 verbose cli 'run-script',
1 verbose cli 'migrate_db'
1 verbose cli ]
2 info using [email protected]
3 info using [email protected]
4 verbose run-script [ 'premigrate_db', 'migrate_db', 'postmigrate_db' ]
5 info lifecycle [email protected]~premigrate_db: [email protected]
6 info lifecycle [email protected]~migrate_db: [email protected]
7 verbose lifecycle [email protected]~migrate_db: unsafe-perm in lifecycle true
8 verbose lifecycle [email protected]~migrate_db: PATH: /usr/local/lib/node_modules/npm/node_modules/npm-lifecycle/node-gyp-bin:/opt/fiware-idm/node_modules/.bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
9 verbose lifecycle [email protected]~migrate_db: CWD: /opt/fiware-idm
10 silly lifecycle [email protected]~migrate_db: Args: [ '-c', 'sequelize --env database db:migrate' ]
11 silly lifecycle [email protected]~migrate_db: Returned: code: 1 signal: null
12 info lifecycle [email protected]~migrate_db: Failed to exec migrate_db script
13 verbose stack Error: [email protected] migrate_db: `sequelize --env database db:migrate`
13 verbose stack Exit status 1
13 verbose stack at EventEmitter.<anonymous> (/usr/local/lib/node_modules/npm/node_modules/npm-lifecycle/index.js:332:16)
13 verbose stack at EventEmitter.emit (events.js:314:20)
13 verbose stack at ChildProcess.<anonymous> (/usr/local/lib/node_modules/npm/node_modules/npm-lifecycle/lib/spawn.js:55:14)
13 verbose stack at ChildProcess.emit (events.js:314:20)
13 verbose stack at maybeClose (internal/child_process.js:1022:16)
13 verbose stack at Process.ChildProcess._handle.onexit (internal/child_process.js:287:5)
14 verbose pkgid [email protected]
15 verbose cwd /opt/fiware-idm
16 verbose Linux 5.11.0-34-generic
17 verbose argv "/usr/local/bin/node" "/usr/local/bin/npm" "run-script" "migrate_db"
18 verbose node v12.22.3
19 verbose npm v6.14.13
20 error code ELIFECYCLE
21 error errno 1
22 error [email protected] migrate_db: `sequelize --env database db:migrate`
22 error Exit status 1
23 error Failed at the [email protected] migrate_db script.
23 error This is probably not a problem with npm. There is likely additional logging output above.
24 verbose exit [ 1, true ]
The following error occurs when removing access_token as primary key.
idm:config error:SequelizeUnknownConstraintError: [object Object]
https://github.com/ging/fiware-idm/blob/c0f256b2294133b54e066efd5d8a035df95c21f0/migrations/20210603073911-hashed-access-tokens.js#L24-L26
The table is as shown:
postgres=# \c idm
You are now connected to database "idm" as user "postgres".
idm=# \d oauth_access_token
Table "public.oauth_access_token"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+--------------
access_token | character varying(255) | | not null |
expires | timestamp with time zone | | |
scope | character varying(2000) | | |
refresh_token | character varying(255) | | |
valid | boolean | | |
extra | json | | |
oauth_client_id | character varying(36) | | |
user_id | character varying(36) | | |
iot_id | character varying(255) | | |
authorization_code | character varying(255) | | |
hash | character(64) | | | NULL::bpchar
Indexes:
"oauth_access_token_pkey" PRIMARY KEY, btree (access_token)
Foreign-key constraints:
"authorization_code_at" FOREIGN KEY (authorization_code) REFERENCES oauth_authorization_code(authorization_code) ON DELETE CASCADE
"oauth_access_token_iot_id_fkey" FOREIGN KEY (iot_id) REFERENCES iot(id) ON DELETE CASCADE
"oauth_access_token_oauth_client_id_fkey" FOREIGN KEY (oauth_client_id) REFERENCES oauth_client(id) ON DELETE CASCADE
"oauth_access_token_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) ON DELETE CASCADE
"refresh_token" FOREIGN KEY (refresh_token) REFERENCES oauth_refresh_token(refresh_token) ON DELETE CASCADE
I replaced access_token with oauth_access_token_pkey and rerun it.
}).then(() => {
debug("Remove access_token as primary key");
// Remove access_token as primary key
return queryInterface.removeConstraint('oauth_access_token', 'oauth_access_token_pkey', {});
The idexes were deleted.
postgres=# \c idm
You are now connected to database "idm" as user "postgres".
idm=# \d oauth_access_token
Table "public.oauth_access_token"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+--------------
access_token | character varying(255) | | not null |
expires | timestamp with time zone | | |
scope | character varying(2000) | | |
refresh_token | character varying(255) | | |
valid | boolean | | |
extra | json | | |
oauth_client_id | character varying(36) | | |
user_id | character varying(36) | | |
iot_id | character varying(255) | | |
authorization_code | character varying(255) | | |
hash | character(64) | | | NULL::bpchar
Foreign-key constraints:
"authorization_code_at" FOREIGN KEY (authorization_code) REFERENCES oauth_authorization_code(authorization_code) ON DELETE CASCADE
"oauth_access_token_iot_id_fkey" FOREIGN KEY (iot_id) REFERENCES iot(id) ON DELETE CASCADE
"oauth_access_token_oauth_client_id_fkey" FOREIGN KEY (oauth_client_id) REFERENCES oauth_client(id) ON DELETE CASCADE
"oauth_access_token_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) ON DELETE CASCADE
"refresh_token" FOREIGN KEY (refresh_token) REFERENCES oauth_refresh_token(refresh_token) ON DELETE CASCADE
Then, I encoutned the following error error:SequelizeUnknownConstraintError: [object Object].
https://github.com/ging/fiware-idm/blob/c0f256b2294133b54e066efd5d8a035df95c21f0/migrations/20210603073911-hashed-access-tokens.js#L27-L28
I removed this code and rerun it. It seems that the new indexes were created.
postgres=# \c idm
You are now connected to database "idm" as user "postgres".
idm=# \d oauth_access_token
Table "public.oauth_access_token"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+--------------
access_token | text | | not null |
expires | timestamp with time zone | | |
scope | character varying(2000) | | |
refresh_token | character varying(255) | | |
valid | boolean | | |
extra | json | | |
oauth_client_id | character varying(36) | | |
user_id | character varying(36) | | |
iot_id | character varying(255) | | |
authorization_code | character varying(255) | | |
hash | character(64) | | not null | NULL::bpchar
Indexes:
"oauth_access_token_hash_pk" PRIMARY KEY, btree (hash)
"oauth_access_token_hash_uk" UNIQUE CONSTRAINT, btree (hash)
Foreign-key constraints:
"authorization_code_at" FOREIGN KEY (authorization_code) REFERENCES oauth_authorization_code(authorization_code) ON DELETE CASCADE
"oauth_access_token_iot_id_fkey" FOREIGN KEY (iot_id) REFERENCES iot(id) ON DELETE CASCADE
"oauth_access_token_oauth_client_id_fkey" FOREIGN KEY (oauth_client_id) REFERENCES oauth_client(id) ON DELETE CASCADE
"oauth_access_token_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) ON DELETE CASCADE
"refresh_token" FOREIGN KEY (refresh_token) REFERENCES oauth_refresh_token(refresh_token) ON DELETE CASCADE
Keyrock has been ready.
keyrock_1 |
keyrock_1 | > [email protected] start /opt/fiware-idm
keyrock_1 | > node ./bin/www
keyrock_1 |
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_PORT to environment value: 3000
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_HOST to environment value: http://localhost:3000
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_PDP_LEVEL to environment value: basic
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_DB_HOST to environment value: postgres
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_DB_PASS to environment value: ********
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_DB_USER to environment value: ********
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_DB_NAME to environment value: idm
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_DB_DIALECT to environment value: postgres
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_DB_PORT to environment value: 5432
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_EMAIL_HOST to environment value: localhost
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_EMAIL_PORT to environment value: 25
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config Setting IDM_EMAIL_ADDRESS to environment value: noreply@localhost
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config ***********************************************
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config WARNING: The current encryption keys match the defaults found in the plaintext
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config template file - please update for a production instance
keyrock_1 | Mon, 20 Sep 2021 05:47:24 GMT idm:config ***********************************************
keyrock_1 | postgres:5432 - Connection refused
keyrock_1 | retry after 5 seconds.
keyrock_1 | Database created
keyrock_1 | Database migrated
keyrock_1 | Database seeded
keyrock_1 | ****************
keyrock_1 | WARNING: Seeding database with an admin user using default credentials.
keyrock_1 | This user must be deleted when running on a production instance
keyrock_1 | ****************
keyrock_1 | Mon, 20 Sep 2021 05:47:31 GMT sequelize deprecated String based operators are now deprecated. Please use Symbol based operators for better security, read more at http://docs.sequelizejs.com/manual/tutorial/querying.html#operators at node_modules/sequelize/lib/sequelize.js:245:13
keyrock_1 | Mon, 20 Sep 2021 05:47:31 GMT idm:server Listening on port 3000
keyrock_1 | Mon, 20 Sep 2021 05:47:31 GMT idm:models Connection has been established successfully