bug: Can not create role (and shouldn't need to, either.)

[IngwiePhoenix]

Describe the bug

I wrote myself a Kubernetes deployment with the intention to run this together with oauth2-proxy to utilize Traefik ForwardAuth to protect services that do not have authentication by default (like some dashboards). When doing so, the following happens:

# kubectl logs -f -n logto deployments/logto-sys
+ export 'CI=true'
+ npm run cli db seed -- --swe

> cli
> logto db seed --swe

- Create tables
info ✖ Create tables
error error: permission denied to create role
    at /etc/logto/node_modules/.pnpm/pg@8.11.3/node_modules/pg/lib/client.js:526:17
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/connectionMethods/query.js:4:24
    at async executeQuery (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/routines/executeQuery.js:130:26)
    at async query (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/connectionMethods/query.js:3:12)
    at async runLifecycleQuery (file:///etc/logto/packages/cli/lib/commands/database/seed/tables.js:53:13)
    at async createTables (file:///etc/logto/packages/cli/lib/commands/database/seed/tables.js:70:5)
    at async oraPromise (file:///etc/logto/packages/cli/lib/utils.js:72:24)
    at async file:///etc/logto/packages/cli/lib/commands/database/seed/index.js:15:27
    at async execTransaction (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/connectionMethods/transaction.js:16:24)
    at async transaction (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/connectionMethods/transaction.js:74:16)
    at async createConnection (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/factories/createConnection.js:108:18)
    at async Object.transaction (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/binders/bindPool.js:108:20)
    at async seedByPool (file:///etc/logto/packages/cli/lib/commands/database/seed/index.js:8:5)
    at async Object.handler (file:///etc/logto/packages/cli/lib/commands/database/seed/index.js:77:13) {
  length: 145,
  severity: 'ERROR',
  code: '42501',
  detail: 'Only roles with the CREATEROLE attribute may create roles.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'user.c',
  line: '316',
  routine: 'CreateRole',
  notices: []
}
error Error ocurred during seeding your database.

  Nothing has changed since the seeding process was in a transaction.
  Try to fix the error and seed again.
error: permission denied to create role
    at /etc/logto/node_modules/.pnpm/pg@8.11.3/node_modules/pg/lib/client.js:526:17
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/connectionMethods/query.js:4:24
    at async executeQuery (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/routines/executeQuery.js:130:26)
    at async query (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/connectionMethods/query.js:3:12)
    at async runLifecycleQuery (file:///etc/logto/packages/cli/lib/commands/database/seed/tables.js:53:13)
    at async createTables (file:///etc/logto/packages/cli/lib/commands/database/seed/tables.js:70:5)
    at async oraPromise (file:///etc/logto/packages/cli/lib/utils.js:72:24)
    at async file:///etc/logto/packages/cli/lib/commands/database/seed/index.js:15:27
    at async execTransaction (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/connectionMethods/transaction.js:16:24)
    at async transaction (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/connectionMethods/transaction.js:74:16)
    at async createConnection (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/factories/createConnection.js:108:18)
    at async Object.transaction (file:///etc/logto/node_modules/.pnpm/@silverhand+slonik@31.0.0-beta.2/node_modules/@silverhand/slonik/dist/src/binders/bindPool.js:108:20)
    at async seedByPool (file:///etc/logto/packages/cli/lib/commands/database/seed/index.js:8:5)
    at async Object.handler (file:///etc/logto/packages/cli/lib/commands/database/seed/index.js:77:13) {
  length: 145,
  severity: 'ERROR',
  code: '42501',
  detail: 'Only roles with the CREATEROLE attribute may create roles.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'user.c',
  line: '316',
  routine: 'CreateRole',
  notices: []
}

Specify --help for available options
+ true

Expected behavior

Since I passed a DB_URL with username, password and everything, it should just create the schemas. The provided credentials are from an operator that generates database, username and password automatically - so, this shouldn't be required.

How to reproduce?

k3s deployment

apiVersion: v1
kind: Namespace
metadata:
  name: logto
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: logto-env
  namespace: logto
data:
  # kubectl port-forward -n logto deployments/logto-sys -c app 3002
  # easier way tho... ?
  ADMIN_ENDPOINT: https://logto.birb.it
  ENDPOINT: https://auth.birb.it
  TRUST_PROXY_HEADER: "true"
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: logto-startup
  namespace: logto
data:
  start.sh: |-
    #!/bin/sh
    set -eux
    export CI=true
    npm run cli db seed -- --swe || true
    npm run alteration deploy latest || true
    npm run cli connector add -- --official
    npm run start
---
apiVersion: postgresql.easymile.com/v1alpha1
kind: PostgresqlDatabase
metadata:
  name: logto-db
  namespace: logto
spec:
  engineConfiguration:
    name: default-cluster-instance
    namespace: postgres
  database: logto
  masterRole: "logto-role"
  dropOnDelete: true
  waitLinkedResourcesDeletion: true
---
apiVersion: postgresql.easymile.com/v1alpha1
kind: PostgresqlUserRole
metadata:
  name: logto-db-user
  namespace: logto
spec:
  mode: MANAGED
  rolePrefix: "logto"
  userPasswordRotationDuration: 720h
  privileges:
    - privilege: OWNER
      database:
        name: logto-db
      generatedSecretName: logto-db-creds
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: logto-sys
  namespace: logto
spec:
  selector:
    matchLabels:
      app: logto
  template:
    metadata:
      labels:
        app: logto
    spec:
      volumes:
        - name: logto-connectors-vol
          emptyDir: {}
        - name: logto-startup-vol
          configMap:
            name: logto-startup
      containers:
        - name: app
          image: ghcr.io/logto-io/logto:1.22
          command:
            - /bin/sh
            - /opt/start.sh
          envFrom:
            - configMapRef: { name: logto-env }
          env:
            - name: DB_URL
              valueFrom:
                secretKeyRef:
                  name: logto-db-creds
                  key: POSTGRES_URL
          volumeMounts:
            - name: logto-connectors-vol
              mountPath: /etc/logto/packages/core/connectors
            - name: logto-startup-vol
              mountPath: /opt
          ports:
            - name: http-admin
              containerPort: 3002
              protocol: TCP
            - name: http-auth
              containerPort: 3001
              protocol: TCP
---
apiVersion: v1
kind: Service
metadata:
  name: logto-svc
  namespace: logto
spec:
  type: ClusterIP
  ports:
    - targetPort: http-auth
      name: http-auth
      port: 3001
    - targetPort: http-admin
      name: http-admin
      port: 3002
  selector:
    app: logto
---
apiVersion: traefik.io/v1alpha1
kind: IngressRoute
metadata:
  name: logto-tr
  namespace: logto
spec:
  entryPoints:
    - websecure
  routes:
    - match: Host(`auth.birb.it`)
      kind: Rule
      services:
        - name: logto-svc
          port: http-auth
          scheme: http
          passHostHeader: true
    - match: Host(`logto.birb.it`)
      kind: Rule
      services:
        - name: logto-svc
          port: http-admin
          scheme: http
          passHostHeader: true

Context

• Logto Cloud
• Self-hosted, Logto version = ghcr.io/logto-io/logto:1.22
  • Container (Docker image)
  • Raw Node.js

Screenshots

N/A

[xiaoyijun]

Hi @IngwiePhoenix , the error message says: "Only roles with the CREATEROLE attribute may create roles."

Please check your db user permissions.

[IngwiePhoenix]

Why is it creating a new role anyway? It has a username, password and a database...all it needs to do is to deploy the table schemas. This is what's confusing me.

Sure I could probably give it the permission, but I use the EasyMile Postgres Operator to manage the accounts and databases. For instance:

apiVersion: postgresql.easymile.com/v1alpha1
kind: PostgresqlDatabase
metadata:
  name: logto-db
  namespace: logto
spec:
  engineConfiguration:
    name: default-cluster-instance
    namespace: postgres
  database: logto
  masterRole: "logto-role"
  dropOnDelete: true
  waitLinkedResourcesDeletion: true
---
apiVersion: postgresql.easymile.com/v1alpha1
kind: PostgresqlUserRole
metadata:
  name: logto-db-user
  namespace: logto
spec:
  mode: MANAGED
  rolePrefix: "logto"
  userPasswordRotationDuration: 720h
  privileges:
    - privilege: OWNER
      database:
        name: logto-db
      generatedSecretName: logto-db-creds

But I wonder why LogTo needs to create a separate role, when it already has creds it can use?

[kpalang]

Gonna chime in here as well as I too was confused by the need to create a sort of s"superadmin" role to let LogTo seed itself.

I also think that LogTo should handle everything it needs to do via the provided db user and not create any other roles.

[charIeszhao]

I think this blog post might explain your doubts about the DB roles:
https://blog.logto.io/implement-multi-tenancy#setup-db-roles-for-tenants