bug: Database Migration Fails with RLS and Authentication Errors

[chenfan0]

Describe the bug

When attempting to perform a database migration, the process fails with one of two critical errors. The specific error depends on whether the db seed command is executed before initiating the migration. Both paths ultimately result in a failed migration.

Scenario 1: Migration without a prior db seed

If the database migration is initiated without first running db seed, the process terminates with a Row-Level Security (RLS) error. The error log indicates that RLS is not enforced on numerous business tables.

Error Message:

index    error Error while initializing app:
index    error Error: Row-level security has to be enforced on EVERY business table when starting Logto.
Found following table(s) without RLS: custom_profile_fields, connectors, account_centers, application_secrets, application_sign_in_experiences, application_user_consent_organization_resource_scopes, hooks, idp_initiated_saml_sso_sessions, logs, oidc_model_instances, oidc_session_extensions, one_time_tokens, organization_application_relations, organization_invitation_role_relations, organization_invitations, saml_application_secrets, saml_application_sessions, scopes, secret_enterprise_sso_connector_relations, secret_social_connector_relations, secrets, sso_connectors, sso_connector_idp_initiated_auth_configs, subject_tokens, user_sso_identities, users, application_user_consent_organization_scopes, application_user_consent_organizations, application_user_consent_resource_scopes, application_user_consent_user_scopes, applications, applications_roles, captcha_providers, custom_phrases, daily_active_users, daily_token_usage, domains, email_templates, logto_configs, organization_jit_email_domains, organization_jit_roles, organization_jit_sso_connectors, organization_role_application_relations, organization_role_resource_scope_relations, organization_role_scope_relations, organization_role_user_relations, organization_roles, organization_scopes, organization_user_relations, organizations, passcodes, personal_access_tokens, resources, roles, roles_scopes, saml_application_configs, sentinel_activities, sign_in_experiences, users_roles, verification_records, verification_statuses, tenants

Did you forget to run `npm cli db alteration deploy`?
    at checkRowLevelSecurity (file:///etc/logto/packages/core/build/main-4R7WX3YC.js:34722:11)
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async Promise.all (index 1)
    at async checkPreconditions (file:///etc/logto/packages/core/build/main-4R7WX3YC.js:34709:3)
    at async Promise.all (index 3)
    at async file:///etc/logto/packages/core/build/main-4R7WX3YC.js:34781:3
cache    info Disconnected from Redis

Following the suggestion, I ran db alteration deploy, but it reported zero alterations (alteration 0).

---

Scenario 2: Migration after db seed

If db seed is executed before starting the migration, the RLS error is avoided. However, the process then fails with a password authentication error for the logto_tenant_logto_admin user.

Error Message:

error error: password authentication failed for user "logto_tenant_logto_admin"
    at Parser.parseErrorMessage (/Users/matth/Projects/selectcode/logto/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/Users/matth/Projects/selectcode/logto/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/matth/Projects/selectcode/logto/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/dist/parser.js:39:38)
    at TLSSocket.<anonymous> (/Users/matth/Projects/selectcode/logto/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/dist/index.js:11:42)
    at TLSSocket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 120,
  severity: 'FATAL',
  code: '28P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'auth.c',
  line: '335',
  routine: 'auth_failed'
}

Expected behavior

The database migration process should complete successfully. The documentation should clearly outline the prerequisite steps, including whether db seed is necessary, to ensure a smooth migration without encountering either RLS or authentication errors.

Actual Behavior

The database migration is blocked. Depending on the pre-migration steps, it fails with one of two distinct, critical errors, making it impossible to migrate the database.

How to reproduce?

For the RLS error (Scenario 1):

1. Set up a Logto instance with a database.
2. Do not run db seed.
3. Attempt to perform a database migration.
4. Observe the RLS error.

For the authentication error (Scenario 2):

1. Set up a Logto instance with a database.
2. Run db seed.
3. Attempt to perform a database migration.
4. Observe the password authentication failure for logto_tenant_logto_admin.

Environment

Self-hosted (Docker image)

Screenshots

No response

[xiaoyijun]

Hi @chenfan0 , when doing the migration, it’s important to make sure you’re not just exporting the raw table data. The database users that creates at runtime, as well as all the RLS configurations and policies, are also part of what needs to be preserved.

If the dump only contains data, then those users and RLS settings won’t come across. It may help to review how you exported the database and confirm that both the schema (with RLS) and the database users were included, not just the data rows.

[chenfan0]

Hi @xiaoyijun ,

Thank you very much for the insightful explanation. You've hit the nail on the head.

I need to clarify that I'm not using pg_dump directly. I am using a cloud provider's Data Transmission Service (DTS) for the migration. Based on your feedback, I now understand that this tool is likely only migrating the table data, while omitting the crucial database users and RLS policies.

This also explains the second error I encountered. I did try a different approach:

1. I first ran npm run cli db seed on the new, empty destination database to create the required schema, users, and RLS policies.
2. Then, I used the DTS tool to migrate only the data from the old database to the new one.

This resulted in the password authentication failed for user "logto_tenant_logto_admin" error. My assumption is that db seed created users with new, randomized passwords on the destination, which did not match the user data being migrated from the source.

Given this situation, what is the recommended procedure for a full Logto database migration, especially when using cloud-native tools like DTS?

Is there a way to:
a) Separately export the RLS policies and database users from the source database and apply them to the destination before running the data migration with DTS?
b) Or is a full pg_dump and pg_restore the only officially supported method to guarantee a successful migration?

Any guidance on the best practice for this scenario would be greatly appreciated.

[xiaoyijun]

Hi @chenfan0 , your approach is generally correct, but note that the password for logto_tenant_logto_admin is generated dynamically. You can try two things:

1. In the database, change the password of logto_tenant_logto_admin (db user) to match the db_password shown in your old tenants table.
2. Overwrite the tenants table in the new database with the one from the old database.