Running windmill / support for roles without passwords

Support
1

Hi there,

I am looking to run Windmill against Neon (running on fly.io)

I hit a few issues that I was able to solve, such as being unable to use pooled connections due to using prepared statements, but I have now hit an issue that I am not sure I can work around…

In short, windmill uses two roles, an admin and user role, and uses SET LOCAL ROLE <role> to switch between those roles. To do this, it will create the roles automatically (if a superuser cred is used) or has SQL you can run to set these up.

In either case, when the app or I run the code for the roles, I get the following error {"error":"Neon does not support roles with empty passwords"}

If I modify the script such that it creates a role with a password and we grant my connecting role the ability to inherit a role, will I still be able to use SET LOCAL ROLE?

I may as well try it, but just also putting this here in case I am missing something.

Thanks in advance!

2

Okay, I was able to get this working!

Adding notes here for anyone else:

  1. create the windmill_admin role via the UI, you must create this role via the UI, because that is what grants it the correct permissions in the neon_superuser group
  2. create your database for windmill with windmill_admin as the owner
  3. connect to psql using the neon interactive thing, but using the right user and db, in my case: psql -h pg.neon.tech -d windmill -U windmill_admin
  4. edit the script provided by windmill (https://github.com/windmill-labs/windmill/blob/main/init-db-as-superuser.sql) to something like the following (NOTE! you must change the password) and run it in psql
CREATE ROLE windmill_user WITH PASSWORD '<yourpass>';

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;

GRANT windmill_user TO windmill_admin;

With this, your server and worker should start if you use windmill_admin as the user you use to connect to the database, and this seems to work even though windmill is using SET LOCAL ROLE as windmill_admin, even though it is already connected as the same user.

3 Likes