logto-io
bug: Can not create role (and shouldn't need to, either.)
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/[email protected]/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/@[email protected]/node_modules/@silverhand/slonik/dist/src/connectionMethods/query.js:4:24
at async executeQuery (file:///etc/logto/node_modules/.pnpm/@[email protected]/node_modules/@silverhand/slonik/dist/src/routines/executeQuery.js:130:26)
at async query (file:///etc/logto/node_modules/.pnpm/@[email protected]/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/@[email protected]/node_modules/@silverhand/slonik/dist/src/connectionMethods/transaction.js:16:24)
at async transaction (file:///etc/logto/node_modules/.pnpm/@[email protected]/node_modules/@silverhand/slonik/dist/src/connectionMethods/transaction.js:74:16)
at async createConnection (file:///etc/logto/node_modules/.pnpm/@[email protected]/node_modules/@silverhand/slonik/dist/src/factories/createConnection.js:108:18)
at async Object.transaction (file:///etc/logto/node_modules/.pnpm/@[email protected]/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/[email protected]/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/@[email protected]/node_modules/@silverhand/slonik/dist/src/connectionMethods/query.js:4:24
at async executeQuery (file:///etc/logto/node_modules/.pnpm/@[email protected]/node_modules/@silverhand/slonik/dist/src/routines/executeQuery.js:130:26)
at async query (file:///etc/logto/node_modules/.pnpm/@[email protected]/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/@[email protected]/node_modules/@silverhand/slonik/dist/src/connectionMethods/transaction.js:16:24)
at async transaction (file:///etc/logto/node_modules/.pnpm/@[email protected]/node_modules/@silverhand/slonik/dist/src/connectionMethods/transaction.js:74:16)
at async createConnection (file:///etc/logto/node_modules/.pnpm/@[email protected]/node_modules/@silverhand/slonik/dist/src/factories/createConnection.js:108:18)
at async Object.transaction (file:///etc/logto/node_modules/.pnpm/@[email protected]/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
- [X] Self-hosted, Logto version =
ghcr.io/logto-io/logto:1.22- [X] Container (Docker image)
- [ ] Raw Node.js
Screenshots
N/A
Hi @IngwiePhoenix , the error message says: "Only roles with the CREATEROLE attribute may create roles."
Please check your db user permissions.
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?
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.
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
That was a great read, thanks!
That said, in Kubernetes environments and the likes (think common hosting providers), you'll often not have more than one set of credentials. Hence why I still think that LogTo should support it - even if not by default, make it an option at the very least (i.e. env.LOGTO_DB_SINGLETON=true)
This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 5 days.
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}
This issue is stale because it has been open for 30 days with no activity.
Has this feature been considered by chance? I finally have time to set out and pick an IdP to set up and configure, and LogTo was still on my list of candidates. :)
This issue is stale because it has been open for 30 days with no activity.