Postgresql multiple databases support
-
I'm building a custom app for Chirpstack as
All-in-Oneapplication 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 hopedmultipleDatabasesoption was available for postgresql add-on but it's not. -
-
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.
@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_DATABASEcould 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 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_DATABASEcould 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.
@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_PREFIXand 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 withGRANT ALL PREVILIGES ONand a prefix. If it doesn't, we have to design this differently. Do you know postgresql? If so, suggestions welcome! -
Something like this maybe - https://stackoverflow.com/questions/22684255/grant-privileges-on-future-tables-in-postgresql
@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.
-
@Lanhild we haven't worked on it. I think last time around atleast I found this was quite hard to do in postgres. I haven't found a way to give access to future databases with a "prefix" in postgres.
Maybe an alternate for postgres is to implement something with a "count" instead . Would that work for your usecase? What's the use case to have multiple databases?
(Not saying we are planning to work on this
Just putting out some ideas). -
@Lanhild we haven't worked on it. I think last time around atleast I found this was quite hard to do in postgres. I haven't found a way to give access to future databases with a "prefix" in postgres.
Maybe an alternate for postgres is to implement something with a "count" instead . Would that work for your usecase? What's the use case to have multiple databases?
(Not saying we are planning to work on this
Just putting out some ideas).@girish said in Postgresql multiple databases support:
Maybe an alternate for postgres is to implement something with a "count" instead . Would that work for your usecase?
I'm not sure to understand?
What's the use case to have multiple databases?
I have some applications that make use of multiple databases that act as environments (e.g.: prod, test, etc.) So instead of having multiple copies of the code running, you only have multiple databases.
Also, multiple databases could benefit to the use of the addon as a "standalone" app - that could act as a simple PostgreSQL cluster.
-
@girish said in Postgresql multiple databases support:
Maybe an alternate for postgres is to implement something with a "count" instead . Would that work for your usecase?
I'm not sure to understand?
What's the use case to have multiple databases?
I have some applications that make use of multiple databases that act as environments (e.g.: prod, test, etc.) So instead of having multiple copies of the code running, you only have multiple databases.
Also, multiple databases could benefit to the use of the addon as a "standalone" app - that could act as a simple PostgreSQL cluster.
@Lanhild said in Postgresql multiple databases support:
I'm not sure to understand?
What I meant is maybe the manifest can use
"postgreSQL": { "prefixCount": 10 }and it creates 10 databases or something like that.Also, multiple databases could benefit to the use of the addon as a "standalone" app - that could act as a simple PostgreSQL cluster.
I think it will be nice to create PostgreSQL as an app and with a small frontend to add/remove databases and maybe manage roles. Maybe this app can not use existing addon. Of course, this app has to be developed!
