bug: "role windmill_user does not exist" on initial install

[martinjuhasz]

Describe the bug

hey all,

i tried running thewindmill docker image for the first time and somehow i ended up with this error error returned from database: role "windmill_user" does not exist.

This happens on initial migrations that are run on setup

2025-01-16T21:45:33.898008Z  INFO windmill-api/src/db.rs:154: Finished applying migration 20221104105107
2025-01-16T21:45:33.898034Z  INFO windmill-api/src/db.rs:128: Started applying migration 20221104111317: completed job-raw lock
2025-01-16T21:45:33.915238Z  INFO windmill-api/src/db.rs:154: Finished applying migration 20221104111317
2025-01-16T21:45:33.915281Z  INFO windmill-api/src/db.rs:128: Started applying migration 20221104135246: flow-dependency job
2025-01-16T21:45:33.933547Z  INFO windmill-api/src/db.rs:154: Finished applying migration 20221104135246
2025-01-16T21:45:33.933594Z  INFO windmill-api/src/db.rs:128: Started applying migration 20221105003256: grant all
2025-01-16T21:45:33.934119Z  INFO windmill-api/src/db.rs:154: Finished applying migration 20221105003256
Error: Migrating database: while executing migration 20221105003256: error returned from database: role "windmill_user" does not exist

Caused by:
    0: while executing migration 20221105003256: error returned from database: role "windmill_user" does not exist
    1: error returned from database: role "windmill_user" does not exist
    2: role "windmill_user" does not exist
Binary is in 'standalone' mode
jemalloc enabled
Connecting to database...
2025-01-16T21:45:41.183529Z  INFO src/main.rs:366: PostgreSQL version: PostgreSQL 14.15 (Debian 14.15-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit (windmill require PG >= 14)
2025-01-16T21:45:41.184069Z  INFO windmill-api/src/db.rs:71: Acquiring global PG lock for potential migration with pid: Some(82)
2025-01-16T21:45:41.184522Z  INFO windmill-api/src/db.rs:91: Acquired global PG lock
2025-01-16T21:45:41.185638Z  INFO windmill-api/src/db.rs:128: Started applying migration 20221105003256: grant all
2025-01-16T21:45:41.185943Z  INFO windmill-api/src/db.rs:154: Finished applying migration 20221105003256
Error: Migrating database: while executing migration 20221105003256: error returned from database: role "windmill_user" does not exist

Caused by:
    0: while executing migration 20221105003256: error returned from database: role "windmill_user" does not exist
    1: error returned from database: role "windmill_user" does not exist
    2: role "windmill_user" does not exist
Binary is in 'standalone' mode
jemalloc enabled
Connecting to database...

any idea what is causing this? the postgres db was also fresh.

To reproduce

run the container

docker run
  -d
  --name='windmill'
  --net='apps'
  --pids-limit 2048
  -e TZ="Europe/Berlin"
  -e HOST_OS="Unraid"
  -e HOST_HOSTNAME="Winston"
  -e HOST_CONTAINERNAME="windmill"
  -e 'DATABASE_URL'='postgres://windmill:REDACTED@postgres:5432/windmill?sslmode=disable'
  -e 'WORKER_GROUP'='default'
  -e 'MODE'='standalone'
  -l net.unraid.docker.managed=dockerman
  -l net.unraid.docker.webui='http://[IP]:[PORT:8000]'
  -l net.unraid.docker.icon='https://raw.githubusercontent.com/sgraaf/Unraid-Docker-Templates/main/windmill/icon.png'
  -p '8005:8000/tcp'
  -p '2525:2525/tcp'
  -v '/mnt/temporary/temp/windmill/log':'/tmp/windmill/logs':'rw'
  -v '/mnt/temporary/temp/windmill/cache':'/tmp/windmill/cache':'rw'
  -v '/var/run/docker.sock':'/var/run/docker.sock':'rw'
  --restart unless-stopped 'ghcr.io/windmill-labs/windmill:main'

Expected behavior

Just do all migrations and launch

Screenshots

No response

Browser information

FIrefox

Application version

:latest

Additional Context

No response

[rubenfiszel]

Hi @martinjuhasz , are you a superuser of that database?
Can you try with connecting to a local postgres similar to in the docker-compose and then find out the difference with your database?

[martinjuhasz]

I'm not the superuser.
My typical approach is to create a user. create a database. and grant all rights to that user to that database.

CREATE DATABASE windmill;
CREATE USER windmill WITH ENCRYPTED PASSWORD 'xyz';
GRANT ALL PRIVILEGES ON DATABASE windmill to windmill;

granting superuser for initial setup worked. i revoked it afterwards.

ALTER USER windmill WITH SUPERUSER
ALTER USER windmill WITH NOSUPERUSER

Is there anything i can do to add additional privileges without granting superuser to be safe in the future? All privileges on the database doesn't seem to be enough.

[rubenfiszel]

Hi, we do have this section on our docs with better initial sql commands: https://www.windmill.dev/docs/advanced/self_host#use-an-external-database

https://raw.githubusercontent.com/windmill-labs/windmill/main/init-db-as-superuser.sql

CREATE ROLE windmill_user;

GRANT ALL
ON ALL TABLES IN SCHEMA public 
TO windmill_user;

GRANT ALL PRIVILEGES 
ON ALL SEQUENCES IN SCHEMA public 
TO windmill_user;

ALTER DEFAULT PRIVILEGES 
    IN SCHEMA public
    GRANT ALL ON TABLES TO windmill_user;

ALTER DEFAULT PRIVILEGES 
    IN SCHEMA public
    GRANT ALL ON SEQUENCES TO windmill_user;


CREATE ROLE windmill_admin WITH BYPASSRLS;
GRANT windmill_user TO windmill_admin;

[Twister42]

I'm asking that here to avoir polluting the issues tracker, but is "WITH BYPASSRLS" important ?
On some cloud providers we are not superusers and can't create roles with this option (scaleway for example)

[rubenfiszel]

It used to be critical but now it's just a performance optimization. It should work without BYPASSRLS because we grant true policy to windmill_admin everywhere relevant by it's not as tested as BYPASSRLS so you may face issues we can fix easily