search

ging / fiware-idm

OAuth 2.0-based authentication of users and devices, user profile management, Single Sign-On (SSO) and Identity Federation across multiple administration domains.
https://keyrock-fiware.github.io
MIT License
36 stars 81 forks source link

migrate_db for PostgreSQL fails. #238

Open fisuda opened 2 years ago

fisuda commented 2 years ago

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.

fiware-idm@8.1.0 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

fiware-idm@8.1.0 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! fiware-idm@8.1.0 migrate_db: sequelize --env database db:migrate npm ERR! Exit status 1 npm ERR! npm ERR! Failed at the fiware-idm@8.1.0 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 npm@6.14.13 3 info using node@v12.22.3 4 verbose run-script [ 'premigrate_db', 'migrate_db', 'postmigrate_db' ] 5 info lifecycle fiware-idm@8.1.0~premigrate_db: fiware-idm@8.1.0 6 info lifecycle fiware-idm@8.1.0~migrate_db: fiware-idm@8.1.0 7 verbose lifecycle fiware-idm@8.1.0~migrate_db: unsafe-perm in lifecycle true 8 verbose lifecycle fiware-idm@8.1.0~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 fiware-idm@8.1.0~migrate_db: CWD: /opt/fiware-idm 10 silly lifecycle fiware-idm@8.1.0~migrate_db: Args: [ '-c', 'sequelize --env database db:migrate' ] 11 silly lifecycle fiware-idm@8.1.0~migrate_db: Returned: code: 1 signal: null 12 info lifecycle fiware-idm@8.1.0~migrate_db: Failed to exec migrate_db script 13 verbose stack Error: fiware-idm@8.1.0 migrate_db: sequelize --env database db:migrate 13 verbose stack Exit status 1 13 verbose stack at EventEmitter. (/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. (/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 fiware-idm@8.1.0 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 fiware-idm@8.1.0 migrate_db: sequelize --env database db:migrate 22 error Exit status 1 23 error Failed at the fiware-idm@8.1.0 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 ]

fisuda commented 2 years ago

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   | > fiware-idm@8.1.0 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