Permission denied to set role "windmill_admin" on Postgres without superuser
I'm running into issues on an self-hosted instance. I did read the self-host part of the docs regarding Postgres usage without superuser. I'm running Windmill (CE v1.441.2) on Kubernetes and Postgres 17.2 in a cluster provided by Crunchy Postgres Operator.
What I did:
- Created a database
- Granted
- Granted usage on
I also tried to
App logs when trying to activate "List all workspaces as superadmin":
Appreciate any help.
What I did:
- Created a database
windmillwindmill and a user windmillwindmill which owns the database and applied https://raw.githubusercontent.com/windmill-labs/windmill/main/init-db-as-superuser.sql as a superuser.- Granted
windmill_userwindmill_user and windmill_adminwindmill_admin to windmillwindmill role.- Granted usage on
publicpublic to windmill_userwindmill_user and windmill_adminwindmill_admin in the windmillwindmill database.windmill=# \du
List of roles
Role name | Attributes
-------------------+------------------------------------------------------------
_crunchypgbouncer |
_crunchyrepl | Replication
ccp_monitoring |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
windmill |
windmill_admin | Cannot login, Bypass RLS
windmill_user | Cannot loginwindmill=# \du
List of roles
Role name | Attributes
-------------------+------------------------------------------------------------
_crunchypgbouncer |
_crunchyrepl | Replication
ccp_monitoring |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
windmill |
windmill_admin | Cannot login, Bypass RLS
windmill_user | Cannot loginwindmill=# \z
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------------------+------+---------------------------------+-------------------+----------
public | pg_stat_statements | view | postgres=arwdDxtm/postgres +| |
| | | =r/postgres +| |
| | | windmill_user=arwdDxtm/postgres | |
public | pg_stat_statements_info | view | postgres=arwdDxtm/postgres +| |
| | | =r/postgres +| |
| | | windmill_user=arwdDxtm/postgres | |windmill=# \z
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------------------+------+---------------------------------+-------------------+----------
public | pg_stat_statements | view | postgres=arwdDxtm/postgres +| |
| | | =r/postgres +| |
| | | windmill_user=arwdDxtm/postgres | |
public | pg_stat_statements_info | view | postgres=arwdDxtm/postgres +| |
| | | =r/postgres +| |
| | | windmill_user=arwdDxtm/postgres | |windmill=# \drg
List of role grants
Role name | Member of | Options | Grantor
----------------+---------------------------+--------------+----------
ccp_monitoring | pg_execute_server_program | INHERIT, SET | postgres
ccp_monitoring | pg_monitor | INHERIT, SET | postgres
windmill | windmill_admin | INHERIT, SET | postgres
windmill | windmill_user | INHERIT, SET | postgres
windmill_admin | windmill_user | INHERIT, SET | postgreswindmill=# \drg
List of role grants
Role name | Member of | Options | Grantor
----------------+---------------------------+--------------+----------
ccp_monitoring | pg_execute_server_program | INHERIT, SET | postgres
ccp_monitoring | pg_monitor | INHERIT, SET | postgres
windmill | windmill_admin | INHERIT, SET | postgres
windmill | windmill_user | INHERIT, SET | postgres
windmill_admin | windmill_user | INHERIT, SET | postgreswindmill=# \dt
Did not find any relations.windmill=# \dt
Did not find any relations.I also tried to
GRANT ALL PRIVILEGES ON DATABASE windmill TO windmill_userGRANT ALL PRIVILEGES ON DATABASE windmill TO windmill_user using connections through pgBouncer and primary only (with restart of all components).App logs when trying to activate "List all workspaces as superadmin":
{"timestamp":"2025-01-03T09:14:08.474363Z","level":"ERROR","message":"Sql error: error returned from database: permission denied to set role \"windmill_admin\"","error":"SqlErr(Database(PgDatabaseError { severity: Error, code: \"42501\", message: \"permission denied to set role \\\"windmill_admin\\\"\", detail: None, hint: None, position: None, where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some(\"guc.c\"), line: Some(6936), routine: Some(\"call_string_check_hook\") }))","target":"windmill_common::error","span":{"email":"admin@windmill.dev","method":"GET","traceId":"32e0fc85-ad29-4c33-858a-fe5858e8da28","uri":"/api/workspaces/list_as_superadmin?per_page=1000","username":"admin@windmill.dev","name":"request"}} {"timestamp":"2025-01-03T09:14:08.474574Z","level":"ERROR","message":"response","latency":"24","status":400,"target":"windmill_api::tracing_init","span":{"email":"admin@windmill.dev","method":"GET","traceId":"32e0fc85-ad29-4c33-858a-fe5858e8da28","uri":"/api/workspaces/list_as_superadmin?per_page=1000","username":"admin@windmill.dev","name":"request"}}{"timestamp":"2025-01-03T09:14:08.474363Z","level":"ERROR","message":"Sql error: error returned from database: permission denied to set role \"windmill_admin\"","error":"SqlErr(Database(PgDatabaseError { severity: Error, code: \"42501\", message: \"permission denied to set role \\\"windmill_admin\\\"\", detail: None, hint: None, position: None, where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some(\"guc.c\"), line: Some(6936), routine: Some(\"call_string_check_hook\") }))","target":"windmill_common::error","span":{"email":"admin@windmill.dev","method":"GET","traceId":"32e0fc85-ad29-4c33-858a-fe5858e8da28","uri":"/api/workspaces/list_as_superadmin?per_page=1000","username":"admin@windmill.dev","name":"request"}} {"timestamp":"2025-01-03T09:14:08.474574Z","level":"ERROR","message":"response","latency":"24","status":400,"target":"windmill_api::tracing_init","span":{"email":"admin@windmill.dev","method":"GET","traceId":"32e0fc85-ad29-4c33-858a-fe5858e8da28","uri":"/api/workspaces/list_as_superadmin?per_page=1000","username":"admin@windmill.dev","name":"request"}}Appreciate any help.
