@girish Postgrest requires there to be something in the OIDC id_token that maps to the name of a role in the database.
Here are the side effects of only being able to use CLOUDRON_POSTGRESQL_USERNAME with postgrest:
All authenticated users would have the same permissions
postgrest's permissions are based on role permissions in the database
To have different different set's of permission requires have different roles available
It currently isn't possible to use the value of CLOUDRON_POSTGRESQL_USERNAME as the role when using Cloudron's OIDC as the id_token jwt presented to postgrest needs to have a claim in it that maps to the name of the role in the database that user's permissions map to
From looking at oidc.js I didn't see any code that would support adding custom claims that would be included in the id_token
Here is the specific spot in the codebase that I think makes the claims for the id_token jwt
Ultimately this means someone would have to run an external OIDC provider like keycloak where they have more control over the claims in the jwt to be then able to make a claim that contains the value of CLOUDRON_POSTGRESQL_USERNAME
It feels like the easiest way to address this would be to enhance the add function in the postgresql-addon nodejs app to support taking in an array of roles the app needs and then executing something like the following pseudo code
roles.foreach( role => function (role) {
queries.push(`CREATE ROLE ${role} NOSUPERUSER NOCREATEDB NOCREATEROLE NOLOGIN`)
queries.push(`GRANT ${role} TO ${username}`)
}
Ps, I left off NOINHERIT as I believe inheritance would be needed to support having generic roles that are given permissions and then a role that corresponds to a specific user that is identified by sid in the jwt granted permissions to one of the generic roles.
Then the roles array would be specified as an attribute of the postgresql addon key in the cloudron manifest:
"postgresql": {
"roles":["Role1CorrespondingtoUser1","Role2CorrespondingToUser2","GenericRole3","GenericRole4"]
}
If your open to pull requests and you think this would make sense I could try to implement this.