> On 08/11/2022 05:42 CET Bo Victor Thomsen <bo.victor.thomsen@xxxxxxxxx> wrote: > > I am both (PostgreSQL) database and QGIS developer and occasionally a DB > admin. From that point of view: > > There is a ton of use cases, where having a private schema for each QGIS > user makes sense. For example, GIS analysis will often create datasets > with limited lifetime, i.e being part of a process. These datasets has > to be stored somewhere. If it's not in a private schema, you'll have > them splattered out over your entire filesystems in different file-based > GIS formats, including some on the users local hard drive. > > Further, having even temporary data stored in your database gives the > user opportunity to off-load some heavy-duty GIS analysis to the > database using the PostGIS extension. Think of your database as a "file > system", a "scratch pad". > > This kind of work is fundamentally different from a traditional 3-tier > setup where your normal user probably is not even aware of the > underlying data structure. And structure changes can be "hidden" in the > business logic layer. > > > All schema definitions tables/views/etc are versioned using git in a structured > > repository of flat sql files. It works well. Table defs etc get code reviewed > > and deployed by more DB-minded engineers, > > This is a good idea. When some kind of analysis workflow is worked out / > stabilised , the resulting tables/views etc. can be quality-checked and > transferred to the "proper" database. > > You could establish a separate database (including -server ?) and let > QGIS users access the central database using Foreign Data Wrappers. Thanks a lot. I don't have my team mate's requirement yet so I can't say if private schemas are an option. If those tables are used as layers, I assume they are meant to be shared with other users. I think in that case it is also better to have them manage privileges manually instead of automating that part as I've outlined in my original post. It still gives me headaches when I think about users who may create objects with dependencies on the versioned part of the schema. Those dependencies may prevent migrations that alter dependent objects. But this can indeed be isolated via FDW with some overhead to keep the schemas in sync. -- Erik
