Postgresql multiple databases support

[vladimir.d]

Hi,

Any plans to make multipleDatabases option available for postgresql add-on in the same was as it's for mysql add-on?

Thanks!

[nebulon]

Hi vladimir, as of now you are the first one asking for that. I also think so far we had no app which required this setup. What is the app or use-case you have in mind?

[vladimir.d]

I'm building a custom app for Chirpstack as All-in-One application container.
It requires two postgresql databases: one for the application server and another for the network server (or maybe even more for other services). I've managed to create two databases for the app user manually and set appropriate access privileges, but not sure if it would be persistent after the cloudron host reboot.
I hoped multipleDatabases option was available for postgresql add-on but it's not.

[marcusquinn]

Related: https://forum.cloudron.io/topic/4258/chirpstack-for-lorawan?_=1619796077885

[girish]

Maybe a workaround if possible would be to package the network server and application server separately. I can look into adding the multipleDatabases flag in the meantime, should be straight forward.

[infogulch]

@girish Perhaps a specific implementation of this would allow the postgresql addon manifest to indicate the number of databases required (default: 1) to provide to the app. And then CLOUDRON_POSTGRESQL_DATABASE could be a comma-separated list of db names?

That seems to be the most flexible nearby strategy. That wouldn't solve an app that needs a variable number of databases (like a db per user or something), but that use-case seems pretty exotic imo. Anyways, just ideas.

[girish]

@infogulch We already have the flag for MySQL - https://docs.cloudron.io/custom-apps/addons/#mysql . So, it will just follow the same pattern. We will inject a CLUODRON_POSTGRESQL_DATABASE_PREFIX and the app can create databases with that prefix. The question is of course if postgres allows this sort of role management i.e if a user can be given access to a "prefix". In MySQL, you can do it with GRANT ALL PREVILIGES ON and a prefix. If it doesn't, we have to design this differently. Do you know postgresql? If so, suggestions welcome!

[girish]

Something like this maybe - https://stackoverflow.com/questions/22684255/grant-privileges-on-future-tables-in-postgresql

[infogulch]

@girish No, I don't have a lot of experience with postgresql. The strategy you describe aligns with my understanding of mysql's philosophy, but postgresql seems to require admins be more explicit. E.g. granting permissions to an arbitrary quoted LIKE pattern is documented in mysql, but postgresql's GRANT docs don't mention anything of the like and look like they require the specific object to exist (by nature of being a valid identifier) before granting permissions. Though I could be mistaken.