CuBiC•3w ago

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 windmill and a user windmill which owns the database and applied https://raw.githubusercontent.com/windmill-labs/windmill/main/init-db-as-superuser.sql as a superuser. - Granted windmill_user and windmill_admin to windmill role. - Granted usage on public to windmill_user and windmill_admin in the windmill 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 login

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 login

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=# \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 | 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 | postgres

windmill=# \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_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.

10 Replies

rubenf•3w ago

The user you're using for windmill doesn't have right to set its role to windmill_admin

CuBiCOP•3w ago

According the the \drg from above is seems it does:

windmill=# \drg
                         List of role grants
   Role name    |         Member of         |   Options    | Grantor
----------------+---------------------------+--------------+----------
...
 windmill       | windmill_admin            | INHERIT, SET | postgres
 ...

windmill=# \drg
                         List of role grants
   Role name    |         Member of         |   Options    | Grantor
----------------+---------------------------+--------------+----------
...
 windmill       | windmill_admin            | INHERIT, SET | postgres
 ...

rubenf•3w ago

You should try setting the role in a session to test

CuBiCOP•3w ago

I did and it seems fine using psql:

bash-4.4$ psql -U windmill -h localhost
Password for user windmill:
psql (17.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

windmill=> set role windmill_admin;
SET
windmill=>

bash-4.4$ psql -U windmill -h localhost
Password for user windmill:
psql (17.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

windmill=> set role windmill_admin;
SET
windmill=>

rubenf•3w ago

Then the only possibility is thats not the user you've configured in the database url Because set role is exactly what it does

CuBiCOP•3w ago

Yes, that's the strange part of that, I can verify in every container that the URL is correct:

I have no name!@windmill-app-66d6f8944d-dmfq7:/usr/src/app$ env | grep -i database
DATABASE_URL=postgresql://shlink:[snip]@postgres-pgbouncer.database.svc:5432/shlink?sslmode=disable

I have no name!@windmill-app-66d6f8944d-dmfq7:/usr/src/app$ env | grep -i database
DATABASE_URL=postgresql://shlink:[snip]@postgres-pgbouncer.database.svc:5432/shlink?sslmode=disable

or without pgBouncer

I have no name!@windmill-app-66d6f8944d-dmfq7:/usr/src/app$ env | grep -i database
DATABASE_URL=postgresql://shlink:[snip]@postgres-primary.database.svc:5432/shlink?sslmode=disable

I have no name!@windmill-app-66d6f8944d-dmfq7:/usr/src/app$ env | grep -i database
DATABASE_URL=postgresql://shlink:[snip]@postgres-primary.database.svc:5432/shlink?sslmode=disable

rubenf•3w ago

Don't use pg bouncer

CuBiCOP•3w ago

I did test both cases, I'm aware that it will block superuser

rubenf•3w ago

I would recommend using a simpler setup and then figuring out when the issue starts appearing but we never see such issue in prod for any of our customers and what windmill does is literally just set role with the v database url provided On EE you can do a live call with one member of our team to help troubleshoot

CuBiCOP•3w ago

Unfortunately it's a homelab cluster, so there's no EE budget 😉 But thank you for having a look. I'll see what I can do to find out what's going on. Found the culprit. I guess it's a case of "it's been a long day for layer 8" 😁 if you look at the URL I posted above closely, you'll see a completely different username and database. I copy-pasted the reference for this secret from another service, so a wrong service URL has been pulled from the secret store.

Permission denied to set role "windmill_admin" on Postgres without superuser

Did you find this page helpful?