bug: Deploy logto with managed databases and locked role creation permissions

[scipe]

Describe the bug

Hey folks.
Sorry for creating it as a bug, but after couple of researching hours I wasn't able to answer my question.

We using one of CSP and their managed database solution (PSQL17). In that CSP users in database cannot create roles themselves and you can do it only via the CSP API/UI.

Question:

1. Is it possible to run logto under only one role/database?
2. If not, is it possible that we will create roles system need with exact same name? If so how to predict those roles?

Expected behavior

Logto can be run on CSP with managed-database and limited amount of permissions (such as CREATEROLE)

How to reproduce?

1. Use latest version of logto
2. CSP with managed database and limited permissions
3. Try to run the system

Environment

Self-hosted (Docker image)

Screenshots

No response

[xiaoyijun]

Hi @scipe , thanks for your question!

Short Answer

Currently, Logto requires CREATEROLE permission during database initialization. There's no built-in option to skip role creation yet.

Background: Why Logto Creates Database Roles

Logto uses PostgreSQL's Row Level Security (RLS) to implement multi-tenant data isolation. The RLS policy uses current_user to match against the db_user column in the tenants table, determining which tenant's data can be accessed. This is a security mechanism to prevent data leakage between tenants.

Role & Table Structure

Logto creates 3 database roles during initialization. Assuming your database name is logto:

Role Name	Purpose
logto_tenant_logto	Parent role that holds base permissions
logto_tenant_logto_default	Database role for default tenant
logto_tenant_logto_admin	Database role for admin tenant

The naming convention is:

• Parent role: logto_tenant_<database_name>
• Tenant roles: logto_tenant_<database_name>_<tenant_id>

These roles are also stored in the tenants table:

id	db_user	db_user_password
admin	logto_tenant_logto_admin	(random 32-char string)
default	logto_tenant_logto_default	(random 32-char string)

At runtime, Logto uses these credentials (db_user + db_user_password) to connect to the database with the appropriate tenant context.

Current Limitation

Even if you pre-create these roles via your CSP's API/UI, the Logto seed command will still attempt to execute CREATE ROLE statements, which will fail due to permission restrictions.

Possible Workarounds (Requires Building from Source)

If you're able to build a custom Docker image or run from source, here's a workaround:

Step 1: Pre-create roles via your CSP's API/UI

-- Parent role (noinherit, no login)
CREATE ROLE logto_tenant_<db_name> NOINHERIT;

-- Tenant roles (remember the passwords you set!)
CREATE ROLE logto_tenant_<db_name>_admin
  WITH INHERIT LOGIN PASSWORD '<your_password_for_admin>'
  IN ROLE logto_tenant_<db_name>;

CREATE ROLE logto_tenant_<db_name>_default
  WITH INHERIT LOGIN PASSWORD '<your_password_for_default>'
  IN ROLE logto_tenant_<db_name>;

Step 2: Modify source code to skip role creation

Comment out or remove the role creation statements in these files:

1. packages/schemas/tables/_before_all.sql - comment out line 3:

   -- create role logto_tenant_${database} password '${password}' noinherit;

2. packages/cli/src/commands/database/seed/tenant.ts - comment out lines 37-41:

   // await pool.query(sql`
   //   create role ${sql.identifier([role])} with inherit login
   //     password '${sql.raw(password)}'
   //     in role ${sql.identifier([parentRole])};
   // `);

Step 3: Run the seed command

Build and run Logto with your modified code. The seed process will:

• Create all tables
• Insert records into the tenants table (with randomly generated passwords)
• Skip role creation (due to your code changes)

Step 4: Update passwords in the tenants table

After seeding completes, the tenants table will have randomly generated passwords that don't match the passwords you set when creating the roles. Update them to match:

UPDATE tenants SET db_user_password = '<your_password_for_admin>' WHERE id = 'admin';
UPDATE tenants SET db_user_password = '<your_password_for_default>' WHERE id = 'default';

Summary

Step	Action
1	Pre-create 3 roles via CSP API/UI
2	Modify source code to skip role creation
3	Run seed command
4	Update tenants table with correct passwords

If Building from Source is Not an Option

Unfortunately, there's no workaround at this time if you cannot modify the source code.

Future Plans

We recognize this is a limitation for users on managed database services with restricted permissions. We'll consider adding a --skip-role-creation flag or similar option in a future release, but it's not on our immediate roadmap.

PRs are welcome if this is a critical blocker for your use case!