How to create additional postgresql role's needed for an application?

[ChristopherMag]

I am working on packaging postgrest and this application makes use of custom created roles in postgres to implement its functionality.

I have the app up and running but when I try to create a role from psql I get ERROR: permission denied to create role.

I tried looking through the cloudron gitlab to see if I could find any examples of another app that already does this but I didn't see any.

It seems like this nodejs app is what cloudron uses to interact with the postgresql instance it offers as a service when provisioning new databases for apps.

This function specifically seems to be used when provisioning a new database for an app and it creates the role of the postgresql user that is created for the app being setup but doesn't seem to provide options for setting up additional roles that the application might need.

I tried to search for create role across all the code in the cloudron gitlab and it doesn't appear to support searching for things in code unless you filter down to a specific project first so if there are other apps already that do this I am not sure how to find them using gitlab search.

I also tried to use google search with site:git.cloudron.io create role but it looks like google is not indexing the content of that gitlab site which seems to be what is desired as the robots.txt has disallow: /.

Is there a method for getting whatever additional roles are needed setup during the application install so that permissions can stay the same but any custom roles that are needed can be setup by cloudron for the app to function?

[ChristopherMag]

As a potential work around for this you can find the postgresql addon's root password by running the following on the cloudron server

sudo docker exec postgresql printenv CLOUDRON_POSTGRESQL_ROOT_PASSWORD

I am assuming that using username root and this password I should be able to create the additional roles needed in the database for postgrest.

[girish]

@ChristopherMag postgres roles are at the installation/cluster level , so this permission is not allowed for apps .

When Cloudron provision postgresql for an app, it creates two roles - one with the same name as the database (CLOUDRON_POSTGRESQL_DATABASE) and now with same name as the username (CLOUDRON_POSTGRESQL_USERNAME). Maybe, you can use one of those already?

[ChristopherMag]

@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.

[girish]

@ChristopherMag great for exploring this further! Since roles are database global, installing/clone etc will cause conflicts. So, we have to have some elaborate scheme to accommodate this. This is quite complicated right now because only this specific app needs this. But TBF, if an app needs custom roles etc, it requires complete control of the database and Cloudron is probably not best suited for this at the moment.